For some time now stored procedures have been the preferred method of data access for enterprise application development. Stored procedures offer better security, better encapsulation, and can implement complex logic without cluttering up application code. However, they also come with disadvantages:
- Developers have a tendency to put business logic inside stored procedures
- You must change development environments to modify the procedures
- It is time consuming to look up the required parameters for a procedure
- Many times the functionality provided by stored procedures is more than required
In-line SQL code embedded into application code is another common choice for data access. While this method is rarely implemented for enterprise development, many small projects use this type of data access. Using in-line SQL allows for fast-paced development; however, you lose the security and encapsulation advantages provided by stored procedures.
Parameterized queries fall somewhere in between stored procedures and in-line SQL. They provide a way to develop secure, encapsulated data access routines, while enabling you to take advantage of the fast-paced development advantages of in-line SQL.
How to use parameterized queries
Using parameterized queries couldn't get much easier. For example, the following line of code (Figure A) illustrates how you would write a parameterized query:
In this example, we are selecting all customers that have a specified CustomerID. Notice that this is very similar to how you would write the Select statement in a stored procedure. The difference is that you embed this directly into your application code or in a resource file. (We'll discuss resource files in a moment.)
To enable ADO.NET to populate the @CustomerID parameter, you simply create a normal SqlParameter and add it to the SqlCommand.Parameters collection for the current command. You then execute the command on your desired connection, and ADO.NET takes care of setting up the command for execution on the SQL server. The following snippet (Figure B) is an example of how to setup and execute the whole command:
|The whole command|
As you can see, setting up and executing a parameterized query is a very simple process. With the help of a data access library, such as Microsoft's Data Application Blocks, the process is simplified to a further degree.
Disadvantages of parameterized queries
When it comes to programming, every approach has trade-offs, and the decision to use parameterized queries is no different. One of the main disadvantages is that since the queries are embedded into your application code, you could end up with the same query in multiple places. This duplication can be eliminated by creating a central location to store your queries. The location could be an XML file, a class in your application with public static string members, a custom .NET attribute, or even a flat file. Using these techniques you will be able to look up the needed query before executing it.
Another potential problem with using parameterized queries is that many companies do not allow any in-line SQL code in the application (and usually not in the data layer, either). I believe this is because when people talk of putting SQL into application code they are referring to ad-hoc (in-line) code, and not parameterized queries. A rule such as this also enables the DBA to have more control over the code which executes on the SQL server, which can be a very good thing for large databases.
When should I use parameterized queries?
You can use parameterized queries in any situation where you need operations executed on the SQL server. However, parameterized queries are best utilized when simple Create, Read, Update, and Delete (CRUD) operations need to be executed. If you are performing complex operations that may take a while to execute or comprise several different SQL statements, it would probably be best to keep the logic inside the SQL server.
While parameterized queries come in handy in many situations, I don't recommend them for use in complex data manipulation logic, because they can potentially clutter your application code. When you have cluttered application code, you inevitably run into severe issues with code maintenance and up-keep.
When it comes to programming data access routines, there are situations where parameterized queries are a better choice when compared to ad-hoc queries and stored procedures. Parameterized queries provide a good middle ground between the other two choices, and can greatly increase developer productivity when used correctly.