Caching Sql Parameters in C#.Net

Here I am going to discuss about how to cache the sql parameters.

Suppose if you want to add records to the table, either you use Direct-SQL and Stored Procedure. Stored Procedure is compiled one so it’s faster than Direct-SQL.

If you are using stored procedure, you call the stored procedure from the client side either by EXEC Or Parameterized call.

Parameterized call reuse the existing execution plan instead of creating the new execution plan every time when you call using EXEC.

So, Parameterized call is more efficient than EXEC.

If you are using Parameterized call, you have to create the array of Sql Parameters to pass the parameter value. You have to create the array of parameters every time when you call the stored procedure.

If you are calling 100 times, 100 times you have to create the array of parameters.

Cache:

Instead of creating the parameters every time, you can the cache the array of sql parameters in the first call. In next call, you can get the Clone of Sql Parameters from Cache. And then assign the values to the clone sql parameter.


Microsoft released the Enterprise Library having the parameter caching mechanism.

Here I am going to discuss the parameter caching is similar to that in a simple way.

Parameter Cache Code:

public class ParameterCache
{

private static Hashtable paramCache = Hashtable.Synchronized(new Hashtable());


// Create and return a copy of the IDataParameter array.
private static IDataParameter[] CloneParameters(IDataParameter[] originalParameters)
{
IDataParameter[] clonedParameters = new IDataParameter[originalParameters.Length];

for (int i = 0, j = originalParameters.Length; i < j; i++)
{
clonedParameters[i] = (IDataParameter)((ICloneable)originalParameters[i]).Clone();
}

return clonedParameters;
}

// Empties all items from the cache
public static void Clear()
{
paramCache.Clear();
}

// Add a parameter array to the cache for the command.
public static void AddParameterSetToCache(string connectionString, string storedProcedure, IDataParameter[] parameters)
{
string key = CreateHashKey(connectionString, storedProcedure);
paramCache[key] = parameters;
}

// Gets a parameter array from the cache for the command. Returns null if no parameters are found.
public static IDataParameter[] GetCachedParameterSet(string connectionString, string storedProcedure)
{
string key = CreateHashKey(connectionString, storedProcedure);
IDataParameter[] cachedParameters = (IDataParameter[])(paramCache[key]);
return CloneParameters(cachedParameters);
}

// Gets if a given stored procedure on a specific connection string has a cached parameter set
public static bool IsParameterSetCached(string connectionString, string storedProcedure)
{
string hashKey = CreateHashKey(connectionString, storedProcedure);
return paramCache[hashKey] != null;
}

// create the hash key based on connectionstring and stored procedure name
private static string CreateHashKey(string connectionString, string storedProcedure)
{
return connectionString + ":" + storedProcedure;
}

}

The above shared class is for caching and accessing the cached parameters.

HashTable – Used to store the array of parameters based on hash key. It is used as a cache.

CreateHashKey – Caching the Sql parameters based on hash key formed from connectionstring and stored procedure name.

AddParameterSetToCache – Cache the parameters in HashTable based on connectionstring and stored procedure name.

CloneParameters – Get the clone of cached parameters.

GetCachedParameterSet – Get the parameters from the HashTable(Cache) based on HashKey.

IsParameterSetCached – Used to check whether parameters already cached based on hashkey.


Clear – Clear the HashTable(Cache)


Customer Class:

public class Customer
{

#region C# 3.0 Automatic Properties

public long CustomerID
{
get;
set;
}

public string Name
{
get;
set;
}

#endregion

}

How to use ParameterCache shared class:

//Add the Customer Information to Customer Table
public void AddCustomer(Customer cust, string connectionString)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlParameter[] param = null;
string spName = "AddCustomer";

try
{
conn = new SqlConnection(connectionString);
cmd = new SqlCommand(spName, conn);
cmd.CommandType = CommandType.StoredProcedure;

param = GetParameters(cust, connectionString, spName);

conn.Open();
cmd.ExecuteNonQuery();

}
catch (Exception)
{
throw;
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();

if (cmd != null)
cmd.Dispose();

param = null;
}




}

The above method is to add the records to the customer table. For that you have to prepare the parameters for stored procedure. GetParameters() method used to prepare the parameters.

// Prepare the Sql Parameters for Adding Customer
// Get the Clone parameters if already cached based on connectionstring and store procedure name
// Otherwise create the new Sql parameter array and then add to cache for next time use.
private SqlParameter[] GetParameters(Customer cust, string connectionString, string storeProcedure)
{
SqlParameter[] param = null;

if (ParameterCache.IsParameterSetCached(connectionString, storeProcedure))
{
param = (SqlParameter[])ParameterCache.GetCachedParameterSet(connectionString, storeProcedure);

param[0].Value = cust.CustomerID;
param[1].Value = cust.Name;
}
else
{
param = new SqlParameter[2];

param[0] = new SqlParameter("@CustomerID", SqlDbType.BigInt, 8);
param[0].Value = cust.CustomerID;
param[1] = new SqlParameter("@Name", SqlDbType.VarChar, 50);
param[1].Value = cust.Name;

ParameterCache.AddParameterSetToCache(connectionString, storeProcedure, param);
}

return param;
}

The above method is for returning array of sql parameters. First it checks the cache, if already exists then get the parameters from the cache. And then assign the values of clone sql parameters.
If not exists, create the new array of sql parameters and add prepared parameters into cache for next time use.

Only first time call, it takes some time to cache the parameters. Next call onwards prepare the sql parameters faster.

Conclusion:
You can get better performance on caching the sql parameters if you are calling the stored procedure frequently.