Posted inLINQ

LINQ To SQL – CRUD Operations

No Comments

We have seen a lot of articles on LINQ to SQL. Before going through this article, I would recommend to have some basic information about LINQ to SQL. Refer to these links for the same.

So, what is LINQ?

LINQ stands for Language Integrated Query. LINQ enables us to query any type of data store (SQL Server documents, objects in memory etc.).

I will not cover the details here in this article. You can read the complete article from the links mentioned above.

Let’s get started!!

We want to achieve this output.

We will create a simple example which is LINQ to SQL for CRUD operations.

First, create an empty application and give it a suitable name.

Now, let’s create a sample table called registration. Here is the script to create a table.

  1. USE [TEST]
  2. GO
  4. /****** Object:  Table [dbo].[registration]    Script Date: 1/16/2017 11:53:27 PM ******/
  5. SET ANSI_NULLS ON
  6. GO
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  11. CREATE TABLE [dbo].[registration](
  12.     [id] [int] IDENTITY(1,1) NOT NULL,
  13.     [FirstName] [nvarchar](max) NULL,
  14.     [MiddleName] [nvarchar](max) NULL,
  15.     [LastName] [nvarchar](max) NULL,
  16.     [Address] [nvarchar](max) NULL,
  17.     [Landmark] [nvarchar](max) NULL,
  18.     [EmailID] [nvarchar](max) NULL,
  19.     [Country] [nvarchar](max) NULL,
  20.     [State] [nvarchar](max) NULL,
  21.     [City] [nvarchar](max) NULL,
  22.     [MobileNumber] [nvarchar](max) NULL,
  23.  CONSTRAINT [PK_registration] PRIMARY KEY CLUSTERED
  24. (
  25.     [id] ASC
  26. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  27. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  29. GO

Adding DBML File

Now, let’s add LINQ to SQL Class file. To do that, click on the Project ->Add New Items -> Data Tab,
select LINQ to SQL Classes, name it as “Sample.dbml”, and Click OK.

LINQ to SQL.

Now, open the Server Explorer in Visual Studio and locate the database that you created. After that, search for the table registration. Drag and drop the table on LINQ to SQL designer class.

When you drag and drop the table on LINQ to SQL designer, it will automatically create a business object for us. Here, sampledatacontext is our main file and the entry point or gateway of our application. The operations which we are going to perform like insert, update, and delete, will happen through this file. The Sampledatacontext will initiate the connection strings for us in web.config file.

So, the connection string here is.

GUI Part

Add a webform. Now, let’s setup the GUI part. First, we will include all the support files like bootstrap and jQuery.

  1. <link href=“Styles/bootstrap.css” rel=“stylesheet” />
  2.    “Scripts/jquery-1.8.2.js”>
  3.    “Scripts/bootstrap.js”>

These are supporting files for our application. We will be including a modal popup here. So first, drag and drop the Script Manager control on the form.

  1. <asp:ScriptManager runat=“server” ID=“ScriptManager1” />

Select Code

Now, let’s place the Grid View inside the UpdatePanel control.

  1. <!– Placing GridView in UpdatePanel–>
  2.             <asp:UpdatePanel ID=“upCrudGrid” runat=“server”>
  3.                 <ContentTemplate>
  5.                     <asp:GridView ID=“GridView1” runat=“server” Width=“940px” HorizontalAlign=“Center”
  6.                         OnRowCommand=“GridView1_RowCommand” AutoGenerateColumns=“false” AllowPaging=“true”
  7.                         CssClass=“table table-hover table-striped”>
  8.                         <Columns>
  11.                             <asp:BoundField DataField=“id” HeaderText=“ID”/>
  12.                             <asp:BoundField DataField=“FirstName” HeaderText=“FirstName”/>
  13.                                   <asp:BoundField DataField=“MiddleName” HeaderText=“MiddleName”/>
  14.                             <asp:BoundField DataField=“LastName” HeaderText=“LastName”/>
  15.                             <asp:BoundField DataField=“Address” HeaderText=“Address”/>
  16.                             <asp:BoundField DataField=“Landmark” HeaderText=“Landmark”/>
  17.                             <asp:BoundField DataField=“EmailID” HeaderText=“Email”/>
  18.                              <asp:BoundField DataField=“Country” HeaderText=“Country”/>
  19.                              <asp:BoundField DataField=“State” HeaderText=“State”/>
  20.                              <asp:BoundField DataField=“City” HeaderText=“City”/>
  21.                              <asp:BoundField DataField=“MobileNumber” HeaderText=“MobileNo”/>
  25.                          <asp:ButtonField CommandName=“editRecord”    ControlStyle-CssClass=“btn btn-info”
  26.                                 ButtonType=“Button” Text=“Edit” HeaderText=“Edit Record” >
  27.                                 <ControlStyle CssClass=“btn btn-primary” ></ControlStyle>
  28.                             </asp:ButtonField>
  29.                             <asp:ButtonField CommandName=“deleteRecord” ControlStyle-CssClass=“btn btn-info”
  30.                                 ButtonType=“Button” Text=“Delete” HeaderText=“Delete Record”>
  31.                                 <ControlStyle CssClass=“btn btn-danger”></ControlStyle>
  32.                             </asp:ButtonField>
  33.                         </Columns>
  34.                     </asp:GridView>
  35.                     <asp:Button ID=“btnAdd” runat=“server” Text=“Add New Record” CssClass=“btn btn-info” OnClick=“btnAdd_Click” />
  36.                 </ContentTemplate>
  37.                 <Triggers>
  38.                 </Triggers>
  39.             </asp:UpdatePanel>

Here, in this code, we are selecting records from our database along with buttons for edit and delete; and at the bottom, we are adding a button for adding a new record.

After setting up the GUI for “Select”, let’s create a simple method for “Select” using LINQ to SQL.

  1. private void BindEmployee()
  2.        {
  3.            SampleDataContext dbcontext = new SampleDataContext();
  4.            GridView1.DataSource = from reg in dbcontext.registrations
  5.                                   select reg;
  6.            GridView1.DataBind();
  7.        }

Create a method as BindEmployee(), create an instance of your Sampledatacontext class, and bind the Grid View with a LINQ query.

So, it’s a simple straight forward code. Now, let’s run the application and check the output.

Output for Select


Insert Code

Now, let’s setup the GUI part for insert.

  1. <!– Add Record Modal Starts here–>
  2.             
    “addModal” class=“modal hide fade” tabindex=“-1” role=“dialog” aria-labelledby=“addModalLabel” aria-hidden=“true”>
  3.                 
    class=“modal-header”>
  4.                     “button” class=“close” data-dismiss=“modal” aria-hidden=“true”>×
  5.                     

    “addModalLabel”>Add New Record

  6.                 
  •                 <asp:UpdatePanel ID=“upAdd” runat=“server”>
  •                     <ContentTemplate>
  •                         
    class=“modal-body”>
  •                             
    class=“table table-bordered table-hover”>
  •                                   
  •                                     
  •                                     
  •                                 
  •                                   
  •                                     
  •                                     
  •                                 
  •                                 
  •                                     
  •                                     
  •                                 
  •                                 
  •                                     
  •                                     
  •                                 
  •                                 
  •                                     
  •                                     
  •                                 
  •                                      
  •                                     
  •                                     
  •                                 
  •                                      
  •                                     
  •                                     
  •                                 
  •                                      
  •                                     
  •                                     
  •                                 
  •                                      
  •                                     
  •                                     
  •                                 
  •                                      
  •                                     
  •                                     
  •                                 
  •                                      
  •                                     
  •                                     
  •                                 
  •                             
    • ID : 
  •                                         “txtIdAdd” runat=“server”>
    		•
    FirstName : 
  •                                         “txtFirstName” runat=“server”>
    		•
    MiddleName :
  •                                         “txtMiddleName” runat=“server”>
    		•
    LastName:
  •                                         “txtLastName” runat=“server”>
    		•
    Address:
  •                                         “txtAddress” runat=“server”>
    		•
    Landmark:
  •                                         “txtLandmark” runat=“server”>
    		•
    EmailID:
  •                                         “txtEmailID” runat=“server”>
    		•
    Country:
  •                                         “txtCountry” runat=“server”>
    		•
    State:
  •                                         “txtState” runat=“server”>
    		•
    City:
  •                                         “txtCity” runat=“server”>
    		•
    Mobilenumber:
  •                                         “txtMobileNumber” runat=“server”>
    		•
  •                         
  •                         
    class=“modal-footer”>
  •                             “btnAddRecord” runat=“server” Text=“Add” CssClass=“btn btn-primary” OnClick=“btnAddRecord_Click” />
  •                             class=“btn btn-info” data-dismiss=“modal” aria-hidden=“true”>Close
  •