Stored Procedures Vs Dynamic SQL Queries in ASP.Net

Stored Procedures vs. Inline Queries

Every developer is recommended to use stored procedures instead of embedded SQL queries while implementing ADO.Net data access model in ASP.Net web applications. There are number of reasons based on data access model performance and efficiency that suggests the use of stored procedures instead of using hardcode sql queries in ASP.Net code. Further in this article we will discuss about some of the reasons to avoid the use of dynamic SQL queries in ASP.Net code.

What are Stored Procedures?

Stored procedures are precompiled objects that contain one or more sql queries. The structure of a stored contains the name of stored procedure, declaration of input or output variables and last sql statements. Stored procedures are the permanently compiled objects stored in the database that encapsulates the complex sql queries from the application.

Stored Procedures, ADO.Net and ASP.Net components

In ASP.Net code you must use CommandType.StoredProcedure to direct the SqlCommand object that the passed argument is a name of stored procedure.
You can use SqlDataAdapter to fill the DataSet by passing the name of stored procedure directly without creating the SqlCommand object because overloaded SqlDataAdapter creates the SqlCommand object internally.

Advantages of Using Stored Procedures

Main reasons of using stored procedures in ASP.Net web applications over the dynamic or embedded sql queries:

• Stored procedures result in improved performance by supporting data input/output plan of database that optimizes the data access plan and cache the data for frequent use.
• Stored procedures are stored into the database individually and permissions can be granted to allow or deny the execution of stored procedures by any particular user.
• Stored procedures are easy to handle and manage due to its storage location in database where it can be altered and saved where as embedded sql queries make it difficult to alter the hard coded queries in compiled applications.
• Stored procedures helps in reducing network traffic due to execution of sql statements in batches instead of multiple requests sent by single or number of clients.

Avoid "sp_"

While creating procedures required for data access do not use "sp_" as a prefix for naming the stored procedure. Stored procedures starting with "sp_" are assigned to the system stored procedures. SQL server looks for the stored procedures having name prefix as "sp_":

• First it looks in the stored procedures of master database
• Second it looks the stored procedures based on any provided qualifier such as database name or owner.
• Then it looks in the stored procedures having dbo as owner if there is no owner specified.

Continue to next tutorial: ADO.Net Data Access Layer (DAL) to get started with design a data access layer for an ASP.Net application.