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:

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] [int] NULL
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)
  25. SET @CustomerID = SCOPE_IDENTITY()
  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='[email protected]',
  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
  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
  13. AS
  14. BEGIN
  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
  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

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.