微软的SqlHelper做数据层(一)
2007-05-20 11:37
CreateCommand创建命令#region CreateCommand创建命令 /** <summary> /// 创建一个由存储过程提供的命令 /// </summary> /// <remarks> /// e.g.: /// SqlCommand command = CreateCommand(conn, "AddCustomer", "CustomerID", "CustomerName"); /// </remarks> /// <param name="connection">一个合法的连接</param> /// <param name="spName">存储过程名</param> /// <param name="sourceColumns">源列名称数组</param> /// <returns>一个合法的命令</returns> internal static SqlCommand CreateCommand(SqlConnection connection, string spName, params string[] sourceColumns) { if (connection == null) throw new ArgumentException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentException("spName"); //创建一个命令 SqlCommand cmd = new SqlCommand(spName, connection); cmd.CommandType = CommandType.StoredProcedure; //如果接受一个参数,则处理它 if ((sourceColumns != null) && (sourceColumns.Length > 0)) { //提取存储过程参数 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); //设置源列的名称 for (int index = 0; index < sourceColumns.Length; index++) commandParameters[index].SourceColumn = sourceColumns[index]; //将参数附加到命令上 AttachParameters(cmd, commandParameters); } return cmd; } #endregion /** <summary> /// 这个方法将一个数组的值赋值到一个SqlParameter数组 /// </summary> /// <param name="commandParameters">要被赋值的SqlParameter数组</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("参数的个数不能匹配参数值的个数"); } //迭代参数数组,把object数组的值赋给相应的参数 for (int i = 0, j = commandParameters.Length; i < j; i++) { //如果数组的值继承自IDbDataParameter,这是赋给它的属性值 if (parameterValues[i] is IDbDataParameter) { IDbDataParameter paraInstance = (IDbDataParameter)parameterValues[i]; if (paraInstance.Value == null) { commandParameters[i].Value = DBNull.Value; } else { commandParameters[i].Value = paraInstance.Value; } } else if (parameterValues[i] == null) { commandParameters[i].Value = DBNull.Value; } else { commandParameters[i].Value = parameterValues[i]; } } } //--上面的这个也不错,--其他的我也分析清楚了但是感觉一个晕为什么呢~~这个类的重载好多阿,- -不太喜欢故而不写出来- - SqlHelperParameterCache- -一般就不要用了,浪费内存但是某些特殊情况不如,自动生成数据实体的时候- -瓦塞塞,好用级了,- -可以通过这家伙生成所有存储过程的实体,- -某些程序就是这么干的- -我们先来看看 /** <summary> /// SqlHelperParameterCache 提供一些函数用来发现存储过程的参数 /// </summary> internal sealed class SqlHelperParameterCache { private methods, variables, and constructors#region private methods, variables, and constructors //这个类仅仅提供静态方法,并使用一个私有的构造器来阻止创建一个实例化对象 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>被发现的参数数组</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); // 将参数置为DBNull.Value foreach (SqlParameter discoveredParameter in discoveredParameters) { discoveredParameter.Value = DBNull.Value; } return discoveredParameters; } /** <summary> /// 深度拷贝参数数组 /// </summary> /// <param name="originalParameters"></param> /// <returns></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 caching functions#region caching functions /** <summary> /// 将参数数组添加到缓存 /// </summary> /// <param name="connectionString">一个合法的连接字符串</param> /// <param name="commandText">命令文本</param> /// <param name="commandParameters">被缓存的参数数组</param> internal 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> /// 从缓存中提取参数 /// </summary> /// <param name="connectionString">一个合法的连接字符串</param> /// <param name="commandText">命令文本</param> /// <returns>参数数组</returns> internal 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 Parameter Discovery Functions#region Parameter Discovery Functions /** <summary> /// 获取存储过程的参数 /// </summary> /// <remarks> /// 这个方法将查询数据库,并将其放置在缓存中 /// </remarks> /// <param name="connectionString">一个合法的连接</param> /// <param name="spName">存储过程名</param> /// <returns>数组参数</returns> internal 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">是否在结果中返回参数值</param> /// <returns>参数数组</returns> internal 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>参数数组</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>参数数组</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">是否在结果中返回参数值</param> /// <returns>参数数组</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 } |