- 浏览: 39238 次
- 性别:
- 来自: 北京
文章分类
最新评论
-
cai314494687:
需要把uploadfile.php文件中的$imgType = ...
phpexcelreader读取excel自动生成表和字段 -
cai314494687:
学习了,写的很不错。但是有一点, 就是下载的示例有一点是错误的 ...
phpexcelreader读取excel自动生成表和字段
using System; using System.Data; using System.Xml; using System.Data.SqlClient; using System.Collections; using System.Configuration; namespace AcrossDB.Utils { /// <summary> ///微软的SqlHelper3.0 /// </summary> public sealed class SqlHelper { #region 私有方法,属性和构造函数 public static readonly string ConnectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString(); public static readonly string ConnectionString2 = ConfigurationManager.AppSettings["ConnectionString2"].ToString(); //public static readonly string userconnection = ConfigurationManager.AppSettings["SqlConnUser"].ToString(); //public static readonly string siteconnection = ConfigurationManager.AppSettings["SqlConnSite"].ToString(); private SqlHelper() { } /// <summary> ///添加参数 /// </summary> /// <param name="command">SqlCommand对象</param> /// <param name="commandParameters">SqlParamete参数数组</param> private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { //RockeyMssage.ValidationRk(); //Timer.EmptySessionFile(); if (command == null) throw new ArgumentNullException("command"); if (commandParameters != null) { foreach (SqlParameter p in commandParameters) { if (p != null) { // Check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } } } /// <summary> /// 分配参数值 /// </summary> /// <param name="commandParameters">要分配参数值的参数数组</param> /// <param name="dataRow">存储参数值的DataRow</param> private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow) { if ((commandParameters == null) || (dataRow == null)) { return; } int i = 0; foreach (SqlParameter commandParameter in commandParameters) { if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1) throw new Exception( string.Format( "Please provide a valid parameter name on the parameter #{0}, the ParameterName property has the following value: '{1}'.", i, commandParameter.ParameterName)); if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1) commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)]; i++; } } /// <summary> ///分配参数值 /// </summary> /// <param name="commandParameters">要分配参数值的参数数组</param> /// <param name="parameterValues">存储参数值的object对象数组</param> private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { return; } if (commandParameters.Length != parameterValues.Length) { throw new ArgumentException("Parameter count does not match Parameter Value count."); } for (int i = 0, j = commandParameters.Length; i < j; i++) { if (parameterValues[i] is IDbDataParameter) { IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i]; if (paramInstance.Value == null) { commandParameters[i].Value = DBNull.Value; } else { commandParameters[i].Value = paramInstance.Value; } } else if (parameterValues[i] == null) { commandParameters[i].Value = DBNull.Value; } else { commandParameters[i].Value = parameterValues[i]; } } } /// <summary> /// 打开(如果需要),分配连接,事务,command类型和参数给一个commandconnection, transaction, command type and parameters /// </summary> /// <param name="command">预准备SqlCommand对象</param> /// <param name="connection">连接</param> /// <param name="transaction">事务或null</param> /// <param name="commandType">命令类型,文本,存储过程等</param> /// <param name="commandText">存储过程或命令的文本</param> /// <param name="commandParameters">命令参数或为null</param> /// <param name="mustCloseConnection"><c>true</c> 是否必须关闭连接</param> private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection) { if (command == null) throw new ArgumentNullException("command"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); if (connection.State != ConnectionState.Open) { mustCloseConnection = true; connection.Open(); } else { mustCloseConnection = false; } command.Connection = connection; command.CommandText = commandText; if (transaction != null) { if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); command.Transaction = transaction; } command.CommandType = commandType; if (commandParameters != null) { AttachParameters(command, commandParameters); } return; } #endregion 私有方法和构造函数 #region ExecuteNonQuery /// <summary> /// 执行SqlCommand /// </summary> /// <remarks> /// 例子: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="connectionString">连接字符串</param> /// <param name="commandType">命令类型</param> /// <param name="commandText">存储过程或sql命令文本</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) { return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); } public static int ExecuteNonQuery(CommandType commandType, string commandText) { return ExecuteNonQuery(ConnectionString, commandType, commandText); } public static int ExecuteNonQuery(CommandType commandType, string commandText, params SqlParameter[] commandParameters) { return ExecuteNonQuery(ConnectionString, commandType, commandText, commandParameters); } /// <summary> /// 执行SqlCommand /// </summary> /// <remarks> /// 例子.: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">连接字符串</param> /// <param name="commandType">命令类型</param> /// <param name="commandText">存储过程或sql命令文本</param> /// <param name="commandParameters">命令参数</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); return ExecuteNonQuery(connection, commandType, commandText, commandParameters); } } /// <summary> /// 执行SqlCommand /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36); /// </remarks> /// <param name="connectionString">连接字符串</param> /// <param name="spName">The name of the stored prcedure</param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a SqlCommand (that returns no resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// 例子: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="commandType">命令类型</param> /// <param name="commandText">存储过程或sql命令文本</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 使用参数执行没有过返回结果的SqlCommand命令对象Execute a SqlCommand (that returns no resultset) against the specified SqlConnection /// </summary> /// <remarks> /// 例子: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="commandType">命令类型</param> /// <param name="commandText">存储过程或sql命令文本</param> /// <param name="commandParameters">命令参数</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); int retval = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); return retval; } /// <summary> /// 使用提供的参数执行没有返回结果的存储过程 /// </summary> /// <remarks> /// 些方法不能获取存储过程的输出参数和返回值 /// /// 例子: /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="parameterValues">分配对象数组给存储过程作为输入参数</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// 执行无参数,无返回结果集的SqlCommand命令 /// </summary> /// <remarks> /// 例子: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="commandType">命令类型</param> /// <param name="commandText">存储过程或sql命令文本</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 用提供的参数执行无返回结果集的SqlCommand命令 /// </summary> /// <remarks> /// 例子: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="commandType">命令类型</param> /// <param name="commandText">存储过程或sql命令文本</param> /// <param name="commandParameters">命令参数</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Finally, execute the command int retval = cmd.ExecuteNonQuery(); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; } /// <summary> ///使用事务执行无返回结果集的SqlCommand命令 /// </summary> /// <remarks> /// 这个例子不能访问存储过程的输出参数和返回值 /// /// 例子: /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="spName">存储过程名字</param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteNonQuery #region ExecuteDataset /// <summary> /// 执行SqlCommand,返回数据集 /// </summary> /// <remarks> /// 例子: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命令类型(stored procedure, text, 等)</param> /// <param name="commandText">存储过程名或sql的命令</param> /// <returns>数据集</returns> public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) { return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null); } public static DataSet ExecuteDataset(CommandType commandType, string commandText) { return ExecuteDataset(ConnectionString, commandType, commandText); } public static DataSet ExecuteDataset(CommandType commandType, string commandText, params SqlParameter[] commandParameters) { return ExecuteDataset(ConnectionString, commandType, commandText, commandParameters); } /// <summary> /// 执行SqlCommand,返回数据集 /// </summary> /// <remarks> /// 例子: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="commandParameters">SqlParamter数组</param> /// <returns>数据集</returns> public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Call the overload that takes a connection in place of the connection string return ExecuteDataset(connection, commandType, commandText, commandParameters); } } /// <summary> /// 调用存储过程,返回数据集体 /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// DataSet ds = ExecuteDataset(connString, "GetOrders", 24, 36); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程名字</param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> /// <returns>数据集</returns> public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// 例子: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <returns>数据集</returns> public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="commandParameters">SqlParamter数组</param> /// <returns>数据集</returns> public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); // Fill the DataSet using default values for DataTable names, etc da.Fill(ds); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); // Return the dataset return ds; } } /// <summary> ///调用存储过程,返回数据集 /// </summary> /// <remarks> /// 例子: /// DataSet ds = ExecuteDataset(conn, "GetOrders", 24, 36); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程名字</param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> /// <returns>数据集</returns> public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteDataset(connection, CommandType.StoredProcedure, spName); } } /// <summary> ///通过事务执行命令对象,返回数据集 /// </summary> /// <remarks> /// 例子: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <returns>数据集</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 通过事务执行命令对象,返回数据集 /// </summary> /// <remarks> /// 例子: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="commandParameters">SqlParamter数组</param> /// <returns>数据集</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataSet ds = new DataSet(); // Fill the DataSet using default values for DataTable names, etc da.Fill(ds); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); // Return the dataset return ds; } } /// <summary> /// 利用事务,通过SqlCommand执行存储过程 /// </summary> /// <remarks> /// 例子: /// DataSet ds = ExecuteDataset(trans, "GetOrders", 24, 36); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="spName">存储过程名字</param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> /// <returns>数据集</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteDataset(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteDataset #region ExecuteDataTable /// <summary> /// 执行SqlCommand,返回数据表 /// </summary> /// <remarks> /// 例子: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命令类型(stored procedure, text, 等)</param> /// <param name="commandText">存储过程名或sql的命令</param> /// <returns>数据集</returns> public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connectionString, commandType, commandText, (SqlParameter[])null); } public static DataTable ExecuteDataTable(CommandType commandType, string commandText) { return ExecuteDataTable(ConnectionString, commandType, commandText); } public static DataTable ExecuteDataTable(CommandType commandType, string commandText, params SqlParameter[] commandParameters) { return ExecuteDataTable(ConnectionString, commandType, commandText, commandParameters); } /// <summary> /// 执行SqlCommand,返回数据集 /// </summary> /// <remarks> /// 例子: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="commandParameters">SqlParamter数组</param> /// <returns>数据集</returns> public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Call the overload that takes a connection in place of the connection string return ExecuteDataTable(connection, commandType, commandText, commandParameters); } } /// <summary> /// 调用存储过程,返回数据集体 /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// DataTable dt = ExecuteDataTable(connString, "GetOrders", 24, 36); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程名字</param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> /// <returns>数据集</returns> public static DataTable ExecuteDataTable(string connectionString, string spName, params object[] parameterValues) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteDataTable(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteDataTable(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// 例子: /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <returns>数据集</returns> public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 返回DataTable /// </summary> /// <remarks> /// 例子: /// DataTalbe dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="commandParameters">SqlParamter数组</param> /// <returns>数据集</returns> public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); // Fill the DataSet using default values for DataTable names, etc da.Fill(dt); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); // Return the dataset return dt; } } /// <summary> ///调用存储过程,返回数据集 /// </summary> /// <remarks> /// 例子: /// DataTable dt = ExecuteDataTable(conn, "GetOrders", 24, 36); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程名字</param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> /// <returns>数据集</returns> public static DataTable ExecuteDataTable(SqlConnection connection, string spName, params object[] parameterValues) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteDataTable(connection, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteDataTable(connection, CommandType.StoredProcedure, spName); } } /// <summary> ///通过事务执行命令对象,返回数据集 /// </summary> /// <remarks> /// 例子: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <returns>数据集</returns> public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteDataTable(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 通过事务执行命令对象,返回数据集 /// </summary> /// <remarks> /// 例子: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="commandParameters">SqlParamter数组</param> /// <returns>数据集</returns> public static DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataSet using (SqlDataAdapter da = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); // Fill the DataSet using default values for DataTable names, etc da.Fill(dt); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); // Return the dataset return dt; } } /// <summary> /// 利用事务,通过SqlCommand执行存储过程 /// </summary> /// <remarks> /// 例子: /// DataTable dt = ExecuteDataTable(trans, "GetOrders", 24, 36); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="spName">存储过程名字</param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> /// <returns>数据集</returns> public static DataTable ExecuteDataTable(SqlTransaction transaction, string spName, params object[] parameterValues) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteDataTable(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteDataTable(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteDataTable #region ExecuteReader /// <summary> /// 决定连通接着关闭的方式 /// </summary> private enum SqlConnectionOwnership { /// <summary>连接属于SqlHelper并由它管理</summary> Internal, /// <summary>连接属于SqlHelper的调用者并由它管理</summary> External } /// <summary> /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior. /// </summary> /// <remarks> /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed. /// /// If the caller provided the connection, we want to leave it to them to manage. /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="transaction">合法的事务或null</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <param name="commandParameters">SqlParamters数组 to be associated with the command or 'null' if no parameters are required</param> /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param> /// <returns>SqlDataReader containing the results of the command</returns> private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) { if (connection == null) throw new ArgumentNullException("connection"); bool mustCloseConnection = false; // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create a reader SqlDataReader dataReader; // Call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == SqlConnectionOwnership.External) { dataReader = cmd.ExecuteReader(); } else { dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } // Detach the SqlParameters from the command object, so they can be used again. // HACK: There is a problem here, the output parameter values are fletched // when the reader is closed, so if the parameters are detached from the command // then the SqlReader can磘 set its values. // When this happen, the parameters can磘 be used again in other command. bool canClear = true; foreach (SqlParameter commandParameter in cmd.Parameters) { if (commandParameter.Direction != ParameterDirection.Input) canClear = false; } if (canClear) { cmd.Parameters.Clear(); } return dataReader; } catch { if (mustCloseConnection) connection.Close(); throw; } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// 例子: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null); } public static SqlDataReader ExecuteReader(CommandType commandType, string commandText) { return ExecuteReader(ConnectionString, commandType, commandText); } public static SqlDataReader ExecuteReader(CommandType commandType, string commandText, params SqlParameter[] commandParameters) { return ExecuteReader(ConnectionString, commandType, commandText, commandParameters); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <param name="commandParameters">SqlParamters数组 used to execute the command</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); SqlConnection connection = null; try { connection = new SqlConnection(connectionString); connection.Open(); // Call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(connection, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal); } catch { // If we fail to return the SqlDatReader, we need to close the connection ourselves if (connection != null) connection.Close(); throw; } } /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// SqlDataReader dr = ExecuteReader(connString, "GetOrders", 24, 36); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteReader(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// 例子: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <param name="commandParameters">SqlParamters数组 used to execute the command</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { // Pass through the call to the private overload using a null transaction value and an externally owned connection return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// SqlDataReader dr = ExecuteReader(conn, "GetOrders", 24, 36); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="spName">存储过程的名字</param> /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteReader(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// 例子: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <param name="commandParameters">SqlParamters数组 used to execute the command</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Pass through to private overload, indicating that the connection is owned by the caller return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// SqlDataReader dr = ExecuteReader(trans, "GetOrders", 24, 36); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="spName">存储过程的名字</param> /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteReader(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteReader #region ExecuteScalar /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// 例子: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <returns>对象</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null); } public static object ExecuteScalar(CommandType commandType, string commandText) { return ExecuteScalar(ConnectionString, commandType, commandText); } public static object ExecuteScalar(CommandType commandType, string commandText, params SqlParameter[] commandParameters) { return ExecuteScalar(ConnectionString, commandType, commandText, commandParameters); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <param name="commandParameters">SqlParamters数组 used to execute the command</param> /// <returns>对象</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Call the overload that takes a connection in place of the connection string return ExecuteScalar(connection, commandType, commandText, commandParameters); } } /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// int orderCount = (int)ExecuteScalar(connString, "GetOrderCount", 24, 36); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param> /// <returns>对象</returns> public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// 例子: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <returns>对象</returns> public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <param name="commandParameters">SqlParamters数组 used to execute the command</param> /// <returns>对象</returns> public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); // Execute the command & return the results object retval = cmd.ExecuteScalar(); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); return retval; } /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// int orderCount = (int)ExecuteScalar(conn, "GetOrderCount", 24, 36); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="spName">存储过程的名字</param> /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param> /// <returns>对象</returns> public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteScalar(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// 例子: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <returns>对象</returns> public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <param name="commandParameters">SqlParamters数组 used to execute the command</param> /// <returns>对象</returns> public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Execute the command & return the results object retval = cmd.ExecuteScalar(); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// int orderCount = (int)ExecuteScalar(trans, "GetOrderCount", 24, 36); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="spName">存储过程的名字</param> /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param> /// <returns>对象</returns> public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // PPull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteScalar(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteScalar #region ExecuteXmlReader /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// 例子: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令 using "FOR XML AUTO"</param> /// <returns>An XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令 using "FOR XML AUTO"</param> /// <param name="commandParameters">SqlParamters数组 used to execute the command</param> /// <returns>An XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); bool mustCloseConnection = false; // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); try { PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataTable XmlReader retval = cmd.ExecuteXmlReader(); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; } catch { if (mustCloseConnection) connection.Close(); throw; } } /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// XmlReader r = ExecuteXmlReader(conn, "GetOrders", 24, 36); /// </remarks> /// <param name="connection">A valid SqlConnection</param> /// <param name="spName">存储过程的名字 using "FOR XML AUTO"</param> /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param> /// <returns>An XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// 例子: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令 using "FOR XML AUTO"</param> /// <returns>An XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText) { // Pass through the call providing null for the set of SqlParameters return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="commandType">The CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程的名字或sql命令 using "FOR XML AUTO"</param> /// <param name="commandParameters">SqlParamters数组 used to execute the command</param> /// <returns>An XmlReader containing the resultset generated by the command</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // Create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataTable XmlReader retval = cmd.ExecuteXmlReader(); // Detach the SqlParameters from the command object, so they can be used again cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the specified /// SqlTransaction using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// XmlReader r = ExecuteXmlReader(trans, "GetOrders", 24, 36); /// </remarks> /// <param name="transaction">A valid SqlTransaction</param> /// <param name="spName">存储过程的名字</param> /// <param name="parameterValues">An array of objects to be assigned as the input values of the stored procedure</param> /// <returns>数据集</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { // Otherwise we can just call the SP without params return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteXmlReader #region FillDataset /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// 例子: /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名</param> public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataTable dataSet, string[] tableNames) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (dataSet == null) throw new ArgumentNullException("dataSet"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Call the overload that takes a connection in place of the connection string FillDataset(connection, commandType, commandText, dataSet, tableNames); } } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="commandParameters">SqlParamter数组</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名 /// </param> public static void FillDataset(string connectionString, CommandType commandType, string commandText, DataTable dataSet, string[] tableNames, params SqlParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (dataSet == null) throw new ArgumentNullException("dataSet"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Call the overload that takes a connection in place of the connection string FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters); } } /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a resultset) against the database specified in /// the connection string using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, 24); /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程名字</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名 /// </param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> public static void FillDataset(string connectionString, string spName, DataTable dataSet, string[] tableNames, params object[] parameterValues) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (dataSet == null) throw new ArgumentNullException("dataSet"); // Create & open a SqlConnection, and dispose of it after we are done using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); // Call the overload that takes a connection in place of the connection string FillDataset(connection, spName, dataSet, tableNames, parameterValues); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// 例子: /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名 /// </param> public static void FillDataset(SqlConnection connection, CommandType commandType, string commandText, DataTable dataSet, string[] tableNames) { FillDataset(connection, commandType, commandText, dataSet, tableNames, null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// 例子: /// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名 /// </param> /// <param name="commandParameters">SqlParamter数组</param> public static void FillDataset(SqlConnection connection, CommandType commandType, string commandText, DataTable dataSet, string[] tableNames, params SqlParameter[] commandParameters) { FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters); } /// <summary> ///调用存储过程,返回数据集 /// using the provided parameter values. This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// FillDataset(conn, "GetOrders", ds, new string[] {"orders"}, 24, 36); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程名字</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名 /// </param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> public static void FillDataset(SqlConnection connection, string spName, DataTable dataSet, string[] tableNames, params object[] parameterValues) { if (connection == null) throw new ArgumentNullException("connection"); if (dataSet == null) throw new ArgumentNullException("dataSet"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters); } else { // Otherwise we can just call the SP without params FillDataset(connection, CommandType.StoredProcedure, spName, dataSet, tableNames); } } /// <summary> /// 执行SqlCommand对象,返回数据集 /// </summary> /// <remarks> /// 例子: /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名 /// </param> public static void FillDataset(SqlTransaction transaction, CommandType commandType, string commandText, DataTable dataSet, string[] tableNames) { FillDataset(transaction, commandType, commandText, dataSet, tableNames, null); } /// <summary> ///执行SqlCommand对象,返回数据集 /// </summary> /// <remarks> /// 例子: /// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名 /// </param> /// <param name="commandParameters">SqlParamter数组</param> public static void FillDataset(SqlTransaction transaction, CommandType commandType, string commandText, DataTable dataSet, string[] tableNames, params SqlParameter[] commandParameters) { FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters); } /// <summary> /// 执行SqlCommand对象,返回数据集 /// </summary> /// <remarks> /// This method provides no access to output parameters or the stored procedure's return value parameter. /// /// 例子: /// FillDataset(trans, "GetOrders", ds, new string[]{"orders"}, 24, 36); /// </remarks> /// <param name="transaction">合法的事务</param> /// <param name="spName">存储过程名字</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名 /// </param> /// <param name="parameterValues">分配给存储过程作为输入参数的对象数组</param> public static void FillDataset(SqlTransaction transaction, string spName, DataTable dataSet, string[] tableNames, params object[] parameterValues) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (dataSet == null) throw new ArgumentNullException("dataSet"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If we receive parameter values, we need to figure out where they go if ((parameterValues != null) && (parameterValues.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Assign the provided values to these parameters based on parameter order AssignParameterValues(commandParameters, parameterValues); // Call the overload that takes SqlParamters数组 FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames, commandParameters); } else { // Otherwise we can just call the SP without params FillDataset(transaction, CommandType.StoredProcedure, spName, dataSet, tableNames); } } /// <summary> ///执行SqlCommand对象,返回数据集 /// <remarks> /// 例子: /// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="transaction">合法的事务</param> /// <param name="commandType">命令类型 (stored procedure, text, 等)</param> /// <param name="commandText">存储过程名字或sql命令</param> /// <param name="dataSet"数据集</param> /// <param name="tableNames">表名 /// </param> /// <param name="commandParameters">SqlParamter数组</param> private static void FillDataset(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, DataTable dataSet, string[] tableNames, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); if (dataSet == null) throw new ArgumentNullException("dataSet"); // Create a command and prepare it for execution SqlCommand command = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // Create the DataAdapter & DataTable using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command)) { // Add the table mappings specified by the user if (tableNames != null && tableNames.Length > 0) { string tableName = "Table"; for (int index = 0; index < tableNames.Length; index++) { 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"); dataAdapter.TableMappings.Add(tableName, tableNames[index]); tableName += (index + 1).ToString(); } } // Fill the DataTable using default values for DataTable names, etc dataAdapter.Fill(dataSet); // Detach the SqlParameters from the command object, so they can be used again command.Parameters.Clear(); } if (mustCloseConnection) connection.Close(); } #endregion //#region UpdateDataset ///// <summary> ///// 执行SqlCommand对象,返回数据集 ///// </summary> ///// <remarks> ///// 例子: ///// UpdateDataset(conn, insertCommand, deleteCommand, updateCommand, dataSet, "Order"); ///// </remarks> ///// <param name="insertCommand">插入命令对象</param> ///// <param name="deleteCommand">删除命令对象</param> ///// <param name="updateCommand">更新命令对象</param> ///// <param name="dataSet">数据集</param> ///// <param name="tableName">表名</param> //public static void UpdateDataset(SqlCommand insertCommand, SqlCommand deleteCommand, SqlCommand updateCommand, DataTable dataSet, string tableName) //{ // if (insertCommand == null) throw new ArgumentNullException("insertCommand"); // if (deleteCommand == null) throw new ArgumentNullException("deleteCommand"); // if (updateCommand == null) throw new ArgumentNullException("updateCommand"); // if (tableName == null || tableName.Length == 0) throw new ArgumentNullException("tableName"); // // Create a SqlDataAdapter, and dispose of it after we are done // using (SqlDataAdapter dataAdapter = new SqlDataAdapter()) // { // // Set the data adapter commands // dataAdapter.UpdateCommand = updateCommand; // dataAdapter.InsertCommand = insertCommand; // dataAdapter.DeleteCommand = deleteCommand; // // Update the dataset changes in the data source // dataAdapter.Update(dataSet, tableName); // // Commit all the changes made to the DataTable // dataSet.AcceptChanges(); // } //} //#endregion #region CreateCommand /// <summary> /// 创建命令对象 /// a stored procedure and optional parameters to be provided /// </summary> /// <remarks> /// 例子: /// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName"); /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="sourceColumns">提供给存储过程的作为列名的字符串数组</param> /// <returns>A valid SqlCommand object</returns> public static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // Create a SqlCommand SqlCommand cmd = new SqlCommand(spName, connection); cmd.CommandType = CommandType.StoredProcedure; // If we receive parameter values, we need to figure out where they go if ((sourceColumns != null) && (sourceColumns.Length > 0)) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Assign the provided source columns to these parameters based on parameter order for (int index = 0; index < sourceColumns.Length; index++) commandParameters[index].SourceColumn = sourceColumns[index]; // Attach the discovered parameters to the SqlCommand object AttachParameters(cmd, commandParameters); } return cmd; } #endregion #region ExecuteNonQueryTypedParams /// <summary> /// 调用存储过程 /// </summary> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>整型</returns> public static int ExecuteNonQueryTypedParams(String connectionString, String spName, DataRow dataRow) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// 调用存储过程Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified SqlConnection /// </summary> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>整型</returns> public static int ExecuteNonQueryTypedParams(SqlConnection connection, String spName, DataRow dataRow) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// 调用存储过程 /// </summary> /// <param name="transaction">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>整型</returns> public static int ExecuteNonQueryTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // Sf the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); } } #endregion #region ExecuteDatasetTypedParams /// <summary> ///调用存储过程 /// </summary> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>数据集</returns> public static DataSet ExecuteDatasetTypedParams(string connectionString, String spName, DataRow dataRow) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); //If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// 调用存储过程 /// </summary> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>数据集</returns> public static DataSet ExecuteDatasetTypedParams(SqlConnection connection, String spName, DataRow dataRow) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// 调用存储过程 /// </summary> /// <param name="transaction">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>数据集</returns> public static DataSet ExecuteDatasetTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteDataset(transaction, CommandType.StoredProcedure, spName); } } #endregion #region ExecuteReaderTypedParams /// <summary> /// 调用存储过程 /// </summary> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReaderTypedParams(String connectionString, String spName, DataRow dataRow) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// 调用存储过程 /// </summary> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteReader(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// 调用存储过程 /// </summary> /// <param name="transaction">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteReader(transaction, CommandType.StoredProcedure, spName); } } #endregion #region ExecuteScalarTypedParams /// <summary> /// Execute a stored procedure via a SqlCommand (that returns a 1x1 resultset) against the database specified in /// the connection string using the dataRow column values as the stored procedure's parameters values. /// This method will query the database to discover the parameters for the /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order. /// </summary> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>对象</returns> public static object ExecuteScalarTypedParams(String connectionString, String spName, DataRow dataRow) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// 返回数量 /// </summary> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>对象</returns> public static object ExecuteScalarTypedParams(SqlConnection connection, String spName, DataRow dataRow) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, spName); } } /// <summary> ///返回数量 /// </summary> /// <param name="transaction">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>对象</returns> public static object ExecuteScalarTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteScalar(transaction, CommandType.StoredProcedure, spName); } } #endregion #region ExecuteXmlReaderTypedParams /// <summary> /// 读取xml,返回XmlReader /// </summary> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>XmlReader</returns> public static XmlReader ExecuteXmlReaderTypedParams(SqlConnection connection, String spName, DataRow dataRow) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteXmlReader(connection, CommandType.StoredProcedure, spName); } } /// <summary> ///读取xml,返回XmlReader /// </summary> /// <param name="transaction">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="dataRow">数据行,保存着存储过程的参数值.</param> /// <returns>XmlReader</returns> public static XmlReader ExecuteXmlReaderTypedParams(SqlTransaction transaction, String spName, DataRow dataRow) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // If the row has values, the store procedure parameters must be initialized if (dataRow != null && dataRow.ItemArray.Length > 0) { // Pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache) SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection, spName); // Set the parameters values AssignParameterValues(commandParameters, dataRow); return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return SqlHelper.ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName); } } #endregion } /// <summary> /// SqlCommand缓存类 /// </summary> public sealed class SqlHelperParameterCache { #region private methods, variables, and constructors //Since this class provides only static methods, make the default constructor private to prevent //instances from being created with "new SqlHelperParameterCache()" private SqlHelperParameterCache() { } private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable()); /// <summary> /// 取出存储过程的参数列表 /// </summary> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="includeReturnValueParameter">是否包含返回值参数</param> /// <returns>SqlParameter数组</returns> private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); SqlCommand cmd = new SqlCommand(spName, connection); cmd.CommandType = CommandType.StoredProcedure; connection.Open(); SqlCommandBuilder.DeriveParameters(cmd); connection.Close(); if (!includeReturnValueParameter) { cmd.Parameters.RemoveAt(0); } SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; cmd.Parameters.CopyTo(discoveredParameters, 0); // Init the parameters with a DBNull value foreach (SqlParameter discoveredParameter in discoveredParameters) { discoveredParameter.Value = DBNull.Value; } return discoveredParameters; } /// <summary> /// 深度复制SqlParamter数组 /// </summary> /// <param name="originalParameters">原始SqlParameter数组</param> /// <returns>SqlParameter数组</returns> private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters) { SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length]; for (int i = 0, j = originalParameters.Length; i < j; i++) { clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone(); } return clonedParameters; } #endregion private methods, variables, and constructors #region caching functions /// <summary> /// 缓存参数数组 /// </summary> /// <param name="connectionString">合法的连接</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <param name="commandParameters">SqlParamters数组 to be cached</param> public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); string hashKey = connectionString + ":" + commandText; paramCache[hashKey] = commandParameters; } /// <summary> /// 从缓存获取SqlCommand数组 /// </summary> /// <param name="connectionString">合法的连接</param> /// <param name="commandText">存储过程的名字或sql命令</param> /// <returns>SqlParamters数组</returns> public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); string hashKey = connectionString + ":" + commandText; SqlParameter[] cachedParameters = paramCache[hashKey] as SqlParameter[]; if (cachedParameters == null) { return null; } else { return CloneParameters(cachedParameters); } } #endregion caching functions #region Parameter Discovery Functions /// <summary> /// 取出存储过程的参数集 /// </summary> /// <remarks> /// 查询数据库,并取出存储过程的参数集缓存起来 /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <returns>SqlParamters数组</returns> public static SqlParameter[] GetSpParameterSet(string connectionString, string spName) { return GetSpParameterSet(connectionString, spName, false); } /// <summary> /// 取出存储过程的参数集 /// </summary> /// <remarks> /// 查询数据库,并取出存储过程的参数集缓存起来 /// </remarks> /// <param name="connectionString">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param> /// <returns>SqlParamters数组</returns> public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); using (SqlConnection connection = new SqlConnection(connectionString)) { return GetSpParameterSetInternal(connection, spName, includeReturnValueParameter); } } /// <summary> /// 取出存储过程的参数集 /// </summary> /// <remarks> /// 查询数据库,并取出存储过程的参数集缓存起来 /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <returns>SqlParamters数组</returns> internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName) { return GetSpParameterSet(connection, spName, false); } /// <summary> /// 取出存储过程的参数集 /// </summary> /// <remarks> /// 查询数据库,并取出存储过程的参数集缓存起来 /// </remarks> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="includeReturnValueParameter">是否包含返回值参数</param> /// <returns>SqlParamters数组</returns> internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter) { if (connection == null) throw new ArgumentNullException("connection"); using (SqlConnection clonedConnection = (SqlConnection)((ICloneable)connection).Clone()) { return GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter); } } /// <summary> /// 取出存储过程的参数集 /// </summary> /// <param name="connection">合法的连接</param> /// <param name="spName">存储过程的名字</param> /// <param name="includeReturnValueParameter">A bool value indicating whether the return value parameter should be included in the results</param> /// <returns>SqlParamters数组</returns> private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); string hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : ""); SqlParameter[] cachedParameters; cachedParameters = paramCache[hashKey] as SqlParameter[]; if (cachedParameters == null) { SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter); paramCache[hashKey] = spParameters; cachedParameters = spParameters; } return CloneParameters(cachedParameters); } #endregion Parameter Discovery Functions } }
以上是sqlhelper.cs代码,下面有下载
在web.config中 加入
<appSettings> <!-- 连接字符串是否加密 --> <add key="ConStringEncrypt" value="false"/> <!-- 数据库连接字符串,(如果采用加密方式,上面一项要设置为true;加密工具,可在官方下载, 如果使用明文这样server=127.0.0.1;database=.....,上面则设置为false。 --> <add key="ConnectionString" value="server=127.0.0.1;database=acrossdb1;uid=sa;pwd=123456"/> <!--其它模块连接字符串,可以不断增加以便同一个项目支持连接多个数据库。如果没有,可以删除该行--> <add key="ConnectionString2" value="server=127.0.0.1;database=acrossdb2;uid=sa;pwd=123456"/> <!--虚拟目录名称(如果是站点,则为空) --> <add key="VirtualPath" value=""/> <!--登录页地址 --> <add key="LoginPage" value="admin/Login.aspx"/> <!--默认菜单是否是展开状态--> <add key="MenuExpanded" value="false"/> <!--实体对象内容缓村的时间(分钟)--> <add key="ModelCache" value="30"/> </appSettings>
- SqlHelper.rar (8.7 KB)
- 下载次数: 9
相关推荐
C# asp.net mvc 都可以运用(SqlHelper数据库操作辅助类)下载下来直接贴到项目就能直接调用,是本人自己编写,适用于大众
摘要:Data Access Application Block 是一个 .NET 组件,包含优化的数据访问代码,可以帮助用户调用存储过程以及向 SQL Server 数据库发出 SQL 文本命令。它返回 SqlDataReader、DataSet 和 XmlReader 对象。您可以...
自己写的类库 方便网站开发 希望能和大家一起分享! 其中 SQLHelper.cs这个网上都能下的 baseljh.cs 是基础类 news.cs 继承了baseljh.cs基类 news.cs是和数据库的数据表new对应...希望能给初学asp.net的朋友一点帮助
SQL访问基础类 SqlHelper.cs 执行带参数SQL,非带参数SQL,存储过程等语句 Access操作辅助类 JetAccess.cs Access的新建,压缩,加密等数据库文件操作 Oledb操作辅助类 OleDbHelper.cs 使用OleDb数据源执行SQL语句 ?...
1.DataTable帮助类(DataTableHelper.cs) 2.Access数据库文件操作辅助类(JetAccessUtil.cs) 5.查询条件组合辅助类(SearchCondition.cs) 6.查询信息实体类(SearchInfo.cs) 8.Sql命令操作函数(可用于安装程序的...
本网上商城采用ASP.NET Web应用程序的典型架构模式:数据库——数据访问层——业务逻辑层——页面层,各层的具体说明如下: (1) 数据库是应用系统的最底层,用来保存系统的所有数据,本系统采用SQL SERVER 2008...
API函数.cs 、asp.net技术精粹.chm、DataAccess.cs、DbHelperSQL.cs、Encoder.cs、Oracle数据库操作.cs、SQLHelper.cs、加密和解密.cs、农历.cs、人民币转换.cs、文本文件读取.cs 等等,诸多实用工具类
XMLMessage 使用XML技术打造的留言板范例: DatabaseMessage 使用数据库打造的留言板范例 <br>第21章(\C21) 示例描述:本章利用数据库访问助手SqlHelper.cs,演示了6种对数据库的不同操作。...
网站很漂亮、很大气,刚做完的毕业设计 数据库在FunkMusicInstall里面 代码在Funk Music里面 FunkMusicInstall里面的安装...然后用VS2010打开项目,修改SqlHelper.cs和Web.config这2个文件中连接数据库的代码就可以了
XMLMessage 使用XML技术打造的留言板范例: DatabaseMessage 使用数据库打造的留言板范例 <br>第21章(\C21) 示例描述:本章利用数据库访问助手SqlHelper.cs,演示了6种对数据库的不同操作。...
本系统的数据访问层是由SQLHelper.cs类来实现的。 业务层: 与系统的业务逻辑相关连,它调用数据层实现访问数据库的功能的同时也为上一层提供访问数据库的接口或方法等服务。 表示层: 实现了系统呈现给用户的...
本系统的数据访问层是由SQLHelper.cs类来实现的。 业务层:与系统的业务逻辑相关连,它调用数据层实现访问数据库的功能的同时也为上一层提供访问数据库的接口或方法等服务。 表示层:实现了系统呈现给用户的界面,与...