Milica Medic

Creating and using CRUD stored procedures

April 7, 2014 by
A typical N-tier application design contains of the several layers from the client (Web or Windows), to the Business Logic Layer, to the Data Access Layer, and finally to the Data Storage Layer.

N-tier application design

The Data Storage Layer consists of SQL Server and database objects. The Data Access Layer is client code written in a language such as C#, VB, VB.Net, Java, PHP etc. The Data Access Layer communicates with the Data Storage Layer to perform CRUD operations. CRUD represents an acronym for the database operations Create, Read, Update, and Delete. The communication between two layers could be in the form of ad hoc SQL statements such as INSERT, SELECT, UPDATE, and DELETE. The stored procedures approach foregoes these SQL statements and uses only the EXECUTE statement on stored procedures.

Why CRUD?

There are several reasons for using stored procedures to perform CRUD operations instead of ad-hoc SQL statements:

Performance

After the first execution of a stored procedure, the procedures execution plan is stored in SQL Server’s procedure cache and reused for all following invocations of the stored procedure.

When any SQL statement is executed in SQL Server, the relational engine will first look through the procedure cache to verify that an existing execution plan for the specified SQL statement exists and reuse any existing plan, saving the overhead of parsing, optimization, and recompiling steps for the SQL statement. If the execution plan doesn’t exist which is the case with the ad-hoc SQL statements, SQL Server will generate a new execution plan for the query.

Decouples the SQL code from the other layers of the application

By removing the SQL statements from the application code, all the SQL can be kept in the database and nothing but stored procedure invocations in the client application. Using stored procedures to encapsulate the database access is also an effective way to decrease database coupling.

Prevents SQL injection attacks

Using stored procedures instead of string concatenation to build dynamic queries from user input data for all SQL Statements reduces the chance of SQL injection attacks because everything placed into a parameter gets quoted in the process.

CRUD stored procedures

There are some common naming conventions to differ CRUD procedures from other stored procedures in the database including:

  • The prefix should differ from the prefix used for other user defined stored procedures
  • Using the table name after the prefix insures that the CRUD procedures for the same table are grouped together
  • The procedure name should end with the name of the CRUD operation that it implements

To update the database schema after adding CRUD procedures, first identify the database entity for which the CRUD methods will be implemented. We’ll use a table Customer to show the implementation of the CRUD operations using the stored procedures:

The CRUD operations are implemented by four stored procedures:

CREATE procedures

The Create procedure performs the INSERT statement which will create a new record. It has one parameter for every column in the table:

The line SET @CustomerID = SCOPE_IDENTITY() captures the identity value. The SCOPE_IDENTITY() function returns the last identity value inserted into an identity column in the same scope (a stored procedure, trigger, function, or batch). Two statements are in the same scope if they are in the same stored procedure, function, or batch.

CREATE procedure

READ procedures

The Read procedure reads the table records based on the primary key specified in the input parameter:

READ procedure

UPDATE procedures

The Update procedure performs an UPDATE statement on the table based on the primary key for a record specified in the WHERE clause of the statement. Same as the Create procedure it has one parameter for every column in the table:

DELETE procedures

The Delete procedure deletes a row specified in the WHERE clause:

Generating CRUD procedures using Visual Studio

Right click on the application folder in the Solution Explorer pane and choose the Add->New Item option:

Choosing the Add New Item option in the Solution Explorer pane

Select DataSet from the Add New Item window:

Selecting DataSet from the Add New Item window

Right click in the opened window and choose the Add->TableAdapter option:

Choosing the Add TableAdapter option

In the TableAdapter Configuration Wizard choose the data connection and in the next window choose the Create new stored procedures option:

Choosing the Create new stored procedures option

In the next window enter a SELECT statement for the Read stored procedure:

Eentering a SELECT statement for the Read stored procedure

In the Advanced Options select the Generate Insert, Update, and Delete statement, the Use optimistic concurrency, and the Refresh the data table options:

The Advanced Options dialog

The Generate Insert, Update, and Delete statement option generates Insert, Update, and Delete statements based on the specified Select statement

The Use optimistic concurrency option does not lock a record when reading it and because there is no locking of records and therefore no additional server resources requirements using optimistic concurrency may improve performance. Also, connections to the server are can serve a larger number of clients in less time because a persistent connection to the database server is not required in order to maintain record locks.

In the next window name the stored procedures and click the Finish button:

Naming the stored procedures and clicking the Finish button

Use the Preview SQL Script button to preview the script and use it for your own procedures:

The Preview SQL Script dialog

Here is code for the cusp_CustomerCreate procedure opened in SSMS:


Milica Medic
168 Views