C# WPF 连接数据库Sqlhelper类_.NET_编程开发_程序员俱乐部

从视频上学习到的WPF连接数据库的方法

需要配置一个 configuration 文件，包含 SQL instance 的相关信息

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Configuration;
 6 using System.Data.SqlClient;
 7 using System.Data;
 8 
 9 namespace ADO
10 {
11     class SqlHelper
12     {
13         private static string connStr = ConfigurationManager.ConnectionStrings["dbConnStr"].ConnectionString;
14 
15         public static int ExecuteNonQuery(string sql)
16         {
17             using (SqlConnection conn = new SqlConnection(connStr))
18             {
19                 conn.Open();
20                 using (SqlCommand cmd = conn.CreateCommand())
21                 {
22                     cmd.CommandText = sql;
23                     return cmd.ExecuteNonQuery();//返回的是值是insert,delete,update影响了多少行
24                 }
25             }
26         }
27 
28         public static object ExecuteScalar(string sql)
29         {
30             using (SqlConnection conn = new SqlConnection(connStr))
31             {
32                 conn.Open();
33                 using (SqlCommand cmd = conn.CreateCommand())
34                 {
35                     cmd.CommandText = sql;
36                     return cmd.ExecuteScalar();
37                 }
38 
39             }
40         }
41         //只用来执行查询结果比较小的sql
42         public static DataSet ExecuteDataSet(string sql)
43         {
44             using (SqlConnection conn = new SqlConnection(connStr))
45             {
46                 conn.Open();
47                 using (SqlCommand cmd = conn.CreateCommand())
48                 {
49                     cmd.CommandText = sql;
50                     SqlDataAdapter adapter = new SqlDataAdapter(cmd);
51                     DataSet dataset = new DataSet();
52                     adapter.Fill(dataset);
53                     return dataset;
54                 }
55 
56             }       
57         }
58         //防止sql注入漏洞攻击
59         public static DataTable ExecuteDataTable(string sql,SqlParameter [] parameters)
60         {
61             using (SqlConnection conn = new SqlConnection(connStr))
62             {
63                 conn.Open();
64                 using (SqlCommand cmd = conn.CreateCommand())
65                 {
66                     cmd.CommandText = sql;
67                     cmd.Parameters.AddRange(parameters);
68 
69                     SqlDataAdapter adapter = new SqlDataAdapter(cmd);
70                     DataSet dataset = new DataSet();
71                     adapter.Fill(dataset);
72                     return dataset.Tables[0];
73                 }
74 
75             }
76         }
77     }
78 }

数据库配置文件如下

使用SQL Server 2K8 R2 作为数据库

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="dbConnStr" connectionString="Data Source=.\mysql; Initial Catalog=test;User ID=sa;Password=123456"/>
    
  </connectionStrings>
</configuration>

在WPF中应用Sqlhelper ，可以方便连接数据库操作