Solved

Cache my Grid view when I do paging and sorting, but hit the database for a page load and a search action

Posted on 2009-07-16
4
331 Views
Last Modified: 2013-12-17
I have a very simple GridView working with Objectdatasource that is calling a Bussiness method that return a DataTable object. in my case I did a top 2000 records. using the built paging and sorting from the Grid View I hit the query each time I do any of the two operations.

I found out that the ObjectDatasource has a Cache property which works but because my page is search page. I want to store the results for each user in a Cache dependency key (this way each user who searches will have his own Cache, using the Session ID key).

Here are the rules I want the Cache to respect:

Page Load always hit the database.

Paging hit the cache if available

Sorting hit the cache if available

Clicking on the search button to get a new set of results  hit the database.

Can this be done? Did anyone did something like that ?

I could not find one sample online for something so basic and simple.

 

Personally I could not get this work, I had problems clearing the Cache.

A code sample would be great.

Thanks in advance.
0
Comment
Question by:TrafficTechDev
  • 2
4 Comments
 
LVL 39

Expert Comment

by:abel
ID: 24936198
> Can this be done? Did anyone did something like that ?
Basically, caching is a very complex matter (it seems easy at first, but then... when to invalidate, what to store, how much, how to notify that data has changed etc). Luckily, many people have already invented this for you. The excellent NHibernate ORM mapper contains full first-level and second-level caching, leaving you at the task at hand: making nice pages.= and writing business logic. Many other ORMs exist that have similar possibilities.

If you create this yourself, you'll hit one very important problem: suppose your rows are approx. 1000 bytes each (some dates, descriptions, memo, texts and ints quickly go so high). The entity object that you use will have about the same size (not really, but let's keep it simple). If you have a records set of 2000 records, that's 2MB altogether. With 500 users on your site that can surmount to 1000GB. This hits limits on most current installations of ASP.NET.

Solution? To-disk caching.

I wouldn't go that way. If your data is indeed about 2000 records, the overhead of simply getting them is not that much. You can keep them around during the request (I mean, prevent multiple SQL SELECT statements) and you should be quite fine.

If you want to do this the right way for the long run, a full blown ORM solution may seem a lot to invest in at first, but will save you all the time you'll need to write all your entities by hand, your caching by hand, your mapping by hand etc.

-- Abel --
0
 

Accepted Solution

by:
TrafficTechDev earned 0 total points
ID: 24938368
Hi, you may be right.
for now I found a way to do it, I have to monitor the server memory to make sure I don't kill it.
I know also that Microsoft is coming with a product called Velocity http://msdn.microsoft.com/en-us/data/cc655792.aspx that should handle large amouts of caching.

The objectdatasource calls a method in the Business layer there I am passing a Boolean value if I want to store the data set in cache or not.


[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select)]

        public static DataSet GetShipmentSearchTest(String UID,

                                                    Int64? SalespersonId,

                                                    Int64? ShipmentId,

                                                    String CustomerName,

                                                    Int16? ShipmentStatusId,

                                                    String ReferenceValue,

                                                    String CityName,

                                                    String ProvinceStateCode,

                                                    Int16? TaskTypeId,

                                                    String SupplierName,

                                                    DateTime? FromShipmentDate,

                                                    DateTime? ToShipmentDate,

                                                    Boolean ApplyGroupFilter,

                                                    Boolean IsAccountant,

                                                    Int64? CustomerId,

                                                    Boolean CanSeeProduce,

                                                    String CountryOrOfficeCode,

                                                    String UniquePageKey,

                                                    Boolean UseCache)

        {

            try

            {

                string CacheKey = string.Empty;

                CacheKey = "GetShipmentSearch" + UniquePageKey;

                DataSet ds = null;

                ShipmentWebService.ShipmentWS ws = new ShipmentWebService.ShipmentWS();

                if (UseCache)

                {

                    ds = (DataSet)CacheData.GetFromCache(CacheKey);

                }

                else

                {

                    CacheData.ClearFromCache(CacheKey);

                }
 
 

                    if (ds == null)

                    {

                        ds = ws.GetShipmentSearchTEST(UID,

                                                        SalespersonId,

                                                        ShipmentId,

                                                        CustomerName,

                                                        ShipmentStatusId,

                                                        ReferenceValue,

                                                        CityName,

                                                        ProvinceStateCode,

                                                        TaskTypeId,

                                                        SupplierName,

                                                        FromShipmentDate,

                                                        ToShipmentDate,

                                                        ApplyGroupFilter,

                                                        IsAccountant,

                                                        CustomerId,

                                                        CanSeeProduce,

                                                        CountryOrOfficeCode

                                                        );

                        CacheData.StoreInCache(CacheKey, ds);

                    }

                return ds;                

                                                    
 

            }

            catch (Exception)

            {

                throw;

            }

        }

Open in new window

0
 
LVL 39

Expert Comment

by:abel
ID: 24938492
Yes, Velocity is a good way to go (though I haven't used it myself). Using a full solution for these kind of things will solve many such problems.

As you may have found out, it is quite tedious to program yourself. To make it reliable, you can use objects that are serializable (most are by default) and that you simply pass the object (i.e,. not the typed object, just the Object object) to the cache.

Anyway, glad you got something going and working :)
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now