Using Parameterized Query in ASP.NET

Introduction
One of the cool and useful things ADO.NET supports is the use of parameterized queries. Parameterized queries aren't a new feature, in fact some of you may already use them in traditional ASP programming. For those not familiar with parameterized queries they are queries that have one or more embedded parameters in the SQL statement. This method of embedding parameters into a SQL statement is, in my opinion, more straightforward and less prone to error than the method of dynamically building up a SQL string. In this article we'll look at how to use parameterized queries in ASP.NET.

First of all, let's step back and take a brief overview of data access in ASP.NET. Realize that while you can still use classic ADO through an ASP page, you really should use ADO.NET - it boasts better performance and contains more features. When using ADO.NET, though, remember that there are two .NET data providers:

1. The SQL Data Provider, and
   
2. The OLEDB Data Provider

If you're using Microsoft SQL Server 7.0 or greater as your RDBMS then you should use the SQL Data Provider because this provider is optimized for Microsoft SQL Server 7.0+ and, therefore, can communicate more efficiently with SQL Server than the OLEDB provider. The OLEDB Data Provider is intended to be used for any OLEDB-compliant data store (which includes Microsoft SQL Server, Microsoft Access, Oracle, Informix, etc.).

The Non-Parameterized Query Approach
Let's take look of the most common ways for performing a particular database query. This time, for our experiment, we will make a simple query to the NWind.mdb database (an MS Access database). We will use OLEDB .NET data provider because this one is not a SQL Server database. First we must add the appropriate directives to the top of our ASP.NET Web page:

<%@ Page Language="VB" %> <%@ Import Namespace="System.Data.OleDb" %>

That means we will use VB as the default coding language and we are going to use OLEDB .NET data provider. Now, here's the code for doing simple query using ADO.NET

<script language="vb" runat="server"> Sub Page_Load(sender as Object, e as EventArgs) 'Get the Database Path, to be used in DB Connection String Dim sDBPath As String = Request.MapPath(".") & "\NWind.mdb" 'Define DB Connection String Dim sCnnString As String = "Provider=Microsoft.Jet.Oledb.4.0;" & _ "Data Source=" & sDBPath 'Define variables as input for our sql query Dim sEmpId As Int32 = 1 Dim sHireDate As String = "1 January 1993" Dim sCountry As String = "USA" 'Populate Connection Object Dim oCnn As New OleDbConnection(sCnnString) 'Define our sql query Dim sSQL As String = "SELECT FirstName, LastName, Title " & _ "FROM Employees " & _ "WHERE ((EmployeeID >" & sEmpId & " AND HireDate >#" & _ sHireDate & "#) AND Country = '" & sCountry & "') " 'Populate Command Object Dim oCmd As New OledbCommand(sSQL, oCnn) 'Opening Connection for our DB operation oCnn.Open() 'Get the results of our query Dim drEmployee As OleDbDataReader = oCmd.ExecuteReader() 'Display Header Response.Write ("<H4>Query Results:</H4>") Try 'Loop over our query results & print it Do While (drEmployee.Read()) Response.Write("<BR>" & drEmployee.GetString(0) & " " & _ drEmployee.GetString(1) & " - " & drEmployee.GetString(2) ) Loop Finally 'Closing the data reader & connection object drEmployee.Close() oCnn.Close() End Try End Sub </script>

In the above code we use an OleDbDataReader object to store our query results, and get the DataReader as a return value from executing the ExecuteReader method of Command object. We also use new VB.NET keywords, i.e. try-finally, which is VB.NET's new approach to error handling (see Overview of Changes from VB6 to VB.NET for more details). Inside the try block we do loop over the values in the DataReader and print them. Now, notice the way we define our SQL query:

Dim sSQL As String = "SELECT FirstName, LastName, Title FROM Employees WHERE ((EmployeeID >" & sEmpId & " AND HireDate >#" & sHireDate & "#) AND Country = '" & sCountry & "')

Our query take input values from variables that we defined before (sEmpId, sHireDate, sCountry) so we have to deal with various marker signs, such as quote (') to enclose our string variables and the pound sign (#) to enclose our date type variables. These are the rule of thumbs, so if you do not follow them, your query will not work!

So What's wrong with this approach? Why are parameterized queries better? Well... nothing's wrong, per se, but it requires you to write down your query very carefully (you have to remember the correct marker for each type of data), also the readability of the SQL query is far from high (have you ever had to read a SQL query that's almost 100 lines long mixed up with those mark signs?). Also don't forget, if you take the input from user then you have to make it safe by escaping the input from the marker signs, like:

Replace(sCountry,"'","''")

So have you ever wondered if you could just simply get rid of all those problems? You can, and the answer is to use parameterized queries! In Part 2 we'll delve into parameterized queries!