Can we use same datatable for pagemethod and webmethod in ASP.NET?
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"];
}
}
}
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.
- 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;}
}
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.
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.
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.
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