using cache to store Data in datatable for custom paging with Datalist, Repeater or Datagrid

• "eboxzone/SearchC.zip">Download source files - 23.5 Kb 

Introduction

There are so many approaches to get data and show it in datagrid or datalist controls in ASP .NET. In this article I'll show you how you can greatly improve your website performance by storing data in Cache and using the same Cached data for custom data paging, which you can bind with DataGrid or DataList or Repeater controls.

Caching is an important concept in computing. When applied to ASP.NET, it can greatly enhance the performance of your Web applications. It can decrease the work load on SQL SERVER, which means if you have a large dataset and you store that in cache, you don't need to go back to get that data again and again from SQL SERVER, specially if you are implementing custom paging. 

You can implement custom paging in DataGrid control but according to Microsoft, paging this way has a big disadvantage when you have thousands of records in database, since every time you navigate to a new page, those records must be retrieved from the data source into memory. That will reduce the performance painfully.

So instead of retrieving the data from database each time you change a page, we ll store all the data in cache and get it from there. In this example, I am implemeting a search solution, which is based on current user session, and when someone searches for any product in northwind database, the result is stored in DataTable and DataTable is stored in cache. When someone requests for next page, we simply go back to stored Cache and get the result from there.

Implementation

The project uses default Northwind database, installed with SQL SERVER. First of all we ll get the data according to search term, this is a simple query which retrieves matching data from Database

SqlConnection Con = new SqlConnection("server=(local);database=NorthWind;uid=sa;pwd=sa;");

SqlCommand Com = new SqlCommand();

Com.Connection = Con;

Com.CommandText = "SELECT ProductName FROM Products WHERE ProductName LIKE '%" + SearchText + "%'";

I have used simple text query, for better performance I would recommend using a stored procedure as every one does. Next we ll simply fille the results to a DataTable

SqlDataAdapter da = new SqlDataAdapter(Com);

da.Fill(dt);

Now here is the actuall part. To implement paging we would need a proper identity column with no missing values, which we would not be able to get from search results. So to overcome this problem, we would create another DataTable and Add a primary column to that table and then add all the other columns, which we want to show in search results, In this case its just product name. But right now this new DataTable is empty so we need to fill it with all the data which we got from search results. To fill it we would loop through all the data and add it to the new datatable. We ll have AutoIncrement = true;  for the primary column of this new DataTable so that it automatically invrements value for primary key for each new row added.

column = new DataColumn();

column.DataType = System.Type.GetType("System.Int32");

column.AutoIncrement = true;

column.ColumnName = "iID";

column.ReadOnly = true;

column.Unique = true;

column.AutoIncrementSeed = 1;

column.AutoIncrementStep = 1;

// Add the Column to the DataColumnCollection.


table.Columns.Add(column);

column = new DataColumn();

column.DataType = System.Type.GetType("System.String");

column.ColumnName = "ProductName";

column.AutoIncrement = false;

column.Caption = "ProductName";

column.ReadOnly = false;

column.Unique = false;

// Add the column to the table.


table.Columns.Add(column);

tot = dt.Rows.Count;

for (int i = 0; i < tot; i++)

{

row = table.NewRow();

row["iID"] = i;

row["ProductName"] = dt.Rows[i].ItemArray[0];

table.Rows.Add(row);

}

Now we have got this new DataTable with a unique primary key, with no missing values. We'll insert this data in cache

Cache.Insert(iID,table,null,System.Web.Caching.Cache.NoAbsoluteExpiration,TimeSpan.FromMinutes(20));

The iID is actually SessionID of the users, so that if there are different searches from different users, we can differentiate that. For this cache I have set it to expire after 20 mins of in-activity, so that if it is not used for 20 mins, it will expire automatically. You can change this time according to your satuation. Or even you can set Absolute expiration time, which means, after that time it will expire, wether it is in use or not. This was the actuall part rest of the part it easy as you just need to check the cache with iID of user and if that exists you would not go back to database, if it doesn't exist you would get the data from database. For each new search, you will simple user Cache.remove(iID) to remove any previous data with the same iID and create new search data.

Now to get paged data we'll simply use RowFilter of DataTable.DefaultView to filter the rows. At this point we know the total no of records from DataTable.Rows.Count, UserDefined pagesize, and total no of pages by deviding row count to page size. We'll use simple calculation to calculate which page we want and how many records.

int FromID = ((PageNum - 1) * PageSize) + 0;

int ToID = PageNum * PageSize;

We know FromID and ToID, which we can use to filter rows

DataView dv;

dv = Table.DefaultView;

dv.RowFilter = "iID >= " + FromID + " AND iID <= " + ToID + "";

Conclution

Caching of Web pages is an effective way of increasing performance while minimizing the use of precious server resources. Choosing the appropriate level for caching data is important for balancing caching versus memory usage. One of the most effective strategies to good Web application performance is to cache data only when necessary. Of course you can change this to your requirements like expiry time or when you need to cache data etc.
Thanks for reading, please leave your comments here.