GridView Paging with ObjectDataSource
Ok I wanna Blog about this.
I read on MSDN, Among the other Datasource, Like sql datasource , etc.
The only Data Source that can call by Page is Object Data Source. So it will not Take All Rows First Than do a Paging. Which is not efficient.
In order The Object Data Source to work with your Grid Request.
You need To : EnablePaging = True. If false it will Not Passed the startrowindex and MaximumRowsParameter.
Supply At Least
- StartRowIndexName if the name is different than ‘startrowindex
- MaximumRowsParameter if it’s different than default.
- TypeName , Your class Type with Namespace if necessary. it is where object data source will look for your select method, and all other operation method stuff
- SelectCountMethod, the method name in your TypeName that is used for counting total rows of the query
- SelectMethod, The method That is used for selecting rows. Here is where the startrowindex and maximumrowsparameter WIll Goes to.
- SortParameterName, If you want to support sorting , This is The name of param on selectMethod .object datasource will passed the gridview SortExpression property automatically
On your GridView, set the PageSize, this will effect the maximumrowsparameter
Example of SelectMethod
1:
2: /// <summary>
3: /// This returns a list of students based on paging.
4: /// </summary>
5: /// <param name="sortExpression">SortExpression,CustomFormat:[columname][asc/desc]</param>
6: /// <param name="startRowIndex">The starting Row from object data Source</param>
7: /// <param name="pageSize">The Size of Row, 5 in this case</param>
8: /// <returns></returns>
9: [DataObjectMethod(DataObjectMethodType.Select)]
10: public static List<Student> GetStudents(string sortExpression, int startRowIndex, int pageSize)
11: {
12: if (String.IsNullOrEmpty(sortBLOCKED EXPRESSION sortExpression = "IDDesc";
13: startRowIndex++;
14: int pagenumber = startRowIndex/pageSize;
15: pagenumber++;
16: var students = new List<Student>();
17: var parameters = new SqlParameter[4];
18: var param = new SqlParameter("PageNumber", SqlDbType.Int);
19: param.Value = pagenumber;
20: parameters[0] = param;
21:
22: param = new SqlParameter("PageSize", SqlDbType.Int);
23: param.Value = pageSize;
24: parameters[1] = param;
25:
26: var howManyRowsReturnedParam = new SqlParameter("@HowManyRows", SqlDbType.Int);
27: howManyRowsReturnedParam.Direction = ParameterDirection.Output;
28: howManyRowsReturnedParam.Value = "";
29: parameters[2] = howManyRowsReturnedParam;
30:
31: param = new SqlParameter("orderby", SqlDbType.VarChar);
32: param.Value = sortExpression;
33: parameters[3] = param;
34:
35:
36: using (
37: SqlDataReader dr = SqlHelper.ExecuteReader(Connections.Conn1, "GetStudent", CommandType.StoredProcedure,
38: parameters))
39: {
40: while (dr.Read())
41: {
42: var s = new Student();
43: s.ID = int.Parse(dr["ID"].ToString());
44: s.Name = dr["Name"].ToString();
45: s.City = dr["City"].ToString();
46: s.State = dr["State"].ToString();
47: s.Phone = dr["Phone"].ToString();
48: s.Email = dr["Email"].ToString();
49: s.CreatedOn = DateTime.Parse(dr["CreatedOn"].ToString());
50:
51: students.Add(s);
52: }
53: }
54: return students;
55: }
56: }
There are also Trick on your Store procedure for Dynamic Sorting, Like this:
1: ROW_NUMBER( ) over (order BY
2: case
3: when @orderby='IDAsc' then [ID] END ASC,
4: case
5: when @orderby='IDDesc' then ID END DESC,
6: case
7: when @orderby='NameAsc' then [NAME] END ASC,
8: case
9: when @orderby='NameDesc' then [NAME] END DESC,
10: case
11: when @orderby='CityAsc' then City END ASC,
So in order to make this custom Ascending / Descending work i need to put the custom sorting expression on variable
protected void gvStudent_sorting(object sender, GridViewSortEventArgs e)
{
string sortDirection = e.SortDirection == SortDirection.Descending ? "Desc" : "Asc";
SortBy = e.SortExpression + sortDirection;
}
Now this is also important, The Object Data Source will Call your Select Method Twice because of it’s own Cache Logic internal. so the first one will be like always requesting for first page. and the second call will be the correct StartRowindex. So it is used for Object Data Source Internal Checking. there’s nothing wrong with it
The object Data Source have a important Method Like Selecting. ODS will called this selecting twice , 1st call Is To Fetch Data, 2nd Is for Gettting The Total Count Method(return integer)
I passed in my Custom Sorting Expression Variable Value Here.
1: private void StudentDataSource_Selecting(object sender, ObjectDataSourceSelectingEventArgs e)
2: {
3: //fecthdata
4: if (!e.ExecutingSelectCount)
5: {
6: //make custom expression param
7: if (SortBy.Length > 0)
8: {
9: e.Arguments.SortExpression = SortBy;
10: }
11: }
12: }