Can we use same datatable for pagemethod and webmethod in ASP.NET?

Question

I am trying to create a new webpage where i need to display almost 10 different gridviews and charts.

Gridviews are binded on pageload event and charts are displayed using jquery-ajax method (using amcharts as well as highcharts) by calling WebMethod.

Initially i implemented the page in a way that after executing same set of stored procedures for gridviews(for showing grid view data) and webmethods(for drawing charts).So same sps are executed twice for this page(one for grid and another for chart).There are 10 sps required to execute for fetching the data.

So for improving the page performance i have created static datatable like this

static DataTable Report1;

and binded the gridview like this.

private void gvbindReport1()
    {
        try
        {            
            Report1 = new DataTable();//refreshed datatable 
            DataSet ReportDS1 = objmvbl.GetReportGraph(ClientID, date_From, date_To);
            if (ReportDS1.Tables.Count > 0)
            {
                Report1 = ReportDS1.Tables[0];//bindinding data to static datatable

            }
            GdReport.DataSource = Report1;
            GdReport.DataBind();
        }
        catch (Exception ex)
        {
            Log.Errlog("Error Occured in  gvbindReport1 : " + ex.Message.ToString());
        }

    }

and inside the webmethod i have used the same datatable for drawing the chart like this

 [System.Web.Services.WebMethod]
    public static string GetDataReport1()
    {
        System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
        Dictionary<string, object> row;
        try
        {
            //processing for the data inside static datatable
            if (Report1.Rows.Count > 0)
            {
                foreach (DataRow dr in Report1.Rows)
                {
                    row = new Dictionary<string, object>();
                    foreach (DataColumn col in Report1.Columns)
                    {
                        row.Add(col.ColumnName, dr[col]);
                    }
                    rows.Add(row);
                }
            }
        }
        catch (Exception ex)
        {
            Log.Errlog("Error Occured in  GetDataReport WebMethod of Report Page : " + ex.Message.ToString());
        }

        return serializer.Serialize(rows);

    }

with this i am able to show both grid and charts.

Now Please tell me that, is this a correct approach to deal with webmethods? i have read that webmethod have no relation to the page and all.Please Tell me the drawbacks of this method.

If this is wrong,Please suggest a better way to improve the page performance?

Rainout

One thing is that you shouldn't be manually invoking JavaScriptSerializer. If you just return the List<Dictionary<string, object>> directly, ASP.NET will handle serializing that. It does that regardless of your return type, so your current code is running JavaScriptSerializer twice on the result (and you have to parse it twice on the client-side). More info: encosia.com/… – Milli

@ Dave Ward:thanks dave for pointing out this.can you tell how to sync the data between page and webmethod? – Rainout

Are you able to dump using DataTables and use statically typed classes and generic List<T> instead? – Ayotte

Answer 1

No, this is not the correct method. Since you have declared the DataTable as static (a static variable has application scope and cannot be instantiated) all

users will get the same result (last updated values).

You can realize this in concurrency testing.

Please check the following scenario:

Consider dtbl is the static dataTable which is initialized on the home page, and you create another instance of `datatable on the index page (both are in page load as given below).

Home

public static DataTable dtbl;
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        dtbl = new DataTable();
        dtbl.Columns.Add("id");
        dtbl.Columns.Add("name");
        for (int i = 0; i < 10; i++)
        {
            DataRow dr = dtbl.NewRow();
            dr["id"] = i.ToString();
            dr["name"] = i + 1;
            dtbl.Rows.Add(dr);
        }
    }
}

Index page

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        home.dtbl = new DataTable();
    }
}

Now put a breakpoint in each page load and run the application,

• Open both the pages in separate tab.
• Refresh the home page and check whether the columns are showing
• Now go to the next tab (index) and refresh it (a new instance is created for dt). It will affect the data table now you will get the new data table at home also.
• So if these two processes/pages are concurrently executed the latest value will get for both the pages. That's why I am saying it will realize this in concurrency testing.

You can make use of a session in this case. Consider the following code:

Home

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        dtbl = new DataTable();
        dtbl.Columns.Add("id");
        dtbl.Columns.Add("name");
        for (int i = 0; i < 10; i++)
        {
            DataRow dr = dtbl.NewRow();
            dr["id"] = i.ToString();
            dr["name"] = i + 1;
            dtbl.Rows.Add(dr);
        }
        if (((DataTable)Session["MyDatatable"]).Columns.Count < 0)
        {
            Session["MyDatatable"] = dtbl;
        }
        else
        {
            dtbl = (DataTable)Session["MyDatatable"];
        }
    }
}

Mcnew

no separate datatable is created for individual users, All are sharing the same so will get the last updated value for all. – Mcnew

Report1 = new DataTable(); this won't work properly?say user1 access the page grid and graph shown.then user2 access new grid and graph will show right? – Rainout

:Thanks for the detailed explanation.Is there any other way to sync the data between two? – Rainout

I believe a session variable is the wrong approach here, as the users are supposed to all be referencing the same data (unless I misunderstood). Session would keep a separate data table for each connected user, which depending on the server resources + traffic + size of data table could be a large resource drain. – Statism

HBomb: all users won't have the same data..client id and daterange will change – Rainout

If the date range will change, then session is the correct variable scope to utilize. However, keep in mind my comment about resource size. If Session is containing 10 very large datasets per user AND you are experiencing heavy traffic, this could be a rather large resource load. – Statism

Of course, you could use the same base data for all users via the Cache object (see my answer below), and perform your filter on the Cached results based on date, and return those filtered results to the client. You may get a performance gain by saving the round trip to the database on every call if you have enough server memory AND the datasets are large. – Statism

@down-Voter : appreciate the spirit and thanks for the support. This is an open community not the Place to show Personal revenge. – Mcnew

Answer 2

First off, do not use, as a general rule of thumb, static variables in an web application. These act as global variables and are not instantiated with each request.

I wouldn't also suggest you using DataTables all the way up to your UI layer. Instead, work with strongly-typed objects.

1. Make a Model of the object you are trying to bind.

Like for example if you have a table called person that has the following fields.

Id | first_name | last_name | audit_ts

You can create an object as such:

public class Person
{
    public int Id {get;set;}
    public string FirstName {get;set;}
    public string LastName {get;set;}
}

2. Now in a separate functions, in some class you can call your stored procedure from the database and then cast your table rows in the person table into the list of Person Object.

3. Now, instead of calling your stored procedure twice to get the same data, which only reduces your application's performance, what you can do is to instead of binding your grid view in your code behind at Page_Load event. Simply bind the HTML table after you make the call to your webmethod which I believe is in your code-behind. You can refer to this post regarding how to bind your HTML table with JSON object returned by your Ajax call.

4. This way, you are making one call to the server and to the database to use the same data to bind your table as well as your charts.

Allocution

Answer 3

This is a good use case for the little used Cache Object Many users understand ViewState and SessionState, however the Cache object is not as widely utilized, and although the concept is very similar, it is much more flexible.

If your page is calling 10 stored procedures twice (once for your grids and a second time for your charts) then lets improve the performance by roughly 100% by eliminating the extra calls with the Cache Object

Have one call to the stored procedures in a separate method that populate your data tables cache object, which is then reused throughout your application.

private void loadReport1IntoCache()
{
  //...load your data from DB into the Report1 variable here


  //this line is new, and it saves your data into a global Cache variable
  //with an absolute expiration of 10 minutes
  Cache.Insert("Report1", Report1, null,
  DateTime.Now.AddMinutes(10d), 
  System.Web.Caching.Cache.NoSlidingExpiration);


}

Then, when you are inside your other methods, you can use the Cache variable instead of calling stored procedures again. For example:

[System.Web.Services.WebMethod]
public static string GetDataReport1()
{
   //first load the application variable before performing your other work
   DataTable myCachedReport1Data = (DataTable)Cache["Report1"];
   //did the Cache expire?
   if (myCachedReport1Data == null)
   {
   //if so refresh it
   loadReport1IntoCache();
   //and then assign the variable the contents of the refresh and proceed
   myCachedReport1Data = (DataTable)Cache["Report1"];
   }

   //other work here, utilizing the myCachedReport1Data variable
}

and for your grid binding:

private void gvbindReport1()
{
    try
    {            
        DataTable myCachedReport1Data = (DataTable)Cache["Report1"];
        //did the Cache expire?
        if (myCachedReport1Data == null)
        {
          //if so refresh it
          loadReport1IntoCache();
          //and then assign the variable the contents of the refresh
          myCachedReport1Data = (DataTable)Cache["Report1"];
        }

        GdReport.DataSource = myCachedReport1Data ;
        GdReport.DataBind();
    }
    catch (Exception ex)
    {
        Log.Errlog("Error Occured in  gvbindReport1 : " +  ex.Message.ToString());
    }

}

Now, you will have to do a few things not mentioned here. You should consider when you want your Cache data to expire (the example given is 10 minutes). Also you should consider if you want it to be an Absolute Number of minutes (Absolute Expiry) or a number of minutes since last access (Sliding Expiry). In your case, probably absolute expiry, but only you know that. Then you will set the expiration when you are setting the variable contents.

See the Cache documentation here: https://msdn.microsoft.com/en-us/library/6hbbsfk6.aspx

Adding Cache data: https://msdn.microsoft.com/en-us/library/18c1wd61.aspx

Retrieving Cache data: https://msdn.microsoft.com/en-us/library/xhy3h9f9.aspx

Statism

Answer 4

Looking at the code sample that you have given (and the parameters date_from and date_to that you are passing to GetReportGraph()) I assume:

1. you have 2 input fields where user is specifying the date range and then submitting the data (causing postback), based on which you are filtering the records and showing in grid as well as chart.

2. as different users would be providing different date ranges, you don't want to show the same data to all users.

3. as the data is filtered, its not going to have thousands of records.

I'm not sure what functionality of grid view you are using. Is it used only to show read only tabular data? If yes, you can consider the approach given by @Nabin Karki Thapa. If not check the alternate approach below:

After you have got the data table and bound it to grid view, immediately serialize it to JSON and register it as a script block (define a JS variable and assign the serialized JSON as it's value).

On the client side, while charting, instead of invoking webmethod, to get the JSON object use the JS variable that you have registered. This way you will avoid the call to web method (AJAX) and extra stored procedure call altogether.

Nador