LINQ To SQL - CRUD Operations

Akshay Phadke
7y
17k
0
2

Article

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.

USE [TEST]
GO
/****** Object: Table [dbo].[registration] Script Date: 1/16/2017 11:53:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[registration](
[id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](max) NULL,
[MiddleName] [nvarchar](max) NULL,
[LastName] [nvarchar](max) NULL,
[Address] [nvarchar](max) NULL,
[Landmark] [nvarchar](max) NULL,
[EmailID] [nvarchar](max) NULL,
[Country] [nvarchar](max) NULL,
[State] [nvarchar](max) NULL,
[City] [nvarchar](max) NULL,
[MobileNumber] [nvarchar](max) NULL,
CONSTRAINT [PK_registration] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
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.

<link href="Styles/bootstrap.css" rel="stylesheet" />
<script src="Scripts/jquery-1.8.2.js"></script>
<script src="Scripts/bootstrap.js"></script>

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.

<asp:ScriptManager runat="server" ID="ScriptManager1" />

Select Code

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

<asp:UpdatePanel ID="upCrudGrid" runat="server">
<ContentTemplate>
<asp:GridView ID="GridView1" runat="server" Width="940px" HorizontalAlign="Center"
OnRowCommand="GridView1_RowCommand" AutoGenerateColumns="false" AllowPaging="true"
CssClass="table table-hover table-striped">
<Columns>
<asp:BoundField DataField="id" HeaderText="ID"/>
<asp:BoundField DataField="FirstName" HeaderText="FirstName"/>
<asp:BoundField DataField="MiddleName" HeaderText="MiddleName"/>
<asp:BoundField DataField="LastName" HeaderText="LastName"/>
<asp:BoundField DataField="Address" HeaderText="Address"/>
<asp:BoundField DataField="Landmark" HeaderText="Landmark"/>
<asp:BoundField DataField="EmailID" HeaderText="Email"/>
<asp:BoundField DataField="Country" HeaderText="Country"/>
<asp:BoundField DataField="State" HeaderText="State"/>
<asp:BoundField DataField="City" HeaderText="City"/>
<asp:BoundField DataField="MobileNumber" HeaderText="MobileNo"/>
<asp:ButtonField CommandName="editRecord" ControlStyle-CssClass="btn btn-info"
ButtonType="Button" Text="Edit" HeaderText="Edit Record" >
<ControlStyle CssClass="btn btn-primary" ></ControlStyle>
</asp:ButtonField>
<asp:ButtonField CommandName="deleteRecord" ControlStyle-CssClass="btn btn-info"
ButtonType="Button" Text="Delete" HeaderText="Delete Record">
<ControlStyle CssClass="btn btn-danger"></ControlStyle>
</asp:ButtonField>
</Columns>
</asp:GridView>
<asp:Button ID="btnAdd" runat="server" Text="Add New Record" CssClass="btn btn-info" OnClick="btnAdd_Click" />
</ContentTemplate>
<Triggers>
</Triggers>
</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.

private void BindEmployee()
{
SampleDataContext dbcontext = new SampleDataContext();
GridView1.DataSource = from reg in dbcontext.registrations
select reg;
GridView1.DataBind();
}

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.

<div id="addModal" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="addModalLabel" aria-hidden="true">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h3 id="addModalLabel">Add New Record</h3>
</div>
<asp:UpdatePanel ID="upAdd" runat="server">
<ContentTemplate>
<div class="modal-body">
<table class="table table-bordered table-hover">
<tr>
<td>ID : </td>
<td>
<asp:TextBox ID="txtIdAdd" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>FirstName : </td>
<td>
<asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>MiddleName :</td>
<td>
<asp:TextBox ID="txtMiddleName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>LastName:</td>
<td>
<asp:TextBox ID="txtLastName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Address:</td>
<td>
<asp:TextBox ID="txtAddress" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Landmark:</td>
<td>
<asp:TextBox ID="txtLandmark" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>EmailID:</td>
<td>
<asp:TextBox ID="txtEmailID" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Country:</td>
<td>
<asp:TextBox ID="txtCountry" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>State:</td>
<td>
<asp:TextBox ID="txtState" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>City:</td>
<td>
<asp:TextBox ID="txtCity" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Mobilenumber:</td>
<td>
<asp:TextBox ID="txtMobileNumber" runat="server"></asp:TextBox></td>
</tr>
</table>
</div>
<div class="modal-footer">
<asp:Button ID="btnAddRecord" runat="server" Text="Add" CssClass="btn btn-primary" OnClick="btnAddRecord_Click" />
<button class="btn btn-info" data-dismiss="modal" aria-hidden="true">Close</button>
</div>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="btnAddRecord" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</div>

Here, we have placed the respective textboxes on the modal popup and called the modal popup on button click in code behind.

We will see how to pop the modal up through code behind.

protected void btnAddRecord_Click(object sender, EventArgs e)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("alert('Record Added Successfully');");
sb.Append("$('#addModal').modal('hide');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "AddHideModalScript", sb.ToString(), false);
}

So, this is the modal popup on "Add New Record" button. Let’s run the application and check.

So, we have created the modal pop up successfully. Now, let’s write the code for inserting.

protected void btnAddRecord_Click(object sender, EventArgs e)
{
using (SampleDataContext dbcontext = new SampleDataContext())
{
registration reg = new registration();
reg.id = Convert.ToInt32(txtIdAdd.Text);
reg.FirstName = txtFirstName.Text;
reg.MiddleName = txtMiddleName.Text;
reg.LastName = txtLastName.Text;
reg.Address = txtAddress.Text;
reg.Landmark = txtLandmark.Text;
reg.EmailID = txtEmailID.Text;
reg.Country = txtCountry.Text;
reg.State = txtState.Text;
reg.City = txtCity.Text;
reg.MobileNumber = txtMobileNumber.Text;
dbcontext.registrations.InsertOnSubmit(reg);
dbcontext.SubmitChanges();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("alert('Record Added Successfully');");
sb.Append("$('#addModal').modal('hide');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "AddHideModalScript", sb.ToString(), false);
BindEmployee();
// clearfields();
}
}

We are wrapping sampledatacontext class inside using block, just to make sure that we are opening and closing our connection respectively. After that, we create a variable reg and pass single records in our registration table, which are defined in our Sampledatacontext class. This will pass the values which are filled with properties i.e. textboxes here. Last, we are calling submitchanges() in order to submit those details in our database, and a popup to show that the records are inserted successfully.

Performing Edit

On button click (Edit), we want to achieve loading of the details of that employee.

Let’s setup the GUI part for editing.

<div id="editModal" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="editModalLabel" aria-hidden="true">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h3 id="editModalLabel">Edit Record</h3>
</div>
<asp:UpdatePanel ID="upEdit" runat="server">
<ContentTemplate>
<div class="modal-body">
<table class="table">
<tr>
<td>ID : </td>
<td>
<asp:TextBox ID="txtID" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>FirstName : </td>
<td>
<asp:TextBox ID="txtFirstNameEdit" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>MiddleName :</td>
<td>
<asp:TextBox ID="txtMiddleNameEdit" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>LastName:</td>
<td>
<asp:TextBox ID="txtLastNameEdit" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Address:</td>
<td>
<asp:TextBox ID="txtAddressEdit" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Landmark:</td>
<td>
<asp:TextBox ID="txtLandmarkEdit" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>EmailID:</td>
<td>
<asp:TextBox ID="txtEmailIDEdit" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Country:</td>
<td>
<asp:TextBox ID="txtCountryEdit" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>State:</td>
<td>
<asp:TextBox ID="txtStateEdit" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>City:</td>
<td>
<asp:TextBox ID="txtCityEdit" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Mobilenumber:</td>
<td>
<asp:TextBox ID="txtMobileNumberEdit" runat="server"></asp:TextBox></td>
</tr>
</table>
</div>
<div class="modal-footer">
<asp:Label ID="lblResult" Visible="false" runat="server"></asp:Label>
<asp:Button ID="btnSave" runat="server" Text="Update" CssClass="btn btn-info" OnClick="btnSave_Click" />
<button class="btn btn-info" data-dismiss="modal" aria-hidden="true">Close</button>
</div>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="GridView1" EventName="RowCommand" />
<asp:AsyncPostBackTrigger ControlID="btnSave" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</div>

Let's write the code for Edit.

if (e.CommandName == "editRecord")
{
int index = Convert.ToInt32(e.CommandArgument);
GridViewRow gr = GridView1.Rows[index];
txtID.Text = gr.Cells[0].Text;
txtFirstNameEdit.Text = gr.Cells[1].Text;
txtMiddleNameEdit.Text = gr.Cells[2].Text;
txtLastNameEdit.Text = gr.Cells[3].Text;
txtAddressEdit.Text = gr.Cells[4].Text;
txtLandmarkEdit.Text = gr.Cells[5].Text;
txtEmailIDEdit.Text = gr.Cells[6].Text;
txtCountryEdit.Text = gr.Cells[7].Text;
txtStateEdit.Text = gr.Cells[8].Text;
txtCityEdit.Text = gr.Cells[9].Text;
txtMobileNumberEdit.Text = gr.Cells[10].Text;
ViewState["id"]= txtID.Text;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("$('#editModal').modal('show');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "EditModalScript", sb.ToString(), false);
}

We are calling the command button for edit. Here, we are retrieving values from databases and allocating them to respective cells.

Now, let's write the code for updating respective values

protected void btnSave_Click(object sender, EventArgs e)
{
using (SampleDataContext dbcontext = new SampleDataContext())
{
{
// var reg = dbcontext.registrations.SingleOrDefault(x => x.id == 4);
var reg = dbcontext.registrations.Single(employee => employee.id == Convert.ToInt32(ViewState["id"].ToString()));
// string id = lblid.Text;
reg.id = Convert.ToInt32(txtID.Text);
reg.FirstName = txtFirstNameEdit.Text.ToString();
reg.MiddleName = txtMiddleNameEdit.Text.ToString();
reg.LastName = txtLastNameEdit.Text.ToString();
reg.Address = txtAddressEdit.Text.ToString();
reg.Landmark = txtLandmarkEdit.Text.ToString();
reg.Country = txtCountryEdit.Text.ToString();
reg.State = txtStateEdit.Text.ToString();
reg.City = txtCityEdit.Text.ToString();
reg.EmailID = txtEmailIDEdit.Text.ToString();
reg.MobileNumber = txtMobileNumberEdit.Text.ToString();
}
dbcontext.SubmitChanges();
BindEmployee();
BindEmployee();
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("alert('Records Updated Successfully');");
sb.Append("$('#editModal').modal('hide');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "EditHideModalScript", sb.ToString(), false);
}
}

So, we are wrapping sampledatacontext class inside, using block, just to make sure that we are opening and closing our connection respectively. After that, we create a variable reg and pass single records in our registration table which are defined in our Sampledatacontext class. This will pass the values which are filled with properties i.e. textboxes here. Last, we are calling submitchanges() in order to submit those details in our database, and a popup to show that the records are inserted successfully.

Delete Code

GUI Part.

<div id="deleteModal" class="modal hide fade" tabindex="-1" role="dialog" aria-labelledby="delModalLabel" aria-hidden="true">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h3 id="delModalLabel">Delete Record</h3>
</div>
<asp:UpdatePanel ID="upDel" runat="server">
<ContentTemplate>
<div class="modal-footer">
<asp:Button ID="btnDelete" runat="server" Text="Delete" CssClass="btn btn-info" OnClick="btnDelete_Click" />
<button class="btn btn-info" data-dismiss="modal" aria-hidden="true">Cancel</button>
</div>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="btnDelete" EventName="Click" />
</Triggers>
</asp:UpdatePanel>
</div>

Code for delete

if (e.CommandName == "deleteRecord")
{
int emp_ID = Convert.ToInt32(e.CommandArgument);
SampleDataContext empDB = new SampleDataContext();
var emp = empDB.registrations.Single(employee => employee.id == emp_ID);
empDB.registrations.DeleteOnSubmit(emp);
empDB.SubmitChanges();
BindEmployee();
}

This is simple straightforward code. On Delete button, we are first capturing the ID of employee and then recreating the sampledatacontext object of our sample class. We are calling DeleteOnsubmit changes and deleting ID. Simple and straightforward code again.

Code for Edit and Delete

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName == "deleteRecord")
{
int emp_ID = Convert.ToInt32(e.CommandArgument);
SampleDataContext empDB = new SampleDataContext();
var emp = empDB.registrations.Single(employee => employee.id == emp_ID);
empDB.registrations.DeleteOnSubmit(emp);
empDB.SubmitChanges();
BindEmployee();
}
else if (e.CommandName == "editRecord")
{
int index = Convert.ToInt32(e.CommandArgument);
GridViewRow gr = GridView1.Rows[index];
// string id = gr.Cells[0].Text;
// lblid.Text = gr.Cells[index].Text;
// int emp_ID = 0;
// lblid.Text = gr.Cells[0].Text;
// txtID.Text = gr.Cells[0].Text;
txtID.Text = gr.Cells[0].Text;
txtFirstNameEdit.Text = gr.Cells[1].Text;
txtMiddleNameEdit.Text = gr.Cells[2].Text;
txtLastNameEdit.Text = gr.Cells[3].Text;
txtAddressEdit.Text = gr.Cells[4].Text;
txtLandmarkEdit.Text = gr.Cells[5].Text;
txtEmailIDEdit.Text = gr.Cells[6].Text;
txtCountryEdit.Text = gr.Cells[7].Text;
txtStateEdit.Text = gr.Cells[8].Text;
txtCityEdit.Text = gr.Cells[9].Text;
txtMobileNumberEdit.Text = gr.Cells[10].Text;
ViewState["id"]= txtID.Text;
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append(@"<script type='text/javascript'>");
sb.Append("$('#editModal').modal('show');");
sb.Append(@"</script>");
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "EditModalScript", sb.ToString(), false);
}
}

So, we are done now. Now, run the application and perform CRUD operation in LINQ to SQL.

Up Next

Ebook Download

View all

Printing in C# Made Easy

Read by 10.8k people

Download Now!

Learn

View all