Insert, Update, Delete in Entity Framework

ADO.NET Entity Framework is an powerful tools to developed applications. This article explains basic CRUD operations in Entity Framework. This tutorial will help you to insert, update, delete, select, search data into SQL Server by using ADO.NET Entity Framework. For this we will use Students table. 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) Design
• Creation of Entity Data Model
• 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 “MyEF”
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.

Creation of Entity Data Model
Create an ADO.NET Entity Data Model in your project by using Entity Data Model Wizard.

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

TestDBEntities db = new TestDBEntities();
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.AddObject(oStudent);
db.SaveChanges();

BindGridView();

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

oStudent.StudentID=int.Parse((txtStudentID.Text));
var result = (from S in db.Students
                where S.StudentID ==oStudent.StudentID
                select S).Single();

result.StudentName  = txtName.Text;
result.Address      = txtAddress.Text;
result.Phone        = txtPhone.Text;
db.Students.ApplyCurrentValues(result);
db.SaveChanges();

BindGridView();

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

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

oStudent.StudentID = int.Parse((txtStudentID.Text));
var result = (from S in db.Students
                where S.StudentID == oStudent.StudentID 
                select S).Single();

db.Students.DeleteObject(result);
db.SaveChanges();

BindGridView();

Select one student form the GridView and click delete button.

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

oStudent.StudentID = int.Parse((txtSearch.Text));
var result = from S in db.Students
                where S.StudentID == oStudent.StudentID
                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.