Can I temporarily store data in my C#.Net app to reduce the need for calls for data from SQL Server?

Question

I created a C#.net app that uses dates from a sql server 2008 database table. Is there a way for me to temporarily store the data so that my program does not have to repeatedly make server calls for the same set of information. I know how to pull the info I need and create a temporary dataset, however, it is only accessable to the particular method or class and then goes away. I need the results to be universally available until the program closes.

This is what I have so far and I am not sure where to go next:

SqlConnection ReportConnect = new SqlConnection(ConnectionString);
String reportQuery = @"SELECT DISTINCT DATE FROM dbo.myTable ORDER BY DATE DESC";

ReportConnect.Open();

SqlCommand cmd = ReportConnect.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.Connection = ReportConnect;
cmd.CommandText = reportQuery.ToString();

SqlDataReader rdr = cmd.ExecuteReader();

while(rdr.Read()) {
   //I can access the results here 
}

//how do I add this data for the life of the program instance to my current
//dataset.  Let's say the dataset is named "activeDataset"

Thanks for any help you can provide on the best way to accomplish my goal.

Answer 1

You could create a singleton object, and store the data in this object.

Be aware that there is a lot more to single ton objects that you will have to think about.

Have a look at

• Implementing the Singleton Pattern in C#
• Singleton pattern
• Implementing Singleton in C#

Answer 2

You should use SQLCacheDependency. Take a look at MSDN

Answer 3

This would be called caching the data.

Answer 4

You could store the datatable in a static variable that would be accesible from any part of your code (is this necessary?).

public class MyDataSetCache
{
    public static DataSet MyDataSet { get; set; }
}

Some other code...

// SQL Statements....
MyDataSetCache.MyDataSet = activeDataset // Editted to follow OP :-)

Answer 5

You can definately use Cache to reduce database hits, Besides using SqlDependency you can have a cache based on time. You can invalidate your cache let's say every 4 hours,and hit the database again. Check out Cache.Insert()

Answer 6

I usually serialize whole object to a file and try to read it first before going to database.

Answer 7

You can use a set of implementation hooks to achieve result:

1. Common data-application layer (data singleton or some data coupling using static class with lesser "visible" methods' dependencies)
2. Use caching -- you can use Dictionary and common string-keys to detect (ContainsKey method) whether data is already fetched or needs sql-server call. This can be useful when you need different DataSets. Dictionary works pretty fast.

Answer 8

If you are going to use key/value pair caching, I recommend you use HttpRuntime.Cache (available outside ASP.NET applications) since it already does alot of work for you.

In it's simplest implementation:

public IList<DateTime> GetUniqueDates()
{
    const string CacheKey = "RepositoryName.UniqueDates";

    Cache cache = HttpRuntime.Cache;

    List<DateTime> result = cache.Get[CacheKey] as List<DateTime>;

    if (result == null)
    {
        // If you're application has multithreaded access to data, you might want to 
        // put a double lock check in here

        using (SqlConnection reportConnect = new SqlConnection(ConnectionString))
        {
            // ...
            result = new List<DateTime>();

            while(reader.Read())
            {
                result.Add((DateTime)reader["Value"]);
            }
        }

        // You can specify various timeout options here
        cache.Insert(CacheKey, result);
    }

    return result;
}

Having said that, I usually use IoC trickery to create a caching layer in front of my repository for the sake of cohesion.