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:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE [dbo].[Customer]( [CustomerID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL, [FirstName] [varchar](20) NULL, [LastName] [varchar](20) NULL, [Email] [varchar](20) NULL, [PhoneNumber] [int] NULL ) |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
IF OBJECT_ID('cusp_CustomerCreate') IS NOT NULL BEGIN DROP PROC usp_CustomerCreate END GO CREATE PROCEDURE usp_CustomerCreate @FirstName varchar(20), @LastName varchar(20), @Email varchar(20), @PhoneNumber int AS BEGIN INSERT INTO Customer ( FirstName, LastName, Email, PhoneNumber) VALUES ( @FirstName, @LastName, @Email, @PhoneNumber) SET @CustomerID = SCOPE_IDENTITY() SELECT FirstName = @FirstName, LastName = @LastName, Email = @Email, PhoneNumber =@PhoneNumber FROM Customer WHERE CustomerID = @CustomerID END |
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.
READ procedures
The Read procedure reads the table records based on the primary key specified in the input parameter:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
IF OBJECT_ID('cusp_CustomerRead') IS NOT NULL BEGIN DROP PROC cusp_CustomerRead END GO CREATE PROC cusp_CustomerRead @CustomerID int AS BEGIN SELECT CustomerID, FirstName, LastName, Email, PhoneNumber FROM Customer WHERE (CustomerID = @CustomerID) END GO |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
IF OBJECT_ID('cusp_CustomerUpdate') IS NOT NULL BEGIN DROP PROC cusp_CustomerUpdate END GO CREATE PROC cusp_CustomerUpdate @CustomerID int, @FirstName varchar(20), @LastName varchar(20), @Email varchar(20), @PhoneNumber int AS BEGIN UPDATE Customer SET FirstName = @FirstName, LastName = @LastName, Email = @Email, PhoneNumber = @PhoneNumber WHERE CustomerID = @CustomerID END GO |
DELETE procedures
The Delete procedure deletes a row specified in the WHERE clause:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
IF OBJECT_ID('cusp_CustomerDelete') IS NOT NULL BEGIN DROP PROC cusp_CustomerDelete END GO CREATE PROC cusp_CustomerDelete @CustomerID int AS BEGIN DELETE FROM Customer WHERE CustomerID = @CustomerID END GO |
Generating CRUD procedures using Visual Studio
Right click on the application folder in the Solution Explorer pane and choose the Add->New Item option:
Select DataSet from the Add New Item window:
Right click in the opened window and choose 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:
In the next window enter 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 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:
Use the Preview SQL Script button to preview the script and use it for your own procedures:
Here is code for the cusp_CustomerCreate procedure opened in SSMS:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
/****** Object: StoredProcedure [dbo].[cusp_CustomerCreate] Script Date: 26-Mar-14 7:17:03 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[cusp_CustomerCreate] ( @FirstName varchar(20), @LastName varchar(20), @Email varchar(20), @PhoneNumber int ) AS SET NOCOUNT OFF; INSERT INTO [Customer] ([FirstName], [LastName], [Email], [PhoneNumber]) VALUES (@FirstName, @LastName, @Email, @PhoneNumber); SELECT CustomerID, FirstName, LastName, Email, PhoneNumber FROM Customer WHERE (CustomerID = SCOPE_IDENTITY()) GO |
View all posts by Milica Medic
- Creating and using CRUD stored procedures - April 7, 2014
- Database table partitioning in SQL Server - April 4, 2014
- SQL Database design: Choosing a primary key - March 16, 2014