Stored Procedure with LINQ to SQL

This article has three parts:

  1. Introduction to Stored Procedure and LINQ To SQL.
  2. Simply uses of Stored Procedure with LINQ.
  3. CRUD operations with uses of Stored Procedure with LINQ To SQL

Introduction to Stored Procedure and LINQ To SQL:

Stored Procedure: Collection and Group of T-SQL commands is called Stored procedure. In this we can write commands of Creation, updation and deletion of parts of Data Definition Language (DDL), Data Manipulation Language (DML).

We all prefer stored procedure writing compared to writing hardcoded query. There are so many reasons to prefer Stored Procedures.

Stored Procedure can do: Input Parameter, Output Parameter and Output the bunch of records.

Why We Should Use Stored Procedure?

  1. Compile one time only.
  2. Executing fast as compare to hardcoded query.
  3. User level protection.
  4. Reduce network traffic.

LINQ to SQL:

Language Integrated Query (LINQ), Linq to SQL works as ORD (Object Relational Designer). Before starting to work on LINQ To SQL you should understood the ORD.

ORD is a canvas where you can create or drag and drop the table or entity on it from Server Explorer. Linq to sql designer can be created with Add New Item and Select LINQ to SQL classes template and you can see that creating a DBML extension kind of file.

Thats DBML is called Object Relational Designer (O/R Designer).

This DBML is used to create entity classes that map to database tables or views and other important things.

Naming Convention of tables you should take care.. Singular or Pluralisation.

There two way to change table or entity name in DBML.

  1. You can change either from database itself.
  2. Tools - Options  Database Tools and select O/R Designer then select False from the Enabled Drop Down list in the Pluralisation of Names group.

Simple Uses of Stored Procedure with LINQ To SQL:

Herewith we are going to use Stored Procedure with Linq To Sql step by step.

Stored Procedure returning the list of friends.

stpGetAllFriends code

  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. -- =============================================  
  6. -- Author: Manoj kalla   
  7. -- Create date: 28-March-2016  
  8. -- Description: To Get All Friends.  
  9. -- =============================================  
  10. CREATE PROCEDURE stpGetAllFriends  
  11. AS  
  12. BEGIN  
  13. -- SET NOCOUNT ON added to prevent extra result sets from  
  14. -- interfering with SELECT statements.  
  15. SET NOCOUNT ON;  
  16. Select * From tblFriends  
  17. END  
  18. GO  
As you can see the above code is a very simple stored procedure code which returns a list of friends from table named tblFriends.
  1. Database name is MemberCDAC its having tblFriends and stpGetAllFriends,

    explorer

  2. Create a new Asp.Net Web Site project named : StoreProceduredWithLinqToSql,

    new

  3. Right click on solution explorer and select Add New Item,

    or press CTRL + SHIFT + A,

    Select LINQ to SQL Classes item

    class

    Named item as FriendDataClasses.dbml,

    name

    Press Yes on above dialogue box. 

  4. Select VIEW option from Visual Studio and select SERVER EXPLORER option or press simply CTRL + W + L,

    server

    You can see and compare previous screen shots of SQL Server with SERVER EXPLORER its same with contents.

  5. Drag N Drop stpGetAllFriends object which inside under section of Stored Procedures.

    dbml

    After draging and dropping our DBML will look like like the above image.

  6. Now right click on solution explorer and add new item and add WEB FORM named it : GetAllFriends.aspx

    GetAllFriends.aspx

  7. From toolbox select Data group/section under there is a GRIDVIEW control.

    Select GridView’s smart tag option select AUTO FORMAT,

    format

    autoformat

  8. GetAllFriends.aspx code
    1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="GetAllFriends.aspx.cs" Inherits="GetAllFriends" %>  
    2.   
    3.     <!DOCTYPE html>  
    4.   
    5.     <html xmlns="http://www.w3.org/1999/xhtml">  
    6.   
    7.     <head runat="server">  
    8.         <title></title>  
    9.     </head>  
    10.   
    11.     <body>  
    12.         <form id="form1" runat="server">  
    13.             <div>  
    14.                 <asp:GridView ID="GridView1" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">  
    15.                     <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />  
    16.                     <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />  
    17.                     <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />  
    18.                     <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />  
    19.                     <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />  
    20.                     <SortedAscendingCellStyle BackColor="#FFF1D4" />  
    21.                     <SortedAscendingHeaderStyle BackColor="#B95C30" />  
    22.                     <SortedDescendingCellStyle BackColor="#F1E5CE" />  
    23.                     <SortedDescendingHeaderStyle BackColor="#93451F" />  
    24.                 </asp:GridView>  
    25.             </div>  
    26.         </form>  
    27.     </body>  
    28.   
    29.     </html>  
  9. GetAllFriends.aspx.cs code
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Web;  
    5. using System.Web.UI;  
    6. using System.Web.UI.WebControls;  
    7.   
    8. public partial class GetAllFriends: System.Web.UI.Page   
    9. {  
    10.     protected void Page_Load(object sender, EventArgs e)  
    11.     {  
    12.         FriendDataClassesDataContext _db = new FriendDataClassesDataContext();  
    13.         GridView1.DataSource = _db.stpGetAllFriends();  
    14.         GridView1.DataBind();  
    15.     }  
    16. }   
  10. Output

    output

CRUD operations with uses of Stored Procedure with LINQ To SQL

Now in this section we are implementing CRUD (Create, Retrieve, Update, Delete) operations against table of database with uses of Stored Procedure running with the help of LINQ TO SQL.

  1. Add new item WEB FORM, named form : CRUDFriends.aspx,

    web

  2. Now see following are items:

    a. Table Structure
    b. Insert Friend Stored Procedure
    c. Update Friend Stored Procedure
    d. Delete Friend Stored Proceudre 

    a. Table Structure:
    1. USE [MemberCDAC]  
    2. GO  
    3. /****** Object: Table [dbo].[tblFriends] Script Date: 03/29/2016 08:57:56 ******/  
    4. SET ANSI_NULLS ON  
    5. GO  
    6. SET QUOTED_IDENTIFIER ON  
    7. GO  
    8. SET ANSI_PADDING ON  
    9. GO  
    10. CREATE TABLE [dbo].[tblFriends](  
    11. [FriendID] [int] IDENTITY(1,1) NOT NULL,  
    12. [Name] [varchar](50) NULL,  
    13. [Place] [varchar](25) NULL,  
    14. [Mobile] [varchar](15) NULL,  
    15. [EmailAddress] [varchar](150) NULL  
    16. ON [PRIMARY]  
    17.   
    18. GO  
    19. SET ANSI_PADDING OFF  
    b. Insert Friend Stored Procedure
    1. SET ANSI_NULLS ON  
    2. GO  
    3. SET QUOTED_IDENTIFIER ON  
    4. GO  
    5. -- =============================================  
    6. -- Author: Manoj Kalla  
    7. -- Create date: 28-March-2016  
    8. -- Description: Insert a new Friend Detail.  
    9. -- =============================================  
    10. CREATE PROCEDURE stpInsertFriend  
    11. -- Add the parameters for the stored procedure here  
    12. @Name varchar(50),  
    13. @Place varchar(25),  
    14. @Mobile varchar(15),  
    15. @EmailAddress varchar(150)  
    16.   
    17. AS  
    18. BEGIN  
    19. -- SET NOCOUNT ON added to prevent extra result sets from  
    20. -- interfering with SELECT statements.  
    21. SET NOCOUNT ON;  
    22.   
    23. Insert into tblFriends (Name,Place,Mobile,EmailAddress) Values(@Name,@Place,@Mobile,@EmailAddress)  
    24.   
    25. END  
    26. GO  
    27.    
    c. Update Friend Stored Procedure
    1. SET ANSI_NULLS ON  
    2. GO  
    3. SET QUOTED_IDENTIFIER ON  
    4. GO  
    5. -- =============================================  
    6. -- Author: Manoj Kalla  
    7. -- Create date: 28-March-2016  
    8. -- Description: Update a Friend Detail.  
    9. -- =============================================  
    10. CREATE PROCEDURE stpUpdateFriend  
    11. -- Add the parameters for the stored procedure here  
    12. @FriendID int,  
    13. @Name varchar(50),  
    14. @Place varchar(25),  
    15. @Mobile varchar(15),  
    16. @EmailAddress varchar(150)  
    17.   
    18. AS  
    19. BEGIN  
    20. -- SET NOCOUNT ON added to prevent extra result sets from  
    21. -- interfering with SELECT statements.  
    22. SET NOCOUNT ON;  
    23.   
    24. Update tblFriends set   
    25. Name = @Name,Place = @Place,Mobile =@Mobile ,EmailAddress =@EmailAddress  
    26. Where FriendID = @FriendID  
    27. END  
    28. GO  
    d. Delete Friend Stored Proceudre
    1. SET ANSI_NULLS ON  
    2. GO  
    3. SET QUOTED_IDENTIFIER ON  
    4. GO  
    5. -- =============================================  
    6. -- Author: Manoj Kalla  
    7. -- Create date: 28-March-2016  
    8. -- Description: Delete a Friend Detail.  
    9. -- =============================================  
    10. CREATE PROCEDURE stpDeleteFriend  
    11. -- Add the parameters for the stored procedure here  
    12. @FriendID int  
    13.   
    14. AS  
    15. BEGIN  
    16. -- SET NOCOUNT ON added to prevent extra result sets from  
    17. -- interfering with SELECT statements.  
    18. SET NOCOUNT ON;  
    19.   
    20. Delete From tblFriends Where FriendID = @FriendID  
    21. END  
    22. GO
    sp

    As you can see we have created three stored procedures as shown above.

  3. Now switch to Visual Studio and click on App_Code folder then Double click on FriendDataClasses.dbml file.

  4. Click on Server Explorer which is left hand side.

    Drag n drop tblFriends on DBML canvas.

    server

  5. Drag n drop stored procedure on DBML canvas.

    dbml

  6. After draggin and dropping stored procedure we have to attach stored procedure with tblFriend entity.

    Right click on tblFriend entity and select Configure Behaviour,

    Configure Behaviour

  7. As you click on Configure Behavior option , Now time to Select BEHAVIOR and CUSTOMISE option to configure Stored Procedure with DBML.

    Configure Behaviour

    In the above screen shot we had configured stpInsertFriend same way we have to do Mapping for all CRUD base stored procedures.

    Configure Behaviour


    Configure Behaviour

  8. CRUDFriends.aspx code
    1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="CRUDFriends.aspx.cs" Inherits="CRUDFriends" %>  
    2.   
    3.     <!DOCTYPE html>  
    4.   
    5.     <html xmlns="http://www.w3.org/1999/xhtml">  
    6.   
    7.     <head runat="server">  
    8.         <title></title>  
    9.         <style type="text/css">  
    10.             .auto-style1 {  
    11.                 text-align: center;  
    12.             }  
    13.         </style>  
    14.     </head>  
    15.   
    16.     <body>  
    17.         <form id="form1" runat="server">  
    18.             <div>  
    19.                 <table style="width: 47%;">  
    20.                     <tr>  
    21.                         <td>Friend ID</td>  
    22.                         <td>  
    23.                             <asp:Label ID="lblFriendID" runat="server" Text="[ID]"></asp:Label>  
    24.                         </td>  
    25.                     </tr>  
    26.                     <tr>  
    27.                         <td>Name</td>  
    28.                         <td>  
    29.                             <asp:TextBox ID="txtName" runat="server" Width="312px"></asp:TextBox>  
    30.                         </td>  
    31.                     </tr>  
    32.                     <tr>  
    33.                         <td>Place</td>  
    34.                         <td>  
    35.                             <asp:TextBox ID="txtPlace" runat="server"></asp:TextBox>  
    36.                         </td>  
    37.                     </tr>  
    38.                     <tr>  
    39.                         <td>Mobile</td>  
    40.                         <td>  
    41.                             <asp:TextBox ID="txtMobile" runat="server"></asp:TextBox>  
    42.                         </td>  
    43.                     </tr>  
    44.                     <tr>  
    45.                         <td>Email Address</td>  
    46.                         <td>  
    47.                             <asp:TextBox ID="txtEmailAddress" runat="server" Width="313px"></asp:TextBox>  
    48.                         </td>  
    49.                     </tr>  
    50.                     <tr>  
    51.                         <td class="auto-style1" colspan="2">  
    52.                             <asp:Button ID="btnSaveUpdate" runat="server" Text="Save" Width="136px" OnClick="btnSaveUpdate_Click" />  
    53.                         </td>  
    54.                     </tr>  
    55.                 </table>  
    56.                 <br />  
    57.                 <asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" Width="50%" AutoGenerateDeleteButton="True" AutoGenerateSelectButton="True" OnRowDeleting="GridView1_RowDeleting" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" DataKeyNames="FriendID">  
    58.                     <AlternatingRowStyle BackColor="White" />  
    59.                     <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />  
    60.                     <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />  
    61.                     <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />  
    62.                     <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />  
    63.                     <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />  
    64.                     <SortedAscendingCellStyle BackColor="#FDF5AC" />  
    65.                     <SortedAscendingHeaderStyle BackColor="#4D0000" />  
    66.                     <SortedDescendingCellStyle BackColor="#FCF6C0" />  
    67.                     <SortedDescendingHeaderStyle BackColor="#820000" />  
    68.                 </asp:GridView>  
    69.             </div>  
    70.         </form>  
    71.     </body>  
    72.   
    73.     </html>  
  9. CRUDFriends.aspx.cs code
    1. using System;  
    2. using System.Collections.Generic;  
    3. using System.Linq;  
    4. using System.Web;  
    5. using System.Web.UI;  
    6. using System.Web.UI.WebControls;  
    7.   
    8. public partial class CRUDFriends: System.Web.UI.Page  
    9. {  
    10.     protected void Page_Load(object sender, EventArgs e)   
    11.     {  
    12.         if (!IsPostBack)   
    13.         {  
    14.             btnSaveUpdate.Text = "Save";  
    15.             GetAllFriends();  
    16.         }  
    17.     }  
    18.   
    19.     private void GetAllFriends()   
    20.     {  
    21.         FriendDataClassesDataContext _friendContext = new FriendDataClassesDataContext();  
    22.         GridView1.DataSource = _friendContext.stpGetAllFriends();  
    23.         GridView1.DataBind();  
    24.         _friendContext.Dispose();  
    25.     }  
    26.   
    27.     protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)   
    28.     {  
    29.         FriendDataClassesDataContext _friendContext = new FriendDataClassesDataContext();  
    30.         _friendContext.stpDeleteFriend(Convert.ToInt16(GridView1.DataKeys[e.RowIndex].Value));  
    31.         GetAllFriends();  
    32.         ClearTextBox();  
    33.     }  
    34.     protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)  
    35.     {  
    36.         FriendDataClassesDataContext _friendContext = new FriendDataClassesDataContext();  
    37.   
    38.         lblFriendID.Text = Convert.ToString(GridView1.SelectedRow.Cells[1].Text);  
    39.         txtName.Text = Convert.ToString(GridView1.SelectedRow.Cells[2].Text);  
    40.         txtPlace.Text = Convert.ToString(GridView1.SelectedRow.Cells[3].Text);  
    41.         txtMobile.Text = Convert.ToString(GridView1.SelectedRow.Cells[4].Text);  
    42.         txtEmailAddress.Text = Convert.ToString(GridView1.SelectedRow.Cells[5].Text);  
    43.         btnSaveUpdate.Text = "Update";  
    44.         _friendContext.Dispose();  
    45.     }  
    46.     protected void btnSaveUpdate_Click(object sender, EventArgs e)  
    47.     {  
    48.         FriendDataClassesDataContext _friendContext = new FriendDataClassesDataContext();  
    49.         if (btnSaveUpdate.Text == "Save")  
    50.         {  
    51.             _friendContext.stpInsertFriend(txtName.Text, txtPlace.Text, txtMobile.Text, txtEmailAddress.Text);  
    52.         } else  
    53.         {  
    54.             _friendContext.stpUpdateFriend(Convert.ToInt16(lblFriendID.Text), txtName.Text, txtPlace.Text, txtMobile.Text, txtEmailAddress.Text);  
    55.         }  
    56.   
    57.         ClearTextBox();  
    58.         GetAllFriends();  
    59.         _friendContext.Dispose();  
    60.     }  
    61.   
    62.     private void ClearTextBox()  
    63.     {  
    64.         lblFriendID.Text = "";  
    65.         txtName.Text = string.Empty;  
    66.         txtPlace.Text = string.Empty;  
    67.         txtMobile.Text = string.Empty;  
    68.         txtEmailAddress.Text = string.Empty;  
    69.         btnSaveUpdate.Text = "Save";  
    70.     }  
    71.   
    72. }  

So, we completed the following things.

  1. Introduction to Stored Procedure and LINQ To SQL.
  2. Simply uses of Stored Procedure with LINQ.
  3. CRUD operations with uses of Stored Procedure with LINQ To SQL.

You can view my articles for Linq To Sql: