RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Baker's Dozen: 13 Tips for Building Database Web Applications Using ASP.NET 3.5, LINQ, and SQL Server 2005 Reporting Services : Page 3

ASP.NET 3.5 and LINQ provide the greatest amount of functionality yet for building data-aware web applications. Even if you prefer to write stored procedures, you can still leverage some of LINQ to SQL functionality for accessing stored procedures inside .NET.

Tip 4: Subclassing Master Pages
If you're strong enough in object-oriented development, you may have wondered about subclassing the master page. Here's an example that shows how to create an abstract class for all future master pages:

   public abstract class cgsBaseMasterPage 
      : MasterPage 
      public abstract Label HeaderText { get; }
      public abstract Label FooterText { get; } 
Then, in any master page that you create, simply refer to the abstract class:

   public partial class cgsAppMasterPage : 
      public override Label HeaderText {
         get { return lblHeader; } }   
      public override Label FooterText {
         get { return lblFooter; } }   
      protected void Page_Load(object sender, 
         EventArgs e)  
Tip 5: AJAX Now Built In!
Roughly two years ago, Microsoft introduced ASP.NET AJAX, which allowed developers to build more responsive user interfaces. Generally, AJAX provides tools so that developers can place web content inside update panels, for purposes of doing partial page refreshes. The end result is that web applications appear more like a standard Windows Forms application, as opposed to the common web scenario, where the browser must fetch and refresh an entire page for every change.

Initially, ASP.NET AJAX was a separate download, because Microsoft released AJAX after Visual Studio 2005. However, AJAX is now part of Visual Studio 2008 and ASP.NET 3.5. After you install Visual Studio 2008, you'll immediately see the AJAX extensions as part of the toolbox (see Figure 3).

Figure 3. AJAX Extensions: The figure shows the AJAX extensions in the Visual Studio toolbox.
Note that while Visual Studio 2008 automatically ships with the AJAX extensions, the AJAX Control Toolkit is still available as a separate download. The toolkit is a set of customizable and extensible ASP.NET AJAX extenders and controls to create interactive web experiences.

Tip 6: Improved Support for Nested Master Pages
ASP.NET 2.0 introduced the concept of master pages, which allowed web developers to easily build web pages with consistent layout. ASP.NET 2.0 also allowed you to build nested master pages so you could build a master page based on another master page. Unfortunately, the WYSIWYG editor in Visual Studio 2005 didn't allow you to edit the nested master page—you had to edit the master page manually.

Fortunately, the WYSIWYG editor in Visual Studio 2008 does allow you to edit nested master pages. Scott Guthrie wrote a good blog post on this subject that includes an example of creating single-column and multi-column master pages.

Tip 7: Paging: Why You Still Can't Beat a Stored Procedure
A common task in web applications is paging result sets. A user may run a query that results in 500 matching rows, but you may only want to show the user 20 rows at a time. Several of the data-bound controls (such as the GridView) offer paging capabilities—but these generally work by retrieving all 500 rows from the database, persisting them in the application layer, and then paging them to the user, one page at a time.

While this approach works, it also possibly retrieves a large number of rows that might never be displayed, and those get stored in the application layer. A more efficient (and scalable) method is to manage the paging in a database stored procedure.

In the example with the 500 rows, suppose you are showing 20 rows at a time, in name order. In SQL Server, you can write the query to return the 500 rows into a temporary table or a Common Table Expression (CTE), and then execute a subsequent query to retrieve and return rows 1 through 20. Then if someone clicks "Next" on a navigation toolbar, you can run the same process to return rows 21 through 40. (In the application, you'll need to track the relative row position that you want to use as the starting point for a subsequent result set.)

SQL Server 2005 makes it easy to handle this through the ROW_NUMBER function that assigns a sequential ranking to each row in a result set, relative to whatever sort order you specify. For example, the following SQL Server 2005 code stores the results of the first query into a CTE:

   WITH CustListTemp AS
     CustomerID, LastName, FirstName,
     Address, City, State, Zip, 
     CASE @SortCol
         THEN LastName + Firstname
       WHEN 'ZIP'  
         THEN ZIP+LastName+Firstname
       ELSE LastName + Firstname 
   AS RowNum
   FROM Customers)
A subsequent query against the CTE retrieves the relative row range (for example, rows 21-40, rows 101-120, etc.), using supplied parameters for the starting row (@StartRowIndex) and the number of rows to retrieve (@MaxRows):

   SELECT TOP (@MaxRows) CustomerID, LastName,
     FirstName, Address, City, State, Zip, 
     RowNum FROM (SELECT CustListTemp.*,
     (SELECT COUNT(*) from CustListTemp) AS RecCount
   FROM CustListTemp )CustList 
     (CASE @StartRowIndex 
      WHEN -1 THEN  RecCount - @MaxRows 
      ELSE  @StartRowIndex END )
     (CASE @StartRowIndex 
      WHEN -1 THEN ( RecCount )- @MaxRows 
      ELSE @StartRowIndex END) + @MaxRows 
   THEN 1
   ELSE 0 END  = 1
I covered this technique in detail in an earlier article that first appeared in the March/April 2007 issue of CoDe Magazine.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date