CRUD Stored Procedures In SQL Server And Visual Studio

Akshay Phadke

10y67.7k190100Article

19 Comments

Join the conversation! Your thoughts help the community grow.

Ravi Kandel10y ago
Thanks for share
Michael Griffiths10y ago
Nicely written piece
Santhakumar Munuswamy10y ago
Thank you for nice share
Kuppurasu Nagaraj10y ago
Nice Sharing..
Praveen Sreeram10y ago
Cool.. Thanks @nagaraj for pointing it out
Akshay Phadke10y ago
Praveen Kumar Sreeram Nagaraj S Corrected !! Cheers !!
Praveen Sreeram10y ago
Akshay Phadke It's just below the Code Snippet of the "usp_CustomerTestData" stored Procedure
Praveen Sreeram10y ago
Akshay Phadke Please search for "SCOPE_IDEBTITY()"
Akshay Phadke10y ago
Praveen Kumar Sreeram Nagaraj S Kindly point it out i didnt findy any ..
Praveen Sreeram10y ago
Thank you Akshay Phadke. Also, please fix the typo as Nagaraj S suggesd :-)
Akshay Phadke10y ago
Praveen Kumar Sreeram Yes yes i understood why you said that .. Sure buddy :)
Akshay Phadke10y ago
Nagaraj S I didnt found out any kindly point it out ..
Nagaraj S10y ago
@Akshay Phadke ..It would have been nice if you have avoided spelling mistake for the KeyWord "SCOPE_IDENTITY()"..Please check and correct it..
Raja10y ago
Great...
Praveen Sreeram10y ago
Thanks Akshay Phadke. The reason for suggesting is..it's the most used one and many people directly write "Select * from TableName" without knowing the implications.. So, It's better to update with the Listing SP along with Custom Paging :-)
Debasis Saha10y ago
Good One..
Akshay Phadke10y ago
Praveen Kumar Sreeram Sure :) Bhavik Patel Thankyou
Praveen Sreeram10y ago
It will be very nice if you also add the stored procedure that could be used to pull multiple records along with CRUD operations..
Bhavik Patel10y ago
nice

Overview

As we all know we are develop/design 3-Tier architecture, N-tier architecture and so on. We all use stored procedures to map queries, so let’s see n-tier architecture. The typical architecture is web clients to business logic layer to Data access layer to data storage.

Introduction

In the above overview I had talked about n-tier architecture and will diagrammatically represent it in this.

The Data access layer is nothing but the client codes that are written in various languages such as c#, Java and So on. The data storage consists of database like SQL server, Oracle and so on. The DAL (Data access layer) communicates with data storage to perform CRUD operation. Here CRUD stands for:

C – CREATE
R – READ
U – UPDATE
D-DELETE

The operations here are generally insert, update and delete. Lets start,

Open SSMS.

Let’s create a table, Customer Info.

CREATE TABLE [dbo].[CustomerInfo](
[CustomerID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[FirstName] [varchar](20) NULL,
[LastName] [varchar](20) NULL,
[Email] [varchar](20) NULL,
[PhoneNumber] [int] NULL

Let’s create a stored procedure.

IF OBJECT_ID('cusp_CustomerTestData') IS NOT NULL
BEGIN
DROP PROC usp_CustomerTestData
END
GO
CREATE PROCEDURE usp_CustomerTestData
@CustomerID int,
@FirstName varchar(20),
@LastName varchar(20),
@Email varchar(20),
@PhoneNumber int
AS
BEGIN
INSERT INTO CustomerInfo (
FirstName,
LastName,
Email,
PhoneNumber)
VALUES (
@FirstName,
@LastName,
@Email,
@PhoneNumber)
SET @CustomerID = SCOPE_IDENTITY()
SELECT
FirstName = @FirstName,
LastName = @LastName,
Email = @Email,
PhoneNumber =@PhoneNumber
FROM CustomerInfo
WHERE CustomerID = @CustomerID
END

SCOPE_IDENTITY() returns the last value inserted.

Let's insert a record.

EXEC usp_CustomerTestData
@CustomerID=1,
@FirstName='Akshay',
@LastName='Phadke',
@Email='[email protected]',
@PhoneNumber='44444'

Now we will do the same for read.

IFOBJECT_ID('cusp_Read')ISNOTNULL
BEGIN
DROPPROC cusp_Read
END
GO
CREATEPROC cusp_Read
@CustomerID int
AS
BEGIN
SELECT CustomerID, FirstName, LastName, Email, PhoneNumber
FROM CustomerInfo
WHERE (CustomerID = @CustomerID)
END
GO

Lets see the records.

EXEC cusp_Read@CustomerID =1

Let's do the same for update.

IFOBJECT_ID('cusp_Update')ISNOTNULL
BEGIN
DROPPROC cusp_Update
END
GO
CREATEPROC cusp_Update
@CustomerID int,
@FirstName varchar(20),
@LastName varchar(20),
@Email varchar(20),
@PhoneNumber int
AS
BEGIN
UPDATE CustomerInfo
SET FirstName= @FirstName,
LastName = @LastName,
Email = @Email,
PhoneNumber = @PhoneNumber
WHERE CustomerID= @CustomerID
END
GO

Delete now.

IFOBJECT_ID('cusp_Delete')ISNOTNULL
BEGIN
DROPPROC cusp_Delete
END
GO
CREATEPROC cusp_Delete
@CustomerID int
AS
BEGIN
DELETE
FROM CustomerInfo
WHERE CustomerID= @CustomerID
END
GO

Now open visual Studio.

Right Click on your solution folder and Add Item.

Now add table adapter option.

Create a new stored procedure.

Click on Advanced options.

Preview the script.

You will see.

Advantages of CRUD using stored Procedures

Performance isthe main thing after successful completion of a stored procedure its execution plan gets stored in cache and hence it is reused.
Prevent SQL injection attacks everything is placed in parameter rather than string.

Conclusion

That’s all from CRUD in SQL and VS. Hope this article was helpful. If you have any doubts regarding this article feel free to ask.