Using Parameterized Query in ASP.NET, Part 2By Hendry Cahyadi
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:
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!
That's it! You've now got a parameterized queries, not requiring any specialized markup characters or
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.|
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.