共享一个ADO.NET用到的SqlHelper类
2011-01-07 17:51 音乐让我说 阅读(541) 评论(0) 编辑 收藏 举报代码如下:
// =================================================================== // 项目说明 //==================================================================== // 文件: SqlHelper.cs // 项目名称:项目管理 // 创建时间:2010-3-25 // =================================================================== using System; using System.Data; using System.Xml; using System.Data.SqlClient; using System.Collections; using System.Configuration; namespace EatCMS.SQLDAL { /// <summary> /// SQL助手类 /// </summary> public sealed class SqlHelper { #region 私有方法 /// <summary> /// 私有构造方法,不能被实例化 /// </summary> private SqlHelper() { } /// <summary> /// 从web.config文件中得到连接字符串 /// </summary> public static string GetConnStringFromWebConfig() { return ConfigurationManager.ConnectionStrings["EatCMSConnString"].ConnectionString; } /// <summary>给 SqlCommand 传递类型为 SqlParameters 数组的参数</summary> /// <param name="command">需要添加参数的SqlCommand</param> /// <param name="commandParameters">SqlParameters 数组</param> private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { foreach (SqlParameter p in commandParameters) { if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } /// <summary> /// 把对象数组中的值分别赋给 SqlParameter 数组 /// </summary> /// <param name="commandParameters">需要添加值的 SqlParameter 数组</param> /// <param name="parameterValues">赋予 SqlParameter 数组的对象数组</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++) { commandParameters[i].Value = parameterValues[i]; } } /// <summary> /// 给 SqlCommand 对象赋予 SqlConnection 对象、SqlTransaction 对象等信息 /// </summary> /// <param name="command">SqlCommand 对象</param> /// <param name="connection">连接对象</param> /// <param name="transaction">一个合法的事务对象或'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数数组,可以为'null'</param> private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters) { if (connection.State != ConnectionState.Open) { connection.Open(); } command.Connection = connection; command.CommandText = commandText; if (transaction != null) { command.Transaction = transaction; } command.CommandType = commandType; if (commandParameters != null) { AttachParameters(command, commandParameters); } return; } #endregion #region ExecuteNonQuery /// <summary> /// 执行不是查询的命令 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) { return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 执行不是查询的命令 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数数组</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); return ExecuteNonQuery(cn, commandType, commandText, commandParameters); } } /// <summary> /// 执行不是查询的命令 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// 执行不是查询的命令 /// </summary> /// <param name="connection">连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 执行不是查询的命令 /// </summary> /// <param name="connection">连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数数组</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); int retval = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return retval; } /// <summary> /// 执行不是查询的命令 /// </summary> /// <param name="connection">连接对象</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// 执行不是查询的命令 /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 执行不是查询的命令 /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数数组</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); int retval = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); return retval; } /// <summary> /// 执行不是查询的命令 /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="spName">存储过程的名称</param> /// <param name="parameterValues">参数数组数组,可以为'null'</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteNonQuery #region ExecuteDataSet /// <summary> /// 将数据填充到 DataSet 中去 /// </summary> /// <param name="connection">连接对象</param> /// <param name="ds">将要被填充数据的数据集对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数数组</param> public static void ExecuteDataset(SqlConnection connection,DataSet ds, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(cmd); for (int i = 0; i < ds.Tables.Count; ++i) { if ( i == 0 ) { da.TableMappings.Add("Table", ds.Tables[i].TableName); } else { da.TableMappings.Add(string.Concat("Table", i.ToString()), ds.Tables[i].TableName); } } da.Fill(ds); cmd.Parameters.Clear(); } /// <summary> /// 将数据填充到 DataSet 中去 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="ds">将要被填充数据的数据集对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数数组</param> public static void ExecuteDataset(string connectionString, DataSet ds, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, cn, (SqlTransaction)null, commandType, commandText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(cmd); for (int i = 0; i < ds.Tables.Count; ++i) { if ( i == 0 ) { da.TableMappings.Add("Table", ds.Tables[i].TableName); } else { da.TableMappings.Add(string.Concat("Table", i.ToString()), ds.Tables[i].TableName); } } da.Fill(ds); cmd.Parameters.Clear(); } } /// <summary> /// 执行命令返回 DataSet 对象 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>DataSet 对象</returns> public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) { return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 执行命令返回 DataSet 对象 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数数组</param> /// <returns>DataSet 对象</returns> public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); return ExecuteDataset(cn, commandType, commandText, commandParameters); } } /// <summary> /// 执行命令返回 DataSet 对象 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>DataSet 对象</returns> public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// 执行命令返回 DataSet 对象 /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>DataSet 对象</returns> public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 执行命令返回 DataSet 对象 /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数数组</param> /// <returns>DataSet 对象</returns> public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cmd.Parameters.Clear(); return ds; } /// <summary> /// 执行命令返回 DataSet 对象 /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>DataSet 对象</returns> public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteDataset(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// 执行命令返回 DataSet 对象 /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>DataSet 对象</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 执行命令返回 DataSet 对象 /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数</param> /// <returns>DataSet 对象</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cmd.Parameters.Clear(); return ds; } /// <summary> /// 执行命令返回 DataSet 对象 /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">参数,可以为'null'</param> /// <returns>DataSet 对象</returns> public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteDataset(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteDataSet #region ExecuteReader /// <summary> ///这个枚举用来指出连接(Connection)对象是由调用者维护,还是由SqlHelper来维护 /// </summary> private enum SqlConnectionOwnership { /// <summary>连接有 SqlHelper 类来管理</summary> Internal, /// <summary>连接由调用者来管理</summary> External } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="connection">合法的连接</param> /// <param name="transaction">合法的事务,可以为 'null'</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数,可以为'null'</param> /// <param name="connectionOwnership">指出连接(Connection)对象是由调用者维护,还是由SqlHelper来维护</param> /// <returns>SqlDataReader 对象</returns> private static SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters); SqlDataReader dr; if (connectionOwnership == SqlConnectionOwnership.External) { dr = cmd.ExecuteReader(); } else { dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } cmd.Parameters.Clear(); return dr; } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) { return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数</param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlConnection cn = new SqlConnection(connectionString); cn.Open(); try { return ExecuteReader(cn, null, commandType, commandText, commandParameters,SqlConnectionOwnership.Internal); } catch { //如果我们返回 SqlDatReader 对象时失败了, 我们需要自己关闭连接 cn.Close(); throw; } } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(string connectionString, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteReader(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数</param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(SqlConnection connection, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteReader(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数</param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /// <summary> /// 得到 SqlDataReader /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>SqlDataReader 对象</returns> public static SqlDataReader ExecuteReader(SqlTransaction transaction, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteReader(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteReader(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteReader #region ExecuteScalar /// <summary> /// 得到数据库返回的表的第一行第一列的值 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>值</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText) { return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 得到数据库返回的表的第一行第一列的值 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数</param> /// <returns>值</returns> public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); return ExecuteScalar(cn, commandType, commandText, commandParameters); } } /// <summary> /// 得到数据库返回的表的第一行第一列的值 /// </summary> /// <param name="connectionString">合法的连接字符串</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>值</returns> public static object ExecuteScalar(string connectionString, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteScalar(connectionString, CommandType.StoredProcedure, spName); } } /// <summary> /// 得到数据库返回的表的第一行第一列的值 /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>值</returns> public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 得到数据库返回的表的第一行第一列的值 /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数</param> /// <returns>值</returns> public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); object retval = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return retval; } /// <summary> /// 得到数据库返回的表的第一行第一列的值 /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>值</returns> public static object ExecuteScalar(SqlConnection connection, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteScalar(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteScalar(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// 得到数据库返回的表的第一行第一列的值 /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <returns>值</returns> public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 得到数据库返回的表的第一行第一列的值 /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句</param> /// <param name="commandParameters">参数</param> /// <returns>值</returns> public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); object retval = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return retval; } /// <summary> /// 得到数据库返回的表的第一行第一列的值 /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>值</returns> public static object ExecuteScalar(SqlTransaction transaction, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteScalar(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteScalar(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteScalar #region ExecuteXmlReader /// <summary> /// 返回包含结果集的 XmlReader /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句,利用 "FOR XML AUTO"</param> /// <returns>XmlReader 对象</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 返回包含结果集的 XmlReader /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句,利用 "FOR XML AUTO"</param> /// <param name="commandParameters">参数</param> /// <returns>XmlReader 对象</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); XmlReader retval = cmd.ExecuteXmlReader(); cmd.Parameters.Clear(); return retval; } /// <summary> /// 返回包含结果集的 XmlReader /// </summary> /// <param name="connection">合法的连接对象</param> /// <param name="spName">存储过程名, 利用 "FOR XML AUTO"</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>XmlReader 对象</returns> public static XmlReader ExecuteXmlReader(SqlConnection connection, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteXmlReader(connection, CommandType.StoredProcedure, spName); } } /// <summary> /// 返回包含结果集的 XmlReader /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句,利用 "FOR XML AUTO"</param> /// <returns>XmlReader 对象</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// 返回包含结果集的 XmlReader /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="commandType">命名类型:存储过程类型或T-SQL语句类型等等</param> /// <param name="commandText">存储过程名或T-SQL语句,利用 "FOR XML AUTO"</param> /// <param name="commandParameters">参数</param> /// <returns>XmlReader 对象</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); XmlReader retval = cmd.ExecuteXmlReader(); cmd.Parameters.Clear(); return retval; } /// <summary> /// 返回包含结果集的 XmlReader /// </summary> /// <param name="transaction">合法的事务对象</param> /// <param name="spName">存储过程名</param> /// <param name="parameterValues">为存储过程提供的输入型的参数数组,可以为'null'</param> /// <returns>XmlReader 对象</returns> public static XmlReader ExecuteXmlReader(SqlTransaction transaction, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteXmlReader(transaction, CommandType.StoredProcedure, spName); } } #endregion ExecuteXmlReader } /// <summary> ///Sql参数缓存类提供了一个功能给存储过程的参数,以实现杠杆作用,在运行时可以发现存储过程的参数 /// </summary> public sealed class SqlHelperParameterCache { #region 私有方法、变量、构造函数 private SqlHelperParameterCache() { } private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable()); /// <summary> /// resolve at run time the appropriate set of SqlParameters for a stored procedure /// </summary> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="spName">the name of the stored procedure</param> /// <param name="includeReturnValueParameter">whether or not to include their return value parameter</param> /// <returns></returns> private static SqlParameter[] DiscoverSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter) { using (SqlConnection cn = new SqlConnection(connectionString)) using (SqlCommand cmd = new SqlCommand(spName,cn)) { cn.Open(); cmd.CommandType = CommandType.StoredProcedure; SqlCommandBuilder.DeriveParameters(cmd); if (!includeReturnValueParameter) { cmd.Parameters.RemoveAt(0); } SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];; cmd.Parameters.CopyTo(discoveredParameters, 0); return discoveredParameters; } } //deep copy of cached SqlParameter array 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 缓存函数 /// <summary> /// add parameter array to the cache /// </summary> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters to be cached</param> public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters) { string hashKey = connectionString + ":" + commandText; paramCache[hashKey] = commandParameters; } /// <summary> /// retrieve a parameter array from the cache /// </summary> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an array of SqlParamters</returns> public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText) { string hashKey = connectionString + ":" + commandText; SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey]; if (cachedParameters == null) { return null; } else { return CloneParameters(cachedParameters); } } #endregion caching functions #region 参数识别函数 /// <summary> /// Retrieves the set of SqlParameters appropriate for the stored procedure /// </summary> /// <remarks> /// This method will query the database for this information, and then store it in a cache for future requests. /// </remarks> /// <param name="connectionString">合法的连接字符串</param> /// <param name="spName">存储过程名</param> /// <returns>an array of SqlParameters</returns> public static SqlParameter[] GetSpParameterSet(string connectionString, string spName) { return GetSpParameterSet(connectionString, spName, false); } /// <summary> /// Retrieves the set of SqlParameters appropriate for the stored procedure /// </summary> /// <remarks> /// This method will query the database for this information, and then store it in a cache for future requests. /// </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>an array of SqlParameters</returns> public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter) { string hashKey = connectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter":""); SqlParameter[] cachedParameters; cachedParameters = (SqlParameter[])paramCache[hashKey]; if (cachedParameters == null) { cachedParameters = (SqlParameter[])(paramCache[hashKey] = DiscoverSpParameterSet(connectionString, spName, includeReturnValueParameter)); } return CloneParameters(cachedParameters); } #endregion Parameter Discovery Functions } }
下载链接:https://files.cnblogs.com/Music/ado-net-sqlhelper-from-foreign-programmer.rar
谢谢浏览!
作者:音乐让我说(音乐让我说 - 博客园)
出处:http://music.cnblogs.com/
文章版权归本人所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。