Insert, Update, Delete in LINQ to SQL

Language Integrated Query or LINQ is a .NET Framework component developed by Microsoft. It provides a compact and expressive syntax to manipulate data. To support LINQ some features are added in C# and Visual Basic. That’s why we can use it in our C# or Visual Basic programs. We can use it in SQL, XML and object. Using LINQ we can easily manipulate our SQL Database. This article describes CRUD operation in LINQ.

Here you will learn basic operations like Insert, Update, Delete, Select, Searching in LINQ to SQL. For this we will use one table “Students”. We will save Student ID, Name, Address, and Phone number of a Student in Students table. This can be summarized as like:

• Table Creations
• Web Project Creations
• User Interface (UI) Creations
• Include LINQ DataContext File
• Select Operation
• Insert Operation
• Update Operation
• Delete Operation
• Search Operation

Table Creations
Create a Database in the SQL Server named “TestDB”. Create a “Students” table in the database. We can use the following SQL scripts:

CREATE TABLE [dbo].[Students](
	[StudentID] [int] IDENTITY(1,1) NOT NULL,
	[StudentName] [nvarchar](150) NULL,
	[Address] [nvarchar](200) NULL,
	[Phone] [nvarchar](50) NULL,
 CONSTRAINT [PK_member] PRIMARY KEY CLUSTERED 
(
	[StudentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
 ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Web API Project Creations
We can create web or desktop application. Let’s start with ASP.Net web application. In order to implement this we need to install Visual Studio 2010/2012. All are available on the internet. Here Visual Studio 2010 is used. All the steps are given bellow:

Open Visual Studio and click “New Project” or File -> New Project. In the New Project dialog box:
Open the Visual C# templates
Select the template ASP.NET Web Application
Set the project name to “MyLinq”
Set the disk location to something like C:\TestProject
Click OK

A default ASP.NET web application project will be created.

User Interface (UI) Creations
We need to create a user interface or UI as like the sample image which is given bellow. We can use the following ASP.NET code to design required UI.

<table>
    <tr>
        <td style="width: 120px">
            Student
            ID</td>
        <td>
            <asp:TextBox ID="txtStudentID" runat="server" Enabled="False"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td style="width: 120px">
            Name</td>
        <td>
            <asp:TextBox ID="txtName" runat="server" Width="250px"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Address</td>
        <td>
            <asp:TextBox ID="txtAddress" runat="server" TextMode="MultiLine" Width="250px"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            Phone</td>
        <td>
            <asp:TextBox ID="txtPhone" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            &nbsp;</td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            &nbsp;</td>
        <td>
            <asp:Button ID="btnAdd" runat="server" Text="Add" Width="80px"
                onclick="btnAdd_Click" />
            <asp:Button ID="btnEdit" runat="server" onclick="btnEdit_Click" Text="Edit"
                Width="80px" />
            <asp:Button ID="btnDelete" runat="server" onclick="btnDelete_Click" Text="Delete"
                Width="80px" />
        </td>
    </tr>
    <tr>
        <td>
            &nbsp;</td>
        <td>
            &nbsp;</td>
    </tr>
    <tr>
        <td>
            &nbsp;</td>
        <td>
            <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
            <asp:Button ID="btnSearch" runat="server" onclick="btnSearch_Click"
                Text="Search" Width="80px" />
        </td>
    </tr>
</table>

 
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
            Width="600px" AutoGenerateSelectButton="True"
            onselectedindexchanged="GridView1_SelectedIndexChanged">
            <Columns>
                <asp:BoundField DataField="StudentID" HeaderText="Student ID" />
                <asp:BoundField DataField="StudentName" HeaderText="Name" />
                <asp:BoundField DataField="Address" HeaderText="Address" />
                <asp:BoundField DataField="Phone" HeaderText="Phone" />
            </Columns>
</asp:GridView>

Here some TextBox & Button and one GridView controls are used.

Include LINQ DataContext File
Write click on the solution explorer and add a data Context file (LINQ to SQL Data Classes). Give its name as “MyLinqDataClasses”. Drag Students table in DataContext file (LINQ to SQL Data Classes).

Select Operation
Create an object of DataContext class and Student class  in the code behind page.

MyLinqDataClassesDataContext db = new MyLinqDataClassesDataContext();
Student oStudent = new Student();

Write the following code in the code behind page.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindGridView();
    }
}

public void BindGridView()
{
    var result = from S in db.Students
                    select new { S.StudentID, S.StudentName, S.Address, S.Phone };

    GridView1.DataSource = result;
    GridView1.DataBind();
}

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
    txtStudentID.Text   = GridView1.SelectedRow.Cells[1].Text;
    txtName.Text        = GridView1.SelectedRow.Cells[2].Text;
    txtAddress.Text     = GridView1.SelectedRow.Cells[3].Text;
    txtPhone.Text       = GridView1.SelectedRow.Cells[4].Text;
}

Insert Operation
Write the following code under the insert button (btnAdd) click event.

oStudent.StudentName    = txtName.Text;
oStudent.Address        = txtAddress.Text;
oStudent.Phone          = txtPhone.Text;
db.Students.InsertOnSubmit(oStudent);
db.SubmitChanges();

BindGridView();

Update Operation
Write the following code under the edit button (btnEdit) click event.

var result = (from S in db.Students
                where S.StudentID == Convert.ToInt16(txtStudentID.Text)
                select S).Single();                         

result.StudentName    = txtName.Text;
result.Address        = txtAddress.Text;
result.Phone          = txtPhone.Text;            
db.SubmitChanges();

BindGridView();

Select one student form the GridView, change the data and click delete button.

Delete Operation
Write the following code under the delete button (btnDelete) click event.

var result = (from S in db.Students
                where S.StudentID == Convert.ToInt16(txtStudentID.Text)
                select S).Single();

db.Students.DeleteOnSubmit(result);
db.SubmitChanges();

BindGridView();

Select one student form the GridView and click delete button.

Search  Operation
Write the following code under the search button (btnSearch) click event.

var result = from S in db.Students
                where S.StudentID == Convert.ToInt16(txtSearch.Text)
                select new { S.StudentID,S.StudentName,S.Address,S.Phone };

GridView1.DataSource = result;
GridView1.DataBind();

Type any student ID in the search TextBox and click search button.