Adding the Caching in the .NET application using the MS SQL Cache

SQL Cache will help in data refresh in Cached table whenever there will be any changes in the table in the database.

To Add the Caching in the .NET application using the MS SQL Cache, Follow below steps:-

1. Execute below command in command prompt for that database and table you want to enable the SQL cache.

Go to the path in command prompt at: C:\Windows\Microsoft.NET\Framework\v4.0.30319

C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -S SQL_SERVER_URL_OR_HOST -U USERNAME -P PASSWORD -ed -d DATABASENAME -et -t TABLENAME

Here,
SQL_SERVER_URL_OR_HOST: SQL Server host or url
USERNAME: Username to connect to server
PASSWORD: Password to connect to server
DATABASENAME: Database name to be used on that server
TABLENAME: Table name on that you want to create SQL caching in particular database.

2. Add below keys in App setting or web config.
Let`s consider I`m creating the cache for Gender master table.

<add key=”CacheGenderMaster” value=”GenderMaster”/>

3. Add two variables in your code file or class to store values. Here cache table name I`m getting from app settings
public static string GenderMaster = “GenderMaster”; //Cache Id
public static string CacheGenderMaster = Utils.GetAppSettingsValue(“CacheGenderMaster”).ToSafeString(); //Cache table name
public static string CacheDataBaseName = Utils.GetAppSettingsValue(“CacheNajmNetDataBase”).ToSafeString(); //Cache database name

4. Write the code te fetch the all Gender from GenderMaster table from the database. I`m creating this in MasterClass

public class MasterRepository
{

public List<GenderMaster> GetAllGenderMaster()
{
try
{
using (UnitOfWork objUnitofWork = new UnitOfWork(Utility.ConDatabaseConnection))
{
CommandDefinition sqlCommand = new CommandDefinition(“GetAllGenders”, commandType: CommandType.StoredProcedure);
List<GenderMaster> genders = objUnitofWork.DBConnection.Query<GenderMaster>(sqlCommand).ToList();
return genders;
}
}
catch (Exception ex)
{
throw ex;
}
}
}

5. And finally use below code to query from the SQL table with Cache implementation

MasterRepository _objMaster = new MasterRepository();

List<GenderMaster> genders = CacheProvider.Get(GenderMaster, CacheGenderMaster, CacheDataBaseName, () => _objMaster.GetAllGenderMaster());
if (genders>0)
{
//Process next
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s