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