I have gone through two different coding standards and am confused as to which one is the best and good
for
development. I would like to share this with you all and take your opinions for wiping out my confusion.
Scenario:
Displaying data from sql server with paging and sorting.
Coding Standard One:
Well, in this one i used an ObjectDataSource with Datatables and displayed the data in gridview.
Created a function in a class file with return type as Datatable. Filled the Datatable with a Simple Select
Stored Procedure and called that function in Object Data Source.
The advantage i found with this one was that, since i had used datatable, not much coding was required for
paging and sorting. Infact, no coding at all for paging. I was able to take the advantage of the features
provided by gridview.
Coding Standard Two:
In this one i used a Generic List instead of a Datatable. I created a Stored Procedure which handles both
the paging and sorting for me using row_number()
Example:
SELECT * FROM
(
SELECT
row_number() OVER (ORDER BY @SortColumn + ' ' + @SortDirection) AS
inRownumber,
(SELECT COUNT(inCategoryId) FROM tblAYBCategory) AS inRecordCount,
inCategoryId,
stCategoryName,
stCategoryDesc,
inParentId
FROM tblAYBCategory
GROUP BY inCategoryId, stCategoryName, stCategoryDesc, inParentId
) AS PAGED
WHERE PAGED.inRownumber BETWEEN (@pagenum - 1) * @pagesize + 1 AND
@pagenum * @pagesize
This would get me only those records which i need to display on the page rather than getting all the 10,000
records and that too with sorting.
I filled the Generic List with the results i got and then binded that with a Repeater.(Since i was not using
the Gridview's paging and sorting feature, i decided to omit it and use a repeater.)
My coding for paging and sorting increased to some extent as compared to Coding Standard One. I also found
it search engine friendly as i could use querystrings for paging parameters.
I used both of them with test records of around 18,000. And both of them worked well with good speed.
Now my dilemma is which one to use in a development scenario. Performance and Development Time both are
important criteria.
Any feedback would be highly appreciated.
Thanks
Humans are like molecules !!!
Stuck to each other and depending on each other for existence !!!
Both the solutions work well and are optimal in terms of coding in front-end and load in the ViewState.
We all know, if you are using DataTable Stored in ViewState to bind the Paged Grids then all the records are stored in datatable in viewstate. This is beneficial in a way that page doesn't makes a round trip to sql server to fetch those records on every
Next Prev page call in the Grid, it simply binds the grid to the ViewState. On the other hand the limitation is the page becomes heavy to load for the first time as it needs to load all the records in the ViewState. This Problem was solved by the ObjectDataSource
control introduced in .NET Framework 2.0. Now the page load all the records but doesn't keeps it in the ViewState, it simply manages the calls as per the Current Page to be displayed on the Grid, but to implement the same SQL server is queried for the Whole
Result Set which is still unfair to Servers.
Talking about the Other Approach.... Row_Number() filter
It is a flexible but a little confusing method, it needs some extra efforts on the Developer's End but for Quality, it is always acceptable. In this approach no result set is stored in ViewState, agreed to the fact that every time, when Next Prev page is
called a call to the SQL Server is made, but it returns only a few records like 10 or 20 or Max. 50 and 100 which is always acceptable and highly optimized.
Conclusion : Both approaches are good enough to be implemented in a Robust Application, considering your requirement you may opt for any of the two.
I'm personally in favor of the Row_number Filter approach (IInd One). :)
Marked as answer by vinod1285 on Feb 04, 2009 11:21 AM
Thanks for your reply. Even i'm personally in favor of using the 2nd approach.
But as a developer it hurts to do that extra bit of coding for the same pay package. :D :P ;)
Wish there were some sort of incentives for doing some extra work for improved application performance. ;)
Also, its tough to make some of the other developers understand who are pampered by the grid's inbuilt
features. They feel like they are being asked to go back to PHP/Classic ASP style of coding. :)
Anyways, thanks again, and i will go for the second approach. ;)
Humans are like molecules !!!
Stuck to each other and depending on each other for existence !!!
vinod1285
Member
68 Points
17 Posts
ObjectDataSource + Datatable vs Generic List + Stored Procedure with row_number() for paging & so...
Feb 03, 2009 02:10 PM|LINK
Hi All,
I have gone through two different coding standards and am confused as to which one is the best and good
for
development. I would like to share this with you all and take your opinions for wiping out my confusion.
Scenario:
Displaying data from sql server with paging and sorting.
Coding Standard One:
Well, in this one i used an ObjectDataSource with Datatables and displayed the data in gridview.
Created a function in a class file with return type as Datatable. Filled the Datatable with a Simple Select
Stored Procedure and called that function in Object Data Source.
The advantage i found with this one was that, since i had used datatable, not much coding was required for
paging and sorting. Infact, no coding at all for paging. I was able to take the advantage of the features
provided by gridview.
Coding Standard Two:
In this one i used a Generic List instead of a Datatable. I created a Stored Procedure which handles both
the paging and sorting for me using row_number()
Example:
SELECT * FROM
(
SELECT
row_number() OVER (ORDER BY @SortColumn + ' ' + @SortDirection) AS
inRownumber,
(SELECT COUNT(inCategoryId) FROM tblAYBCategory) AS inRecordCount,
inCategoryId,
stCategoryName,
stCategoryDesc,
inParentId
FROM tblAYBCategory
GROUP BY inCategoryId, stCategoryName, stCategoryDesc, inParentId
) AS PAGED
WHERE PAGED.inRownumber BETWEEN (@pagenum - 1) * @pagesize + 1 AND @pagenum * @pagesize
This would get me only those records which i need to display on the page rather than getting all the 10,000
records and that too with sorting.
I filled the Generic List with the results i got and then binded that with a Repeater.(Since i was not using
the Gridview's paging and sorting feature, i decided to omit it and use a repeater.)
My coding for paging and sorting increased to some extent as compared to Coding Standard One. I also found
it search engine friendly as i could use querystrings for paging parameters.
I used both of them with test records of around 18,000. And both of them worked well with good speed.
Now my dilemma is which one to use in a development scenario. Performance and Development Time both are
important criteria.
Any feedback would be highly appreciated.
Thanks
Stuck to each other and depending on each other for existence !!!
alok.arora
Contributor
3058 Points
530 Posts
Re: ObjectDataSource + Datatable vs Generic List + Stored Procedure with row_number() for paging ...
Feb 03, 2009 03:06 PM|LINK
Hi Vinod,
Both the solutions work well and are optimal in terms of coding in front-end and load in the ViewState.
We all know, if you are using DataTable Stored in ViewState to bind the Paged Grids then all the records are stored in datatable in viewstate. This is beneficial in a way that page doesn't makes a round trip to sql server to fetch those records on every Next Prev page call in the Grid, it simply binds the grid to the ViewState. On the other hand the limitation is the page becomes heavy to load for the first time as it needs to load all the records in the ViewState. This Problem was solved by the ObjectDataSource control introduced in .NET Framework 2.0. Now the page load all the records but doesn't keeps it in the ViewState, it simply manages the calls as per the Current Page to be displayed on the Grid, but to implement the same SQL server is queried for the Whole Result Set which is still unfair to Servers.
Talking about the Other Approach.... Row_Number() filter
It is a flexible but a little confusing method, it needs some extra efforts on the Developer's End but for Quality, it is always acceptable. In this approach no result set is stored in ViewState, agreed to the fact that every time, when Next Prev page is called a call to the SQL Server is made, but it returns only a few records like 10 or 20 or Max. 50 and 100 which is always acceptable and highly optimized.
Conclusion : Both approaches are good enough to be implemented in a Robust Application, considering your requirement you may opt for any of the two.
I'm personally in favor of the Row_number Filter approach (IInd One). :)
vinod1285
Member
68 Points
17 Posts
Re: ObjectDataSource + Datatable vs Generic List + Stored Procedure with row_number() for paging ...
Feb 04, 2009 08:38 AM|LINK
Hi Alok
Thanks for your reply. Even i'm personally in favor of using the 2nd approach.
But as a developer it hurts to do that extra bit of coding for the same pay package. :D :P ;)
Wish there were some sort of incentives for doing some extra work for improved application performance. ;)
Also, its tough to make some of the other developers understand who are pampered by the grid's inbuilt
features. They feel like they are being asked to go back to PHP/Classic ASP style of coding. :)
Anyways, thanks again, and i will go for the second approach. ;)
Stuck to each other and depending on each other for existence !!!