Me: I'm going to write some example code to properly compare table adapters with my other options.
Marc: But you already hate table adapters how are you going to be objective?
...a day later...
Me: Okay I'll start with stored procedures and data readers, nothing can be less fun than that.
So I proceeded to have lots of fun implementing my web site data layer using data readers and stored procedures.
To begin with I wanted to fill my GridView with some data. This is something I'm sure we've all done so many times. When I was first working with ASP.NET I begin with Scott Mitchell's data tutorials. These tutorial use dynamic SQL. After lots of advice and help from the guys on the SQLDownUnder list I have eliminated any dynamic SQL from my stored procedure. I think the result is nice and reasonably maintainable. This stored procedure has the following features that I find are usually requested in the systems I build:
- Paging
- Sorting
- Searching
To do paging two queries are required one that returns the page of results and one that gets the total number of results. I have chosen to make the total rows an output parameter for my stored procedure. This has two advantages I can see. The first advantage is that the where clause is repeated across both queries and this way they are in the same place for maintenance. The second plus is that it allows a single database round trip to get the list.
1 -- Author: Andrew Browne
2 -- Create date: 12 November 2007
3 -- Description: Retrieves a product list one
4 -- page at a time
5 -- =============================================
6 CREATE PROCEDURE [BrowniesBikes].[GetProductList]
7 @SearchString NVARCHAR(50) = '',
8 @StartRow INT = 1,
9 @EndRow INT = 10,
10 @SortColumn NVARCHAR(50) = '',
11 @TotalProducts INT OUTPUT
12 AS
13 BEGIN
14 SET NOCOUNT ON;
15
16 DECLARE @LIKE_SEARCH as nvarchar(50)
17 SET @LIKE_SEARCH = '%' + lower(@SearchString) + '%'
18
19 SELECT @TotalProducts = COUNT(*)
20 FROM Production.Product
21 WHERE(lower(Name) LIKE @LIKE_SEARCH
22 OR lower(ProductNumber) LIKE @LIKE_SEARCH)
23
24 SELECT RowRank, ProductID, Name, ProductNumber
25 FROM
26 (SELECT ProductID,
27 Name,
28 ProductNumber,
29 ROW_NUMBER()
30 OVER(
31 ORDER BY
32 CASE WHEN
33 @SortColumn = 'ProductNumber'
34 THEN
35 ProductNumber
36 ELSE
37 Name
38 END
39 )
40 AS RowRank FROM Production.Product
41 WHERE(lower(Name) LIKE @LIKE_SEARCH OR
42 lower(ProductNumber) LIKE @LIKE_SEARCH)
43 ) AS ProductsWithRowNumber
44 WHERE RowRank > @StartRow
45 AND RowRank <= @EndRow
46 END
This stored procedure will only work with SQL 2005 because of the over clause.
The code to retrieve this was fairly straight forward if you want all the details grab the source code. The main pain point that I found was this routine for manually mapping the results from the reader:
78 private static Product
79 GetProductSummaryFromReader(
80 IDataReader reader
81 )
82 {
83 Product product = new Product();
84 product.ProductID =
85 Convert.ToInt32(reader["ProductId"]);
86 product.Name =
87 Convert.ToString(reader["Name"]);
88 product.ProductNumber =
89 Convert.ToString(
90 reader["ProductNumber"]
91 );
92 return product;
93 }
This code feels a little fragile with all the convert calls in there but at least it's all in one place.
Print | posted on Monday, November 26, 2007 10:25 AM