Sharepoint webpart data states

Posted on 2009-12-18
Last Modified: 2012-06-27
Greeings all experts,

If your grid takes 5 minutes to retreive records (Please don't ask why, I learned not to in my current position ;) )  then essentially you are regenerating that query each time someone chooses to do something on the page.

My goal was to prevent this.  I want to query the data once, store it in objects, then reference the objects each time I need the data.

I would like it to persist in memory until the functions are complete.

//gathers connection string information

// interface to connection string

//Here is where I want to display the data.

Currently I am thinking something like this

call the interface to get the database connection string and open a connection

//set a List<T> which will be static as I don't want to regenerate this
if (!Page.IsPostBack)
   LoadData(); //LoadData will open the database, and fill a datatable with data
_data = new DataLoad();
List<t> = _data.LoadObjects(_datatable) // After this is done, I don't want to bother with the connection, LoadData method, i don't even want to relaod this list when a user changes parameters.
//Here is where I am generating the grid for my data display.

Now the question.

when I call the LoadObjects(_dataTable).  The datatable is not always avalable.

Do I have the right Idea in developing this or is the requerying of the database necessary?
Question by:rg20
    LVL 51

    Expert Comment

    There are a few performance issues with a data grid.
    - The time it takes to retrieve the data from the database
    - The time it takes to render the data as HTML
    - The time it takes to send the page to the client
    - The time it takes to draw the grid in the browser.

    It sounds like you are sure the data retrieval is the greatest penalty.  So, if the data changes infrequently I'd recommend you load the data into the HttpRuntime.Cache.

    I has some interesting properties.  It's global and you can provide hints to the cache about when to drop it from memory if the server is short of RAM.  You can even add a SqlCacheDependency so that a query polled from the server can drop the cache when the data is no longer valid (if it changes infrequently)

    You simply have to check the cache first before you use the object.  If it's not there (because the system had to clear the cache) you repopulate and reuse it.

    However, I think you should also consider looking at Sharepoint's Output Caching.
    LVL 9

    Author Comment

    Ok, point taken and will be explored as a possible solution.

    BUT... (you knew there had to be one).

    How can I store the database tables as ojbects so that they are accessable even during a reload?

    Would claiming the variable as static take less memory resources than using cache?

    Lets say for example (forgetting about memory issues for the moment) we had a webpart that defined the connection to the database.  On the first load of the page, the connetion exists through the interface.

    So Now I have my connetion string

    Now I have a grid, where a user can type in a number (for the sake of argument lets say a multiplier)

    Now the values from the database are read in and the number is multiplied by 2.  The user changes the number to 3.  I don't want to have to reload the database to have to recalculate it, since I put it in objects.

    I am worried also that the cache is going to have problems unloading when the site is left.  This is a leftover worry from the asp classic days where session varialbles did not always clear when the browser was closed and the OnSession_Close in the gloabal.asa could not be counted on.
    LVL 51

    Accepted Solution

    OK, first off static objects can't be managed properly by the web application so they can't be serialized or released if memory is short.  The HttpRuntime.Cache is static variables that can be managed by the web application including releasing them if required.

    Static variables will NOT be released until the site is restarted, with the HttpRuntime.Cache you can put on an expiration date or duration.  If you are worried about memory being left over static variables are worse.  You have 100% control of the cache far more precisely than session or static variables.

    NOTE: Sharepoint stores sessions in the SQL database so it has a timed job that runs every minute to clear the old sessions.

    The advantage with OutputCache is the rendered grid is cached, so you save time retrieving the data AND binding it to a data grid.

    Based on your requirements I'd pull the data from the database using a SqlDataReader and store it in an ArrayList of custom objects that store each row.  The DataSet object or DataGrid's are large cumbersome objects that consume far more space that is required to store the data. Then I'd bind the ArrayList to a DataGrid as required and rendered it on the fly using your recalculation methods.  That way you minimize round trips to the database.

    By the way, if you are using NLB with Sharepoint you have to be VERY careful about storing objects in memory.  The only way you can guarantee a user will hit the original server with their copy of the cache is by using Affinity.
    LVL 9

    Author Closing Comment

    I am still working on the issue of connections.  I got it to work, but it seems to work no matter which page I put the webpart.  Not good.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Note:  There are two main ways to deploy InfoPath forms:  Server-side and directly through the SharePoint site.  Deploying a server-side InfoPath form means the form is approved by the Administrator, thus allowing greater functionality in the form. …
    SharePoint Designer 2010 has tools and commands to do everything that can be done with web parts in the browser, and then some – except uploading a web part straight into a page that is edited in SPD. So, can it be done? Scenario For a recent pr…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    759 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

    12 Experts available now in Live!

    Get 1:1 Help Now