Fetch, Insert, Update, Delete Data with “Database First Model Approach” using ADO.NET Entity Framework 3.5 and Visual Studio 2008.

In this article you will learn how to Fetch, Insert, Update and Delete Data using the "Database First Model Approach" and the ADO.NET Entity Framework 3.5 and Visual Studio 2008.

What is Entity Framework?

1. Entity Framework is an Object/Relational Mapping (O/RM) framework.
2. It is an enhancement to ADO.NET that gives developers an automated mechanism for accessing & storing the data in a database and working with the results in addition to Data Reader and DataSet.
3. An Entity Framework Model (ERM) defines a schema of entities and their relationships with one another.
4. Entities are not the same as objects.
5. Entities define the schema of an object, but not its behaviour.
6. So, an entity is something like the schema of a table in your database, except that it describes the schema of your business objects.
7. Entity Framework 3.5 supports the Database First model.

DATABASE FIRST MODEL

REQUIREMENTS

For implementing the Database First model, you need to download and install the Microsoft .NET Framework 3.5 Service Pack 1. You can download this from the following link:

http://www.microsoft.com/en-us/download/details.aspx?id=22

CREATE TABLE

1. You need to create a table for implementing the Database First model.
2. I have used the employee table for this tutorial.
3. You can use the following employee table script to create a table.

USE [Test]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Employee](

      [id] [int] IDENTITY(1,1) NOT NULL,

      [name] [varchar](50) NULL,

      [address] [varchar](50) NULL,

 CONSTRAINT [PK_Employee] 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

SET ANSI_PADDING OFF

GO

SET IDENTITY_INSERT [dbo].[Employee] ON

INSERT [dbo].[Employee] ([id], [name], [address]) VALUES (1, N'Employee One', N'Address One')

INSERT [dbo].[Employee] ([id], [name], [address]) VALUES (2, N'Employee Tow', N'Address Two')

INSERT [dbo].[Employee] ([id], [name], [address]) VALUES (3, N'Employee Three', N'Address Three')

SET IDENTITY_INSERT [dbo].[Employee] OFF

GENERATING MODEL FROM DATABASE

1. Create a new "Employee" Web site in Visual Studio 2008.

2. In the Solution Explorer, right-click on the project and choose "Add New Item".
   
   
    

3. It will open an "Add New Item" dialog box. Choose "ADO.NET Entity Data Model" and change the default name form "Model.edmx" to "Employee.edmx". Then click on the "Add" button.
   
   
    

4. After clicking on the Add button, this will open the "Entity Data Model Wizard".
   
   
    

5. In the wizard choose "Generate from database" and click on the "Next" button.

6. Here you have two options. One is you can choose the existing SQL connection and another option is you can create your own SQL connection.
   
   
    

7. For a new connection, click on the "New Connection" button. It will open the "Connection properties" window. Fill in all the details and click on the "OK" button. The new connection will be shown in the Entity Data Model Wizard.
   
   
    

8. Click on "Yes, include the sensitive data in the connection string." and click on the "Next" button.
   
   
    

9. Choose the required tables and name the Model Namespace as "EmployeeModel".

10. Click on the "Finish" button. The model will be generated and opened in the EDM Designer.
    
    
    

CREATE A GRIDVIEW TO MANAGE THE DATA

1. Open the default.aspx page and add the grid view as below:
 

<asp:GridView ID="grdEmployeeData" AutoGenerateColumns="False" runat="server"

    CellPadding="4" ForeColor="#333333" GridLines="None">

    <RowStyle BackColor="#EFF3FB" />

    <Columns>

        <asp:TemplateField HeaderText="Employee ID">

            <ItemTemplate>

                <%# Eval("id") %>

            </ItemTemplate>

        </asp:TemplateField>

        <asp:TemplateField HeaderText="Employee Name">

            <ItemTemplate>

                <%# Eval("name")%>

            </ItemTemplate>

        </asp:TemplateField>

        <asp:TemplateField HeaderText="Employee Adress">

            <ItemTemplate>

                <%# Eval("address")%>

            </ItemTemplate>

        </asp:TemplateField>

    </Columns>

    <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

    <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

    <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />

    <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

    <EditRowStyle BackColor="#2461BF" />

    <AlternatingRowStyle BackColor="White" />

</asp:GridView>

2. Go to the "default.aspx.cs" page.

3. Include the namespace at the top of the page. This namespace will be given from the "Model Namespace" from Step 8.

using EmployeeModel;

DISPLAY DATA IN THE GRIDVIEW

The following code will help to display the data in the grid view.
 

private void getData()

{

    //Here TestEntities is the class which is given from "Save entity connection setting in web.config"

    TestEntities context = new TestEntities();

 

    var query = from data in context.Employee

                orderby data.name

                select data;

 

    //Bind Data to Gridview

    grdEmployeeData.DataSource = query;

    grdEmployeeData.DataBind();

}

Output




INSERT DATA IN THE EMPLOYEE TABLE

Use the following code to add the new employee:
 

private void insertData()

{

    //Here TestEntities is the class which is given from "Save entity connection setting in web.config"

    TestEntities context = new TestEntities();

 

    // Create a new employee

    Employee objEmployee = new Employee();

    objEmployee.name = "Employee Four";

    objEmployee.address = "Address Four";

 

    //Add the created Employee object to context.

    context.AddToEmployee(objEmployee);

    context.SaveChanges();

}

Output




UPDATE DATA IN THE DATABASE

You can use the following code to update the employee details:
 

public void updateData()

{

    //Here TestEntities is the class which is given from "Save entity connection setting in web.config"

    TestEntities context = new TestEntities();

 

    var query = from data in context.Employee

                orderby data.name

                select data;

 

    foreach (Employee details in query)

    {

        if (details.id == 1)

        {

            //Assign the new values to name whose id is 1

            details.name = "Updated Employee One";

        }

    }

 

    //Save the changes back to database.

    context.SaveChanges();

}

Output




DELETE THE EMPLOYEE DETAILS FROM DATABASE

You can use the following code to delete the employee details from the database:
 

public void deleteData()

{

    //Here TestEntities is the class which is given from "Save entity connection setting in web.config"

    TestEntities context = new TestEntities();

 

    var query = (from data in context.Employee

                where data.id == 1

                orderby data.name

                select data).First();

 

    context.Attach(query);

    //DeleteObject is used to the delete the entity onject.

    context.DeleteObject(query);

    context.SaveChanges();

}

OUTPUT