第一步：DBUtility层 用于底层操作SqlHelper(四)

2008-01-12 17:16

   #endregion ExecuteXmlReader
}

///

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

   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
   /// whether or not to include their return value parameter
   ///
   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;

     SqlCommandBuilder.DeriveParameters(cmd);

     if (!includeReturnValueParameter)
     {
      cmd.Parameters.RemoveAt(0);
     }

     SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];;

     cmd.Parameters.CopyTo(discoveredParameters, 0);

     return discoveredParameters;
    }
   }

   //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)
   {
    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)
   {
    string hashKey = connectionString + ":" + commandText;

    SqlParameter[] cachedParameters = (SqlParameter[])paramCache[hashKey];
    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)
   {
    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 Parameter Discovery Functions

}
}