SqlHelper:
//---- SqlHelper- -主角要出場了- -怎麼說呢下面的這個我看還不錯哈
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
}
//--如果您認真看的上面有個提問為什麼要克隆- -這是利用原型設計模式，克隆一個拷貝然後直接用，如果您是用來做儲存過程的實體~~就不必克隆了--省記憶體
//--接下來改寫下- -原創阿
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace Ajaxren
{
/**//// <summary>
/// 快取引數用的
/// </summary>
public class SqlParametersCache : System.Collections.Generic.Dictionary<string, SqlParameter[]>
{
public static SqlParametersCache t = new SqlParametersCache();
//--單例構造模式
SqlParametersCache()
{
}
/**//// <summary>
/// 返回物件的例項----因為本例是單例構造模式
/// </summary>
public static SqlParametersCache GetSqlParametersContent
{
get
{
return t;
}
}
/**//// <summary>
/// 檢索緩衝引數,檢索的最終形式為ConnectionString,SqlCommandText
/// </summary>
/// <param name="ConnectionString">連線資料庫的字串</param>
/// <param name="SqlCommandText">儲存過程的名稱</param>
/// <param name="includeReturnValueParameter">是否包含一個ReturnValue的返回資料~~預設的~~討厭~~~~</param>
/// <param name="return">SqlParameter[]返回一個引數集合</param>
public SqlParameter[] GetCatchSqlParameter(string ConnectionString, string SqlCommandText, bool includeReturnValueParameter)
{
string c = "";
if (ConnectionString == null) throw new ArgumentNullException("請輸入你的連線字串");
if (SqlCommandText == null || SqlCommandText.Length == 0) throw new ArgumentNullException("請輸入你的儲存過程名字");
//--檢查集合裡面是否有指定資料
if (this.ContainsKey(ConnectionString + ":" + SqlCommandText) != false)
{
//--有資料的是在返回的時候在判斷this[ConnectionString + ":" + SqlCommandText]key裡面包含的資料如果不存在--呼叫this.GoToSqlSererSelectParameter在次找到該資料
if (this[ConnectionString + ":" + SqlCommandText] == null)
{
//--再次緩衝這個物件咯~~-呼叫this.GoToSqlSererSelectParameter找到引數
this[ConnectionString + ":" + SqlCommandText] = this.GoToSqlSererSelectParameter(ConnectionString, SqlCommandText, includeReturnValueParameter);
}
//--返回資料
return Cps(this[ConnectionString + ":" + SqlCommandText]);
}
else//-不存在再次緩衝-----key--------------------------------------value-呼叫this.GoToSqlSererSelectParameter找到引數
this.Add(ConnectionString + ":" + SqlCommandText, this.GoToSqlSererSelectParameter(ConnectionString, SqlCommandText, includeReturnValueParameter));
return this[ConnectionString + ":" + SqlCommandText];
}
/**//// <summary>
/// 去Sql中檢索儲存過程的引數
/// </summary>
/// <param name="ConnectionString">檢索的字串</param>
/// <param name="SqlCommandText">儲存過程的名稱</param>
/// <param name="includeReturnValueParameter">是否包含一個ReturnValue的返回資料~~預設的~~討厭~~~~</param>
private SqlParameter[] GoToSqlSererSelectParameter(string ConnectionString, string SqlCommandText, bool includeReturnValueParameter)
{
SqlConnection BugConnection = new SqlConnection(ConnectionString);
SqlCommand BugCommand = new SqlCommand(SqlCommandText, BugConnection);
try
{
BugCommand.CommandType = CommandType.StoredProcedure;
BugConnection.Open();
//----反向去SqlServer-找到引數----寫入到--BugCommand裡面
SqlCommandBuilder.DeriveParameters(BugCommand);
}
catch (SqlException)
{
}
finally
{
BugConnection.Close();
}
//---false不儲存刪除第一個引數--這個引數是ParameterDirection.ReturnValue型別
if (!includeReturnValueParameter)
{
if (BugCommand.Parameters[0].Direction == ParameterDirection.ReturnValue)
{
BugCommand.Parameters.RemoveAt(0);
}
}
//--是否包含返回引數
SqlParameter[] discoveredParameters = new SqlParameter[BugCommand.Parameters.Count];
//---將引數拷貝到引數集合中--要拷貝到的引數集合--從指定目標陣列索引處開始
BugCommand.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value--將物件復空值
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
try
{
discoveredParameter.Value = DBNull.Value;
}
catch (System.Exception)
{
}
}
return Cps(discoveredParameters);
}
//--作用建立物件的副本
private static SqlParameter[] Cps(SqlParameter[] originalParameters)
{
SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length];
for (int i = 0, j = originalParameters.Length; i < j; i++)
{
//----複製建構函式，以便使用當前例項的值初始化Parameter 類的新例項。
clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone();
}
return clonedParameters;
}
}
}
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 }//--如果您認真看的上面有個提問為什麼要克隆- -這是利用原型設計模式，克隆一個拷貝然後直接用，如果您是用來做儲存過程的實體~~就不必克隆了--省記憶體
//--接下來改寫下- -原創阿
using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;namespace Ajaxren{ /**//// <summary> /// 快取引數用的 /// </summary> public class SqlParametersCache : System.Collections.Generic.Dictionary<string, SqlParameter[]> { public static SqlParametersCache t = new SqlParametersCache(); //--單例構造模式 SqlParametersCache() { } /**//// <summary> /// 返回物件的例項----因為本例是單例構造模式 /// </summary> public static SqlParametersCache GetSqlParametersContent { get { return t; } } /**//// <summary> /// 檢索緩衝引數,檢索的最終形式為ConnectionString,SqlCommandText /// </summary> /// <param name="ConnectionString">連線資料庫的字串</param> /// <param name="SqlCommandText">儲存過程的名稱</param> /// <param name="includeReturnValueParameter">是否包含一個ReturnValue的返回資料~~預設的~~討厭~~~~</param> /// <param name="return">SqlParameter[]返回一個引數集合</param> public SqlParameter[] GetCatchSqlParameter(string ConnectionString, string SqlCommandText, bool includeReturnValueParameter) { string c = ""; if (ConnectionString == null) throw new ArgumentNullException("請輸入你的連線字串"); if (SqlCommandText == null || SqlCommandText.Length == 0) throw new ArgumentNullException("請輸入你的儲存過程名字"); //--檢查集合裡面是否有指定資料 if (this.ContainsKey(ConnectionString + ":" + SqlCommandText) != false) { //--有資料的是在返回的時候在判斷this[ConnectionString + ":" + SqlCommandText]key裡面包含的資料如果不存在--呼叫this.GoToSqlSererSelectParameter在次找到該資料 if (this[ConnectionString + ":" + SqlCommandText] == null) { //--再次緩衝這個物件咯~~-呼叫this.GoToSqlSererSelectParameter找到引數 this[ConnectionString + ":" + SqlCommandText] = this.GoToSqlSererSelectParameter(ConnectionString, SqlCommandText, includeReturnValueParameter); } //--返回資料 return Cps(this[ConnectionString + ":" + SqlCommandText]); } else//-不存在再次緩衝-----key--------------------------------------value-呼叫this.GoToSqlSererSelectParameter找到引數 this.Add(ConnectionString + ":" + SqlCommandText, this.GoToSqlSererSelectParameter(ConnectionString, SqlCommandText, includeReturnValueParameter)); return this[ConnectionString + ":" + SqlCommandText]; } /**//// <summary> /// 去Sql中檢索儲存過程的引數 /// </summary> /// <param name="ConnectionString">檢索的字串</param> /// <param name="SqlCommandText">儲存過程的名稱</param> /// <param name="includeReturnValueParameter">是否包含一個ReturnValue的返回資料~~預設的~~討厭~~~~</param> private SqlParameter[] GoToSqlSererSelectParameter(string ConnectionString, string SqlCommandText, bool includeReturnValueParameter) { SqlConnection BugConnection = new SqlConnection(ConnectionString); SqlCommand BugCommand = new SqlCommand(SqlCommandText, BugConnection); try { BugCommand.CommandType = CommandType.StoredProcedure; BugConnection.Open(); //----反向去SqlServer-找到引數----寫入到--BugCommand裡面 SqlCommandBuilder.DeriveParameters(BugCommand); } catch (SqlException) { } finally { BugConnection.Close(); } //---false不儲存刪除第一個引數--這個引數是ParameterDirection.ReturnValue型別 if (!includeReturnValueParameter) { if (BugCommand.Parameters[0].Direction == ParameterDirection.ReturnValue) { BugCommand.Parameters.RemoveAt(0); } } //--是否包含返回引數 SqlParameter[] discoveredParameters = new SqlParameter[BugCommand.Parameters.Count]; //---將引數拷貝到引數集合中--要拷貝到的引數集合--從指定目標陣列索引處開始 BugCommand.Parameters.CopyTo(discoveredParameters, 0); // Init the parameters with a DBNull value--將物件復空值 foreach (SqlParameter discoveredParameter in discoveredParameters) { try { discoveredParameter.Value = DBNull.Value; } catch (System.Exception) { } } return Cps(discoveredParameters); } //--作用建立物件的副本 private static SqlParameter[] Cps(SqlParameter[] originalParameters) { SqlParameter[] clonedParameters = new SqlParameter[originalParameters.Length]; for (int i = 0, j = originalParameters.Length; i < j; i++) { //----複製建構函式，以便使用當前例項的值初始化Parameter 類的新例項。 clonedParameters[i] = (SqlParameter)((ICloneable)originalParameters[i]).Clone(); } return clonedParameters; } }}