Can we use same datatable for pagemethod and webme

问题:

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?

回答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 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 next tab (index) and refresh it (a new instance is created for dt). It will affect the datatable now you will get the new datatable in 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"];
        }
    }
}

回答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.

回答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

回答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.