///

/// 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 caching functions

///

/// Add parameter array to the cache
///

/// A valid connection string for a SqlConnection
/// The stored procedure name or T-SQL command
/// 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;
}

///

/// Retrieve a parameter array from the cache
///

/// A valid connection string for a SqlConnection
/// The stored procedure name or T-SQL command
/// 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 = paramCache[hashKey] as SqlParameter[];
if (cachedParameters == null)
{
return null;
}
else
{
return CloneParameters(cachedParameters);
}
}

#endregion caching functions

#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.
///
/// A valid connection string for a SqlConnection
/// The name of the stored procedure
/// An array of SqlParameters
public static SqlParameter[] GetSpParameterSet(string connectionString, string spName)
{
return GetSpParameterSet(connectionString, spName, false);
}

///

/// 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.
///
/// 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)
{
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);
}
}

///

/// 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.
///
/// A valid SqlConnection object
/// The name of the stored procedure
/// An array of SqlParameters
internal static SqlParameter[] GetSpParameterSet(SqlConnection connection, string spName)
{
return GetSpParameterSet(connection, spName, false);
}

///

/// 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.
///
/// A valid SqlConnection object
/// 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
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);
}
}

///

/// Retrieves the set of SqlParameters appropriate for the stored procedure
///

/// A valid SqlConnection object
/// 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
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

}
}

[@more@]