Caching

The SqlClient class has built-in caching system.

Caching will avoid unnecessary queries to a database.

Use caching for frequently accessed data.

Usage a cache is very simple, just specify a duration of storage in cache.

C#

using (SqlClient client = new SqlClient())
{
  // cache duration
  client.CacheDuration = 60; // 60 seconds = 1 minute
  // sql query or stored procedure name
  client.CommandText = "MyStoredProcedure"; 
  // parameters (if necessary)
  client.Parameters.Add("@par1", SqlDbType.Int).Value = 123;
  client.Parameters.Add("@par2", SqlDbType.NVarChar, 50).Value = "hello, world!";
  // query execution
  var table = client.GetTable();
  if(table.Rows.Count > 0)
  {
    // has results
    Console.WriteLine("Rows count: {0}", table.Rows.Count);
    // each the table row
    foreach(DataRow r in table.Rows)
    {
      Console.WriteLine("{0}, {1}, {2}", r[0], r[1], r[2]);
    }
  }
  else
  {
    // no results
    Console.WriteLine("Data not found...");
  }
}

Visual Basic .NET

Using client As New SqlClient()
  ' cache duration
  client.CacheDuration = 60 ' 60 seconds = 1 minute
  ' sql query or stored procedure name
  client.CommandText = "MyStoredProcedure"
  ' parameters (if necessary)
  client.Parameters.Add("@par1", SqlDbType.Int).Value = 123
  client.Parameters.Add("@par2", SqlDbType.NVarChar, 50).Value = "hello, world!"
  ' query execution
  Dim table As DataTable = client.GetTable()
  If table.Rows.Count > 0 Then
    ' has results
    Console.WriteLine("Rows count: {0}", table.Rows.Count)
    ' each the table row
    For Each r As DataRow In table.Rows
      Console.WriteLine("{0}, {1}, {2}", r(0), r(1), r(2))
    Next
  Else
    ' no results
    Console.WriteLine("Data not found...")
  End If
End Using

You can also specify a duration of caching when creating an instance of SqlCleint.

C#

using (SqlClient client = new SqlClient(60)) // 60 seconds = 1 minute
{
  // sql query or stored procedure name
  client.CommandText = "MyStoredProcedure"; 
  // ...
}

Visual Basic .NET

Using client As New SqlClient(60) ' cache duration: 60 seconds = 1 minute
  ' sql query or stored procedure name
  client.CommandText = "MyStoredProcedure"
  ' ...
End Using

By default caching method is automatically determined.

For Windows applications will be used MemoryCache. In applications, ASP. NET - standard WebCache.

You can specify the desired caching method:

C#

using (SqlClient client = new SqlClient())
{
  // set caching in file system
  client.CacheType = CachingType.File;
  // cache duration
  client.CacheDuration = 60; // 60 seconds = 1 minute
  // sql query or stored procedure name
  client.CommandText = "MyStoredProcedure"; 
  // ...
}

Visual Basic .NET

Using client As New SqlClient()
  ' set caching in file system
  client.CacheType = CachingType.File
  ' cache duration
  client.CacheDuration = 60 ' 60 seconds = 1 minute
  ' sql query or stored procedure name
  client.CommandText = "MyStoredProcedure"
  ' ...
End Using

But better to do it in the application settings (ASP .NET, Windows). For example, ASP .NET web.config file:

<appSettings> 
  <clear /> 
  <!--Cache type--> 
  <add key="NeData:Sql:CacheType" value="File" /> 
  <!--File cache storage path--> 
  <add key="NeData:Sql:CachePath" value="C:\Cache\MyApplication" /> 
  <!--File cache buffer size (Kb)--> 
  <add key="NeData:Sql:CacheBufferSize" value="10240" /> 
</appSettings>