通过接口实现适用于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 }
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步