ADO.NET 操作MySQL
1 using MySql.Data.MySqlClient; 2 3 using System; 4 using System.Collections.Generic; 5 using System.Data; 6 using System.Linq; 7 using System.Text; 8 using System.Threading.Tasks; 9 10 namespace DAL 11 { 12 /// <summary> 13 /// 数据库连接,可通过begin开启事务 14 /// <example>参数标识符采用@符号,in语句如下 15 /// <code> 16 /// sql="select * from dual where id in (@list)";</code> 17 /// <code>Dictionary<string,object> dic={{"list",new int[]{1,2,3}}};</code> 18 /// </example> 19 /// </summary> 20 public class DbConnect : IDisposable, IAsyncDisposable 21 { 22 /// <summary> 23 /// 连接实例 24 /// </summary> 25 public MySqlConnection Connection; 26 27 /// <summary> 28 /// IEnumerable参数转换为字典 29 /// </summary> 30 /// <param name="paramName">参数名</param> 31 /// <param name="param">参数值</param> 32 /// <returns></returns> 33 public static Dictionary<string, object> paramsToDictionary(IEnumerable<string> paramName, IEnumerable<object> param) 34 { 35 if (paramName.Count() != param.Count()) 36 { 37 throw new ArgumentException("参数名称与参数列表数量不一致"); 38 } 39 Dictionary<string, object> paramDict = new Dictionary<string, object>(); 40 var paramNameEnumerator = paramName.GetEnumerator(); 41 var paramEnumerator = param.GetEnumerator(); 42 while (paramNameEnumerator.MoveNext()) 43 { 44 paramEnumerator.MoveNext(); 45 paramDict[paramNameEnumerator.Current] = paramEnumerator.Current; 46 } 47 return paramDict; 48 } 49 50 /// <summary> 51 /// 按照SQL和参数字典生成Command对象 52 /// </summary> 53 /// <param name="sql"></param> 54 /// <param name="paramDict"></param> 55 /// <returns></returns> 56 public MySqlCommand GenerateCmd(string sql, Dictionary<string, object> paramDict) 57 { 58 using MySqlCommand cmd = new MySqlCommand(sql, Connection); 59 foreach (var item in paramDict) 60 { 61 if (item.Value is not string && item.Value is System.Collections.IEnumerable InList) 62 { 63 var enumerator = InList.GetEnumerator(); 64 StringBuilder paramNameBuilder = new StringBuilder(); 65 int count = 0; 66 while (enumerator.MoveNext()) 67 { 68 string paramName = $"{item.Key}_{count:0000}"; 69 paramNameBuilder.Append(" @"); 70 paramNameBuilder.Append(paramName); 71 paramNameBuilder.Append(","); 72 cmd.Parameters.AddWithValue(paramName, enumerator.Current); 73 count++; 74 } 75 if (count == 0) 76 { 77 cmd.Parameters.AddWithValue(item.Key, item.Value); 78 } 79 else 80 { 81 cmd.CommandText = cmd.CommandText 82 //.Replace("(?" + item.Key + ")", "(" + paramNameBuilder.ToString().TrimEnd(',') + ")") 83 .Replace("(@" + item.Key + ")", "(" + paramNameBuilder.ToString().TrimEnd(',') + ")"); 84 } 85 } 86 else 87 { 88 cmd.Parameters.AddWithValue(item.Key, item.Value); 89 } 90 } 91 return cmd; 92 } 93 94 #region 同步调用相关方法 95 /// <summary> 96 /// 按照指定的连接字符串创建连接 97 /// </summary> 98 /// <param name="connectionString">连接字符串</param> 99 public DbConnect(string connectionString)100 {101 Connection = new MySqlConnection(connectionString);102 Connection.Open();103 }104 105 /// <inheritdoc cref="ExecRowCount(string, Dictionary{string, object})"/>106 /// <param name="sql">SQL语句</param>107 public long ExecRowCount(string sql) => ExecRowCount(sql, new Dictionary<string, object>());108 109 /// <inheritdoc cref="ExecRowCount(string, Dictionary{string, object})"/>110 /// <param name="sql">SQL语句</param>111 /// <param name="paramName">参数名称</param>112 /// <param name="param">参数值</param>113 public long ExecRowCount(string sql, IEnumerable<string> paramName, IEnumerable<object> param) => ExecRowCount(sql, paramsToDictionary(paramName, param));114 115 /// <summary>116 /// 查询行数117 /// </summary>118 /// <param name="sql">SQL语句</param>119 /// <param name="paramDict">参数字典</param>120 /// <returns>行数</returns>121 public long ExecRowCount(string sql, Dictionary<string, object> paramDict)122 {123 try124 {125 using MySqlCommand cmd = GenerateCmd($@"select count(*) from ({sql}) countTable", paramDict);126 return (long)cmd.ExecuteScalar();127 }128 catch (Exception e)129 {130 Console.WriteLine("SQL错误" + sql + e.Message);131 throw;132 }133 }134 135 /// <inheritdoc cref="ExecSQL(string, Dictionary{string, object})"/>136 /// <param name="sql">SQL语句</param>137 public DataTable ExecSQL(string sql) => ExecSQL(sql, new Dictionary<string, object>());138 139 /// <inheritdoc cref="ExecSQL(string, Dictionary{string, object})"/>140 /// <param name="sql">SQL语句</param>141 /// <param name="paramName">参数名称</param>142 /// <param name="param">参数值</param>143 public DataTable ExecSQL(string sql, IEnumerable<string> paramName, IEnumerable<object> param) => ExecSQL(sql, paramsToDictionary(paramName, param));144 145 /// <summary>146 /// 查询数据147 /// </summary>148 /// <param name="sql">SQL语句</param>149 /// <param name="paramDict">参数字典</param>150 /// <returns>结果表</returns>151 public DataTable ExecSQL(string sql, Dictionary<string, object> paramDict)152 {153 try154 {155 using MySqlCommand cmd = GenerateCmd(sql, paramDict);156 cmd.CommandTimeout = 60;157 DataSet dataSet = new DataSet();158 dataSet.EnforceConstraints = false;159 dataSet.Tables.Add(new DataTable());160 dataSet.Tables[0].Load(cmd.ExecuteReader());161 return dataSet.Tables[0];162 }163 catch (Exception e)164 {165 Console.WriteLine("SQL错误" + sql + e.Message);166 throw;167 }168 }169 170 /// <inheritdoc cref="ExecCmd(string, Dictionary{string, object})"/>171 public int ExecCmd(string sql) => ExecCmd(sql, new Dictionary<string, object>());172 173 /// <inheritdoc cref="ExecCmd(string, Dictionary{string, object})"/>174 /// <param name="sql">SQL语句</param>175 /// <param name="paramName">参数名称</param>176 /// <param name="param">参数值</param>177 public int ExecCmd(string sql, IEnumerable<string> paramName, IEnumerable<object> param) => ExecCmd(sql, paramsToDictionary(paramName, param));178 179 /// <summary>180 /// 执行命令181 /// </summary>182 /// <param name="sql">SQL语句</param>183 /// <param name="paramDict">参数字典</param>184 /// <returns>修改行数</returns>185 public int ExecCmd(string sql, Dictionary<string, object> paramDict)186 {187 try188 {189 using MySqlCommand cmd = GenerateCmd(sql, paramDict);190 return cmd.ExecuteNonQuery();191 }192 catch (Exception e)193 {194 Console.WriteLine("SQL错误" + sql + e.Message);195 throw;196 }197 }198 199 /// <summary>200 /// 释放对象201 /// </summary>202 public void Dispose() => Connection.Dispose();203
没有评论:
发表评论