SQLHELPER支持连接字符串、事务、连接对象的Sql Server数据库处理类（对于类型的判断采用if语句）
1 using System; 2 using System.Data; 3 using System.Data.SqlClient; 4 using System.Collections; 5 using System.Collections.Generic; 6 7 namespace TaskWarning 8 { 9 10 /// <summary> 11 /// SQLHelper类是提供用于高性能、可升级的sql数据操作 12 /// </summary> 13 public sealed class SqlHelper //: DBHelper 14 { 15 // 用于缓存参数的HASH表 16 private readonly static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); 17 18 /// <summary> 19 /// 使用事务、连接对象或连接字符串执行一个sql命令（不返回数据集） 20 /// </summary> 21 /// <remarks> 22 ///举例: 23 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, 24 /// "PublishOrders", new SqlParameter("@prodid", 24)); 25 /// </remarks> 26 /// <param name="obj">事务、连接对象或连接字符串</param> 27 /// <param name="cmdType">命令类型（存储过程，文本等）</param> 28 /// <param name="cmdText">存储过程名称或者sql语句</param> 29 /// <param name="commandParameters">执行命令所用参数的集合</param> 30 /// <returns>执行命令所影响的行数</returns> 31 public static int ExecuteNonQuery(object obj, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 32 { 33 SqlConnection con = new SqlConnection(); 34 SqlTransaction tran = null; 35 if (obj.GetType() == typeof(string)) 36 { 37 con = new SqlConnection((obj as string)); 38 } 39 if (obj.GetType() == typeof(SqlConnection)) 40 { 41 con = obj as SqlConnection; 42 } 43 if (obj.GetType() == typeof(SqlTransaction)) 44 { 45 con = (obj as SqlTransaction).Connection; 46 tran = (obj as SqlTransaction); 47 } 48 if (con == null) 49 { 50 Exception err = new Exception("链接字符串配置错误！"); 51 throw err; 52 } 53 SqlCommand cmd = new SqlCommand(); 54 PrepareCommand(cmd, con, tran, cmdType, cmdText, commandParameters); 55 int val = cmd.ExecuteNonQuery(); 56 cmd.Parameters.Clear(); 57 return val; 58 } 59 60 /// <summary> 61 /// 使用事务、连接对象或连接字符串执行一个sql命令（不返回数据集） 62 /// </summary> 63 /// <remarks> 64 ///举例: 65 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, 66 /// "PublishOrders", new SqlParameter("@prodid", 24)); 67 /// </remarks> 68 /// <param name="obj">事务、连接对象或连接字符串</param> 69 /// <param name="cmdText">存储过程名称或者sql语句</param> 70 /// <param name="commandParameters">执行命令所用参数的集合</param> 71 /// <returns>执行命令所影响的行数</returns> 72 public static int ExecuteNonQuery(object obj, string cmdText, params SqlParameter[] commandParameters) 73 { 74 SqlConnection con = new SqlConnection(); 75 SqlTransaction tran = null; 76 if (obj.GetType() == typeof(string)) 77 { 78 con = new SqlConnection((obj as string)); 79 } 80 if (obj.GetType() == typeof(SqlConnection)) 81 { 82 con = obj as SqlConnection; 83 } 84 if (obj.GetType() == typeof(SqlTransaction)) 85 { 86 con = (obj as SqlTransaction).Connection; 87 tran = (obj as SqlTransaction); 88 } 89 if (con == null) 90 { 91 Exception err = new Exception("链接字符串配置错误！"); 92 throw err; 93 } 94 SqlCommand cmd = new SqlCommand(); 95 PrepareCommand(cmd, con, tran, CommandType.Text, cmdText, commandParameters); 96 int val = cmd.ExecuteNonQuery(); 97 cmd.Parameters.Clear(); 98 return val; 99 } 100 101 /// <summary> 102 /// 使用事务、连接对象或连接字符串执行一个sql命令（返回数据集） 103 /// </summary> 104 /// <remarks> 105 /// 举例: 106 /// SqlDataReader r = ExecuteReader(SqlConnection, CommandType.StoredProcedure, 107 /// "PublishOrders", new SqlParameter("@prodid", 24)); 108 /// </remarks> 109 /// <param name="obj">事务、连接对象或连接字符串</param> 110 /// <param name="cmdType">命令类型（存储过程，文本等）</param> 111 /// <param name="cmdText">存储过程名称或者sql语句</param> 112 /// <param name="commandParameters">执行命令所用参数的集合</param> 113 /// <returns>包含结果的读取器</returns> 114 public static SqlDataReader ExecuteReader(object obj, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 115 { 116 SqlConnection con = new SqlConnection(); 117 SqlTransaction tran = null; 118 if (obj.GetType() == typeof(string)) 119 { 120 con = new SqlConnection((obj as string)); 121 } 122 if (obj.GetType() == typeof(SqlConnection)) 123 { 124 con = obj as SqlConnection; 125 } 126 if (obj.GetType() == typeof(SqlTransaction)) 127 { 128 con = (obj as SqlTransaction).Connection; 129 tran = (obj as SqlTransaction); 130 } 131 if (con == null) 132 { 133 Exception err = new Exception("链接字符串配置错误！"); 134 throw err; 135 } 136 //创建一个SqlCommand对象 137 SqlCommand cmd = new SqlCommand(); 138 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 139 //因此commandBehaviour.CloseConnection 就不会执行 140 try 141 { 142 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数 143 PrepareCommand(cmd, con, tran, cmdType, cmdText, commandParameters); 144 //调用 SqlCommand 的 ExecuteReader 方法 145 SqlDataReader reader = cmd.ExecuteReader(); 146 //清除参数 147 cmd.Parameters.Clear(); 148 return reader; 149 } 150 catch 151 { 152 throw; 153 } 154 } 155 156 /// <summary> 157 /// 使用事务、连接对象或连接字符串执行一个sql命令（返回数据集） 158 /// </summary> 159 /// <remarks> 160 /// 举例: 161 /// SqlDataReader r = ExecuteReader(SqlConnection, "PublishOrders", new SqlParameter("@prodid", 24)); 162 /// </remarks> 163 /// <param name="obj">事务、连接对象或连接字符串</param> 164 /// <param name="cmdText">存储过程名称或者sql语句</param> 165 /// <param name="commandParameters">执行命令所用参数的集合</param> 166 /// <returns>包含结果的读取器</returns> 167 public static SqlDataReader ExecuteReader(object obj, string cmdText, params SqlParameter[] commandParameters) 168 { 169 SqlConnection con = new SqlConnection(); 170 SqlTransaction tran = null; 171 if (obj.GetType() == typeof(string)) 172 { 173 con = new SqlConnection((obj as string)); 174 } 175 if (obj.GetType() == typeof(SqlConnection)) 176 { 177 con = obj as SqlConnection; 178 } 179 if (obj.GetType() == typeof(SqlTransaction)) 180 { 181 con = (obj as SqlTransaction).Connection; 182 tran = (obj as SqlTransaction); 183 } 184 if (con == null) 185 { 186 Exception err = new Exception("链接字符串配置错误！"); 187 throw err; 188 } 189 //创建一个SqlCommand对象 190 SqlCommand cmd = new SqlCommand(); 191 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 192 //因此commandBehaviour.CloseConnection 就不会执行 193 try 194 { 195 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数 196 PrepareCommand(cmd, con, tran, CommandType.Text, cmdText, commandParameters); 197 //调用 SqlCommand 的 ExecuteReader 方法 198 SqlDataReader reader = cmd.ExecuteReader(); 199 //清除参数 200 cmd.Parameters.Clear(); 201 return reader; 202 } 203 catch 204 { 205 throw; 206 } 207 } 208 209 210 /// <summary> 211 /// 使用事务、连接对象或连接字符串执行一个sql命令（返回数据集） 212 /// </summary> 213 /// <remarks> 214 /// 举例: 215 /// DataSet r = ExecuteDataSet(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 216 /// </remarks> 217 /// <param name="obj">事务、连接对象或连接字符串</param> 218 /// <param name="cmdType">命令类型（存储过程，文本等）</param> 219 /// <param name="cmdText">存储过程名称或者sql语句</param> 220 /// <param name="commandParameters">执行命令所用参数的集合</param> 221 /// <returns>包含结果的读取器</returns> 222 public static DataSet ExecuteDataSet(object obj, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 223 { 224 SqlConnection con = new SqlConnection(); 225 SqlTransaction tran = null; 226 if (obj.GetType() == typeof(string)) 227 { 228 con = new SqlConnection((obj as string)); 229 } 230 if (obj.GetType() == typeof(SqlConnection)) 231 { 232 con = obj as SqlConnection; 233 } 234 if (obj.GetType() == typeof(SqlTransaction)) 235 { 236 con = (obj as SqlTransaction).Connection; 237 tran = (obj as SqlTransaction); 238 } 239 if (con == null) 240 { 241 Exception err = new Exception("链接字符串配置错误！"); 242 throw err; 243 } 244 //创建一个SqlCommand对象 245 SqlCommand cmd = new SqlCommand(); 246 //在这里我们用一个try/catch结构执行sql文本命令/存储过程， 247 //因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 248 //因此commandBehaviour.CloseConnection 就不会执行 249 try 250 { 251 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数 252 PrepareCommand(cmd, con, tran, cmdType, cmdText, commandParameters); 253 //调用 SqlCommand 的 ExecuteReader 方法 254 SqlDataAdapter da = new SqlDataAdapter(cmd); 255 DataSet ds = new DataSet(); 256 da.Fill(ds, "Table1"); 257 //清除参数 258 cmd.Parameters.Clear(); 259 return ds; 260 } 261 catch 262 { 263 //关闭连接，抛出异常 264 throw; 265 } 266 } 267 268 269 270 /// <summary> 271 /// 使用事务、连接对象或连接字符串执行一个sql命令（返回数据集） 272 /// </summary> 273 /// <remarks> 274 /// 举例: 275 /// DataSet r = ExecuteDataSet(trans, "PublishOrders", new SqlParameter("@prodid", 24)); 276 /// </remarks> 277 /// <param name="obj">事务、连接对象或连接字符串</param> 278 /// <param name="cmdText">存储过程名称或者sql语句</param> 279 /// <param name="commandParameters">执行命令所用参数的集合</param> 280 /// <returns>包含结果的读取器</returns> 281 public static DataSet ExecuteDataSet(object obj, string cmdText, params SqlParameter[] commandParameters) 282 { 283 SqlConnection con = new SqlConnection(); 284 SqlTransaction tran = null; 285 if (obj.GetType() == typeof(string)) 286 { 287 con = new SqlConnection((obj as string)); 288 } 289 if (obj.GetType() == typeof(SqlConnection)) 290 { 291 con = obj as SqlConnection; 292 } 293 if (obj.GetType() == typeof(SqlTransaction)) 294 { 295 con = (obj as SqlTransaction).Connection; 296 tran = (obj as SqlTransaction); 297 } 298 if (con == null) 299 { 300 Exception err = new Exception("链接字符串配置错误！"); 301 throw err; 302 } 303 //创建一个SqlCommand对象 304 SqlCommand cmd = new SqlCommand(); 305 //在这里我们用一个try/catch结构执行sql文本命令/存储过程， 306 //因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 307 //因此commandBehaviour.CloseConnection 就不会执行 308 try 309 { 310 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数 311 PrepareCommand(cmd, con, tran, CommandType.Text, cmdText, commandParameters); 312 //调用 SqlCommand 的 ExecuteReader 方法 313 SqlDataAdapter da = new SqlDataAdapter(cmd); 314 DataSet ds = new DataSet(); 315 da.Fill(ds, "Table1"); 316 //清除参数 317 cmd.Parameters.Clear(); 318 return ds; 319 } 320 catch 321 { 322 //关闭连接，抛出异常 323 throw; 324 } 325 } 326 327 328 /// <summary> 329 /// 使用事务、连接对象或连接字符串执行一个sql命令并返回一个数据集的第一列 330 /// </summary> 331 /// <remarks> 332 /// 举例: 333 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 334 /// </remarks> 335 /// <param name="obj">事务、连接对象或连接字符串</param> 336 /// <param name="cmdType">命令类型（存储过程，文本等）</param> 337 /// <param name="cmdText">存储过程名称或者sql语句</param> 338 /// <param name="commandParameters">执行命令所用参数的集合</param> 339 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> 340 341 public static object ExecuteScalar(object obj, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 342 { 343 SqlConnection con = new SqlConnection(); 344 SqlTransaction tran = null; 345 if (obj.GetType() == typeof(string)) 346 { 347 con = new SqlConnection((obj as string)); 348 } 349 if (obj.GetType() == typeof(SqlConnection)) 350 { 351 con = obj as SqlConnection; 352 } 353 if (obj.GetType() == typeof(SqlTransaction)) 354 { 355 con = (obj as SqlTransaction).Connection; 356 tran = (obj as SqlTransaction); 357 } 358 if (con == null) 359 { 360 Exception err = new Exception("链接字符串配置错误！"); 361 throw err; 362 } 363 SqlCommand cmd = new SqlCommand(); 364 PrepareCommand(cmd, con, tran, cmdType, cmdText, commandParameters); 365 object val = cmd.ExecuteScalar(); 366 cmd.Parameters.Clear(); 367 return val; 368 } 369 public static object ExecuteScalar(object obj, string cmdText, params SqlParameter[] commandParameters) 370 { 371 SqlConnection con = new SqlConnection(); 372 SqlTransaction tran = null; 373 if (obj.GetType() == typeof(string)) 374 { 375 con = new SqlConnection((obj as string)); 376 } 377 if (obj.GetType() == typeof(SqlConnection)) 378 { 379 con = obj as SqlConnection; 380 } 381 if (obj.GetType() == typeof(SqlTransaction)) 382 { 383 con = (obj as SqlTransaction).Connection; 384 tran = (obj as SqlTransaction); 385 } 386 if (con == null) 387 { 388 Exception err = new Exception("链接字符串配置错误！"); 389 throw err; 390 } 391 SqlCommand cmd = new SqlCommand(); 392 PrepareCommand(cmd, con, tran, CommandType.Text, cmdText, commandParameters); 393 object val = cmd.ExecuteScalar(); 394 cmd.Parameters.Clear(); 395 return val; 396 } 397 398 399 /// <summary> 400 /// 将参数集合添加到缓存 401 /// </summary> 402 /// <param name="cacheKey">添加到缓存的变量</param> 403 /// <param name="cmdParms">一个将要添加到缓存的sql参数集合</param> 404 public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) 405 { 406 parmCache[cacheKey] = commandParameters; 407 } 408 409 /// <summary> 410 /// 找回缓存参数集合 411 /// </summary> 412 /// <param name="cacheKey">用于找回参数的关键字</param> 413 /// <returns>缓存的参数集合</returns> 414 public static SqlParameter[] GetCachedParameters(string cacheKey) 415 { 416 SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; 417 418 if (cachedParms == null) 419 return null; 420 421 SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; 422 423 for (int i = 0, j = cachedParms.Length; i < j; i++) 424 clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); 425 426 return clonedParms; 427 } 428 429 /// <summary> 430 /// 准备执行一个命令 431 /// </summary> 432 /// <param name="cmd">sql命令</param> 433 /// <param name="conn">Sql连接</param> 434 /// <param name="trans">Sql事务</param> 435 /// <param name="cmdType">命令类型例如 存储过程或者文本</param> 436 /// <param name="cmdText">命令文本,例如：Select * from Products</param> 437 /// <param name="cmdParms">执行命令的参数</param> 438 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 439 { 440 if (conn.State != ConnectionState.Open) 441 conn.Open(); 442 443 cmd.Connection = conn; 444 cmd.CommandText = cmdText; 445 //SqlParameterCollection aaa = new SqlParameterCollection(); 446 //foreach (SqlParameterCollection cc in aaa) 447 //{ 448 // cmd.Parameters.Add(cc); 449 //} 450 451 if (trans != null) 452 cmd.Transaction = trans; 453 454 cmd.CommandType = cmdType; 455 456 if (cmdParms != null) 457 { 458 foreach (SqlParameter parm in cmdParms) 459 cmd.Parameters.Add(parm); 460 } 461 } 462 463 /// <summary> 464 /// 通过SqlParameter类型的参数列表自动生成Insert语句 465 /// </summary> 466 /// <param name="tableName">要操作的表名</param> 467 /// <param name="cmdParms">SqlParameter类型的参数列表</param> 468 /// <returns>string 类型的 Insert 语句 </returns> 469 public static string BuilderInsert(string tableName, SqlParameter[] cmdParms) 470 { 471 //SqlParameter[] pars = new SqlParameter[] 472 //{ 473 // new SqlParameter("@asdf","asdf"), 474 // new SqlParameter("@sf",1), 475 // new SqlParameter("@af",Convert.ToDateTime("2012-01-01")) 476 477 //}; 478 string fieldsList = ""; 479 string valuesList = ""; 480 int i = 0; 481 foreach (SqlParameter pars in cmdParms) 482 { 483 if (i != 0) 484 { 485 fieldsList = fieldsList + ","; 486 valuesList = valuesList + ","; 487 } 488 fieldsList = String.Format("{0}{1}", fieldsList, pars.ParameterName.Substring(1)); 489 valuesList = String.Format("{0}{1}", valuesList, pars.ParameterName); 490 i++; 491 } 492 return string.Format("Insert Into {0}({1}) Values ({2})", tableName, fieldsList, valuesList); 493 } 494 495 496 /// <summary> 497 /// 通过SqlParameter类型的参数列表自动生成Insert语句 498 /// </summary> 499 /// <param name="tableName">要操作的表名</param> 500 /// <param name="cmdParms">SqlParameter类型的参数列表</param> 501 /// <param name="conditions">SqlParameter类型的条件，用与Update语句的Where子句</param> 502 /// <returns>string 类型的 Update 语句 </returns> 503 public static string BuilderUpdate(string tableName, SqlParameter[] cmdParms, SqlParameter[] conditions) 504 { 505 string statement = ""; 506 int i = 0; 507 foreach (SqlParameter pars in cmdParms) 508 { 509 if (i != 0) 510 { 511 statement = statement + ","; 512 } 513 statement = String.Format("{0}{1}={2}", statement, pars.ParameterName.Substring(1), 514 pars.ParameterName); 515 i++; 516 } 517 string condition = ""; 518 int j = 0; 519 foreach (SqlParameter pars in conditions) 520 { 521 if (j != 0) 522 { 523 condition = condition + " and "; 524 } 525 condition = String.Format("{0}{1}={2}", condition, pars.ParameterName.Substring(1), 526 pars.ParameterName); 527 j++; 528 } 529 return string.Format("Update {0} Set {1} Where {2}", tableName, statement, condition); 530 } 531 532 533 /// <summary> 534 /// 生成update语句 535 /// </summary> 536 /// <param name="tableName">数据表名</param> 537 /// <param name="SrchStr">联合查询语句</param> 538 /// <param name="cmdParms">参数</param> 539 /// <param name="ConditionIndexs">条件参数索引</param> 540 /// <returns>返回一个update语句</returns> 541 public static string BuilderUpdate(string tableName, string SrchStr, SqlParameter[] cmdParms, int[] ConditionIndexs) 542 { 543 string statement = ""; 544 for (int i = 0; i < cmdParms.Length; i++) 545 { 546 if (ConditionIndexs != null && (ConditionIndexs as ICollection<int>).Contains(i)) 547 continue; 548 if (!string.IsNullOrEmpty(statement)) statement = statement + ","; 549 statement = String.Format("{0}{1}={2}", statement, cmdParms[i].ParameterName.Substring(1), 550 cmdParms[i].ParameterName); 551 } 552 string condition = ""; 553 for (int i = 0; ConditionIndexs != null && i < ConditionIndexs.Length; i++) 554 { 555 int j = ConditionIndexs[i]; 556 if (!string.IsNullOrEmpty(condition)) 557 { 558 condition = condition + " and "; 559 } 560 condition = String.Format("{0}{1}={2}", condition, cmdParms[j].ParameterName.Substring(1), 561 cmdParms[j].ParameterName); 562 j++; 563 } 564 if (!string.IsNullOrEmpty(SrchStr)) 565 { 566 SrchStr = SrchStr.Substring(SrchStr.IndexOf("from")); 567 if (!string.IsNullOrEmpty(condition)) condition = "and " + condition; 568 } 569 else 570 { 571 SrchStr = "where"; 572 if (string.IsNullOrEmpty(condition)) SrchStr = ""; 573 } 574 return string.Format("Update {0} Set {1} {2} {3}", tableName, statement, SrchStr, condition); 575 } 576 577 578 /// <summary> 579 /// 生成Delete语句 580 /// </summary> 581 /// <param name="tableName">要删除数据的表名</param> 582 /// <param name="conditions">条件的参数集合</param> 583 /// <returns>返回一个delete语句</returns> 584 public static string BuilderDelete(string tableName, SqlParameter[] conditions) 585 { 586 //SqlParameter[] pars = new SqlParameter[] 587 //{ 588 // new SqlParameter("@asdf","asdf"), 589 // new SqlParameter("@sf",1), 590 // new SqlParameter("@af",Convert.ToDateTime("2012-01-01")) 591 592 //}; 593 string condition = ""; 594 int i = 0; 595 foreach (SqlParameter pars in conditions) 596 { 597 if (i != 0) 598 { 599 condition = condition + " and "; 600 } 601 condition = String.Format("{0}{1}={2}", condition, pars.ParameterName.Substring(1), 602 pars.ParameterName); 603 i++; 604 } 605 return string.Format("Delete From {0} where {1}", tableName, condition); 606 } 607 608 609 610 } 611 }
对于类型的判断采用switch语句
1 using System; 2 using System.Data; 3 using System.Data.SqlClient; 4 using System.Collections; 5 using System.Collections.Generic; 6 7 namespace TaskWarning 8 { 9 10 /// <summary> 11 /// SQLHelper类是提供用于高性能、可升级的sql数据操作 12 /// </summary> 13 public sealed class SqlHelper //: DBHelper 14 { 15 // 用于缓存参数的HASH表 16 private readonly static Hashtable parmCache = Hashtable.Synchronized(new Hashtable()); 17 18 /// <summary> 19 /// 使用事务、连接对象或连接字符串执行一个sql命令（不返回数据集） 20 /// </summary> 21 /// <remarks> 22 ///举例: 23 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, 24 /// "PublishOrders", new SqlParameter("@prodid", 24)); 25 /// </remarks> 26 /// <param name="obj">事务、连接对象或连接字符串</param> 27 /// <param name="cmdType">命令类型（存储过程，文本等）</param> 28 /// <param name="cmdText">存储过程名称或者sql语句</param> 29 /// <param name="commandParameters">执行命令所用参数的集合</param> 30 /// <returns>执行命令所影响的行数</returns> 31 public static int ExecuteNonQuery(object obj, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 32 { 33 SqlConnection con = new SqlConnection(); 34 SqlTransaction tran = null; 35 Type type = obj.GetType(); 36 switch (type.FullName) 37 { 38 case "System.String": 39 con = new SqlConnection((obj as string)); 40 break; 41 case "System.Data.SqlClient.SqlConnection": 42 con = obj as SqlConnection; 43 break; 44 case "System.Data.SqlClient.SqlTransaction": 45 con = (obj as SqlTransaction).Connection; 46 tran = (obj as SqlTransaction); 47 break; 48 case "": 49 default: 50 Exception err = new Exception("参数错误：链接字符串配置错误！"); 51 throw err; 52 } 53 54 SqlCommand cmd = new SqlCommand(); 55 PrepareCommand(cmd, con, tran, cmdType, cmdText, commandParameters); 56 int val = cmd.ExecuteNonQuery(); 57 cmd.Parameters.Clear(); 58 return val; 59 } 60 61 /// <summary> 62 /// 使用事务、连接对象或连接字符串执行一个sql命令（不返回数据集） 63 /// </summary> 64 /// <remarks> 65 ///举例: 66 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, 67 /// "PublishOrders", new SqlParameter("@prodid", 24)); 68 /// </remarks> 69 /// <param name="obj">事务、连接对象或连接字符串</param> 70 /// <param name="cmdText">存储过程名称或者sql语句</param> 71 /// <param name="commandParameters">执行命令所用参数的集合</param> 72 /// <returns>执行命令所影响的行数</returns> 73 public static int ExecuteNonQuery(object obj, string cmdText, params SqlParameter[] commandParameters) 74 { 75 SqlConnection con = new SqlConnection(); 76 SqlTransaction tran = null; 77 Type type = obj.GetType(); 78 switch (type.FullName) 79 { 80 case "System.String": 81 con = new SqlConnection((obj as string)); 82 break; 83 case "System.Data.SqlClient.SqlConnection": 84 con = obj as SqlConnection; 85 break; 86 case "System.Data.SqlClient.SqlTransaction": 87 con = (obj as SqlTransaction).Connection; 88 tran = (obj as SqlTransaction); 89 break; 90 case "": 91 default: 92 Exception err = new Exception("参数错误：链接字符串配置错误！"); 93 throw err; 94 } 95 96 SqlCommand cmd = new SqlCommand(); 97 PrepareCommand(cmd, con, tran, CommandType.Text, cmdText, commandParameters); 98 int val = cmd.ExecuteNonQuery(); 99 cmd.Parameters.Clear(); 100 return val; 101 } 102 103 /// <summary> 104 /// 使用事务、连接对象或连接字符串执行一个sql命令（返回数据集） 105 /// </summary> 106 /// <remarks> 107 /// 举例: 108 /// SqlDataReader r = ExecuteReader(SqlConnection, CommandType.StoredProcedure, 109 /// "PublishOrders", new SqlParameter("@prodid", 24)); 110 /// </remarks> 111 /// <param name="obj">事务、连接对象或连接字符串</param> 112 /// <param name="cmdType">命令类型（存储过程，文本等）</param> 113 /// <param name="cmdText">存储过程名称或者sql语句</param> 114 /// <param name="commandParameters">执行命令所用参数的集合</param> 115 /// <returns>包含结果的读取器</returns> 116 public static SqlDataReader ExecuteReader(object obj, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 117 { 118 SqlConnection con = new SqlConnection(); 119 SqlTransaction tran = null; 120 Type type = obj.GetType(); 121 switch (type.FullName) 122 { 123 case "System.String": 124 con = new SqlConnection((obj as string)); 125 break; 126 case "System.Data.SqlClient.SqlConnection": 127 con = obj as SqlConnection; 128 break; 129 case "System.Data.SqlClient.SqlTransaction": 130 con = (obj as SqlTransaction).Connection; 131 tran = (obj as SqlTransaction); 132 break; 133 case "": 134 default: 135 Exception err = new Exception("参数错误：链接字符串配置错误！"); 136 throw err; 137 } 138 //创建一个SqlCommand对象 139 SqlCommand cmd = new SqlCommand(); 140 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 141 //因此commandBehaviour.CloseConnection 就不会执行 142 try 143 { 144 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数 145 PrepareCommand(cmd, con, tran, cmdType, cmdText, commandParameters); 146 //调用 SqlCommand 的 ExecuteReader 方法 147 SqlDataReader reader = cmd.ExecuteReader(); 148 //清除参数 149 cmd.Parameters.Clear(); 150 return reader; 151 } 152 catch 153 { 154 throw; 155 } 156 } 157 158 /// <summary> 159 /// 使用事务、连接对象或连接字符串执行一个sql命令（返回数据集） 160 /// </summary> 161 /// <remarks> 162 /// 举例: 163 /// SqlDataReader r = ExecuteReader(SqlConnection, "PublishOrders", new SqlParameter("@prodid", 24)); 164 /// </remarks> 165 /// <param name="obj">事务、连接对象或连接字符串</param> 166 /// <param name="cmdText">存储过程名称或者sql语句</param> 167 /// <param name="commandParameters">执行命令所用参数的集合</param> 168 /// <returns>包含结果的读取器</returns> 169 public static SqlDataReader ExecuteReader(object obj, string cmdText, params SqlParameter[] commandParameters) 170 { 171 SqlConnection con = new SqlConnection(); 172 SqlTransaction tran = null; 173 Type type = obj.GetType(); 174 switch (type.FullName) 175 { 176 case "System.String": 177 con = new SqlConnection((obj as string)); 178 break; 179 case "System.Data.SqlClient.SqlConnection": 180 con = obj as SqlConnection; 181 break; 182 case "System.Data.SqlClient.SqlTransaction": 183 con = (obj as SqlTransaction).Connection; 184 tran = (obj as SqlTransaction); 185 break; 186 case "": 187 default: 188 Exception err = new Exception("参数错误：链接字符串配置错误！"); 189 throw err; 190 } 191 //创建一个SqlCommand对象 192 SqlCommand cmd = new SqlCommand(); 193 //在这里我们用一个try/catch结构执行sql文本命令/存储过程，因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 194 //因此commandBehaviour.CloseConnection 就不会执行 195 try 196 { 197 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数 198 PrepareCommand(cmd, con, tran, CommandType.Text, cmdText, commandParameters); 199 //调用 SqlCommand 的 ExecuteReader 方法 200 SqlDataReader reader = cmd.ExecuteReader(); 201 //清除参数 202 cmd.Parameters.Clear(); 203 return reader; 204 } 205 catch 206 { 207 throw; 208 } 209 } 210 211 212 /// <summary> 213 /// 使用事务、连接对象或连接字符串执行一个sql命令（返回数据集） 214 /// </summary> 215 /// <remarks> 216 /// 举例: 217 /// DataSet r = ExecuteDataSet(trans, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 218 /// </remarks> 219 /// <param name="obj">事务、连接对象或连接字符串</param> 220 /// <param name="cmdType">命令类型（存储过程，文本等）</param> 221 /// <param name="cmdText">存储过程名称或者sql语句</param> 222 /// <param name="commandParameters">执行命令所用参数的集合</param> 223 /// <returns>包含结果的读取器</returns> 224 public static DataSet ExecuteDataSet(object obj, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 225 { 226 SqlConnection con = new SqlConnection(); 227 SqlTransaction tran = null; 228 Type type = obj.GetType(); 229 switch (type.FullName) 230 { 231 case "System.String": 232 con = new SqlConnection((obj as string)); 233 break; 234 case "System.Data.SqlClient.SqlConnection": 235 con = obj as SqlConnection; 236 break; 237 case "System.Data.SqlClient.SqlTransaction": 238 con = (obj as SqlTransaction).Connection; 239 tran = (obj as SqlTransaction); 240 break; 241 case "": 242 default: 243 Exception err = new Exception("参数错误：链接字符串配置错误！"); 244 throw err; 245 } 246 //创建一个SqlCommand对象 247 SqlCommand cmd = new SqlCommand(); 248 //在这里我们用一个try/catch结构执行sql文本命令/存储过程， 249 //因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 250 //因此commandBehaviour.CloseConnection 就不会执行 251 try 252 { 253 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数 254 PrepareCommand(cmd, con, tran, cmdType, cmdText, commandParameters); 255 //调用 SqlCommand 的 ExecuteReader 方法 256 SqlDataAdapter da = new SqlDataAdapter(cmd); 257 DataSet ds = new DataSet(); 258 da.Fill(ds, "Table1"); 259 //清除参数 260 cmd.Parameters.Clear(); 261 return ds; 262 } 263 catch 264 { 265 //关闭连接，抛出异常 266 throw; 267 } 268 } 269 270 271 272 /// <summary> 273 /// 使用事务、连接对象或连接字符串执行一个sql命令（返回数据集） 274 /// </summary> 275 /// <remarks> 276 /// 举例: 277 /// DataSet r = ExecuteDataSet(trans, "PublishOrders", new SqlParameter("@prodid", 24)); 278 /// </remarks> 279 /// <param name="obj">事务、连接对象或连接字符串</param> 280 /// <param name="cmdText">存储过程名称或者sql语句</param> 281 /// <param name="commandParameters">执行命令所用参数的集合</param> 282 /// <returns>包含结果的读取器</returns> 283 public static DataSet ExecuteDataSet(object obj, string cmdText, params SqlParameter[] commandParameters) 284 { 285 SqlConnection con = new SqlConnection(); 286 SqlTransaction tran = null; 287 Type type = obj.GetType(); 288 switch (type.FullName) 289 { 290 case "System.String": 291 con = new SqlConnection((obj as string)); 292 break; 293 case "System.Data.SqlClient.SqlConnection": 294 con = obj as SqlConnection; 295 break; 296 case "System.Data.SqlClient.SqlTransaction": 297 con = (obj as SqlTransaction).Connection; 298 tran = (obj as SqlTransaction); 299 break; 300 case "": 301 default: 302 Exception err = new Exception("参数错误：链接字符串配置错误！"); 303 throw err; 304 } 305 //创建一个SqlCommand对象 306 SqlCommand cmd = new SqlCommand(); 307 //在这里我们用一个try/catch结构执行sql文本命令/存储过程， 308 //因为如果这个方法产生一个异常我们要关闭连接，因为没有读取器存在， 309 //因此commandBehaviour.CloseConnection 就不会执行 310 try 311 { 312 //调用 PrepareCommand 方法，对 SqlCommand 对象设置参数 313 PrepareCommand(cmd, con, tran, CommandType.Text, cmdText, commandParameters); 314 //调用 SqlCommand 的 ExecuteReader 方法 315 SqlDataAdapter da = new SqlDataAdapter(cmd); 316 DataSet ds = new DataSet(); 317 da.Fill(ds, "Table1"); 318 //清除参数 319 cmd.Parameters.Clear(); 320 return ds; 321 } 322 catch 323 { 324 //关闭连接，抛出异常 325 throw; 326 } 327 } 328 329 330 /// <summary> 331 /// 使用事务、连接对象或连接字符串执行一个sql命令并返回一个数据集的第一列 332 /// </summary> 333 /// <remarks> 334 /// 举例: 335 /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 336 /// </remarks> 337 /// <param name="obj">事务、连接对象或连接字符串</param> 338 /// <param name="cmdType">命令类型（存储过程，文本等）</param> 339 /// <param name="cmdText">存储过程名称或者sql语句</param> 340 /// <param name="commandParameters">执行命令所用参数的集合</param> 341 /// <returns>用 Convert.To{Type}把类型转换为想要的 </returns> 342 343 public static object ExecuteScalar(object obj, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters) 344 { 345 SqlConnection con = new SqlConnection(); 346 SqlTransaction tran = null; 347 Type type = obj.GetType(); 348 switch (type.FullName) 349 { 350 case "System.String": 351 con = new SqlConnection((obj as string)); 352 break; 353 case "System.Data.SqlClient.SqlConnection": 354 con = obj as SqlConnection; 355 break; 356 case "System.Data.SqlClient.SqlTransaction": 357 con = (obj as SqlTransaction).Connection; 358 tran = (obj as SqlTransaction); 359 break; 360 case "": 361 default: 362 Exception err = new Exception("参数错误：链接字符串配置错误！"); 363 throw err; 364 } 365 SqlCommand cmd = new SqlCommand(); 366 PrepareCommand(cmd, con, tran, cmdType, cmdText, commandParameters); 367 object val = cmd.ExecuteScalar(); 368 cmd.Parameters.Clear(); 369 return val; 370 } 371 public static object ExecuteScalar(object obj, string cmdText, params SqlParameter[] commandParameters) 372 { 373 SqlConnection con = new SqlConnection(); 374 SqlTransaction tran = null; 375 Type type = obj.GetType(); 376 switch (type.FullName) 377 { 378 case "System.String": 379 con = new SqlConnection((obj as string)); 380 break; 381 case "System.Data.SqlClient.SqlConnection": 382 con = obj as SqlConnection; 383 break; 384 case "System.Data.SqlClient.SqlTransaction": 385 con = (obj as SqlTransaction).Connection; 386 tran = (obj as SqlTransaction); 387 break; 388 case "": 389 default: 390 Exception err = new Exception("参数错误：链接字符串配置错误！"); 391 throw err; 392 } 393 394 395 SqlCommand cmd = new SqlCommand(); 396 PrepareCommand(cmd, con, tran, CommandType.Text, cmdText, commandParameters); 397 object val = cmd.ExecuteScalar(); 398 cmd.Parameters.Clear(); 399 return val; 400 } 401 402 403 /// <summary> 404 /// 将参数集合添加到缓存 405 /// </summary> 406 /// <param name="cacheKey">添加到缓存的变量</param> 407 /// <param name="cmdParms">一个将要添加到缓存的sql参数集合</param> 408 public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters) 409 { 410 parmCache[cacheKey] = commandParameters; 411 } 412 413 /// <summary> 414 /// 找回缓存参数集合 415 /// </summary> 416 /// <param name="cacheKey">用于找回参数的关键字</param> 417 /// <returns>缓存的参数集合</returns> 418 public static SqlParameter[] GetCachedParameters(string cacheKey) 419 { 420 SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey]; 421 422 if (cachedParms == null) 423 return null; 424 425 SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length]; 426 427 for (int i = 0, j = cachedParms.Length; i < j; i++) 428 clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone(); 429 430 return clonedParms; 431 } 432 433 /// <summary> 434 /// 准备执行一个命令 435 /// </summary> 436 /// <param name="cmd">sql命令</param> 437 /// <param name="conn">Sql连接</param> 438 /// <param name="trans">Sql事务</param> 439 /// <param name="cmdType">命令类型例如 存储过程或者文本</param> 440 /// <param name="cmdText">命令文本,例如：Select * from Products</param> 441 /// <param name="cmdParms">执行命令的参数</param> 442 private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms) 443 { 444 if (conn.State != ConnectionState.Open) 445 conn.Open(); 446 447 cmd.Connection = conn; 448 cmd.CommandText = cmdText; 449 //SqlParameterCollection aaa = new SqlParameterCollection(); 450 //foreach (SqlParameterCollection cc in aaa) 451 //{ 452 // cmd.Parameters.Add(cc); 453 //} 454 455 if (trans != null) 456 cmd.Transaction = trans; 457 458 cmd.CommandType = cmdType; 459 460 if (cmdParms != null) 461 { 462 foreach (SqlParameter parm in cmdParms) 463 cmd.Parameters.Add(parm); 464 } 465 } 466 467 /// <summary> 468 /// 通过SqlParameter类型的参数列表自动生成Insert语句 469 /// </summary> 470 /// <param name="tableName">要操作的表名</param> 471 /// <param name="cmdParms">SqlParameter类型的参数列表</param> 472 /// <returns>string 类型的 Insert 语句 </returns> 473 public static string BuilderInsert(string tableName, SqlParameter[] cmdParms) 474 { 475 //SqlParameter[] pars = new SqlParameter[] 476 //{ 477 // new SqlParameter("@asdf","asdf"), 478 // new SqlParameter("@sf",1), 479 // new SqlParameter("@af",Convert.ToDateTime("2012-01-01")) 480 481 //}; 482 string fieldsList = ""; 483 string valuesList = ""; 484 int i = 0; 485 foreach (SqlParameter pars in cmdParms) 486 { 487 if (i != 0) 488 { 489 fieldsList = fieldsList + ","; 490 valuesList = valuesList + ","; 491 } 492 fieldsList = String.Format("{0}{1}", fieldsList, pars.ParameterName.Substring(1)); 493 valuesList = String.Format("{0}{1}", valuesList, pars.ParameterName); 494 i++; 495 } 496 return string.Format("Insert Into {0}({1}) Values ({2})", tableName, fieldsList, valuesList); 497 } 498 499 500 /// <summary> 501 /// 通过SqlParameter类型的参数列表自动生成Insert语句 502 /// </summary> 503 /// <param name="tableName">要操作的表名</param> 504 /// <param name="cmdParms">SqlParameter类型的参数列表</param> 505 /// <param name="conditions">SqlParameter类型的条件，用与Update语句的Where子句</param> 506 /// <returns>string 类型的 Update 语句 </returns> 507 public static string BuilderUpdate(string tableName, SqlParameter[] cmdParms, SqlParameter[] conditions) 508 { 509 string statement = ""; 510 int i = 0; 511 foreach (SqlParameter pars in cmdParms) 512 { 513 if (i != 0) 514 { 515 statement = statement + ","; 516 } 517 statement = String.Format("{0}{1}={2}", statement, pars.ParameterName.Substring(1), 518 pars.ParameterName); 519 i++; 520 } 521 string condition = ""; 522 int j = 0; 523 foreach (SqlParameter pars in conditions) 524 { 525 if (j != 0) 526 { 527 condition = condition + " and "; 528 } 529 condition = String.Format("{0}{1}={2}", condition, pars.ParameterName.Substring(1), 530 pars.ParameterName); 531 j++; 532 } 533 return string.Format("Update {0} Set {1} Where {2}", tableName, statement, condition); 534 } 535 536 537 /// <summary> 538 /// 生成update语句 539 /// </summary> 540 /// <param name="tableName">数据表名</param> 541 /// <param name="SrchStr">联合查询语句</param> 542 /// <param name="cmdParms">参数</param> 543 /// <param name="ConditionIndexs">条件参数索引</param> 544 /// <returns>返回一个update语句</returns> 545 public static string BuilderUpdate(string tableName, string SrchStr, SqlParameter[] cmdParms, int[] ConditionIndexs) 546 { 547 string statement = ""; 548 for (int i = 0; i < cmdParms.Length; i++) 549 { 550 if (ConditionIndexs != null && (ConditionIndexs as ICollection<int>).Contains(i)) 551 continue; 552 if (!string.IsNullOrEmpty(statement)) statement = statement + ","; 553 statement = String.Format("{0}{1}={2}", statement, cmdParms[i].ParameterName.Substring(1), 554 cmdParms[i].ParameterName); 555 } 556 string condition = ""; 557 for (int i = 0; ConditionIndexs != null && i < ConditionIndexs.Length; i++) 558 { 559 int j = ConditionIndexs[i]; 560 if (!string.IsNullOrEmpty(condition)) 561 { 562 condition = condition + " and "; 563 } 564 condition = String.Format("{0}{1}={2}", condition, cmdParms[j].ParameterName.Substring(1), 565 cmdParms[j].ParameterName); 566 j++; 567 } 568 if (!string.IsNullOrEmpty(SrchStr)) 569 { 570 SrchStr = SrchStr.Substring(SrchStr.IndexOf("from")); 571 if (!string.IsNullOrEmpty(condition)) condition = "and " + condition; 572 } 573 else 574 { 575 SrchStr = "where"; 576 if (string.IsNullOrEmpty(condition)) SrchStr = ""; 577 } 578 return string.Format("Update {0} Set {1} {2} {3}", tableName, statement, SrchStr, condition); 579 } 580 581 582 /// <summary> 583 /// 生成Delete语句 584 /// </summary> 585 /// <param name="tableName">要删除数据的表名</param> 586 /// <param name="conditions">条件的参数集合</param> 587 /// <returns>返回一个delete语句</returns> 588 public static string BuilderDelete(string tableName, SqlParameter[] conditions) 589 { 590 //SqlParameter[] pars = new SqlParameter[] 591 //{ 592 // new SqlParameter("@asdf","asdf"), 593 // new SqlParameter("@sf",1), 594 // new SqlParameter("@af",Convert.ToDateTime("2012-01-01")) 595 596 //}; 597 string condition = ""; 598 int i = 0; 599 foreach (SqlParameter pars in conditions) 600 { 601 if (i != 0) 602 { 603 condition = condition + " and "; 604 } 605 condition = String.Format("{0}{1}={2}", condition, pars.ParameterName.Substring(1), 606 pars.ParameterName); 607 i++; 608 } 609 return string.Format("Delete From {0} where {1}", tableName, condition); 610 } 611 612 613 614 } 615 }
8851
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
