Read and write data from cache.

Atul Patil

9y787

I have a table USER,I want stored data of USER table into cache memory of SQL SERVER and next time fetch data from cache in SQL SERVER for the performance purpose.Then how can i do?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Manish Kumar9y ago
Accepted answer
Which version of Sql server you are currently using.
In sql server 2014 onwards microsoft has introduced In-Memory OLTP where you can create table in memory check this

https://www.simple-talk.com/sql/learn-sql-server/introducing-sql-server-in-memory-oltp/

Thanks,
Manish
0

Brijesh Rathor9y ago

public DataTable GetDataTableFromCacheOrDatabase() 
{ 
DataTable dataTable = HttpContext.Current.Cache["secret key"] as DataTable; 
if(dataTable == null) 
{        
dataTable = GetDataTableFromDatabase(); 
HttpContext.Current.Cache["secret key"] = dataTable; 
} 
return 
dataTable; 
}

You're going to need some mechanism to flush the data table from the cache if it changes. For example you could use an SqlCacheDependency.

As requested, to clear the cache an hour after the table is added you would do:

HttpContext.Current.Cache.Insert("secret key", dataTable, null, 
DateTime.Now.AddHours(1), System.Web.Caching.Cache.NoSlidingExpiration);