C# 对mysql 操作的封装
最近写了一个类，实现了对mysql数据库的基本操作的封装。有需要的可以拿去。先上效果，另外有问题可以自己修改或者联系。
数据库结构：
一：效果
1.1原数据表
1.2：连接数据库
MySQLBase sqlbase = new MySQLBase("test", "localhost", "root", "Zhong123456789");
1.3：增加操作
var restult = sqlbase.InsertDTTableOneRow("userinfo", new string[] { "username", "userage" }, new MySqlDbType[] {MySqlDbType.VarChar, MySqlDbType.Int16}, new string[] { "dfd2323fdf", "19" });
1.4:删除操作
restult= sqlbase.DeleteDTTableOneRow("userinfo", new string[] { "userage","username" }, new MySqlDbType[] { MySqlDbType.Int16 , MySqlDbType.VarChar }, new string[] { "99" ,"zwf"},"or");
1.5修改操作
restult= sqlbase.UpdateDTTableOneRow("userinfo", "userage", MySqlDbType.Int16,"99", new string[] { "userage", "username" }, new MySqlDbType[] { MySqlDbType.Int16, MySqlDbType.VarChar }, new string[] { "19", "zwf1" }, "or");
1.6 执行存储过程，无参数。
restult= sqlbase.RunStoredProcedure("test_userinfo_run");
存储过程如下：workbecnch 编写
CREATE DEFINER=`root`@`localhost` PROCEDURE `test_userinfo_run`()
BEGIN
select userage from userinfo where iduserinfo=1;
insert into userinfo (username,userage) values("sssss","99");
END
二：源码，封装到类里面了。log 部分请自行注销。
1 public class MySQLBase 2 { 3 //连接数据库字符串 4 string Connstr; 5 6 7 //数据库连接字符串 8 //数据库连接字符串 9 // public static string Conn = "Database='wp';Data Source='localhost';User Id='root';Password='root';charset='utf8';pooling=true"; 10 // public static string Conn = "Database='数据库名';Data Source='数据库服务器地址';User Id='数据库用户名';Password='密码';charset='utf8';pooling=true"; 11 /// <summary> 12 /// Conn = "Database='数据库名';Data Source='数据库服务器地址';User Id='数据库用户名';Password='密码';charset='utf8';pooling=true"; 13 /// </summary> 14 /// <param name="connectionstr"></param> 15 public MySQLBase(string connectionstr) 16 { 17 Connstr = connectionstr; 18 } 19 public MySQLBase(string dtname, string dtsource, string userid, string userpassword) 20 { 21 Connstr = string.Format("Database='{0}';Data Source={1};User Id={2};Password={3};charset='utf8';pooling=true", dtname, dtsource, userid, userpassword); 22 } 23 24 25 // 用于缓存参数的HASH表 26 // private Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); 27 28 /// <summary> 29 /// 用现有的数据库连接执行一个sql命令（不返回数据集）,适用于数据库的基本操作 30 /// </summary> 31 /// <param name="connection">一个现有的数据库连接</param> 32 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 33 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 34 /// <param name="commandParameters">执行命令所用参数的集合</param> 35 /// <returns>执行命令所影响的行数</returns> 36 public int ExecuteNonQuery(CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 37 { 38 try 39 { 40 using (MySqlConnection con = new MySqlConnection(Connstr)) 41 { 42 MySqlCommand cmd = new MySqlCommand(); 43 PrepareCommand(cmd, con, null, cmdType, cmdText, commandParameters); 44 int val = cmd.ExecuteNonQuery(); 45 cmd.Parameters.Clear(); 46 return val; 47 } 48 49 } 50 catch(Exception ex) 51 { 52 MyBasicFun.LogWarn("ExecuteNonQuery Abnormal!" + ex.Message); 53 return -1; 54 } 55 56 } 57 58 59 60 /// <summary> 61 ///使用现有的SQL事务执行一个sql命令（不返回数据集），适用于数据库的基本操作 62 /// </summary> 63 /// <remarks> 64 ///举例: 65 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); 66 /// </remarks> 67 /// <param name="trans">一个现有的事务</param> 68 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 69 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 70 /// <param name="commandParameters">执行命令所用参数的集合</param> 71 /// <returns>执行命令所影响的行数</returns> 72 public int ExecuteNonQuery(MySqlTransaction trans, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 73 { 74 try 75 { 76 MySqlCommand cmd = new MySqlCommand(); 77 PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters); 78 int val = cmd.ExecuteNonQuery(); 79 cmd.Parameters.Clear(); 80 return val; 81 } 82 catch(Exception ex) 83 { 84 MyBasicFun.LogWarn("ExecuteNonQuery(MySqlTransaction trans," + 85 " CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) Abnormal!" + ex.Message); 86 return -1; 87 } 88 89 } 90 91 /// <summary> 92 /// 用执行的数据库连接执行一个返回数据集的sql命令，适用于数据库的基本操作 93 /// </summary> 94 /// <remarks> 95 /// 举例: 96 /// MySqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); 97 /// </remarks> 98 /// <param name="connectionString">一个有效的连接字符串</param> 99 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 100 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 101 /// <param name="commandParameters">执行命令所用参数的集合</param> 102 /// <returns>包含结果的读取器</returns> 103 public MySqlDataReader ExecuteReader( CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 104 { 105 try 106 { 107 //创建一个MySqlConnection对象 108 using (MySqlConnection conn = new MySqlConnection(Connstr)) 109 { 110 //创建一个MySqlCommand对象 111 MySqlCommand cmd = new MySqlCommand(); 112 //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数 113 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 114 //调用 MySqlCommand 的 ExecuteReader 方法 115 MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 116 //清除参数 117 cmd.Parameters.Clear(); 118 return reader; 119 120 121 } 122 } 123 catch(Exception ex) 124 { 125 MyBasicFun.LogWarn("ExecuteReader Abnormal!" + ex.Message); 126 return null; 127 } 128 129 130 } 131 /// <summary> 132 /// 返回DataSet 133 /// </summary> 134 /// <param name="connectionString">一个有效的连接字符串</param> 135 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 136 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 137 /// <param name="commandParameters">执行命令所用参数的集合</param> 138 /// <returns></returns> 139 public DataSet GetDataSet( CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 140 { 141 try 142 { 143 //创建一个MySqlConnection对象 144 using (MySqlConnection conn = new MySqlConnection(Connstr)) 145 { 146 MySqlCommand cmd = new MySqlCommand(); 147 //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数 148 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 149 //调用 MySqlCommand 的 ExecuteReader 方法 150 MySqlDataAdapter adapter = new MySqlDataAdapter(); 151 adapter.SelectCommand = cmd; 152 DataSet ds = new DataSet(); 153 154 adapter.Fill(ds); 155 156 //清除参数 157 cmd.Parameters.Clear(); 158 return ds; 159 160 } 161 } 162 catch(Exception ex) 163 { 164 165 MyBasicFun.LogWarn("GetDataSet Abnormal!" + ex.Message); 166 return null; 167 } 168 169 170 171 } 172 173 /// <summary> 174 /// 输出多一个dataadapter,用了返回更新数据库 175 /// </summary> 176 /// <param name="connectionString"></param> 177 /// <param name="cmdType"></param> 178 /// <param name="cmdText"></param> 179 /// <param name="commandParameters"></param> 180 /// <param name="adapter"></param> 181 /// <returns></returns> 182 public DataSet GetDataSet(CommandType cmdType, string cmdText, out MySqlDataAdapter adapter, params MySqlParameter[] commandParameters) 183 { 184 adapter = new MySqlDataAdapter(); 185 try 186 { 187 188 //创建一个MySqlConnection对象 189 using (MySqlConnection conn = new MySqlConnection(Connstr)) 190 { 191 //创建一个MySqlCommand对象 192 MySqlCommand cmd = new MySqlCommand(); 193 //调用 PrepareCommand 方法，对 MySqlCommand 对象设置参数 194 PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters); 195 //调用 MySqlCommand 的 ExecuteReader 方法 196 197 adapter.SelectCommand = cmd; 198 DataSet ds = new DataSet(); 199 200 adapter.Fill(ds); 201 //清除参数 202 cmd.Parameters.Clear(); 203 conn.Close(); 204 return ds; 205 206 } 207 } 208 catch(Exception ex) 209 { 210 MyBasicFun.LogWarn("GetDataSet Abnormal!" + ex.Message); 211 return null; 212 213 } 214 215 } 216 217 /// <summary> 218 /// 通过dataset更新数据库 219 /// </summary> 220 /// <param name="adapter"></param> 221 /// <param name="dataSet"></param> 222 public void UpdateDB(MySqlDataAdapter adapter, DataSet dataSet) 223 { 224 225 try 226 { 227 // MySqlCommandBuilder thisBuilder = new MySqlCommandBuilder(adapter); 228 adapter.Update(dataSet); 229 230 } 231 catch (Exception ex) 232 { 233 MyBasicFun.LogWarn(" UpdateDB(MySqlDataAdapter adapter, DataSet dataSet) Abnormal!" + ex.Message); 234 235 } 236 237 } 238 239 /// <summary> 240 /// 用指定的数据库连接字符串执行一个命令并返回一个数据集的第一列 241 /// </summary> 242 /// <remarks> 243 ///例如: 244 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter("@prodid", 24)); 245 /// </remarks> 246 ///<param name="connectionString">一个有效的连接字符串</param> 247 /// <param name="cmdType">命令类型(存储过程, 文本, 等等)</param> 248 /// <param name="cmdText">存储过程名称或者sql命令语句</param> 249 /// <param name="commandParameters">执行命令所用参数的集合</param> 250 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> 251 public object ExecuteScalar( CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) 252 { 253 try 254 { 255 using (MySqlConnection connection = new MySqlConnection(Connstr)) 256 { 257 MySqlCommand cmd = new MySqlCommand(); 258 PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters); 259 object val = cmd.ExecuteScalar(); 260 cmd.Parameters.Clear(); 261 return val; 262 } 263 } 264 catch(Exception ex) 265 { 266 MyBasicFun.LogWarn(" ExecuteScalar( CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters) Abnormal!" + ex.Message); 267 return null; 268 } 269 270 271 272 } 273 274 275 ///// <summary> 276 ///// 将参数集合添加到缓存 277 ///// </summary> 278 ///// <param name="cacheKey">添加到缓存的变量</param> 279 ///// <param name="commandParameters">一个将要添加到缓存的sql参数集合</param> 280 //public void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters) 281 //{ 282 // parmCache[cacheKey] = commandParameters; 283 //} 284 285 ///// <summary> 286 ///// 找回缓存参数集合 287 ///// </summary> 288 ///// <param name="cacheKey">用于找回参数的关键字</param> 289 ///// <returns>缓存的参数集合</returns> 290 //public MySqlParameter[] GetCachedParameters(string cacheKey) 291 //{ 292 // MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey]; 293 294 // if (cachedParms == null) 295 // return null; 296 297 // MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length]; 298 299 // for (int i = 0, j = cachedParms.Length; i < j; i++) 300 // clonedParms[i] = (MySqlParameter)((ICloneable)cachedParms[i]).Clone(); 301 302 // return clonedParms; 303 //} 304 305 /// <summary> 306 /// 准备执行一个命令 307 /// </summary> 308 /// <param name="cmd">sql命令</param> 309 /// <param name="conn">OleDb连接</param> 310 /// <param name="trans">OleDb事务</param> 311 /// <param name="cmdType">命令类型例如 存储过程或者文本</param> 312 /// <param name="cmdText">命令文本,例如:Select * from Products</param> 313 /// <param name="cmdParms">执行命令的参数</param> 314 private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] cmdParms) 315 { 316 317 if (conn.State != ConnectionState.Open) 318 conn.Open(); 319 320 cmd.Connection = conn; 321 cmd.CommandText = cmdText; 322 323 if (trans != null) 324 cmd.Transaction = trans; 325 326 cmd.CommandType = cmdType; 327 328 if (cmdParms != null) 329 { 330 foreach (MySqlParameter parm in cmdParms) 331 cmd.Parameters.Add(parm); 332 } 333 } 334 335 336 337 /// <summary> 338 /// 在数据库的tablename数据表里面加入一行，返回，返回受影响的行数，-1返回则代表异常。"commandType">commandType.tex 339 /// </summary> 340 /// <param name="tablename"></param> 341 /// <param name="dtparaname"></param> 342 /// <param name="dtparatypes"></param> 343 /// <param name="dtvalues"></param> 344 /// <returns></returns> 345 public int InsertDTTableOneRow(string tablename,string[] dtparaname, MySqlDbType[] dtparatypes,string[] dtvalues) 346 { 347 int result = 0; 348 try 349 { 350 //string commandtext = "insert into userinfo (username,userage) values (@name,@age)"; 351 // para[0].Value = 5; 352 // para[1].Value = "zwf"; 353 // var commandtext = string.Format("insert into {0}({0},{0});charset='utf8';pooling=true", dtname, dtsource, userid, userpassword); 354 355 var commandtext = "insert into " + tablename.ToString() + " ("; 356 var endstr = "values ("; 357 358 359 MySqlParameter[] para = new MySqlParameter[dtparaname.Length]; 360 for (int i = 0; i < dtparaname.Length; i++) 361 { 362 363 if(i == dtparaname.Length - 1) 364 { 365 endstr += "@para" + i.ToString(); 366 commandtext += dtparaname[i].ToString(); 367 } 368 else 369 { 370 endstr += "@para" + i.ToString() + ","; 371 commandtext += dtparaname[i].ToString() + ","; 372 } 373 374 375 if (i == dtparaname.Length - 1) 376 { 377 commandtext += ") "; 378 endstr += ") "; 379 } 380 para[i] = new MySqlParameter("@para" + i.ToString(), dtparatypes[i]); 381 para[i] .Value= dtvalues[i]; 382 } 383 commandtext += endstr; 384 result = ExecuteNonQuery(CommandType.Text, commandtext, para); 385 } 386 catch(Exception ex) 387 { 388 result = -1; 389 MyBasicFun.LogWarn("InsertDTTableOneRow Abnormal!" + ex.Message); 390 } 391 return result; 392 } 393 394 /// <summary> 395 /// 删除数据库里面的tablename 数据表的满足特定条件的一行，返回受影响的行数，-1返回则代表异常。"commandType">commandType.tex 396 /// </summary> 397 /// <param name="tablename">数据表名称</param> 398 /// <param name="dtparaname">删除条件对于的数据表的字段名称，可以多个</param> 399 /// <param name="dtparatypes">MySqlDbType[]</param> 400 /// <param name="dtvalues">删除条件对于的数据表的字段名称对应的字段的值 </param> 401 /// <param name="condition_connectstr">连接条件的逻辑字符串，可以是 and 或者 or </param> 402 /// 403 /// <returns></returns> 404 public int DeleteDTTableOneRow(string tablename, string[] dtparaname, MySqlDbType[] dtparatypes, string[] dtvalues,string condition_connectstr="and") 405 { 406 int result = 0; 407 try 408 { 409 // commandtext = "delete from userinfo where iduserinfo=@id and "; 410 411 var commandtext = "delete from " + tablename.ToString() + " where "; 412 MySqlParameter[] para = new MySqlParameter[dtparaname.Length]; 413 for (int i = 0; i < dtparaname.Length; i++) 414 { 415 416 if (i == dtparaname.Length - 1) 417 { 418 419 commandtext += dtparaname[i].ToString()+"=@para"+i.ToString(); 420 } 421 else 422 { 423 commandtext += dtparaname[i].ToString() + "=@para" + i.ToString() + " "+condition_connectstr+" "; 424 } 425 para[i] = new MySqlParameter("@para" + i.ToString(), dtparatypes[i]); 426 para[i].Value = dtvalues[i]; 427 } 428 result= ExecuteNonQuery(CommandType.Text, commandtext, para); 429 } 430 catch (Exception ex) 431 { 432 result = -1; 433 MyBasicFun.LogWarn("DeleteDTTableOneRow Abnormal!" + ex.Message); 434 } 435 return result; 436 } 437 438 439 public int UpdateDTTableOneRow(string tablename, 440 string set_dtparaname, MySqlDbType set_dtparatypes, string set_dtvalues, 441 string[] condition_dtparaname,MySqlDbType[] condition_dtparatypes, string[] condition_dtvalues, string condition_connectstr = "and") 442 { 443 int result = 0; 444 try 445 { 446 // commandtext = "delete from userinfo where iduserinfo=@id and "; 447 448 var commandtext = "update " + tablename.ToString() + " set "+ set_dtparaname+ "=@para0 where "; 449 MySqlParameter[] set_para = new MySqlParameter[condition_dtparaname.Length+1]; 450 set_para[0] = new MySqlParameter("@para0", set_dtparatypes); 451 set_para[0].Value = set_dtvalues; 452 453 for (int i = 1; i < condition_dtparaname.Length+1; i++) 454 { 455 456 if (i == condition_dtparaname.Length) 457 { 458 459 commandtext += condition_dtparaname[i-1].ToString() + "=@para" + i.ToString(); 460 } 461 else 462 { 463 commandtext += condition_dtparaname[i-1].ToString() + "=@para" + i.ToString() + " " + condition_connectstr + " "; 464 } 465 466 set_para[i] = new MySqlParameter("@para" + i.ToString(), condition_dtparatypes[i-1]); 467 set_para[i].Value = condition_dtvalues[i-1]; 468 } 469 result = ExecuteNonQuery(CommandType.Text, commandtext, set_para); 470 } 471 catch (Exception ex) 472 { 473 result = -1; 474 MyBasicFun.LogWarn("UpdateDTTableOneRow Abnormal!" + ex.Message); 475 } 476 return result; 477 } 478 479 480 /// <summary> 481 /// 执行一个无参数的存储过程 482 /// </summary> 483 /// <param name="procedurename">存储过程的名称</param> 484 /// <returns></returns> 485 public int RunStoredProcedure(string procedurename) 486 { 487 int result = 0; 488 try 489 { 490 var commandType = CommandType.StoredProcedure; 491 result = ExecuteNonQuery(commandType, procedurename, null); 492 } 493 catch (Exception ex) 494 { 495 result = -1; 496 MyBasicFun.LogWarn("RunStoredProcedure Abnormal!" + ex.Message); 497 } 498 return result; 499 } 500 501 502 503 504 505 506 }
转载请说明出处。
BR!
