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

5 (5 votes)

The first article in this series explored the differences (and similarities) between classic ASP inline syntax and Razor syntax, the templating technology behind ASP.NET Razor Web Pages. The work required so far to port intermixed HTML and simple conditional and selection statements across from classic ASP to Razor didn't require a lot of effort. That is about to change in this article as I look at data access.

The other articles in the series are

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" ?>
        <add name="classified" 
             connectionString="Provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|/Classified_2000.mdb" 
             providerName="System.Data.OleDb" />
        <compilation debug="true" targetFramework="4.0" />

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:


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.


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
    varHighBid = rsHighBid("MaxBidAmount")
  End If
  Set rsHighBid = Nothing
  Dim rsBid
  Set rsBid = Server.CreateObject("ADODB.Recordset")
  rsBid.Open "Bid", objConn, adOpenForwardOnly, adLockOptimistic, adCmdTable
  rsBid("ItemID") = Request.Form("ItemID")
  rsBid("BidderID") = Session("PersonID")
  rsBid("BidAmount") = CCur(Request.Form("Bid"))
  rsBid("BidChange") = CCur(Request.Form("Bid")) - varHighBid

  Response.Redirect "BrowseListings.asp"


@Imports System.Configuration
@Imports System.Data.OleDb
@Imports System.Data
    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"))

            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)
            End With
        End Using
    End Using
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:

    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)
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>
        Dim blnFirst = True
    @<p>Bid History (Newest to Oldest)</p>

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

            <th>Bidder ID</th>
            <th>Amount Bid</th>
            <th>Last Change</th>
        @For Each item In rsBids
                <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
                End If
            blnFirst = False
    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
        <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
        End If
    blnFirst = False

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.


You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 26394


- Byron Smith

Excellent article. I particularly like the part about the Database helper and the warning against SQL injection attacks. I do have a question, however. We use SQL Server 2005 at my work place (I really hope we can upgrade it to a newer edition, perhaps even to 2014 when it is released). Are there any performance penalties for using OleDb access as opposed to the SqlClient (in general, would there be any performance distinctions between using OleDb, Odbc, or SqlClient, for accessing a SQL Server such as 2005?). I am asking mainly out of idle curiosity, as I tend to write all my ADO.NET code against the SQL client. Unfortunately, I had to learn VB.NET and ADO.NET rather quickly and I have not yet learned the Entity Framework. Thank you for doing this series of posts.

- Mike

In a pure .NET environment, SqlClient is recommended for best performance. OleDb is the recommended provider for unmanaged code such as classic ASP.

- Byron Smith

Thank you. By the way, I just bought your book last night on WebMatrix from Wrox (I prefer buying the e-book PDF rather than having the Kindle version, though I might buy that too). I'm looking forward to reading it! I love WebMatrix and I hope that Microsoft will continue to support and update it for a long time to come, because I think it is perfect for beginning web developers.

- kiyokura

Nice article!

I think to use the DataBase class is a good way but I think it more better to use the Dapper dot net.

In particular , when necessary transaction , it is to the much simpler than using ADO.NET or DataBase class.

- Scott

Just an FYI: I get an exception when clicking the link for Razor Syntax and Visual Basic.

"A potentially dangerous Request.Path value was detected from the client (:)."

- Mike


Thanks for reporting that. It's been fixed now.

Recent Comments

Gfw 03/02/2017 09:48
In response to Free SSL Certificates On IIS With LetsEncrypt
I have used WinSimple for about the last 9 months - works great. One thing that you want to make of...

Ted Driver 02/02/2017 13:24
In response to Free SSL Certificates On IIS With LetsEncrypt
This looks great is you have command line access to your web server - what about those of us on Is...

Carl T. 06/11/2016 05:43
In response to Server.MapPath Equivalent in ASP.NET Core
Very succinct and easy to follow. Worked perfectly the first time for me. Thanks!!...

Manoj Kulkarni 04/11/2016 05:47
In response to Entity Framework Core DbContext Updated
Great post....

Sivu 19/10/2016 08:21
In response to Entity Framework Core TrackGraph For Disconnected Data
Oh that's very very very nice ! Thanks for the write up Mike, much appreciated for the taking the to...

Mark 12/10/2016 16:42
In response to ASP.NET Web Pages vNext or Razor Pages
Although "Web Pages" was removed from the roadmap, has it just been renamed to "Razor Pages"?...

Satyabrata 12/10/2016 09:20
In response to Entity Framework Core TrackGraph For Disconnected Data
Nice article. Please write more articles featuring ASP.Net web pages. Thank you...

Julian 26/09/2016 14:27
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Fantastic, many thanks Mike! Had got half way down this road before finding your article - saved...

Abolfazl Roshanzamir 14/09/2016 05:36
In response to Loading ASP.NET Core MVC Views From A Database Or Other Location
Nice article. Thanke you so much ....

cyrus 02/09/2016 15:12
In response to ASP.NET Web Pages vNext or Razor Pages
I've got some news. As Damian stated in this link: https://github.com/aspnet/Mvc/issues/5208 “We...