CRUD Stored Procedures In SQL Server And Visual Studio

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.