Migrating Classic ASP To ASP.NET Razor Web Pages Part Two- Data Access

The other articles in the series are

• Razor Syntax And Visual Basic
• Include Files and Reusable Code

I am using the Classifieds sample from the code download that comes with Chapter 15 of Wrox' seminal ASP title: Beginning Active Server Pages 3.0 (ISBN: 978-0-7645-4363-0) to illustrate the challenges faced when migrating from classic ASP to ASP.NET Razor Web Pages. The sample makes use of an Access mdb file as a datastore, and all SQL is presented inline with the code. Since the Classifieds sample was created to illustrate various lessons from the book, it contains a useful mix of approaches to communicating with the database. I will cover how to handle each of these, as well as some that don't feature in the sample.

The vast majority of classic ASP sites used plain ADO code for data access. Connections to databases are declared and opened in include files and made available to all pages that need one. The RecordSet object is used as the container for data that has been retrieved from the database. In many cases, RecordSets were also used for inserting and updating data, although this was considered to be a cause of poor performance for busier sites. The Classifieds sample includes examples of this as well as code that is susceptible to SQL Injection, where user input is concatenated directly into SQL statements. This can be very dangerous. The migration offers an opportunity to improve the performance and security of the code.

ASP.NET offers a range of data access technologies. ADO.NET is the successor to ADO and features a similar collection of objects and methods. It forms the basis of all data access within the .NET framework. The Entity Framework is also an option. This is an Object/Relational Mapping tool (ORM). It maps data taken from the database to strongly typed objects that represent the entities that your application is concerned with. In the case of the Classifieds sample, these entities would include an Item object which has properties such as Description, AskingPrice and so on. The Entity Framework requires a fair amount of learning, so it is not a candidate for this migration. The Web Pages framework includes a helper class called Database, which acts as a wrapper around ADO.NET code. It greatly simplifies data access code and is probably the best option for a quick migration. Nevertheless, this article will feature examples of both the Database helper at work as well as plain ADO.NET code.

Connection Strings

The custom in classic ASP is to keep a connection string in an include file and to use that to open a connection in every page. In ASP.NET, connection strings are usually kept in an XML-based configuration file named web.config. A simple web.config file is authomatically generated when you create a site from the Empty Site template. Here it is with the connection string added:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="classified" 
             connectionString="Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|/Classified_2000.mdb" 
             providerName="System.Data.OleDb" />
    </connectionStrings>
    <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
</configuration>

The connection string is placed in a connectionStrings node within the configuration section of the config file. It has a name and a connectionString attribute which is in a similar format to the one that exists in the classic ASP site. The Data Source keyword points to a location called DataDirectory which is delimited by pipe symbols:

|DataDirectory|

At runtime this resolves to a location that has been designated as the home for data files. By default, in ASP.NET this is a folder named App_Data. It is a special folder that is protected by the ASP.NET framework from being browsed directly despite the fact that it is within the root of the web site. Consequently, it is a secure location for file based databases like Access or SQL Compact data files.

The final part of the connection string entry is the providerName value. This specifies the .NET framework data provider library that will be used for connecting to the database and issuing commands against it. System.Data.OleDb is the library used for Jet (and other OleDb providers). If you wanted to use the generic ODBC provider, you would specify System.Data.Odbc instead. If you are using SQL Server, the appropriate provider is System.Data.SqlClient.

Plain ADO.NET

The ADO code in the sample mainly uses Recordset objects for inserting and updating data. It also features the use of the Command object for certain operations. The Recordset's ADO.NET contemporary is the Dataset. It is possible to use this to update and insert data, but the recommended approach is to use the ADO.NET version of the Command object to execute SQL INSERT and UPDATE statements against the database.

The ADO.NET API is very similar to the ADO one. To illustrate this, here's the AddBid page from the classic ASP site followed by a .NET version:

<!--#include file="Clssfd.asp"-->
<%
  Dim objCmd, rsHighBid, varHighBid

  Set objCmd = Server.CreateObject("ADODB.Command")
  Set objCmd.ActiveConnection = objConn
  strSQL = "SELECT Max(BidAmount) AS MaxBidAmount FROM Bid " & _
           "WHERE ItemID = " & Request("ItemID") & ";"
  objCmd.CommandType = adCmdText
  objCmd.CommandText = strSQL
  Set rsHighBid = objCmd.Execute

  If IsNull( rsHighBid("MaxBidAmount") ) Then
    varHighBid = 0
  Else
    varHighBid = rsHighBid("MaxBidAmount")
  End If
  rsHighBid.Close
  Set rsHighBid = Nothing
    
  Dim rsBid
  Set rsBid = Server.CreateObject("ADODB.Recordset")
  rsBid.Open "Bid", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable
  rsBid.AddNew
  rsBid("ItemID") = Request.Form("ItemID")
  rsBid("BidderID") = Session("PersonID")
  rsBid("BidAmount") = CCur(Request.Form("Bid"))
  rsBid("BidChange") = CCur(Request.Form("Bid")) - varHighBid

  rsBid.Update
  Response.Redirect "BrowseListings.asp"
%>

@Imports System.Configuration
@Imports System.Data.OleDb
@Imports System.Data
@Code
    Dim strConnect = ConfigurationManager.ConnectionStrings("classified").ConnectionString   
    Dim strSQL = "SELECT Max(BidAmount) AS MaxBidAmount FROM Bid WHERE ItemID = @Id;" 
    Dim varHighBid = 0
    Using objConn = New OleDbConnection(strConnect)
        Using objCmd = New OleDbCommand(strSQL, objConn)
            objCmd.Parameters.AddWithValue("@Id", Request("ItemID"))

            objConn.Open()
            Dim result = objCmd.ExecuteScalar()
            If Not IsDBNull(result) Then varHighBid = Convert.ToInt32(result)
        End Using
        strSQL = "INSERT INTO Bid (ItemId, BidderId, BidAmount, BidChange) VALUES " & _
                 "(@ItemId, @BidderId, @BidAmount, @BidChange)"
        Using objCmd = New OleDbCommand(strSQL, objConn)
            With objCmd
                .Parameters.AddWithValue("@ItemId", Request.Form("ItemID"))
                .Parameters.AddWithValue("@BidderId", Session("PersonID"))
                .Parameters.AddWithValue("@BidAmount", Request.Form("Bid"))
                .Parameters.AddWithValue("@BidChange", Request.Form("Bid") - varHighBid)
                .ExecuteNonQuery()
            End With
        End Using
    End Using
    Response.Redirect("BrowseListings.vbhtml") 
End Code

The first three lines in the .NET version are Imports statements. They allow types that belong to the specified namespaces to be used without having to provide their fully qualified names. The first one makes the System.Configuration namespace available which contains the ConfigurationManager class. This provides an API to access the web.config file. You can see it used to obtain the connection string that you created earlier. Without the Imports statement, the code required would be:

Dim strConnect = System.Configuration.ConfigurationManager.ConnectionStrings("classified").ConnectionString

The Connection and Command objects are instantiated within Using blocks. This ensures that the objects are marked for closing and disposal by the runtime when the End Using line is reached. This way, memory leaks are prevented without you having to remember to call Close() on the objects.

The example above uses parameters to prevent any possibility of SQL Injection - a precaution that is noticeably absent from the classic ASP code. Even with this additional layer of safety, the .NET version requires less code than the classic ASP version. There is no need to explicitly state the CommandType of the Command object because CommandType.Text is the sensible default option in .NET. The second block of code shows a new item being created in the database via a SQL INSERT statement rather than populating a Recordset or DataSet with a copy of the target table first.

The Database Helper

Learning the ADO.NET API isn't particularly challenging but applying that knowledge in code can get fairly boring when you need to write pretty much the same thing over and over again. The Database helper class was introduced partly to simplify data access in Web Pages, but also to reduce the amount of code you need to write. The following code achieves exactly the same thing as the ADO.NET code above:

@Code
    Dim objConn = Database.Open("classified")
    Dim strSQL = "SELECT Max(BidAmount) AS MaxBidAmount FROM Bid WHERE ItemID = @0;"
    Dim varHighBid = 0
    Dim result = objConn.QueryValue(strSQL, Request("ItemID"))
    If Not IsDBNull(result) Then varHighBid = result
    strSQL = "INSERT INTO Bid (ItemId, BidderId, BidAmount, BidChange) VALUES " & _
                 "(@0, @1, @2, @3)"
    objConn.Execute(strSQL, Request.Form("ItemID"), Session("PersonID"), Request.Form("Bid"),
                    Request.Form("Bid") - varHighBid)
    Response.Redirect("BrowseListings.vbhtml")
End Code

The Database class has a number of methods, two of which are used here: QueryValue and Execute. The QueryValue method is used to retrieve a single value. Since that is all the SQL asked for, it was used to obtain the highest current bid. The Execute method is used when no return value (except the number of affected rows) is required from the database operation. It is used for INSERT, UPDATE and DELETE commands. The other notable methods are Query and QuerySingle. The first of those is used to retrieve multiple rows while the second brings back the first or only row. Parameter markers are always named @0, @1, @2 and so on, incrementing by 1 each time. The parameter values are passed in after the SQL to be executed.

Here's a example of the Query method being used in item.vbhtml and the results being output to the browser:

@If Request.QueryString("Action") = "Edit" Then
    strSQL = "SELECT * FROM Bid WHERE ItemID = @0" & _
    " ORDER BY TimeStamp DESC;"
    Dim rsBids = objConn.Query(strSQL, Request("Item"))

    If Not rsBids.Any() Then
    @<p>No bids currently placed</p>
    Else
        Dim blnFirst = True
    @<p>Bid History (Newest to Oldest)</p>

    @<table border="2" cellspacing="3" cellpadding="3">

        <tr>
            <th>Bidder ID</th>
            <th>Timestamp</th>
            <th>Amount Bid</th>
            <th>Last Change</th>
        </tr>
        @For Each item In rsBids
            @<tr>
                <td>@item.BidderID</td>
                <td>@item.Timestamp</td>
                <td align=RIGHT>@Convert.ToDecimal(item.BidAmount).ToString("c")</td>
                <td align=RIGHT>@Convert.ToDecimal(item.BidChange).ToString("c")</td>
                @If blnFirst Then
                    @<td align=RIGHT>
                        <a href="saledetailsforseller.vbhtml?item=@Request("item")&BidID=@item.bidid">
                            Click to sell to this bidder
                        </a>
                    </td>
                End If
            </tr>
            blnFirst = False
        Next
    </table>
    End If
End If

The results of the Query method are returned to an IEnumerable(Of Object). At runtime, these objects are late bound to DynamicRecord objects, which have properties dynamically generated from the database column names. That is why dot notation is used to reference the values instead of the indexing syntax that is used when iterating fields in an ADO RecordSet object. Having said that, you can use string indexers if you like (illustrated below) which might make migration a bit quicker:

@For Each item In rsBids
    @<tr>
        <td>@item("BidderID")</td>
        <td>@item("Timestamp")</td>
        <td align=RIGHT>@Convert.ToDecimal(item("BidAmount")).ToString("c")</td>
        <td align=RIGHT>@Convert.ToDecimal(item("BidChange")).ToString("c")</td>
        @If blnFirst Then
            @<td align=RIGHT>
                <a href="saledetailsforseller.vbhtml?item=@Request("item")&BidID=@item.bidid">
                    Click to sell to this bidder
                </a>
            </td>
        End If
    </tr>
    blnFirst = False
Next

In classic ADO, you test to see if the RecordSet EOF or BOF property is true to determine if any data was returned by the query. In .NET, you can use the Any() extension method, and then a simple For Each... Next loop.

Stored Procedures

The Database helper is hardwired to work only with CommandType.Text. Nevertheless, it is possible to use the helper to execute stored procedures (or saved queries in the case of Access) using the following syntax, where 'GetItemsByCategory' is the name of the procedure:

Dim objConn = Database.Open("classified")
Dim strSQL = "EXEC GetItemsByCategory @CategoryId = @0"
Dim data = objConn.Query(strSQL, Request("CategoryId"))

Additional parameters are specifed in a comma-separated list and their value placeholders are named and numbered in exactly the same way as for the commands you have seen already.

At this point, the migration is complete. The application has been re-written in ASP.NET without wholesale changes. Large chunks of HTML have remained untouched. Blocks of code have been upgraded from VBScript to VB.NET without a lot of changes - mainly those that are needed to cope with a language that requires variables to be typed at declaration, either explicitly using the As keyword, or implicitly through assignment. The data access code has required most overhaul, but the the general pattern of the code is so similar to ADO that the learning curve is not steep.

The sample is unusual in that in only features one Include file - the one that created and opened a connection on each page. The line of code that made the file content available to each page was replaced with a different line that resulted in exactly the same thing:

Dim objConn = Database.Open("classified")

This article has looked at two data access options for classic ASP developers migrating to ASP.NET. Both share similarites to ADO which may make them easier to assimilate and get you migrated sooner rather than later. Nevertheless, there are a wide trange of other options available, including Microsoft's recommended data access technology - Entity Framework. this framework allows you to work with data in an Object Oriented way. You can find more by visiting the Entity Framework tag on this site.

In the next article, I explore how the equivalent of Include files are handled in Razor Web Pages and look at a number of ways in which code can be reused.