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

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.

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.

 

Date Posted: Monday, March 3, 2014 1:26 PM
Last Updated: Sunday, October 5, 2014 12:04 PM
Posted by: Mikesdotnetting
Total Views to date: 15598

4 Comments

Wednesday, March 5, 2014 7:51 AM - 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.

Wednesday, March 5, 2014 8:26 AM - Mike

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

Wednesday, March 5, 2014 9:45 PM - 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.

Thursday, March 13, 2014 1:37 PM - 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.
https://code.google.com/p/dapper-dot-net/

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

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Bino 11/27/2014 7:05 PM
In response to MVC 5 with EF 6 in Visual Basic - Async and Stored Procedures with the Entity Framework
Copy +...

Manas 11/27/2014 5:30 AM
In response to Scheduled Tasks In ASP.NET With Quartz.Net
Hi Mike, Thank you for awesome article. My concern is it might impact website if we use or is...

priya 11/26/2014 6:50 PM
In response to Create PDFs in ASP.NET - getting started with iTextSharp
very nice.....its save my time...

ransems 11/24/2014 12:29 AM
In response to Adding A Controller
Love the article. I dislike that the world thinks c# articles are the way to go. Love the VB, keep...

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...