1 #region 私有构造函数和方法 2 3 private SqlHelper() { } 4 5 /// <summary> 6 /// 将SqlParameter参数数组(参数值)分配给SqlCommand命令. 7 /// 这个方法将给任何一个参数分配DBNull.Value; 8 /// 该操作将阻止默认值的使用. 9 /// </summary> 10 /// <param name="command">命令名</param> 11 /// <param name="commandParameters">SqlParameters数组</param> 12 private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) 13 { 14 if (command == null) throw new ArgumentNullException("command"); 15 if (commandParameters != null) 16 { 17 foreach (SqlParameter p in commandParameters) 18 { 19 if (p != null) 20 { 21 // 检查未分配值的输出参数,将其分配以DBNull.Value. 22 if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && 23 (p.Value == null)) 24 { 25 p.Value = DBNull.Value; 26 } 27 command.Parameters.Add(p); 28 } 29 } 30 } 31 } 32 33 /// <summary> 34 /// 将DataRow类型的列值分配到SqlParameter参数数组. 35 /// </summary> 36 /// <param name="commandParameters">要分配值的SqlParameter参数数组</param> 37 /// <param name="dataRow">将要分配给存储过程参数的DataRow</param> 38 private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow) 39 { 40 if ((commandParameters == null) || (dataRow == null)) 41 { 42 return; 43 } 44 45 int i = 0; 46 // 设置参数值 47 foreach (SqlParameter commandParameter in commandParameters) 48 { 49 // 创建参数名称,如果不存在,只抛出一个异常. 50 if (commandParameter.ParameterName == null || 51 commandParameter.ParameterName.Length <= 1) 52 throw new Exception( 53 string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName)); 54 // 从dataRow的表中获取为参数数组中数组名称的列的索引. 55 // 如果存在和参数名称相同的列,则将列值赋给当前名称的参数. 56 if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1) 57 commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)]; 58 i++; 59 } 60 } 61 62 /// <summary> 63 /// 将一个对象数组分配给SqlParameter参数数组. 64 /// </summary> 65 /// <param name="commandParameters">要分配值的SqlParameter参数数组</param> 66 /// <param name="parameterValues">将要分配给存储过程参数的对象数组</param> 67 private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) 68 { 69 if ((commandParameters == null) || (parameterValues == null)) 70 { 71 return; 72 } 73 74 // 确保对象数组个数与参数个数匹配,如果不匹配,抛出一个异常. 75 if (commandParameters.Length != parameterValues.Length) 76 { 77 throw new ArgumentException("参数值个数与参数不匹配."); 78 } 79 80 // 给参数赋值 81 for (int i = 0, j = commandParameters.Length; i < j; i++) 82 { 83 // If the current array value derives from IDbDataParameter, then assign its Value property 84 if (parameterValues[i] is IDbDataParameter) 85 { 86 IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i]; 87 if (paramInstance.Value == null) 88 { 89 commandParameters[i].Value = DBNull.Value; 90 } 91 else 92 { 93 commandParameters[i].Value = paramInstance.Value; 94 } 95 } 96 else if (parameterValues[i] == null) 97 { 98 commandParameters[i].Value = DBNull.Value; 99 } 100 else 101 { 102 commandParameters[i].Value = parameterValues[i]; 103 } 104 } 105 } 106 107 /// <summary> 108 /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数 109 /// </summary> 110 /// <param name="command">要处理的SqlCommand</param> 111 /// <param name="connection">数据库连接</param> 112 /// <param name="transaction">一个有效的事务或者是null值</param> 113 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param> 114 /// <param name="commandText">存储过程名或都T-SQL命令文本</param> 115 /// <param name="commandParameters">和命令相关联的SqlParameter参数数组,如果没有参数为'null'</param> 116 /// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param> 117 private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection) 118 { 119 if (command == null) throw new ArgumentNullException("command"); 120 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); 121 122 // If the provided connection is not open, we will open it 123 if (connection.State != ConnectionState.Open) 124 { 125 mustCloseConnection = true; 126 connection.Open(); 127 } 128 else 129 { 130 mustCloseConnection = false; 131 } 132 133 // 给命令分配一个数据库连接. 134 command.Connection = connection; 135 136 // 设置命令文本(存储过程名或SQL语句) 137 command.CommandText = commandText; 138 139 // 分配事务 140 if (transaction != null) 141 { 142 if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 143 command.Transaction = transaction; 144 } 145 146 // 设置命令类型. 147 command.CommandType = commandType; 148 149 // 分配命令参数 150 if (commandParameters != null) 151 { 152 AttachParameters(command, commandParameters); 153 } 154 return; 155 } 156 157 #endregion 私有构造函数和方法结束 158 159 #region 数据库连接 160 /// <summary> 161 /// 一个有效的数据库连接字符串 162 /// </summary> 163 /// <returns></returns> 164 public static string GetConnSting() 165 { 166 return ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString; 167 } 168 /// <summary> 169 /// 一个有效的数据库连接对象 170 /// </summary> 171 /// <returns></returns> 172 public static SqlConnection GetConnection() 173 { 174 SqlConnection Connection = new SqlConnection(SqlHelper.GetConnSting()); 175 return Connection; 176 } 177 #endregion 178 179 #region ExecuteNonQuery命令 180 181 /// <summary> 182 /// 执行指定连接字符串,类型的SqlCommand. 183 /// </summary> 184 /// <remarks> 185 /// 示例: 186 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); 187 /// </remarks> 188 /// <param name="connectionString">一个有效的数据库连接字符串</param> 189 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param> 190 /// <param name="commandText">存储过程名称或SQL语句</param> 191 /// <returns>返回命令影响的行数</returns> 192 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) 193 { 194 return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); 195 } 196 197 /// <summary> 198 /// 执行指定连接字符串,类型的SqlCommand.如果没有提供参数,不返回结果. 199 /// </summary> 200 /// <remarks> 201 /// 示例: 202 /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 203 /// </remarks> 204 /// <param name="connectionString">一个有效的数据库连接字符串</param> 205 /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param> 206 /// <param name="commandText">存储过程名称或SQL语句</param> 207 /// <param name="commandParameters">SqlParameter参数数组</param> 208 /// <returns>返回命令影响的行数</returns> 209 public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 210 { 211 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 212 213 using (SqlConnection connection = new SqlConnection(connectionString)) 214 { 215 connection.Open(); 216 217 return ExecuteNonQuery(connection, commandType, commandText, commandParameters); 218 } 219 } 220 221 /// <summary> 222 /// 执行指定连接字符串的存储过程,将对象数组的值赋给存储过程参数, 223 /// 此方法需要在参数缓存方法中探索参数并生成参数. 224 /// </summary> 225 /// <remarks> 226 /// 这个方法没有提供访问输出参数和返回值. 227 /// 示例: 228 /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36); 229 /// </remarks> 230 /// <param name="connectionString">一个有效的数据库连接字符串/param> 231 /// <param name="spName">存储过程名称</param> 232 /// <param name="parameterValues">分配到存储过程输入参数的对象数组</param> 233 /// <returns>返回受影响的行数</returns> 234 public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) 235 { 236 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 237 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 238 239 // 如果存在参数值 240 if ((parameterValues != null) && (parameterValues.Length > 0)) 241 { 242 // 从探索存储过程参数(加载到缓存)并分配给存储过程参数数组. 243 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 244 245 // 给存储过程参数赋值 246 AssignParameterValues(commandParameters, parameterValues); 247 248 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); 249 } 250 else 251 { 252 // 没有参数情况下 253 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); 254 } 255 } 256 257 /// <summary> 258 /// 执行指定数据库连接对象的命令 259 /// </summary> 260 /// <remarks> 261 /// 示例: 262 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders"); 263 /// </remarks> 264 /// <param name="connection">一个有效的数据库连接对象</param> 265 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param> 266 /// <param name="commandText">存储过程名称或T-SQL语句</param> 267 /// <returns>返回影响的行数</returns> 268 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) 269 { 270 return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null); 271 } 272 273 /// <summary> 274 /// 执行指定数据库连接对象的命令 275 /// </summary> 276 /// <remarks> 277 /// 示例: 278 /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); 279 /// </remarks> 280 /// <param name="connection">一个有效的数据库连接对象</param> 281 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param> 282 /// <param name="commandText">T存储过程名称或T-SQL语句</param> 283 /// <param name="commandParameters">SqlParamter参数数组</param> 284 /// <returns>返回影响的行数</returns> 285 public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 286 { 287 if (connection == null) throw new ArgumentNullException("connection"); 288 289 // 创建SqlCommand命令,并进行预处理 290 SqlCommand cmd = new SqlCommand(); 291 bool mustCloseConnection = false; 292 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); 293 294 // Finally, execute the command 295 int retval = cmd.ExecuteNonQuery(); 296 297 // 清除参数,以便再次使用. 298 cmd.Parameters.Clear(); 299 if (mustCloseConnection) 300 connection.Close(); 301 return retval; 302 } 303 304 /// <summary> 305 /// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数. 306 /// </summary> 307 /// <remarks> 308 /// 此方法不提供访问存储过程输出参数和返回值 309 /// 示例: 310 /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36); 311 /// </remarks> 312 /// <param name="connection">一个有效的数据库连接对象</param> 313 /// <param name="spName">存储过程名</param> 314 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 315 /// <returns>返回影响的行数</returns> 316 public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues) 317 { 318 if (connection == null) throw new ArgumentNullException("connection"); 319 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 320 321 // 如果有参数值 322 if ((parameterValues != null) && (parameterValues.Length > 0)) 323 { 324 // 从缓存中加载存储过程参数 325 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 326 327 // 给存储过程分配参数值 328 AssignParameterValues(commandParameters, parameterValues); 329 330 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); 331 } 332 else 333 { 334 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); 335 } 336 } 337 338 /// <summary> 339 /// 执行带事务的SqlCommand. 340 /// </summary> 341 /// <remarks> 342 /// 示例.: 343 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); 344 /// </remarks> 345 /// <param name="transaction">一个有效的数据库连接对象</param> 346 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param> 347 /// <param name="commandText">存储过程名称或T-SQL语句</param> 348 /// <returns>返回影响的行数/returns> 349 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText) 350 { 351 return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null); 352 } 353 354 /// <summary> 355 /// 执行带事务的SqlCommand(指定参数). 356 /// </summary> 357 /// <remarks> 358 /// 示例: 359 /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 360 /// </remarks> 361 /// <param name="transaction">一个有效的数据库连接对象</param> 362 /// <param name="commandType">命令类型(存储过程,命令文本或其它.)</param> 363 /// <param name="commandText">存储过程名称或T-SQL语句</param> 364 /// <param name="commandParameters">SqlParamter参数数组</param> 365 /// <returns>返回影响的行数</returns> 366 public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 367 { 368 if (transaction == null) throw new ArgumentNullException("transaction"); 369 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 370 371 // 预处理 372 SqlCommand cmd = new SqlCommand(); 373 bool mustCloseConnection = false; 374 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 375 376 // 执行 377 int retval = cmd.ExecuteNonQuery(); 378 379 // 清除参数集,以便再次使用. 380 cmd.Parameters.Clear(); 381 return retval; 382 } 383 384 /// <summary> 385 /// 执行带事务的SqlCommand(指定参数值). 386 /// </summary> 387 /// <remarks> 388 /// 此方法不提供访问存储过程输出参数和返回值 389 /// 示例: 390 /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36); 391 /// </remarks> 392 /// <param name="transaction">一个有效的数据库连接对象</param> 393 /// <param name="spName">存储过程名</param> 394 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 395 /// <returns>返回受影响的行数</returns> 396 public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues) 397 { 398 if (transaction == null) throw new ArgumentNullException("transaction"); 399 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 400 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 401 402 // 如果有参数值 403 if ((parameterValues != null) && (parameterValues.Length > 0)) 404 { 405 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 406 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 407 408 // 给存储过程参数赋值 409 AssignParameterValues(commandParameters, parameterValues); 410 411 // 调用重载方法 412 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); 413 } 414 else 415 { 416 // 没有参数值 417 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); 418 } 419 } 420 421 #endregion ExecuteNonQuery方法结束 422 423 #region ExecuteDataset方法 424 425 /// <summary> 426 /// 执行指定数据库连接字符串的命令,返回DataSet. 427 /// </summary> 428 /// <remarks> 429 /// 示例: 430 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders"); 431 /// </remarks> 432 /// <param name="connectionString">一个有效的数据库连接字符串</param> 433 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 434 /// <param name="commandText">存储过程名称或T-SQL语句</param> 435 /// <returns>返回一个包含结果集的DataSet</returns> 436 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) 437 { 438 return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null); 439 } 440 441 /// <summary> 442 /// 执行指定数据库连接字符串的命令,返回DataSet. 443 /// </summary> 444 /// <remarks> 445 /// 示例: 446 /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 447 /// </remarks> 448 /// <param name="connectionString">一个有效的数据库连接字符串</param> 449 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 450 /// <param name="commandText">存储过程名称或T-SQL语句</param> 451 /// <param name="commandParameters">SqlParamters参数数组</param> 452 /// <returns>返回一个包含结果集的DataSet</returns> 453 public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 454 { 455 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 456 457 // 创建并打开数据库连接对象,操作完成释放对象. 458 using (SqlConnection connection = new SqlConnection(connectionString)) 459 { 460 connection.Open(); 461 462 // 调用指定数据库连接字符串重载方法. 463 return ExecuteDataset(connection, commandType, commandText, commandParameters); 464 } 465 } 466 467 /// <summary> 468 /// 执行指定数据库连接字符串的命令,直接提供参数值,返回DataSet. 469 /// </summary> 470 /// <remarks> 471 /// 此方法不提供访问存储过程输出参数和返回值. 472 /// 示例: 473 /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36); 474 /// </remarks> 475 /// <param name="connectionString">一个有效的数据库连接字符串</param> 476 /// <param name="spName">存储过程名</param> 477 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 478 /// <returns>返回一个包含结果集的DataSet</returns> 479 public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues) 480 { 481 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 482 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 483 484 if ((parameterValues != null) && (parameterValues.Length > 0)) 485 { 486 // 从缓存中检索存储过程参数 487 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 488 489 // 给存储过程参数分配值 490 AssignParameterValues(commandParameters, parameterValues); 491 492 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters); 493 } 494 else 495 { 496 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); 497 } 498 } 499 500 /// <summary> 501 /// 执行指定数据库连接对象的命令,返回DataSet. 502 /// </summary> 503 /// <remarks> 504 /// 示例: 505 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders"); 506 /// </remarks> 507 /// <param name="connection">一个有效的数据库连接对象</param> 508 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 509 /// <param name="commandText">存储过程名或T-SQL语句</param> 510 /// <returns>返回一个包含结果集的DataSet</returns> 511 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) 512 { 513 return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null); 514 } 515 516 /// <summary> 517 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataSet. 518 /// </summary> 519 /// <remarks> 520 /// 示例: 521 /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 522 /// </remarks> 523 /// <param name="connection">一个有效的数据库连接对象</param> 524 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 525 /// <param name="commandText">存储过程名或T-SQL语句</param> 526 /// <param name="commandParameters">SqlParamter参数数组</param> 527 /// <returns>返回一个包含结果集的DataSet</returns> 528 public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 529 { 530 if (connection == null) throw new ArgumentNullException("connection"); 531 532 // 预处理 533 SqlCommand cmd = new SqlCommand(); 534 bool mustCloseConnection = false; 535 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); 536 537 // 创建SqlDataAdapter和DataSet. 538 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 539 { 540 DataSet ds = new DataSet(); 541 542 // 填充DataSet. 543 da.Fill(ds); 544 545 cmd.Parameters.Clear(); 546 547 if (mustCloseConnection) 548 connection.Close(); 549 550 return ds; 551 } 552 } 553 554 /// <summary> 555 /// 执行指定数据库连接对象的命令,指定参数值,返回DataSet. 556 /// </summary> 557 /// <remarks> 558 /// 此方法不提供访问存储过程输入参数和返回值. 559 /// 示例.: 560 /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36); 561 /// </remarks> 562 /// <param name="connection">一个有效的数据库连接对象</param> 563 /// <param name="spName">存储过程名</param> 564 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 565 /// <returns>返回一个包含结果集的DataSet</returns> 566 public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues) 567 { 568 if (connection == null) throw new ArgumentNullException("connection"); 569 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 570 571 if ((parameterValues != null) && (parameterValues.Length > 0)) 572 { 573 // 比缓存中加载存储过程参数 574 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 575 576 // 给存储过程参数分配值 577 AssignParameterValues(commandParameters, parameterValues); 578 579 return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters); 580 } 581 else 582 { 583 return ExecuteDataset(connection, CommandType.StoredProcedure, spName); 584 } 585 } 586 587 /// <summary> 588 /// 执行指定事务的命令,返回DataSet. 589 /// </summary> 590 /// <remarks> 591 /// 示例: 592 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders"); 593 /// </remarks> 594 /// <param name="transaction">事务</param> 595 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 596 /// <param name="commandText">存储过程名或T-SQL语句</param> 597 /// <returns>返回一个包含结果集的DataSet</returns> 598 public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText) 599 { 600 return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null); 601 } 602 603 /// <summary> 604 /// 执行指定事务的命令,指定参数,返回DataSet. 605 /// </summary> 606 /// <remarks> 607 /// 示例: 608 /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 609 /// </remarks> 610 /// <param name="transaction">事务</param> 611 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 612 /// <param name="commandText">存储过程名或T-SQL语句</param> 613 /// <param name="commandParameters">SqlParamter参数数组</param> 614 /// <returns>返回一个包含结果集的DataSet</returns> 615 public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 616 { 617 if (transaction == null) throw new ArgumentNullException("transaction"); 618 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 619 620 // 预处理 621 SqlCommand cmd = new SqlCommand(); 622 bool mustCloseConnection = false; 623 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 624 625 // 创建 DataAdapter & DataSet 626 using (SqlDataAdapter da = new SqlDataAdapter(cmd)) 627 { 628 DataSet ds = new DataSet(); 629 da.Fill(ds); 630 cmd.Parameters.Clear(); 631 return ds; 632 } 633 } 634 635 /// <summary> 636 /// 执行指定事务的命令,指定参数值,返回DataSet. 637 /// </summary> 638 /// <remarks> 639 /// 此方法不提供访问存储过程输入参数和返回值. 640 /// 示例.: 641 /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36); 642 /// </remarks> 643 /// <param name="transaction">事务</param> 644 /// <param name="spName">存储过程名</param> 645 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 646 /// <returns>返回一个包含结果集的DataSet</returns> 647 public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues) 648 { 649 if (transaction == null) throw new ArgumentNullException("transaction"); 650 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 651 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 652 653 if ((parameterValues != null) && (parameterValues.Length > 0)) 654 { 655 // 从缓存中加载存储过程参数 656 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 657 658 // 给存储过程参数分配值 659 AssignParameterValues(commandParameters, parameterValues); 660 661 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters); 662 } 663 else 664 { 665 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName); 666 } 667 } 668 669 #endregion ExecuteDataset数据集命令结束 670 671 #region ExecuteReader 数据阅读器 672 673 /// <summary> 674 /// 枚举,标识数据库连接是由SqlHelper提供还是由调用者提供 675 /// </summary> 676 private enum SqlConnectionOwnership 677 { 678 /// <summary>由SqlHelper提供连接</summary> 679 Internal, 680 /// <summary>由调用者提供连接</summary> 681 External 682 } 683 684 /// <summary> 685 /// 执行指定数据库连接对象的数据阅读器. 686 /// </summary> 687 /// <remarks> 688 /// 如果是SqlHelper打开连接,当连接关闭DataReader也将关闭. 689 /// 如果是调用都打开连接,DataReader由调用都管理. 690 /// </remarks> 691 /// <param name="connection">一个有效的数据库连接对象</param> 692 /// <param name="transaction">一个有效的事务,或者为 'null'</param> 693 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 694 /// <param name="commandText">存储过程名或T-SQL语句</param> 695 /// <param name="commandParameters">SqlParameters参数数组,如果没有参数则为'null'</param> 696 /// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由SqlHelper提供</param> 697 /// <returns>返回包含结果集的SqlDataReader</returns> 698 private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) 699 { 700 if (connection == null) throw new ArgumentNullException("connection"); 701 702 bool mustCloseConnection = false; 703 // 创建命令 704 SqlCommand cmd = new SqlCommand(); 705 try 706 { 707 PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 708 709 // 创建数据阅读器 710 SqlDataReader dataReader; 711 712 if (connectionOwnership == SqlConnectionOwnership.External) 713 { 714 dataReader = cmd.ExecuteReader(); 715 } 716 else 717 { 718 dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 719 } 720 721 // 清除参数,以便再次使用.. 722 // HACK: There is a problem here, the output parameter values are fletched 723 // when the reader is closed, so if the parameters are detached from the command 724 // then the SqlReader can磘 set its values. 725 // When this happen, the parameters can磘 be used again in other command. 726 bool canClear = true; 727 foreach (SqlParameter commandParameter in cmd.Parameters) 728 { 729 if (commandParameter.Direction != ParameterDirection.Input) 730 canClear = false; 731 } 732 733 if (canClear) 734 { 735 cmd.Parameters.Clear(); 736 } 737 738 return dataReader; 739 } 740 catch 741 { 742 if (mustCloseConnection) 743 connection.Close(); 744 throw; 745 } 746 } 747 748 /// <summary> 749 /// 执行指定数据库连接字符串的数据阅读器. 750 /// </summary> 751 /// <remarks> 752 /// 示例: 753 /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders"); 754 /// </remarks> 755 /// <param name="connectionString">一个有效的数据库连接字符串</param> 756 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 757 /// <param name="commandText">存储过程名或T-SQL语句</param> 758 /// <returns>返回包含结果集的SqlDataReader</returns> 759 public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) 760 { 761 return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null); 762 } 763 764 /// <summary> 765 /// 执行指定数据库连接字符串的数据阅读器,指定参数. 766 /// </summary> 767 /// <remarks> 768 /// 示例: 769 /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 770 /// </remarks> 771 /// <param name="connectionString">一个有效的数据库连接字符串</param> 772 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 773 /// <param name="commandText">存储过程名或T-SQL语句</param> 774 /// <param name="commandParameters">SqlParamter参数数组(new SqlParameter("@prodid", 24))</param> 775 /// <returns>返回包含结果集的SqlDataReader</returns> 776 public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 777 { 778 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 779 SqlConnection connection = null; 780 try 781 { 782 connection = new SqlConnection(connectionString); 783 connection.Open(); 784 785 return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal); 786 } 787 catch 788 { 789 // If we fail to return the SqlDatReader, we need to close the connection ourselves 790 if (connection != null) connection.Close(); 791 throw; 792 } 793 794 } 795 796 /// <summary> 797 /// 执行指定数据库连接字符串的数据阅读器,指定参数值. 798 /// </summary> 799 /// <remarks> 800 /// 此方法不提供访问存储过程输出参数和返回值参数. 801 /// 示例: 802 /// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36); 803 /// </remarks> 804 /// <param name="connectionString">一个有效的数据库连接字符串</param> 805 /// <param name="spName">存储过程名</param> 806 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 807 /// <returns>返回包含结果集的SqlDataReader</returns> 808 public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues) 809 { 810 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 811 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 812 813 if ((parameterValues != null) && (parameterValues.Length > 0)) 814 { 815 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 816 817 AssignParameterValues(commandParameters, parameterValues); 818 819 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters); 820 } 821 else 822 { 823 return ExecuteReader(connectionString, CommandType.StoredProcedure, spName); 824 } 825 } 826 827 /// <summary> 828 /// 执行指定数据库连接对象的数据阅读器. 829 /// </summary> 830 /// <remarks> 831 /// 示例: 832 /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders"); 833 /// </remarks> 834 /// <param name="connection">一个有效的数据库连接对象</param> 835 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 836 /// <param name="commandText">存储过程名或T-SQL语句</param> 837 /// <returns>返回包含结果集的SqlDataReader</returns> 838 public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText) 839 { 840 return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null); 841 } 842 843 /// <summary> 844 /// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数. 845 /// </summary> 846 /// <remarks> 847 /// 示例: 848 /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 849 /// </remarks> 850 /// <param name="connection">一个有效的数据库连接对象</param> 851 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 852 /// <param name="commandText">命令类型 (存储过程,命令文本或其它)</param> 853 /// <param name="commandParameters">SqlParamter参数数组</param> 854 /// <returns>返回包含结果集的SqlDataReader</returns> 855 public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 856 { 857 return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External); 858 } 859 860 /// <summary> 861 /// [调用者方式]执行指定数据库连接对象的数据阅读器,指定参数值. 862 /// </summary> 863 /// <remarks> 864 /// 此方法不提供访问存储过程输出参数和返回值参数. 865 /// 示例: 866 /// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36); 867 /// </remarks> 868 /// <param name="connection">一个有效的数据库连接对象</param> 869 /// <param name="spName">T存储过程名</param> 870 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 871 /// <returns>返回包含结果集的SqlDataReader</returns> 872 public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues) 873 { 874 if (connection == null) throw new ArgumentNullException("connection"); 875 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 876 877 if ((parameterValues != null) && (parameterValues.Length > 0)) 878 { 879 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 880 881 AssignParameterValues(commandParameters, parameterValues); 882 883 return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters); 884 } 885 else 886 { 887 return ExecuteReader(connection, CommandType.StoredProcedure, spName); 888 } 889 } 890 891 /// <summary> 892 /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值. 893 /// </summary> 894 /// <remarks> 895 /// 示例: 896 /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders"); 897 /// </remarks> 898 /// <param name="transaction">一个有效的连接事务</param> 899 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 900 /// <param name="commandText">存储过程名称或T-SQL语句</param> 901 /// <returns>返回包含结果集的SqlDataReader</returns> 902 public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText) 903 { 904 return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null); 905 } 906 907 /// <summary> 908 /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数. 909 /// </summary> 910 /// <remarks> 911 /// 示例: 912 /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 913 /// </remarks> 914 /// <param name="transaction">一个有效的连接事务</param> 915 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 916 /// <param name="commandText">存储过程名称或T-SQL语句</param> 917 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 918 /// <returns>返回包含结果集的SqlDataReader</returns> 919 public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 920 { 921 if (transaction == null) throw new ArgumentNullException("transaction"); 922 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 923 924 return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External); 925 } 926 927 /// <summary> 928 /// [调用者方式]执行指定数据库事务的数据阅读器,指定参数值. 929 /// </summary> 930 /// <remarks> 931 /// 此方法不提供访问存储过程输出参数和返回值参数. 932 /// 933 /// 示例: 934 /// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36); 935 /// </remarks> 936 /// <param name="transaction">一个有效的连接事务</param> 937 /// <param name="spName">存储过程名称</param> 938 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 939 /// <returns>返回包含结果集的SqlDataReader</returns> 940 public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues) 941 { 942 if (transaction == null) throw new ArgumentNullException("transaction"); 943 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 944 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 945 946 // 如果有参数值 947 if ((parameterValues != null) && (parameterValues.Length > 0)) 948 { 949 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 950 951 AssignParameterValues(commandParameters, parameterValues); 952 953 return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters); 954 } 955 else 956 { 957 // 没有参数值 958 return ExecuteReader(transaction, CommandType.StoredProcedure, spName); 959 } 960 } 961 962 #endregion ExecuteReader数据阅读器 963 964 #region ExecuteScalar 返回结果集中的第一行第一列 965 966 /// <summary> 967 /// 执行指定数据库连接字符串的命令,返回结果集中的第一行第一列. 968 /// </summary> 969 /// <remarks> 970 /// 示例: 971 /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"); 972 /// </remarks> 973 /// <param name="connectionString">一个有效的数据库连接字符串</param> 974 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 975 /// <param name="commandText">存储过程名称或T-SQL语句</param> 976 /// <returns>返回结果集中的第一行第一列</returns> 977 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText) 978 { 979 // 执行参数为空的方法 980 return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null); 981 } 982 983 /// <summary> 984 /// 执行指定数据库连接字符串的命令,指定参数,返回结果集中的第一行第一列. 985 /// </summary> 986 /// <remarks> 987 /// 示例: 988 /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); 989 /// </remarks> 990 /// <param name="connectionString">一个有效的数据库连接字符串</param> 991 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 992 /// <param name="commandText">存储过程名称或T-SQL语句</param> 993 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 994 /// <returns>返回结果集中的第一行第一列</returns> 995 public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 996 { 997 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 998 // 创建并打开数据库连接对象,操作完成释放对象. 999 using (SqlConnection connection = new SqlConnection(connectionString)) 1000 { 1001 connection.Open(); 1002 1003 // 调用指定数据库连接字符串重载方法. 1004 return ExecuteScalar(connection, commandType, commandText, commandParameters); 1005 } 1006 } 1007 1008 /// <summary> 1009 /// 执行指定数据库连接字符串的命令,指定参数值,返回结果集中的第一行第一列. 1010 /// </summary> 1011 /// <remarks> 1012 /// 此方法不提供访问存储过程输出参数和返回值参数. 1013 /// 1014 /// 示例: 1015 /// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36); 1016 /// </remarks> 1017 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1018 /// <param name="spName">存储过程名称</param> 1019 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1020 /// <returns>返回结果集中的第一行第一列</returns> 1021 public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues) 1022 { 1023 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1024 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1025 1026 // 如果有参数值 1027 if ((parameterValues != null) && (parameterValues.Length > 0)) 1028 { 1029 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1030 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 1031 1032 // 给存储过程参数赋值 1033 AssignParameterValues(commandParameters, parameterValues); 1034 1035 // 调用重载方法 1036 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); 1037 } 1038 else 1039 { 1040 // 没有参数值 1041 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); 1042 } 1043 } 1044 1045 /// <summary> 1046 /// 执行指定数据库连接对象的命令,返回结果集中的第一行第一列. 1047 /// </summary> 1048 /// <remarks> 1049 /// 示例: 1050 /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); 1051 /// </remarks> 1052 /// <param name="connection">一个有效的数据库连接对象</param> 1053 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1054 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1055 /// <returns>返回结果集中的第一行第一列</returns> 1056 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText) 1057 { 1058 // 执行参数为空的方法 1059 return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null); 1060 } 1061 1062 /// <summary> 1063 /// 执行指定数据库连接对象的命令,指定参数,返回结果集中的第一行第一列. 1064 /// </summary> 1065 /// <remarks> 1066 /// 示例: 1067 /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); 1068 /// </remarks> 1069 /// <param name="connection">一个有效的数据库连接对象</param> 1070 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1071 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1072 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1073 /// <returns>返回结果集中的第一行第一列</returns> 1074 public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 1075 { 1076 if (connection == null) throw new ArgumentNullException("connection"); 1077 1078 // 创建SqlCommand命令,并进行预处理 1079 SqlCommand cmd = new SqlCommand(); 1080 1081 bool mustCloseConnection = false; 1082 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); 1083 1084 // 执行SqlCommand命令,并返回结果. 1085 object retval = cmd.ExecuteScalar(); 1086 1087 // 清除参数,以便再次使用. 1088 cmd.Parameters.Clear(); 1089 1090 if (mustCloseConnection) 1091 connection.Close(); 1092 1093 return retval; 1094 } 1095 1096 /// <summary> 1097 /// 执行指定数据库连接对象的命令,指定参数值,返回结果集中的第一行第一列. 1098 /// </summary> 1099 /// <remarks> 1100 /// 此方法不提供访问存储过程输出参数和返回值参数. 1101 /// 1102 /// 示例: 1103 /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36); 1104 /// </remarks> 1105 /// <param name="connection">一个有效的数据库连接对象</param> 1106 /// <param name="spName">存储过程名称</param> 1107 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1108 /// <returns>返回结果集中的第一行第一列</returns> 1109 public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues) 1110 { 1111 if (connection == null) throw new ArgumentNullException("connection"); 1112 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1113 1114 // 如果有参数值 1115 if ((parameterValues != null) && (parameterValues.Length > 0)) 1116 { 1117 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1118 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1119 1120 // 给存储过程参数赋值 1121 AssignParameterValues(commandParameters, parameterValues); 1122 1123 // 调用重载方法 1124 return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters); 1125 } 1126 else 1127 { 1128 // 没有参数值 1129 return ExecuteScalar(connection, CommandType.StoredProcedure, spName); 1130 } 1131 } 1132 1133 /// <summary> 1134 /// 执行指定数据库事务的命令,返回结果集中的第一行第一列. 1135 /// </summary> 1136 /// <remarks> 1137 /// 示例: 1138 /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"); 1139 /// </remarks> 1140 /// <param name="transaction">一个有效的连接事务</param> 1141 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1142 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1143 /// <returns>返回结果集中的第一行第一列</returns> 1144 public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText) 1145 { 1146 // 执行参数为空的方法 1147 return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null); 1148 } 1149 1150 /// <summary> 1151 /// 执行指定数据库事务的命令,指定参数,返回结果集中的第一行第一列. 1152 /// </summary> 1153 /// <remarks> 1154 /// 示例: 1155 /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); 1156 /// </remarks> 1157 /// <param name="transaction">一个有效的连接事务</param> 1158 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1159 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1160 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1161 /// <returns>返回结果集中的第一行第一列</returns> 1162 public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 1163 { 1164 if (transaction == null) throw new ArgumentNullException("transaction"); 1165 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1166 1167 // 创建SqlCommand命令,并进行预处理 1168 SqlCommand cmd = new SqlCommand(); 1169 bool mustCloseConnection = false; 1170 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 1171 1172 // 执行SqlCommand命令,并返回结果. 1173 object retval = cmd.ExecuteScalar(); 1174 1175 // 清除参数,以便再次使用. 1176 cmd.Parameters.Clear(); 1177 return retval; 1178 } 1179 1180 /// <summary> 1181 /// 执行指定数据库事务的命令,指定参数值,返回结果集中的第一行第一列. 1182 /// </summary> 1183 /// <remarks> 1184 /// 此方法不提供访问存储过程输出参数和返回值参数. 1185 /// 1186 /// 示例: 1187 /// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36); 1188 /// </remarks> 1189 /// <param name="transaction">一个有效的连接事务</param> 1190 /// <param name="spName">存储过程名称</param> 1191 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1192 /// <returns>返回结果集中的第一行第一列</returns> 1193 public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues) 1194 { 1195 if (transaction == null) throw new ArgumentNullException("transaction"); 1196 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1197 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1198 1199 // 如果有参数值 1200 if ((parameterValues != null) && (parameterValues.Length > 0)) 1201 { 1202 // PPull the parameters for this stored procedure from the parameter cache () 1203 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1204 1205 // 给存储过程参数赋值 1206 AssignParameterValues(commandParameters, parameterValues); 1207 1208 // 调用重载方法 1209 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters); 1210 } 1211 else 1212 { 1213 // 没有参数值 1214 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName); 1215 } 1216 } 1217 1218 #endregion ExecuteScalar 1219 1220 #region ExecuteXmlReader XML阅读器 1221 /// <summary> 1222 /// 执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回. 1223 /// </summary> 1224 /// <remarks> 1225 /// 示例: 1226 /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders"); 1227 /// </remarks> 1228 /// <param name="connection">一个有效的数据库连接对象</param> 1229 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1230 /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param> 1231 /// <returns>返回XmlReader结果集对象.</returns> 1232 public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText) 1233 { 1234 // 执行参数为空的方法 1235 return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null); 1236 } 1237 1238 /// <summary> 1239 /// 执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数. 1240 /// </summary> 1241 /// <remarks> 1242 /// 示例: 1243 /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 1244 /// </remarks> 1245 /// <param name="connection">一个有效的数据库连接对象</param> 1246 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1247 /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param> 1248 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1249 /// <returns>返回XmlReader结果集对象.</returns> 1250 public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 1251 { 1252 if (connection == null) throw new ArgumentNullException("connection"); 1253 1254 bool mustCloseConnection = false; 1255 // 创建SqlCommand命令,并进行预处理 1256 SqlCommand cmd = new SqlCommand(); 1257 try 1258 { 1259 PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); 1260 1261 // 执行命令 1262 XmlReader retval = cmd.ExecuteXmlReader(); 1263 1264 // 清除参数,以便再次使用. 1265 cmd.Parameters.Clear(); 1266 1267 return retval; 1268 } 1269 catch 1270 { 1271 if (mustCloseConnection) 1272 connection.Close(); 1273 throw; 1274 } 1275 } 1276 1277 /// <summary> 1278 /// 执行指定数据库连接对象的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数值. 1279 /// </summary> 1280 /// <remarks> 1281 /// 此方法不提供访问存储过程输出参数和返回值参数. 1282 /// 1283 /// 示例: 1284 /// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36); 1285 /// </remarks> 1286 /// <param name="connection">一个有效的数据库连接对象</param> 1287 /// <param name="spName">存储过程名称 using "FOR XML AUTO"</param> 1288 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1289 /// <returns>返回XmlReader结果集对象.</returns> 1290 public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues) 1291 { 1292 if (connection == null) throw new ArgumentNullException("connection"); 1293 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1294 1295 // 如果有参数值 1296 if ((parameterValues != null) && (parameterValues.Length > 0)) 1297 { 1298 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1299 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1300 1301 // 给存储过程参数赋值 1302 AssignParameterValues(commandParameters, parameterValues); 1303 1304 // 调用重载方法 1305 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters); 1306 } 1307 else 1308 { 1309 // 没有参数值 1310 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName); 1311 } 1312 } 1313 1314 /// <summary> 1315 /// 执行指定数据库事务的SqlCommand命令,并产生一个XmlReader对象做为结果集返回. 1316 /// </summary> 1317 /// <remarks> 1318 /// 示例: 1319 /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders"); 1320 /// </remarks> 1321 /// <param name="transaction">一个有效的连接事务</param> 1322 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1323 /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param> 1324 /// <returns>返回XmlReader结果集对象.</returns> 1325 public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText) 1326 { 1327 // 执行参数为空的方法 1328 return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null); 1329 } 1330 1331 /// <summary> 1332 /// 执行指定数据库事务的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数. 1333 /// </summary> 1334 /// <remarks> 1335 /// 示例: 1336 /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); 1337 /// </remarks> 1338 /// <param name="transaction">一个有效的连接事务</param> 1339 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1340 /// <param name="commandText">存储过程名称或T-SQL语句 using "FOR XML AUTO"</param> 1341 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1342 /// <returns>返回XmlReader结果集对象.</returns> 1343 public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) 1344 { 1345 if (transaction == null) throw new ArgumentNullException("transaction"); 1346 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1347 1348 // 创建SqlCommand命令,并进行预处理 1349 SqlCommand cmd = new SqlCommand(); 1350 bool mustCloseConnection = false; 1351 PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 1352 1353 // 执行命令 1354 XmlReader retval = cmd.ExecuteXmlReader(); 1355 1356 // 清除参数,以便再次使用. 1357 cmd.Parameters.Clear(); 1358 return retval; 1359 } 1360 1361 /// <summary> 1362 /// 执行指定数据库事务的SqlCommand命令,并产生一个XmlReader对象做为结果集返回,指定参数值. 1363 /// </summary> 1364 /// <remarks> 1365 /// 此方法不提供访问存储过程输出参数和返回值参数. 1366 /// 1367 /// 示例: 1368 /// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36); 1369 /// </remarks> 1370 /// <param name="transaction">一个有效的连接事务</param> 1371 /// <param name="spName">存储过程名称</param> 1372 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1373 /// <returns>返回一个包含结果集的DataSet.</returns> 1374 public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues) 1375 { 1376 if (transaction == null) throw new ArgumentNullException("transaction"); 1377 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1378 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1379 1380 // 如果有参数值 1381 if ((parameterValues != null) && (parameterValues.Length > 0)) 1382 { 1383 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1384 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1385 1386 // 给存储过程参数赋值 1387 AssignParameterValues(commandParameters, parameterValues); 1388 1389 // 调用重载方法 1390 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters); 1391 } 1392 else 1393 { 1394 // 没有参数值 1395 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName); 1396 } 1397 } 1398 1399 #endregion ExecuteXmlReader 阅读器结束 1400 1401 #region FillDataset 填充数据集 1402 /// <summary> 1403 /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集. 1404 /// </summary> 1405 /// <remarks> 1406 /// 示例: 1407 /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); 1408 /// </remarks> 1409 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1410 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1411 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1412 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1413 /// <param name="tableNames">表映射的数据表数组 1414 /// 用户定义的表名 (可有是实际的表名.)</param> 1415 public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataSet dataSet, string[] tableNames) 1416 { 1417 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1418 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1419 1420 // 创建并打开数据库连接对象,操作完成释放对象. 1421 using (SqlConnection connection = new SqlConnection(connectionString)) 1422 { 1423 connection.Open(); 1424 1425 // 调用指定数据库连接字符串重载方法. 1426 FillDataset(connection, commandType, commandText, dataSet, tableNames); 1427 } 1428 } 1429 1430 /// <summary> 1431 /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集.指定命令参数. 1432 /// </summary> 1433 /// <remarks> 1434 /// 示例: 1435 /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 1436 /// </remarks> 1437 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1438 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1439 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1440 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1441 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1442 /// <param name="tableNames">表映射的数据表数组 1443 /// 用户定义的表名 (可有是实际的表名.) 1444 /// </param> 1445 public static void FillDataset(string connectionString, CommandType commandType, 1446 string commandText, DataSet dataSet, string[] tableNames, 1447 params SqlParameter[] commandParameters) 1448 { 1449 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1450 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1451 // 创建并打开数据库连接对象,操作完成释放对象. 1452 using (SqlConnection connection = new SqlConnection(connectionString)) 1453 { 1454 connection.Open(); 1455 1456 // 调用指定数据库连接字符串重载方法. 1457 FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters); 1458 } 1459 } 1460 1461 /// <summary> 1462 /// 执行指定数据库连接字符串的命令,映射数据表并填充数据集,指定存储过程参数值. 1463 /// </summary> 1464 /// <remarks> 1465 /// 此方法不提供访问存储过程输出参数和返回值参数. 1466 /// 1467 /// 示例: 1468 /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24); 1469 /// </remarks> 1470 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1471 /// <param name="spName">存储过程名称</param> 1472 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1473 /// <param name="tableNames">表映射的数据表数组 1474 /// 用户定义的表名 (可有是实际的表名.) 1475 /// </param> 1476 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1477 public static void FillDataset(string connectionString, string spName, 1478 DataSet dataSet, string[] tableNames, 1479 params object[] parameterValues) 1480 { 1481 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1482 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1483 // 创建并打开数据库连接对象,操作完成释放对象. 1484 using (SqlConnection connection = new SqlConnection(connectionString)) 1485 { 1486 connection.Open(); 1487 1488 // 调用指定数据库连接字符串重载方法. 1489 FillDataset(connection, spName, dataSet, tableNames, parameterValues); 1490 } 1491 } 1492 1493 /// <summary> 1494 /// 执行指定数据库连接对象的命令,映射数据表并填充数据集. 1495 /// </summary> 1496 /// <remarks> 1497 /// 示例: 1498 /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); 1499 /// </remarks> 1500 /// <param name="connection">一个有效的数据库连接对象</param> 1501 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1502 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1503 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1504 /// <param name="tableNames">表映射的数据表数组 1505 /// 用户定义的表名 (可有是实际的表名.) 1506 /// </param> 1507 public static void FillDataset(SqlConnection connection, CommandType commandType, 1508 string commandText, DataSet dataSet, string[] tableNames) 1509 { 1510 FillDataset(connection, commandType, commandText, dataSet, tableNames, null); 1511 } 1512 1513 /// <summary> 1514 /// 执行指定数据库连接对象的命令,映射数据表并填充数据集,指定参数. 1515 /// </summary> 1516 /// <remarks> 1517 /// 示例: 1518 /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 1519 /// </remarks> 1520 /// <param name="connection">一个有效的数据库连接对象</param> 1521 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1522 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1523 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1524 /// <param name="tableNames">表映射的数据表数组 1525 /// 用户定义的表名 (可有是实际的表名.) 1526 /// </param> 1527 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1528 public static void FillDataset(SqlConnection connection, CommandType commandType, 1529 string commandText, DataSet dataSet, string[] tableNames, 1530 params SqlParameter[] commandParameters) 1531 { 1532 FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters); 1533 } 1534 1535 /// <summary> 1536 /// 执行指定数据库连接对象的命令,映射数据表并填充数据集,指定存储过程参数值. 1537 /// </summary> 1538 /// <remarks> 1539 /// 此方法不提供访问存储过程输出参数和返回值参数. 1540 /// 1541 /// 示例: 1542 /// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36); 1543 /// </remarks> 1544 /// <param name="connection">一个有效的数据库连接对象</param> 1545 /// <param name="spName">存储过程名称</param> 1546 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1547 /// <param name="tableNames">表映射的数据表数组 1548 /// 用户定义的表名 (可有是实际的表名.) 1549 /// </param> 1550 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1551 public static void FillDataset(SqlConnection connection, string spName, 1552 DataSet dataSet, string[] tableNames, 1553 params object[] parameterValues) 1554 { 1555 if (connection == null) throw new ArgumentNullException("connection"); 1556 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1557 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1558 1559 // 如果有参数值 1560 if ((parameterValues != null) && (parameterValues.Length > 0)) 1561 { 1562 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1563 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1564 1565 // 给存储过程参数赋值 1566 AssignParameterValues(commandParameters, parameterValues); 1567 1568 // 调用重载方法 1569 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters); 1570 } 1571 else 1572 { 1573 // 没有参数值 1574 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames); 1575 } 1576 } 1577 1578 /// <summary> 1579 /// 执行指定数据库事务的命令,映射数据表并填充数据集. 1580 /// </summary> 1581 /// <remarks> 1582 /// 示例: 1583 /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); 1584 /// </remarks> 1585 /// <param name="transaction">一个有效的连接事务</param> 1586 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1587 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1588 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1589 /// <param name="tableNames">表映射的数据表数组 1590 /// 用户定义的表名 (可有是实际的表名.) 1591 /// </param> 1592 public static void FillDataset(SqlTransaction transaction, CommandType commandType, 1593 string commandText, 1594 DataSet dataSet, string[] tableNames) 1595 { 1596 FillDataset(transaction, commandType, commandText, dataSet, tableNames, null); 1597 } 1598 1599 /// <summary> 1600 /// 执行指定数据库事务的命令,映射数据表并填充数据集,指定参数. 1601 /// </summary> 1602 /// <remarks> 1603 /// 示例: 1604 /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 1605 /// </remarks> 1606 /// <param name="transaction">一个有效的连接事务</param> 1607 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1608 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1609 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1610 /// <param name="tableNames">表映射的数据表数组 1611 /// 用户定义的表名 (可有是实际的表名.) 1612 /// </param> 1613 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1614 public static void FillDataset(SqlTransaction transaction, CommandType commandType, 1615 string commandText, DataSet dataSet, string[] tableNames, 1616 params SqlParameter[] commandParameters) 1617 { 1618 FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters); 1619 } 1620 1621 /// <summary> 1622 /// 执行指定数据库事务的命令,映射数据表并填充数据集,指定存储过程参数值. 1623 /// </summary> 1624 /// <remarks> 1625 /// 此方法不提供访问存储过程输出参数和返回值参数. 1626 /// 1627 /// 示例: 1628 /// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36); 1629 /// </remarks> 1630 /// <param name="transaction">一个有效的连接事务</param> 1631 /// <param name="spName">存储过程名称</param> 1632 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1633 /// <param name="tableNames">表映射的数据表数组 1634 /// 用户定义的表名 (可有是实际的表名.) 1635 /// </param> 1636 /// <param name="parameterValues">分配给存储过程输入参数的对象数组</param> 1637 public static void FillDataset(SqlTransaction transaction, string spName, 1638 DataSet dataSet, string[] tableNames, 1639 params object[] parameterValues) 1640 { 1641 if (transaction == null) throw new ArgumentNullException("transaction"); 1642 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1643 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1644 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1645 1646 // 如果有参数值 1647 if ((parameterValues != null) && (parameterValues.Length > 0)) 1648 { 1649 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1650 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1651 1652 // 给存储过程参数赋值 1653 AssignParameterValues(commandParameters, parameterValues); 1654 1655 // 调用重载方法 1656 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters); 1657 } 1658 else 1659 { 1660 // 没有参数值 1661 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames); 1662 } 1663 } 1664 1665 /// <summary> 1666 /// [私有方法][内部调用]执行指定数据库连接对象/事务的命令,映射数据表并填充数据集,DataSet/TableNames/SqlParameters. 1667 /// </summary> 1668 /// <remarks> 1669 /// 示例: 1670 /// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); 1671 /// </remarks> 1672 /// <param name="connection">一个有效的数据库连接对象</param> 1673 /// <param name="transaction">一个有效的连接事务</param> 1674 /// <param name="commandType">命令类型 (存储过程,命令文本或其它)</param> 1675 /// <param name="commandText">存储过程名称或T-SQL语句</param> 1676 /// <param name="dataSet">要填充结果集的DataSet实例</param> 1677 /// <param name="tableNames">表映射的数据表数组 1678 /// 用户定义的表名 (可有是实际的表名.) 1679 /// </param> 1680 /// <param name="commandParameters">分配给命令的SqlParamter参数数组</param> 1681 private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, 1682 string commandText, DataSet dataSet, string[] tableNames, 1683 params SqlParameter[] commandParameters) 1684 { 1685 if (connection == null) throw new ArgumentNullException("connection"); 1686 if (dataSet == null) throw new ArgumentNullException("dataSet"); 1687 1688 // 创建SqlCommand命令,并进行预处理 1689 SqlCommand command = new SqlCommand(); 1690 bool mustCloseConnection = false; 1691 PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); 1692 1693 // 执行命令 1694 using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command)) 1695 { 1696 1697 // 追加表映射 1698 if (tableNames != null && tableNames.Length > 0) 1699 { 1700 string tableName = "Table"; 1701 for (int index = 0; index < tableNames.Length; index++) 1702 { 1703 if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames"); 1704 dataAdapter.TableMappings.Add(tableName, tableNames[index]); 1705 tableName += (index + 1).ToString(); 1706 } 1707 } 1708 1709 // 填充数据集使用默认表名称 1710 dataAdapter.Fill(dataSet); 1711 1712 // 清除参数,以便再次使用. 1713 command.Parameters.Clear(); 1714 } 1715 1716 if (mustCloseConnection) 1717 connection.Close(); 1718 } 1719 #endregion 1720 1721 #region UpdateDataset 更新数据集 1722 /// <summary> 1723 /// 执行数据集更新到数据库,指定inserted, updated, or deleted命令. 1724 /// </summary> 1725 /// <remarks> 1726 /// 示例: 1727 /// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); 1728 /// </remarks> 1729 /// <param name="insertCommand">[追加记录]一个有效的T-SQL语句或存储过程</param> 1730 /// <param name="deleteCommand">[删除记录]一个有效的T-SQL语句或存储过程</param> 1731 /// <param name="updateCommand">[更新记录]一个有效的T-SQL语句或存储过程</param> 1732 /// <param name="dataSet">要更新到数据库的DataSet</param> 1733 /// <param name="tableName">要更新到数据库的DataTable</param> 1734 public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataSet dataSet, string tableName) 1735 { 1736 if (insertCommand == null) throw new ArgumentNullException("insertCommand"); 1737 if (deleteCommand == null) throw new ArgumentNullException("deleteCommand"); 1738 if (updateCommand == null) throw new ArgumentNullException("updateCommand"); 1739 if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName"); 1740 1741 // 创建SqlDataAdapter,当操作完成后释放. 1742 using (SqlDataAdapter dataAdapter = new SqlDataAdapter()) 1743 { 1744 // 设置数据适配器命令 1745 dataAdapter.UpdateCommand = updateCommand; 1746 dataAdapter.InsertCommand = insertCommand; 1747 dataAdapter.DeleteCommand = deleteCommand; 1748 1749 // 更新数据集改变到数据库 1750 dataAdapter.Update(dataSet, tableName); 1751 1752 // 提交所有改变到数据集. 1753 dataSet.AcceptChanges(); 1754 } 1755 } 1756 #endregion 1757 1758 #region CreateCommand 创建一条SqlCommand命令 1759 /// <summary> 1760 /// 创建SqlCommand命令,指定数据库连接对象,存储过程名和参数. 1761 /// </summary> 1762 /// <remarks> 1763 /// 示例: 1764 /// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName"); 1765 /// </remarks> 1766 /// <param name="connection">一个有效的数据库连接对象</param> 1767 /// <param name="spName">存储过程名称</param> 1768 /// <param name="sourceColumns">源表的列名称数组</param> 1769 /// <returns>返回SqlCommand命令</returns> 1770 public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns) 1771 { 1772 if (connection == null) throw new ArgumentNullException("connection"); 1773 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1774 1775 // 创建命令 1776 SqlCommand cmd = new SqlCommand(spName, connection); 1777 cmd.CommandType = CommandType.StoredProcedure; 1778 1779 // 如果有参数值 1780 if ((sourceColumns != null) && (sourceColumns.Length > 0)) 1781 { 1782 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1783 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1784 1785 // 将源表的列到映射到DataSet命令中. 1786 for (int index = 0; index < sourceColumns.Length; index++) 1787 commandParameters[index].SourceColumn = sourceColumns[index]; 1788 1789 // Attach the discovered parameters to the SqlCommand object 1790 AttachParameters(cmd, commandParameters); 1791 } 1792 1793 return cmd; 1794 } 1795 #endregion 1796 1797 #region ExecuteNonQueryTypedParams 类型化参数(DataRow) 1798 /// <summary> 1799 /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回受影响的行数. 1800 /// </summary> 1801 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1802 /// <param name="spName">存储过程名称</param> 1803 /// <param name="dataRow">使用DataRow作为参数值</param> 1804 /// <returns>返回影响的行数</returns> 1805 public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow) 1806 { 1807 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1808 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1809 1810 // 如果row有值,存储过程必须初始化. 1811 if (dataRow != null && dataRow.ItemArray.Length > 0) 1812 { 1813 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1814 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 1815 1816 // 分配参数值 1817 AssignParameterValues(commandParameters, dataRow); 1818 1819 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); 1820 } 1821 else 1822 { 1823 return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); 1824 } 1825 } 1826 1827 /// <summary> 1828 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回受影响的行数. 1829 /// </summary> 1830 /// <param name="connection">一个有效的数据库连接对象</param> 1831 /// <param name="spName">存储过程名称</param> 1832 /// <param name="dataRow">使用DataRow作为参数值</param> 1833 /// <returns>返回影响的行数</returns> 1834 public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow) 1835 { 1836 if (connection == null) throw new ArgumentNullException("connection"); 1837 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1838 1839 // 如果row有值,存储过程必须初始化. 1840 if (dataRow != null && dataRow.ItemArray.Length > 0) 1841 { 1842 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1843 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1844 1845 // 分配参数值 1846 AssignParameterValues(commandParameters, dataRow); 1847 1848 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); 1849 } 1850 else 1851 { 1852 return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); 1853 } 1854 } 1855 1856 /// <summary> 1857 /// 执行指定连接数据库事物的存储过程,使用DataRow做为参数值,返回受影响的行数. 1858 /// </summary> 1859 /// <param name="transaction">一个有效的连接事务 object</param> 1860 /// <param name="spName">存储过程名称</param> 1861 /// <param name="dataRow">使用DataRow作为参数值</param> 1862 /// <returns>返回影响的行数</returns> 1863 public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 1864 { 1865 if (transaction == null) throw new ArgumentNullException("transaction"); 1866 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1867 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1868 1869 // Sf the row has values, the store procedure parameters must be initialized 1870 if (dataRow != null && dataRow.ItemArray.Length > 0) 1871 { 1872 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1873 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1874 1875 // 分配参数值 1876 AssignParameterValues(commandParameters, dataRow); 1877 1878 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); 1879 } 1880 else 1881 { 1882 return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); 1883 } 1884 } 1885 #endregion 1886 1887 #region ExecuteDatasetTypedParams 类型化参数(DataRow) 1888 /// <summary> 1889 /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回DataSet. 1890 /// </summary> 1891 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1892 /// <param name="spName">存储过程名称</param> 1893 /// <param name="dataRow">使用DataRow作为参数值</param> 1894 /// <returns>返回一个包含结果集的DataSet.</returns> 1895 public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow) 1896 { 1897 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1898 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1899 1900 //如果row有值,存储过程必须初始化. 1901 if (dataRow != null && dataRow.ItemArray.Length > 0) 1902 { 1903 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1904 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 1905 1906 // 分配参数值 1907 AssignParameterValues(commandParameters, dataRow); 1908 1909 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters); 1910 } 1911 else 1912 { 1913 return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); 1914 } 1915 } 1916 1917 /// <summary> 1918 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回DataSet. 1919 /// </summary> 1920 /// <param name="connection">一个有效的数据库连接对象</param> 1921 /// <param name="spName">存储过程名称</param> 1922 /// <param name="dataRow">使用DataRow作为参数值</param> 1923 /// <returns>返回一个包含结果集的DataSet.</returns> 1924 /// 1925 public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow) 1926 { 1927 if (connection == null) throw new ArgumentNullException("connection"); 1928 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1929 1930 // 如果row有值,存储过程必须初始化. 1931 if (dataRow != null && dataRow.ItemArray.Length > 0) 1932 { 1933 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1934 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 1935 1936 // 分配参数值 1937 AssignParameterValues(commandParameters, dataRow); 1938 1939 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters); 1940 } 1941 else 1942 { 1943 return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName); 1944 } 1945 } 1946 1947 /// <summary> 1948 /// 执行指定连接数据库事务的存储过程,使用DataRow做为参数值,返回DataSet. 1949 /// </summary> 1950 /// <param name="transaction">一个有效的连接事务 object</param> 1951 /// <param name="spName">存储过程名称</param> 1952 /// <param name="dataRow">使用DataRow作为参数值</param> 1953 /// <returns>返回一个包含结果集的DataSet.</returns> 1954 public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 1955 { 1956 if (transaction == null) throw new ArgumentNullException("transaction"); 1957 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 1958 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1959 1960 // 如果row有值,存储过程必须初始化. 1961 if (dataRow != null && dataRow.ItemArray.Length > 0) 1962 { 1963 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1964 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 1965 1966 // 分配参数值 1967 AssignParameterValues(commandParameters, dataRow); 1968 1969 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters); 1970 } 1971 else 1972 { 1973 return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName); 1974 } 1975 } 1976 1977 #endregion 1978 1979 #region ExecuteReaderTypedParams 类型化参数(DataRow) 1980 /// <summary> 1981 /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回DataReader. 1982 /// </summary> 1983 /// <param name="connectionString">一个有效的数据库连接字符串</param> 1984 /// <param name="spName">存储过程名称</param> 1985 /// <param name="dataRow">使用DataRow作为参数值</param> 1986 /// <returns>返回包含结果集的SqlDataReader</returns> 1987 public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow) 1988 { 1989 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 1990 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 1991 1992 // 如果row有值,存储过程必须初始化. 1993 if (dataRow != null && dataRow.ItemArray.Length > 0) 1994 { 1995 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 1996 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 1997 1998 // 分配参数值 1999 AssignParameterValues(commandParameters, dataRow); 2000 2001 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters); 2002 } 2003 else 2004 { 2005 return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName); 2006 } 2007 } 2008 2009 2010 /// <summary> 2011 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回DataReader. 2012 /// </summary> 2013 /// <param name="connection">一个有效的数据库连接对象</param> 2014 /// <param name="spName">存储过程名称</param> 2015 /// <param name="dataRow">使用DataRow作为参数值</param> 2016 /// <returns>返回包含结果集的SqlDataReader</returns> 2017 public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow) 2018 { 2019 if (connection == null) throw new ArgumentNullException("connection"); 2020 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2021 2022 // 如果row有值,存储过程必须初始化. 2023 if (dataRow != null && dataRow.ItemArray.Length > 0) 2024 { 2025 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2026 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 2027 2028 // 分配参数值 2029 AssignParameterValues(commandParameters, dataRow); 2030 2031 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters); 2032 } 2033 else 2034 { 2035 return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName); 2036 } 2037 } 2038 2039 /// <summary> 2040 /// 执行指定连接数据库事物的存储过程,使用DataRow做为参数值,返回DataReader. 2041 /// </summary> 2042 /// <param name="transaction">一个有效的连接事务 object</param> 2043 /// <param name="spName">存储过程名称</param> 2044 /// <param name="dataRow">使用DataRow作为参数值</param> 2045 /// <returns>返回包含结果集的SqlDataReader</returns> 2046 public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 2047 { 2048 if (transaction == null) throw new ArgumentNullException("transaction"); 2049 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 2050 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2051 2052 // 如果row有值,存储过程必须初始化. 2053 if (dataRow != null && dataRow.ItemArray.Length > 0) 2054 { 2055 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2056 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 2057 2058 // 分配参数值 2059 AssignParameterValues(commandParameters, dataRow); 2060 2061 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters); 2062 } 2063 else 2064 { 2065 return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName); 2066 } 2067 } 2068 #endregion 2069 2070 #region ExecuteScalarTypedParams 类型化参数(DataRow) 2071 /// <summary> 2072 /// 执行指定连接数据库连接字符串的存储过程,使用DataRow做为参数值,返回结果集中的第一行第一列. 2073 /// </summary> 2074 /// <param name="connectionString">一个有效的数据库连接字符串</param> 2075 /// <param name="spName">存储过程名称</param> 2076 /// <param name="dataRow">使用DataRow作为参数值</param> 2077 /// <returns>返回结果集中的第一行第一列</returns> 2078 public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow) 2079 { 2080 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 2081 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2082 2083 // 如果row有值,存储过程必须初始化. 2084 if (dataRow != null && dataRow.ItemArray.Length > 0) 2085 { 2086 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2087 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); 2088 2089 // 分配参数值 2090 AssignParameterValues(commandParameters, dataRow); 2091 2092 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); 2093 } 2094 else 2095 { 2096 return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); 2097 } 2098 } 2099 2100 /// <summary> 2101 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回结果集中的第一行第一列. 2102 /// </summary> 2103 /// <param name="connection">一个有效的数据库连接对象</param> 2104 /// <param name="spName">存储过程名称</param> 2105 /// <param name="dataRow">使用DataRow作为参数值</param> 2106 /// <returns>返回结果集中的第一行第一列</returns> 2107 public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow) 2108 { 2109 if (connection == null) throw new ArgumentNullException("connection"); 2110 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2111 2112 // 如果row有值,存储过程必须初始化. 2113 if (dataRow != null && dataRow.ItemArray.Length > 0) 2114 { 2115 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2116 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 2117 2118 // 分配参数值 2119 AssignParameterValues(commandParameters, dataRow); 2120 2121 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters); 2122 } 2123 else 2124 { 2125 return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName); 2126 } 2127 } 2128 2129 /// <summary> 2130 /// 执行指定连接数据库事务的存储过程,使用DataRow做为参数值,返回结果集中的第一行第一列. 2131 /// </summary> 2132 /// <param name="transaction">一个有效的连接事务 object</param> 2133 /// <param name="spName">存储过程名称</param> 2134 /// <param name="dataRow">使用DataRow作为参数值</param> 2135 /// <returns>返回结果集中的第一行第一列</returns> 2136 public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 2137 { 2138 if (transaction == null) throw new ArgumentNullException("transaction"); 2139 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 2140 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2141 2142 // 如果row有值,存储过程必须初始化. 2143 if (dataRow != null && dataRow.ItemArray.Length > 0) 2144 { 2145 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2146 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 2147 2148 // 分配参数值 2149 AssignParameterValues(commandParameters, dataRow); 2150 2151 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters); 2152 } 2153 else 2154 { 2155 return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName); 2156 } 2157 } 2158 #endregion 2159 2160 #region ExecuteXmlReaderTypedParams 类型化参数(DataRow) 2161 /// <summary> 2162 /// 执行指定连接数据库连接对象的存储过程,使用DataRow做为参数值,返回XmlReader类型的结果集. 2163 /// </summary> 2164 /// <param name="connection">一个有效的数据库连接对象</param> 2165 /// <param name="spName">存储过程名称</param> 2166 /// <param name="dataRow">使用DataRow作为参数值</param> 2167 /// <returns>返回XmlReader结果集对象.</returns> 2168 public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow) 2169 { 2170 if (connection == null) throw new ArgumentNullException("connection"); 2171 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2172 2173 // 如果row有值,存储过程必须初始化. 2174 if (dataRow != null && dataRow.ItemArray.Length > 0) 2175 { 2176 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2177 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); 2178 2179 // 分配参数值 2180 AssignParameterValues(commandParameters, dataRow); 2181 2182 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters); 2183 } 2184 else 2185 { 2186 return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName); 2187 } 2188 } 2189 2190 /// <summary> 2191 /// 执行指定连接数据库事务的存储过程,使用DataRow做为参数值,返回XmlReader类型的结果集. 2192 /// </summary> 2193 /// <param name="transaction">一个有效的连接事务 object</param> 2194 /// <param name="spName">存储过程名称</param> 2195 /// <param name="dataRow">使用DataRow作为参数值</param> 2196 /// <returns>返回XmlReader结果集对象.</returns> 2197 public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) 2198 { 2199 if (transaction == null) throw new ArgumentNullException("transaction"); 2200 if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); 2201 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2202 2203 // 如果row有值,存储过程必须初始化. 2204 if (dataRow != null && dataRow.ItemArray.Length > 0) 2205 { 2206 // 从缓存中加载存储过程参数,如果缓存中不存在则从数据库中检索参数信息并加载到缓存中. () 2207 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); 2208 2209 // 分配参数值 2210 AssignParameterValues(commandParameters, dataRow); 2211 2212 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters); 2213 } 2214 else 2215 { 2216 return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName); 2217 } 2218 } 2219 #endregion 2220 2221 } 2222 2223 /// <summary> 2224 /// SqlHelperParameterCache提供缓存存储过程参数,并能够在运行时从存储过程中探索参数. 2225 /// </summary> 2226 public sealed class SqlHelperParameterCache 2227 { 2228 #region 私有方法,字段,构造函数 2229 // 私有构造函数,妨止类被实例化. 2230 private SqlHelperParameterCache() { } 2231 2232 // 这个方法要注意 2233 private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable()); 2234 2235 /// <summary> 2236 /// 探索运行时的存储过程,返回SqlParameter参数数组. 2237 /// 初始化参数值为 DBNull.Value. 2238 /// </summary> 2239 /// <param name="connection">一个有效的数据库连接</param> 2240 /// <param name="spName">存储过程名称</param> 2241 /// <param name="includeReturnValueParameter">是否包含返回值参数</param> 2242 /// <returns>返回SqlParameter参数数组</returns> 2243 private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter) 2244 { 2245 if (connection == null) throw new ArgumentNullException("connection"); 2246 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2247 2248 SqlCommand cmd = new SqlCommand(spName, connection); 2249 cmd.CommandType = CommandType.StoredProcedure; 2250 2251 connection.Open(); 2252 // 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中. 2253 SqlCommandBuilder.DeriveParameters(cmd); 2254 connection.Close(); 2255 // 如果不包含返回值参数,将参数集中的每一个参数删除. 2256 if (!includeReturnValueParameter) 2257 { 2258 cmd.Parameters.RemoveAt(0); 2259 } 2260 2261 // 创建参数数组 2262 SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; 2263 // 将cmd的Parameters参数集复制到discoveredParameters数组. 2264 cmd.Parameters.CopyTo(discoveredParameters, 0); 2265 2266 // 初始化参数值为 DBNull.Value. 2267 foreach (SqlParameter discoveredParameter in discoveredParameters) 2268 { 2269 discoveredParameter.Value = DBNull.Value; 2270 } 2271 return discoveredParameters; 2272 } 2273 2274 /// <summary> 2275 /// SqlParameter参数数组的深层拷贝. 2276 /// </summary> 2277 /// <param name="originalParameters">原始参数数组</param> 2278 /// <returns>返回一个同样的参数数组</returns> 2279 private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters) 2280 { 2281 SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length]; 2282 2283 for (int i = 0, j = originalParameters.Length; i < j; i++) 2284 { 2285 clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone(); 2286 } 2287 2288 return clonedParameters; 2289 } 2290 2291 #endregion 私有方法,字段,构造函数结束 2292 2293 #region 缓存方法 2294 2295 /// <summary> 2296 /// 追加参数数组到缓存. 2297 /// </summary> 2298 /// <param name="connectionString">一个有效的数据库连接字符串</param> 2299 /// <param name="commandText">存储过程名或SQL语句</param> 2300 /// <param name="commandParameters">要缓存的参数数组</param> 2301 public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters) 2302 { 2303 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 2304 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); 2305 2306 string hashKey = connectionString + ":" + commandText; 2307 2308 paramCache[hashKey] = commandParameters; 2309 } 2310 2311 /// <summary> 2312 /// 从缓存中获取参数数组. 2313 /// </summary> 2314 /// <param name="connectionString">一个有效的数据库连接字符</param> 2315 /// <param name="commandText">存储过程名或SQL语句</param> 2316 /// <returns>参数数组</returns> 2317 public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText) 2318 { 2319 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 2320 if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); 2321 2322 string hashKey = connectionString + ":" + commandText; 2323 2324 SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[]; 2325 if (cachedParameters == null) 2326 { 2327 return null; 2328 } 2329 else 2330 { 2331 return CloneParameters(cachedParameters); 2332 } 2333 } 2334 2335 #endregion 缓存方法结束 2336 2337 #region 检索指定的存储过程的参数集 2338 2339 /// <summary> 2340 /// 返回指定的存储过程的参数集 2341 /// </summary> 2342 /// <remarks> 2343 /// 这个方法将查询数据库,并将信息存储到缓存. 2344 /// </remarks> 2345 /// <param name="connectionString">一个有效的数据库连接字符</param> 2346 /// <param name="spName">存储过程名</param> 2347 /// <returns>返回SqlParameter参数数组</returns> 2348 public static SqlParameter[] GetSpParameterSet(string connectionString, string spName) 2349 { 2350 return GetSpParameterSet(connectionString, spName, false); 2351 } 2352 2353 /// <summary> 2354 /// 返回指定的存储过程的参数集 2355 /// </summary> 2356 /// <remarks> 2357 /// 这个方法将查询数据库,并将信息存储到缓存. 2358 /// </remarks> 2359 /// <param name="connectionString">一个有效的数据库连接字符.</param> 2360 /// <param name="spName">存储过程名</param> 2361 /// <param name="includeReturnValueParameter">是否包含返回值参数</param> 2362 /// <returns>返回SqlParameter参数数组</returns> 2363 public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter) 2364 { 2365 if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); 2366 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2367 2368 using (SqlConnection connection = new SqlConnection(connectionString)) 2369 { 2370 return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter); 2371 } 2372 } 2373 2374 /// <summary> 2375 /// [内部]返回指定的存储过程的参数集(使用连接对象). 2376 /// </summary> 2377 /// <remarks> 2378 /// 这个方法将查询数据库,并将信息存储到缓存. 2379 /// </remarks> 2380 /// <param name="connection">一个有效的数据库连接字符</param> 2381 /// <param name="spName">存储过程名</param> 2382 /// <returns>返回SqlParameter参数数组</returns> 2383 internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName) 2384 { 2385 return GetSpParameterSet(connection, spName, false); 2386 } 2387 2388 /// <summary> 2389 /// [内部]返回指定的存储过程的参数集(使用连接对象) 2390 /// </summary> 2391 /// <remarks> 2392 /// 这个方法将查询数据库,并将信息存储到缓存. 2393 /// </remarks> 2394 /// <param name="connection">一个有效的数据库连接对象</param> 2395 /// <param name="spName">存储过程名</param> 2396 /// <param name="includeReturnValueParameter"> 2397 /// 是否包含返回值参数 2398 /// </param> 2399 /// <returns>返回SqlParameter参数数组</returns> 2400 internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter) 2401 { 2402 if (connection == null) throw new ArgumentNullException("connection"); 2403 using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone()) 2404 { 2405 return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter); 2406 } 2407 } 2408 2409 /// <summary> 2410 /// [私有]返回指定的存储过程的参数集(使用连接对象) 2411 /// </summary> 2412 /// <param name="connection">一个有效的数据库连接对象</param> 2413 /// <param name="spName">存储过程名</param> 2414 /// <param name="includeReturnValueParameter">是否包含返回值参数</param> 2415 /// <returns>返回SqlParameter参数数组</returns> 2416 private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter) 2417 { 2418 if (connection == null) throw new ArgumentNullException("connection"); 2419 if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); 2420 2421 string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : ""); 2422 2423 SqlParameter[] cachedParameters; 2424 2425 cachedParameters = paramCache[hashKey] as SqlParameter[]; 2426 if (cachedParameters == null) 2427 { 2428 SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter); 2429 paramCache[hashKey] = spParameters; 2430 cachedParameters = spParameters; 2431 } 2432 2433 return CloneParameters(cachedParameters); 2434 } 2435 2436 #endregion 参数集检索结束
1.添加名为SqlHelper的类,将以上代码复制进去。
2.添加引用
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Xml;
3.添加链接字符串
打开App.config增加以下代码:
<connectionStrings>
<add name="ConStr" connectionString="server=.;database=test;uid=sa;pwd=123456"/>
</connectionStrings>
4.数据库连接以及增删改查操作
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Data.SqlClient; namespace SqlhelperTest { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { //连接数据库 SqlConnection con = SqlHelper.GetConnection(); Console.WriteLine("数据库连接成功"); //建立一张表 SqlHelper.ExecuteNonQuery(con, CommandType.Text, "create table student(id int primary key,name varchar(20),age int)"); Console.WriteLine("建表成功"); //插入数据 SqlHelper.ExecuteNonQuery(con, CommandType.Text, "insert into student values(1,'Ghazi',21)"); SqlHelper.ExecuteNonQuery(con, CommandType.Text, "insert into student values(2,'Jack',20)"); Console.WriteLine("数据插入成功"); //查询 DataSet ds = SqlHelper.ExecuteDataset(con, CommandType.Text, "select* from student"); foreach (DataRow col in ds.Tables[0].Rows) { for (int i = 0; i < 3; i++) { Console.WriteLine(col[i].ToString()); } } //修改 SqlHelper.ExecuteNonQuery(con, CommandType.Text, "update student set age=22 where id=2"); Console.WriteLine("数据修改成功"); DataSet ds1 = SqlHelper.ExecuteDataset(con, CommandType.Text, "select* from student"); foreach (DataRow col in ds1.Tables[0].Rows) { for (int i = 0; i < 3; i++) { Console.WriteLine(col[i].ToString()); } } //删除 SqlHelper.ExecuteNonQuery(con, CommandType.Text, "delete from student where id=2"); Console.WriteLine("数据删除成功"); DataSet ds2 = SqlHelper.ExecuteDataset(con, CommandType.Text, "select* from student"); foreach (DataRow col in ds2.Tables[0].Rows) { for (int i = 0; i < 3; i++) { Console.WriteLine(col[i].ToString()); } } } } }
5.相关知识
DataSet类详解 https://msdn.microsoft.com/zh-cn/library/system.data.dataset.aspx
http://www.cnblogs.com/losesea/archive/2012/12/08/2809142.html
常用SQL语句 http://www.cnblogs.com/buzaixian/archive/2009/09/21/1571365.html