CRUD Operations In Entity Framework

In this article, we will see basic CRUD operation in EF. We have seen articles on EF before. So, before going through this article, I would recommend some basic reads on EF.

• Introduction To Entity Framework
• Model First Approach Of Entity Framework

What is Entity Framework?

Entity Framework is an ORM framework. ORM stands for Object Relational Mapping.

What is an Object Relational Mapping framework?

Object Relational Mapping framework automatically creates classes, which are based on the database tables and the opposite is also true, i.e., it can also automatically generate necessary SQL to create the database tables, which are based on the classes.

Let’s get started!!

We want to achieve this output

We will create a simple application. First, create a simple empty Web application and give it a suitable name. Now, let’s create a table called "test". Here is the script to create a table. 

USE [TESTING]
GO

/****** Object:  Table [dbo].[test]    Script Date: 2/9/2017 8:19:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[test](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [MiddleName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [Address] [nvarchar](50) NULL,
    [Landmark] [nvarchar](50) NULL,
    [EmailID] [nvarchar](50) NULL,
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]

GO

Adding ADO.NET Entity Data Model file

Now, let's add ADO.NET Entity data model file to our project. Right click on the project ->Add-> ADO.NET Entity Data Models and name it Employee.

Click "OK" and select "EF Designer from database" option.

Click on Next and select the option "include the sensitive data in connection string" and click on "Next". It will ask you which database objects to include in our model . So, select the table which you created.

Click on "Finish" and it will create an Employee.edmx file in our project.

The fields are properly mapped as you can see from the above screenshot with ID as the primary key and the respective fields are associated properly on the designer surface.

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; i.e., bootstrap and jQuery. 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 update panel control.

<!-- Placing GridView in UpdatePanel-->
            <asp:UpdatePanel ID="upCrudGrid" runat="server">
                <ContentTemplate>

                    <asp:GridView ID="GridView1" runat="server" Width="940px" HorizontalAlign="Center"
                        OnRowCommand="GridView1_RowCommand" OnPageIndexChanging="GridView1_OnPageIndexChanging"  AutoGenerateColumns="false" AllowPaging="true"
                        CssClass="table table-hover table-striped" PageIndex="3" PageSize="3">
                        <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: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 the code, we are selecting records from our database and buttons for editing and deleting 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 EF. 

private void BindGrid()
        {
            using (TESTINGEntities test = new TESTINGEntities())
            {
                if (test.tests.Count() > 0)
                {
                    GridView1.DataSource = (from em in test.tests
                                            select new {em.id, em.FirstName, em.MiddleName, em.LastName, em.Address, em.Landmark, em.EmailID }).ToList();
                    GridView1.DataBind();
                }

                else
                {
                    GridView1.DataSource = null;
                    GridView1.DataBind();

                }

            }
        }

And on Page Load let’s call that method BindGrid() as 

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

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

Output for Select

Insert Code

Now let’s setup the GUI part for inserting 

<!-- Add Record Modal Starts here-->
            <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>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>




                            </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>
            <!--Add Record Modal Ends here-->

Here we had placed the respective textboxes on the modal popup and calling the modal popup on button click in code behind.

We will see how to popup the modal through code behind

 

protected void btnAdd_Click(object sender, EventArgs e)
        {

            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.Append(@"<script type='text/javascript'>");
            sb.Append("$('#addModal').modal('show');");
            sb.Append(@"</script>");
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "AddShowModalScript", sb.ToString(), false);
        }

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

protected void btnAddRecord_Click(object sender, EventArgs e)
        {

            using (TESTINGEntities test = new TESTINGEntities())
            {
                test testing = new test();
                testing.FirstName = txtFirstName.Text;
                testing.MiddleName = txtMiddleName.Text;
                testing.LastName = txtLastName.Text;
                testing.Address = txtAddress.Text;
                testing.Landmark = txtLandmark.Text;
                testing.EmailID = txtEmailID.Text;
                test.tests.Add(testing);
                test.SaveChanges();
                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);
                BindGrid();


            }
        }

So, we are wrapping testingentites inside a using block just to make sure we are properly opening and closing connections properly.We had created a variable called as test and passing those single fields in our test table which is defined in our testentities.

In LINQ to SQL we were having submitchanges() in order to save the form entries.To find out refer LINQ to SQL CRUD operations here,

• LINQ To SQL - CRUD Operations

Here in EF we have savechanges() to save the form fields. And a popup to show that records are inserted successfully.

Performing Edit

On button click (Edit) we want to load all the details of that employee

Let’s setup the GUI part for editing, 

<!-- Edit Modal Starts here -->
            <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:Label ID="lblID" runat="server"></asp:Label>
                                   </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>

                            </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>
            <!-- Edit Modal Ends here -->

Let’s write Code for Editing, 

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "editRecord")
            {

                int index = Convert.ToInt32(e.CommandArgument);

                GridViewRow gr = GridView1.Rows[index];
                lblID.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;
                ViewState["id"] = lblID.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 editing and here we are retrieving values from databases and allocating respective cells. Now we will write code for updating respective values,

protected void btnSave_Click(object sender, EventArgs e)
        {
            using (TESTINGEntities test = new TESTINGEntities())
            {
                int id = Convert.ToInt32(ViewState["id"].ToString());
                  var reg = test.tests.Single(employee => employee.id == id);

                reg.FirstName = txtFirstNameEdit.Text;
                reg.MiddleName = txtMiddleNameEdit.Text;
                reg.LastName = txtLastNameEdit.Text;
                reg.Address = txtAddressEdit.Text;
                reg.Landmark = txtLandmarkEdit.Text;
                reg.EmailID = txtEmailIDEdit.Text;
                test.SaveChanges();
                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);
            }

            BindGrid();

        }

So, we are wrapping testentities class inside using block just to make sure we are opening and closing our connection respectively. After that we had created a variable reg and passing single records in our registration table which are defined in our testentities class this will pass the values which are filled with properties i.e. textboxes here. Last, we are calling savechanges() in order to submit those details in our database.

Delete Code

GUI Part 

<!-- Delete Record Modal Starts here-->
            <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-body">
                            Are you sure you want to delete the record?
                           <%-- <asp:HiddenField ID="hfCode" runat="server" />--%>
                        </div>
                        <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>
            <!--Delete Record Modal Ends here -->

Code for Delete operation 

else if (e.CommandName == "deleteRecord")
            {

                int emp_ID = Convert.ToInt32(e.CommandArgument);
                TESTINGEntities empDB = new TESTINGEntities();
                var emp = empDB.tests.Single(employee => employee.id == emp_ID);
                empDB.tests.Remove(emp);
                empDB.SaveChanges();
                BindGrid();
            }

This is simple straight forward code of Delete button. We are first capturing the ID of employee and then we have created the test entities object of our sample class. We are calling save changes  and deleting ID. Simple and straight forward code again.

Code for Edit and Delete

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "editRecord")
            {

                int index = Convert.ToInt32(e.CommandArgument);

                GridViewRow gr = GridView1.Rows[index];
                lblID.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;
                ViewState["id"] = lblID.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);
            }

            else if (e.CommandName == "deleteRecord")
            {

                int emp_ID = Convert.ToInt32(e.CommandArgument);
                TESTINGEntities empDB = new TESTINGEntities();
                var emp = empDB.tests.Single(employee => employee.id == emp_ID);
                empDB.tests.Remove(emp);
                empDB.SaveChanges();
                BindGrid();
            }

        }

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