Shorten development time using parameterized queries in Ado.net

Ado

For some time, stored procedures have been the preferred way for enterprise applications to develop data access. Stored procedures are more secure, encapsulate more powerful, and can perform complex logic without disrupting application code. However, it also has some drawbacks:

• Developers tend to add business logic to the stored process.

• You must change the development environment when you change the process.

• The parameters required for the lookup process are time-consuming.

• Many times, stored procedures provide more functionality than you need.

Inline SQL code embedded in the application code is another common method of data access. Although this approach is rarely used in the development process, many small projects apply this type of data access method. The application of inline SQL enables rapid development, but it does not have the security and encapsulation advantages of stored procedures.

A parameterized query is between a stored procedure and an inline SQL. It provides a secure, encapsulated approach to data Access program development and allows you to leverage the rapid development advantages of inline SQL.

How to apply a parameterized query

The application of parameterized queries is not so easy. For example, the following code (figure A) shows how to write a parameterized query:

Figure A parameterized Query

In this example, we select all users with the specified CustomerID. Note that this process is very similar to writing a SELECT statement in a stored procedure. The difference is that you embed it directly into your application code or source file. (We'll discuss the source file later.) ）

To enable Ado.net to transplant @customerid parameters, you simply create a normal sqlparameter and add it to the sqlcommand.parameters set of the current command. Then you can execute the command on the desired connection, ADO. NET establishes commands that are executed on the SQL Server. The following code fragment (Figure B) is an example that shows how to create and execute an entire command:

Figure B Entire command

As you can see, establishing and executing parameterized queries is a very simple process. This process can be further simplified with the aid of a data access library, such as the Microsoft Data Application Block.

Disadvantages of parameterized queries

When it comes to programming, each method has its advantages and disadvantages, and the decision to apply a parameterized query is no exception. One of its main drawbacks is that because queries are embedded in application code, they may end up with the same query in several places. I can set up a central location for storing queries to eliminate this duplication. This location can be an XML file, a class with a public static string member in the application, and a custom. NET attribute, or an empty file. With these tips, you can find the query you want before you execute it.

Another potential problem with applying parameterized queries is that many companies do not allow inline SQL in their applications (and the data tier). I think that's because people are talking about inserting SQL into application code, they're referring to special (inline) code, not parameterized queries. Such a rule also gives DBAs greater control over the execution of code on the SQL Server, which is good for large databases.

when should parameterized queries be used?

You can apply parameterized queries in any case where you need to perform an operation on a SQL Server. However, parameterized queries are primarily used for creating, reading, updating, and Deleting (CRUD) operations that need to be performed. If you're performing a complex operation that takes a long time or is made up of different SQL statements, it's a good idea to keep this operation on the SQL Server.

Although parameterized queries are easy to apply in many cases, because it may disrupt your application code, I do not recommend that you apply it in complex data manipulation logic. When your application code is disrupted, you are bound to experience serious code maintenance problems.

In many cases of writing data access programs, parametric processes are a good choice compared to ad hoc queries and stored procedures. The parameterized query is between the other two choices, and if applied properly, it can significantly improve the development efficiency.