Stored Procedure with LINQ to SQL

Manoj Kalla
Mar 30, 2016
7k
0
2
Article

This article has three parts:

Introduction to Stored Procedure and LINQ To SQL.
Simply uses of Stored Procedure with LINQ.
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?

Compile one time only.
Executing fast as compare to hardcoded query.
User level protection.
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.

You can change either from database itself.
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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Manoj kalla
-- Create date: 28-March-2016
-- Description: To Get All Friends.
-- =============================================
CREATE PROCEDURE stpGetAllFriends
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select * From tblFriends
END
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.

Database name is MemberCDAC its having tblFriends and stpGetAllFriends,
Create a new Asp.Net Web Site project named : StoreProceduredWithLinqToSql,
Right click on solution explorer and select Add New Item,

or press CTRL + SHIFT + A,

Select LINQ to SQL Classes item

Named item as FriendDataClasses.dbml,

Press Yes on above dialogue box.
Select VIEW option from Visual Studio and select SERVER EXPLORER option or press simply CTRL + W + L,

You can see and compare previous screen shots of SQL Server with SERVER EXPLORER its same with contents.
Drag N Drop stpGetAllFriends object which inside under section of Stored Procedures.

After draging and dropping our DBML will look like like the above image.
Now right click on solution explorer and add new item and add WEB FORM named it : GetAllFriends.aspx
From toolbox select Data group/section under there is a GRIDVIEW control.

Select GridView’s smart tag option select AUTO FORMAT,
GetAllFriends.aspx code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GetAllFriends.aspx.cs" Inherits="GetAllFriends" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<SortedAscendingCellStyle BackColor="#FFF1D4" />
<SortedAscendingHeaderStyle BackColor="#B95C30" />
<SortedDescendingCellStyle BackColor="#F1E5CE" />
<SortedDescendingHeaderStyle BackColor="#93451F" />
</asp:GridView>
</div>
</form>
</body>
</html>

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;
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. }
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.

Add new item WEB FORM, named form : CRUDFriends.aspx,
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]
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)
17. AS
18. BEGIN
19. -- SET NOCOUNT ON added to prevent extra result sets from
20. -- interfering with SELECT statements.
21. SET NOCOUNT ON;
23. Insert into tblFriends (Name,Place,Mobile,EmailAddress) Values(@Name,@Place,@Mobile,@EmailAddress)
25. END
26. GO
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)
18. AS
19. BEGIN
20. -- SET NOCOUNT ON added to prevent extra result sets from
21. -- interfering with SELECT statements.
22. SET NOCOUNT ON;
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
14. AS
15. BEGIN
16. -- SET NOCOUNT ON added to prevent extra result sets from
17. -- interfering with SELECT statements.
18. SET NOCOUNT ON;
20. Delete From tblFriends Where FriendID = @FriendID
21. END
22. GO
As you can see we have created three stored procedures as shown above.
Now switch to Visual Studio and click on App_Code folder then Double click on FriendDataClasses.dbml file.
Click on Server Explorer which is left hand side.

Drag n drop tblFriends on DBML canvas.
Drag n drop stored procedure on DBML canvas.
After draggin and dropping stored procedure we have to attach stored procedure with tblFriend entity.

Right click on tblFriend entity and select Configure Behaviour,
As you click on Configure Behavior option , Now time to Select BEHAVIOR and CUSTOMISE option to configure Stored Procedure with DBML.

In the above screen shot we had configured stpInsertFriend same way we have to do Mapping for all CRUD base stored procedures.
CRUDFriends.aspx code
1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="CRUDFriends.aspx.cs" Inherits="CRUDFriends" %>
3. <!DOCTYPE html>
5. <html xmlns="http://www.w3.org/1999/xhtml">
7. <head runat="server">
8. <title></title>
9. <style type="text/css">
10. .auto-style1 {
11. text-align: center;
12. }
13. </style>
14. </head>
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>
73. </html>
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;
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. }
19. private void GetAllFriends()
20. {
21. FriendDataClassesDataContext _friendContext = new FriendDataClassesDataContext();
22. GridView1.DataSource = _friendContext.stpGetAllFriends();
23. GridView1.DataBind();
24. _friendContext.Dispose();
25. }
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();
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. }
57. ClearTextBox();
58. GetAllFriends();
59. _friendContext.Dispose();
60. }
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. }
72. }