Sorting a large gridview in c # from memory
My "senior" programmer stores the grid data in a session variable so it can be sorted.
Every day we have to restart our server because it starts to dangle. I feel like this is because all of these session variables are being moved around in memory.
Is this the best way to sort the data? Is there a best practice? We have to use Stored Procedures because that's his rule. The web application is very slow and I don't know enough about SQL to speed it up.
Any thoughts on if session variables can go? Is there a better way?
DATA UPLOAD METHOD
SqlConnection conn = new SqlConnection (ConfigurationManager.AppSettings ["ConnectionString"].ToString ());
SqlCommand cmd = conn.CreateCommand ();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "RandomStoredProdcedureName";
try
{
DataTable GridData = new DataTable ();
conn.Open ();
using (SqlDataAdapter Sqlda = new SqlDataAdapter (cmd))
{
Sqlda.Fill (GridData);
}
//Persist the table in the Session object. (for sorting)
Session ["GridData"] = GridData;
gvDetails.DataSource = GridData;
gvDetails.DataBind ();
}
catch (Exception ex)
{
Removed for brevity
}
finally
{
if (conn != null) {
conn.Close ();
}
}
SORTING METHOD
protected void gvDetails_Sorting (object sender, GridViewSortEventArgs e) {
//Retrieve the table from the session object.
DataTable GridData = Session ["GridData"] as DataTable;
if (GridData != null) {
//Sort the data.
GridData.DefaultView.Sort = e.SortExpression + " " + GetSortDirection (e.SortExpression);
gvDetails.DataSource = GridData;
gvDetails.DataBind ();
}
}
source to share
The user cannot view more entries than the number of rows shown by your grid - if your grid has 25 rows, all more than 25 rows is a waste of memory / CPU time to process. (Keep in mind that these numbers are not a'absolutes - some applications may need more lines, some may need less. This also depends on how well users can see and understand the data. Some users work better with multiple lines at a time, some require many lines - it might even be user preference, but the bottom line is that pulling out a huge result set is almost always a waste.)
You shouldn't be fetching more data from the database at a given time than the number of rows you can display in the grid. The sort must be done on the database so that you can only return the subset of rows that you really need. This minimizes network traffic between your webserver and the database server, and minimizes memory usage on the webserver as you only store the data in the view / session state you really need.
If you use view state to store data (which is better than using Session
it because you only store data as long as the user is looking at a particular grid page) that data is sent to the browser - you also minimize network traffic between the web server and the browser.
If the user tries to sort the data differently or navigate to a different page, simply repeat the process above and grab new lines according to the new page index and / or new sort field.
Edit
I just noticed that you added a comment on the question that you usually have 15 users and return 50-1000 rows. Unless your web server has very little memory (or if your database rows are huge), this is unlikely to cause memory errors. Are you storing other large result sets in session variables?
source to share
I think the sort can be done in SQL. But we also need to see SP. I never keep DataTables in sessions and I don't think this is a good way.
If the data is too big to get from the database every time your developer needs it, he can create a view and get the data from that perspective. It will be much faster
source to share
I can't see where the DataTable is, there is a problem. My suggestion is to do something like this
public void GetData()
{
using(var conn = new SqlConnection (ConfigurationManager.AppSettings ["ConnectionString"].ToString ()))
{
SqlCommand cmd = conn.CreateCommand ();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "RandomStoredProdcedureName";
conn.Open ();
try {
using(var GridData = new DataTable ())
{
using (SqlDataAdapter Sqlda = new SqlDataAdapter (cmd))
{
Sqlda.Fill (GridData);
}
//Persist the table in the Session object. (for sorting)
Session ["GridData"] = CREATE_YOUR_OWN_OBJECTS_FROM_THE_DT(GridData);
}
} catch (Exception ex) {
Removed for brevity
} finally {
if (conn != null) {
conn.Close ();
}
}
}
}
Of course, there are better ways to do this, in which case you have to implement sorting, but I'm pretty sure the problem is with IDisposable objects that were never placed at all
source to share