Using Parameterized Query in ASP.NET, Part 2

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