sqlhelperparametercache.cs

SharpNuke源代码

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace Microsoft.ApplicationBlocks.Data
{
	// SqlHelperParameterCache provides functions to leverage a static cache of procedure parameters, and the
	// ability to discover parameters for stored procedures at run-time.
	public sealed class SqlHelperParameterCache
	{

		#region "private methods, variables, and constructors"
		
		// Since this class provides only static methods, make the default constructor private to prevent
		// instances from being created with "new SqlHelperParameterCache()".
		private SqlHelperParameterCache() 
		{
		} // New
		
		private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());
		
		// resolve at run time the appropriate set of SqlParameters for a stored procedure
		// Parameters:
		// - connectionString - a valid connection string for a SqlConnection
		// - spName - the name of the stored procedure
		// - includeReturnValueParameter - whether or not to include their return value parameter>
		// Returns: SqlParameter()
		private static SqlParameter[] DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter, params object[] parameterValues)
		{
			
			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;
			SqlParameter[] discoveredParameters;
			connection.Open();
			SqlCommandBuilder.DeriveParameters(cmd);
			connection.Close();
			if (! includeReturnValueParameter)
			{
				cmd.Parameters.RemoveAt(0);
			}
			
			discoveredParameters = new SqlParameter[cmd.Parameters.Count - 1+ 1];
			cmd.Parameters.CopyTo(discoveredParameters, 0);
			
			// Init the parameters with a DBNull value
			foreach (SqlParameter discoveredParameter in discoveredParameters)
			{
				discoveredParameter.Value = DBNull.Value;
			}
			
			return discoveredParameters;
		} // DiscoverSpParameterSet
		
		// Deep copy of cached SqlParameter array
		private static SqlParameter[] CloneParameters(SqlParameter[] originalParameters)
		{
			
			int i;
			int j = originalParameters.Length;
			SqlParameter[] clonedParameters = new SqlParameter[j];
			
			for (i = 0; i < j; i++)
			{
				clonedParameters[i] = ((SqlParameter)(((ICloneable) originalParameters[i]).Clone()));
			}
			
			return clonedParameters;
		} // CloneParameters
		
		#endregion
		
		#region "caching functions"
		
		// add parameter array to the cache
		// Parameters
		// -connectionString - a valid connection string for a SqlConnection
		// -commandText - the stored procedure name or T-SQL command
		// -commandParameters - an array of SqlParamters to be cached
		public static void CacheParameterSet (string connectionString, string commandText, params SqlParameter[] commandParameters)
		{
			if (connectionString == null || connectionString.Length == 0)
			{
				throw (new ArgumentNullException("connectionString"));
			}
			if (commandText == null || commandText.Length == 0)
			{
				throw (new ArgumentNullException("commandText"));
			}
			
			string hashKey = connectionString + ":" + commandText;
			
			paramCache[hashKey] = commandParameters;
		} // CacheParameterSet
		
		// retrieve a parameter array from the cache
		// Parameters:
		// -connectionString - a valid connection string for a SqlConnection
		// -commandText - the stored procedure name or T-SQL command
		// Returns: An array of SqlParamters
		public static SqlParameter[] GetCachedParameterSet(string connectionString, string commandText)
		{
			if (connectionString == null || connectionString.Length == 0)
			{
				throw (new ArgumentNullException("connectionString"));
			}
			if (commandText == null || commandText.Length == 0)
			{
				throw (new ArgumentNullException("commandText"));
			}
			
			string hashKey = connectionString + ":" + commandText;
			SqlParameter[] cachedParameters = ((SqlParameter[]) paramCache[hashKey]);
			
			if (cachedParameters == null)
			{
				return null;
			}
			else
			{
				return CloneParameters(cachedParameters);
			}
		} // GetCachedParameterSet
		
		#endregion
		
		#region "Parameter Discovery Functions"
		// Retrieves the set of SqlParameters appropriate for the stored procedure.
		// This method will query the database for this information, and then store it in a cache for future requests.
		// Parameters:
		// -connectionString - a valid connection string for a SqlConnection
		// -spName - the name of the stored procedure
		// Returns: An array of SqlParameters
		public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
		{
			return GetSpParameterSet(connectionString, spName, false);
		} // GetSpParameterSet
		
		// Retrieves the set of SqlParameters appropriate for the stored procedure.
		// This method will query the database for this information, and then store it in a cache for future requests.
		// Parameters:
		// -connectionString - a valid connection string for a SqlConnection
		// -spName - the name of the stored procedure
		// -includeReturnValueParameter - a bool value indicating whether the return value parameter should be included in the results
		// Returns: An array of SqlParameters
		public static SqlParameter[] GetSpParameterSet(string connectionString, string spName, bool includeReturnValueParameter)
		{
			SqlParameter[] returnValue;
			if (connectionString == null || connectionString.Length == 0)
			{
				throw (new ArgumentNullException("connectionString"));
			}
			SqlConnection connection = null;
			try
			{
				connection = new SqlConnection(connectionString);
				returnValue = GetSpParameterSetInternal(connection, spName, includeReturnValueParameter);
			}
			finally
			{
				if (connection != null)
				{
					connection.Dispose();
				}
			}
			return returnValue; // GetSpParameterSet
		}
		
		// Retrieves the set of SqlParameters appropriate for the stored procedure.
		// This method will query the database for this information, and then store it in a cache for future requests.
		// Parameters:
		// -connection - a valid SqlConnection object
		// -spName - the name of the stored procedure
		// -includeReturnValueParameter - a bool value indicating whether the return value parameter should be included in the results
		// Returns: An array of SqlParameters
		public static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
		{
			SqlParameter[] returnValue;
			
			returnValue = GetSpParameterSet(connection, spName, false);
			return returnValue; // GetSpParameterSet
		}
		
		// Retrieves the set of SqlParameters appropriate for the stored procedure.
		// This method will query the database for this information, and then store it in a cache for future requests.
		// Parameters:
		// -connection - a valid SqlConnection object
		// -spName - the name of the stored procedure
		// -includeReturnValueParameter - a bool value indicating whether the return value parameter should be included in the results
		// Returns: An array of SqlParameters
		public static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter)
		{
			SqlParameter[] returnValue;
			if (connection == null)
			{
				throw (new ArgumentNullException("connection"));
			}
			SqlConnection clonedConnection = null;
			try
			{
				clonedConnection = ((SqlConnection)(((ICloneable) connection).Clone()));
				returnValue = GetSpParameterSetInternal(clonedConnection, spName, includeReturnValueParameter);
			}
			finally
			{
				if (clonedConnection != null)
				{
					clonedConnection.Dispose();
				}
			}
			return returnValue; // GetSpParameterSet
		}
		
		// Retrieves the set of SqlParameters appropriate for the stored procedure.
		// This method will query the database for this information, and then store it in a cache for future requests.
		// Parameters:
		// -connection - a valid SqlConnection object
		// -spName - the name of the stored procedure
		// -includeReturnValueParameter - a bool value indicating whether the return value parameter should be included in the results
		// Returns: An array of SqlParameters
		private static SqlParameter[] GetSpParameterSetInternal(SqlConnection connection, string spName, bool includeReturnValueParameter)
		{
			if (connection == null)
			{
				throw (new ArgumentNullException("connection"));
			}
			
			SqlParameter[] cachedParameters;
			string hashKey;
			
			if (spName == null || spName.Length == 0)
			{
				throw (new ArgumentNullException("spName"));
			}
			
			hashKey = connection.ConnectionString + ":" + spName + (includeReturnValueParameter ? ":include ReturnValue Parameter" : string.Empty);
			
			cachedParameters = ((SqlParameter[]) paramCache[hashKey]);
			
			if (cachedParameters == null)
			{
				SqlParameter[] spParameters = DiscoverSpParameterSet(connection, spName, includeReturnValueParameter, null);
				paramCache[hashKey] = spParameters;
				cachedParameters = spParameters;
			}
			
			return CloneParameters(cachedParameters);
			
		} // GetSpParameterSet
		#endregion
		
	} // SqlHelperParameterCache
}