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.


  1. CREATE TABLE [dbo].[CustomerInfo](  
  2. [CustomerID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,  
  3. [FirstName] [varchar](20) NULL,  
  4. [LastName] [varchar](20) NULL,  
  5. [Email] [varchar](20) NULL,  
  6. [PhoneNumber] [intNULL  
Let’s create a stored procedure.


  1. IF OBJECT_ID('cusp_CustomerTestData'IS NOT NULL  
  2. BEGIN  
  3. DROP PROC usp_CustomerTestData  
  4. END  
  5. GO  
  6. CREATE PROCEDURE usp_CustomerTestData  
  7. @CustomerID int,  
  8. @FirstName varchar(20),  
  9. @LastName varchar(20),  
  10. @Email varchar(20),  
  11. @PhoneNumber int  
  12. AS  
  13. BEGIN  
  14. INSERT INTO CustomerInfo (  
  15. FirstName,  
  16. LastName,  
  17. Email,  
  18. PhoneNumber)  
  19. VALUES (  
  20. @FirstName,  
  21. @LastName,  
  22. @Email,  
  23. @PhoneNumber)  
  24.   
  25. SET @CustomerID = SCOPE_IDENTITY()  
  26.   
  27. SELECT  
  28. FirstName = @FirstName,  
  29. LastName = @LastName,  
  30. Email = @Email,  
  31. PhoneNumber =@PhoneNumber  
  32. FROM CustomerInfo  
  33. WHERE CustomerID = @CustomerID  
  34. END  

SCOPE_IDENTITY() returns the last value inserted.

Let's insert a record.


  1. EXEC usp_CustomerTestData  
  2. @CustomerID=1,  
  3. @FirstName='Akshay',  
  4. @LastName='Phadke',  
  5. @Email='asas@ss.com',  
  6. @PhoneNumber='44444'  
Now we will do the same for read.


  1. IFOBJECT_ID('cusp_Read')ISNOTNULL  
  2. BEGIN  
  3. DROPPROC cusp_Read  
  4. END  
  5. GO  
  6. CREATEPROC cusp_Read  
  7. @CustomerID int  
  8. AS  
  9. BEGIN  
  10.   
  11. SELECT CustomerID, FirstName, LastName, Email, PhoneNumber  
  12. FROM CustomerInfo   
  13. WHERE (CustomerID = @CustomerID)  
  14. END  
  15. GO  
Lets see the records.


  1. EXEC cusp_Read@CustomerID =1  
Let's do the same for update.


  1. IFOBJECT_ID('cusp_Update')ISNOTNULL  
  2. BEGIN  
  3. DROPPROC cusp_Update  
  4. END  
  5. GO  
  6. CREATEPROC cusp_Update  
  7. @CustomerID int,  
  8. @FirstName varchar(20),  
  9. @LastName varchar(20),  
  10. @Email varchar(20),  
  11. @PhoneNumber int  
  12.   
  13. AS  
  14. BEGIN  
  15.   
  16. UPDATE CustomerInfo  
  17. SET FirstName= @FirstName,  
  18. LastName = @LastName,  
  19. Email = @Email,  
  20. PhoneNumber = @PhoneNumber  
  21. WHERE CustomerID= @CustomerID  
  22. END  
  23. GO  
Delete now.


  1. IFOBJECT_ID('cusp_Delete')ISNOTNULL  
  2. BEGIN  
  3. DROPPROC cusp_Delete  
  4. END  
  5. GO  
  6. CREATEPROC cusp_Delete  
  7. @CustomerID int  
  8. AS  
  9. BEGIN  
  10. DELETE  
  11. FROM CustomerInfo  
  12. WHERE CustomerID= @CustomerID  
  13.   
  14. END  
  15. 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.