using System; using System.Data; using System.Xml; using System.Data.SqlClient; using System.Configuration; using System.Collections;
namespace SqlServerDAL { public sealed class SqlHelper { public static readonly string conn = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString; public static SqlTransaction getTran() { SqlConnection cn = new SqlConnection(SqlHelper.conn);
SqlTransaction tran = null; cn.Open(); tran = cn.BeginTransaction(); return tran;
} #region private utility methods & constructors /// /// 将命令和一组参数对象联系起来,给输出类型参数对象赋空值 /// ///命令对象 ///参数数组 private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { foreach (SqlParameter par in commandParameters) { if ((par.Direction == ParameterDirection.InputOutput) && (par.Value == null)) { par.Value = DBNull.Value; } command.Parameters.Add(par); } } /// /// 对一组参数对象赋值 /// ///参数对象 ///参数值 public static void AssignParameterValues(SqlParameter[] commandParameters, params object[] parameterValues) { if ((parameterValues == 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] != null && (commandParameters[i].Direction == ParameterDirection.Input || commandParameters[i].Direction == ParameterDirection.InputOutput)) { commandParameters[i].Value = parameterValues[i]; } } } /// /// 对一组参数对象赋值 /// /// /// public static void AssignParameterValues(SqlParameter[] commandParameters, Hashtable parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { return; } if (commandParameters.Length != parameterValues.Count) { throw new ArgumentException("parameter count does not mathc prameter value coount."); } for (int i = 0, j = parameterValues.Count; i < j; i++) { if (parameterValues[commandParameters[i].ParameterName] != null && (commandParameters[i].Direction == ParameterDirection.Input || commandParameters[i].Direction == ParameterDirection.InputOutput)) { commandParameters[i].Value = parameterValues[commandParameters[i].ParameterName]; } } } /// /// 将一组COMMAND相关对象关联 /// /// /// /// /// /// /// private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters) { if (connection.State != ConnectionState.Open) { connection.Open(); } command.CommandType = commandType; command.CommandText = commandText; command.Connection = connection; if (transaction != null) {
command.Transaction = transaction; } if (commandParameters != null) { AttachParameters(command, commandParameters); } }
#endregion #region ExecuteNonQuery public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) { return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); } 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); } } public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) { if ((parameterValues != null) && (parameterValues.Length > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); SqlHelper.AssignParameterValues(commandParameters, parameterValues); return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); } } public static int ExecuteNonQuery(string connectionString, string spName, Hashtable parameterValues) { if ((parameterValues != null) && (parameterValues.Count > 0)) { SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); AssignParameterValues(commandParameters, parameterValues); return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); } } public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null); } 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; } 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); } } public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null); } 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; } 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 #region ExecuteDataSet public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) { return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null); } 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); } } 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); } } public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null); } 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; } 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); } } public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null); } 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; } 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 #region ExecuteReader private enum sqlConnectionOwnership { Internal, External } 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; } public static SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) { return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null); } 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 { cn.Close(); throw; } } 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); } } public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null); } public static SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, sqlConnectionOwnership.External); } 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); } } public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null); } public static SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, sqlConnectionOwnership.External); } 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 #region ExecuteScalar public static object ExecuteScalar(string connectionString, CommandType commandType, string commandText) { return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null); } 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); } } 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); } } public static object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null); } 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 retValue = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return retValue; } 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); } } public static object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null); } 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; } 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 } public sealed class SqlHelperParameterCache { #region private methods,variables,and conestructors //类提供的都是静态方法，将默认构造函数设置为私有的以便阻止利用"new SqlHelperParameterCache()"来实例化类 private SqlHelperParameterCache() { } //存储过程参数缓存导HashTable中 private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable()); /**/ /// /// resolve at run time the appropriate set of SqlParameters for a stored procedure /// 在运行时得到一个存储过程的一系列参数信息 /// ///a valid connection string for a SqlConnection ///一个连接对象的有效连接串 ///the name of the stored procedure ///存储过程名 ///是否有返回值参数 ///参数对象数组，存储过程的所有参数信息 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; //从sqlcommand指定的存储过程中检索信息，并填充指定的sqlcommand对象的Parameters集合中。 SqlCommandBuilder.DeriveParameters(cmd); if (!includeReturnValueParameter) { //移除第一个参数对象，因为没有返回值，而默认情况下，第一个参数对象是返回值 cmd.Parameters.RemoveAt(0); } SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count]; cmd.Parameters.CopyTo(discoveredParameters, 0); return discoveredParameters;
} } }
//复制缓存参数数据（克隆） private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters) { SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length]; for (int i = 0; i < originalParameters.Length; i++) { clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone(); } return clonedParameters; } #endregion #region caching functions /// ///将参数数组添加到缓存中 /// ///有效的连接串 ///一个存储过名程名或者T-SQL命令 ///一个要被缓存的参数数组 public static void CacheParameterSet(string connectionString, string commandText, params SqlParameter[] commandParameters) { string hashKey = connectionString + ":" + commandText; paramCache[hashKey] = commandParameters; } /// /// 从缓存中获得参数对象数组 /// ///有效的连接串 ///一个存储过程名或者T-SQL命令 ///一个参数对象数组 public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText) { string hashKey = connectionString + ":" + commandText; SqlParameter[] cacheParameters = (SqlParameter[])paramCache[hashKey]; if (cacheParameters == null) { //如果缓存中没有参数，返回空 return null; } else { //返回参数的副本 return CloneParameters(cacheParameters); } } #endregion #region Parameter Discovery Functions /// /// 获得存储过程的参数集 /// /// /// 这个方法从数据库中获得信息，并将之存储在缓存，以便以后使用 /// ///有效的连接串 ///一个存储过程名或者T-SQL命令 ///一个参数对象数组 public static SqlParameter[] GetSpParameterSet(string connectionString, string spName) { return GetSpParameterSet(connectionString, spName, false); }
/// /// 获得存储过程的参数集 /// /// /// 这个方法从数据库中获得信息，并将之存储在缓存，以便之后的使用 /// ///a valid connection string for a SqlConnection ///the name of the stored procedure ///a bool value indicating whether the return value parameter should be included in the results ///an array of SqlParameters ///有效的连接串 ///一个存储过程名 /// ///是否有返回值参数 ///一个参数对象数组 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 } }
