Using Parameterized Query in ASP.NETBy Hendry Cahyadi
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:
- The SQL Data Provider, and
- 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:
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
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.
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:
Our query take input values from variables that we defined before (
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:
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!