Published: Wednesday, September 26, 2001
Using Parameterized Query in ASP.NET, Part 2
By Hendry Cahyadi
Read Part 1
In Part 1 we examined the common approach developers use to issue a
SQL query. In this part, we'll examine a better way: using parameterized queries!
Using Parameterized Queries
With parameterized queries, instead of using the various markup signs, we can use a parameter as an input sign
for our query. Now, here's how the former code appears if we write it over using parameterized queries:
<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 > ? AND HireDate > ?) AND Country = ?)"
'Populate Command Object
Dim oCmd As New OledbCommand(sSQL, oCnn)
'Add up the parameter, associated it with its value
oCmd.Parameters.Add("EmployeeID", sEmpId)
oCmd.Parameters.Add("HireDate", sHireDate)
oCmd.Parameters.Add("Country", sCountry)
'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>
|
Take a look at the variable (sSQL) where we define our parameterized query. We use the question
mark sign (?) for each input into the query. Notice that we use the question mark regardless
of the type of our variable. To associated the parameter in the query we have to add parameters to the
Parameters collection of the Command object. You can give the name of your parameter as whatever
you want, as long as it's unique! Remember that you have to add the parameter in the same order as the order
of the question mark signs that you put in your query!
oCmd.Parameters.Add("EmployeeID", sEmpId)
oCmd.Parameters.Add("HireDate", sHireDate)
oCmd.Parameters.Add("Country", sCountry)
|
That's it! You've now got a parameterized queries, not requiring any specialized markup characters or
annoying escaping.
If you want to do another parameterized query with the same Command object, don't forget to clear the
Parameters collection. To accomplish this, simply call the Clear method of Parameters
collection to remove all of the parameters from the collection.
| Using Parameterized Queries with SQL Server |
|---|
|
When using the SqlClient data provider, you will have to use a slightly different syntax for your parameterized
queries. (Note that the syntax we examined thus far works fine in SQL Server when using the OleDb data
provider.) SQL Server allows
for a slightly different syntax for parameterized queries, though, one that closely resembles the syntax of
a SQL stored procedure. In a future article, we'll examine how to use this special parameterized query syntax
for Microsoft SQL Server.
|
Conclusion
Using parameterized query makes our tasks for doing query much more easy and simple. Your query is more readable, you just only need to remember single sign mark in your query. If you're using Sql Server, you can take advantage of using named parameter. And you don't have to bother to do escaping.
So when you think query, always think parameterized query.
Happy Programming
By Hendry Cahyadi
