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 behavior.
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 the "ADO.NET Entity Data Model" and change the default name from "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 the 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 the 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 delete the entity object.
    context.DeleteObject(query);
    context.SaveChanges();
}

OUTPUT