When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, September 26, 2001

Using Parameterized Query in ASP.NET

By Hendry Cahyadi


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.

- continued -

'

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!

  • Read Part 2!



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article