通过接口实现适用于SqlServer和MySql的SqlHelper

SqlHelper
 1 using System;
 2 using System.Collections.Generic;
 3 using System.Configuration;
 4 using System.Data;
 5 using System.Data.SqlClient;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Threading.Tasks;
 9 
10 namespace SqlHelper
11 {
12     class DBHelper
13     {
14         private static readonly string connStr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
15         public static IDbConnection CreateConnection()
16         {
17             SqlConnection conn = new SqlConnection(connStr);
18             conn.Open();
19             return conn;
20         }
21 
22         public static int ExecuteNonQuery(IDbConnection conn, string sql, Dictionary<string, object> parameters)
23         {
24             using (IDbCommand cmd = conn.CreateCommand())
25             {
26                 cmd.CommandText = sql;
27                 foreach (KeyValuePair<string, object> kv in parameters)
28                 {
29                     IDataParameter parameter = cmd.CreateParameter();
30                     parameter.ParameterName = kv.Key;
31                     parameter.Value = kv.Value;
32                     cmd.Parameters.Add(parameter);
33                 }
34                 return cmd.ExecuteNonQuery();
35             }
36         }
37 
38         public static int ExecuteNonQuery(string sql, Dictionary<string, object> parameters)
39         {
40             using (IDbConnection conn = CreateConnection())
41             {
42                 return ExecuteNonQuery(conn, sql, parameters);
43             }
44         }
45 
46         public static object ExecuteScalar(IDbConnection conn, string sql, Dictionary<string, object> parameters)
47         {
48             using (IDbCommand cmd = conn.CreateCommand())
49             {
50                 cmd.CommandText = sql;
51                 foreach (KeyValuePair<string, object> kv in parameters)
52                 {
53                     IDataParameter parameter = cmd.CreateParameter();
54                     parameter.ParameterName = kv.Key;
55                     parameter.Value = kv.Value;
56                     cmd.Parameters.Add(parameter);
57                 }
58                 return cmd.ExecuteScalar();
59             }
60         }
61 
62         public static object ExecuteScalar(string sql, Dictionary<string, object> parameters)
63         {
64             using (IDbConnection conn = CreateConnection())
65             {
66                 return ExecuteScalar(conn, sql, parameters);
67             }
68         }
69 
70         public static DataTable ExecuteQuery(IDbConnection conn, string sql, Dictionary<string, object> parameters)
71         {
72             DataTable table = new DataTable();
73             using (IDbCommand cmd = conn.CreateCommand())
74             {
75                 cmd.CommandText = sql;
76                 foreach (KeyValuePair<string, object> kv in parameters)
77                 {
78                     IDataParameter parameter = cmd.CreateParameter();
79                     parameter.ParameterName = kv.Key;
80                     parameter.Value = kv.Value;
81                     cmd.Parameters.Add(parameter);
82                 } 
83                 using (IDataReader reader = cmd.ExecuteReader())
84                 {
85                     table.Load(reader);
86                 }
87                 return table;
88             }
89         }
90 
91         public static DataTable ExecuteQuery(string sql, Dictionary<string, object> parameters)
92         {
93             using (IDbConnection conn = CreateConnection())
94             {
95                 return ExecuteQuery(conn, sql, parameters);
96             }
97         }
98     }
99 }