This article will demonstrate how to use the Entity Framework 4 to create, read, update, and delete (CRUD) records from a SQL Server database.  This article builds upon the database that was generated in the first article, Create a Database Using Model First Development.  You'll need to download Visual Studio 2010 Beta 2 from Microsoft's site in order for the sample code to work.  You can download the sample code and database script here (code) (script).

The goal of this article is to create a web page that allows a user to maintain the records in the UserAccounts table.  The final web page looks like the following figure.

Figure 1

The drop down list at the top of the page allows you to navigate from user to user and display's the properties on the page.  The user can simply click the Save button to add or update records or click the Delete button to remove a record.

Part 1 of this article demonstrated how to create two entities using the Entity Framework 4.  The two entities are UserAccount and Address.  Each entity had an Id field defined as the primary key and various fields specific to the entity.  The one thing I forgot to show you in the first article was how to tell the Entity Framework to define the Id field as an Identity field.

1.    Launch Visual Studio and open the OrderSystem project.

2.    Double click on the OrderDB.edmx file in the Solution Explorer to view the Entity Framework designer.

3.    Click on the Id field in the UserAccount entity and view its properties.

4.    To define a field as an Identity field you need to change the StoreGeneratedPattern property to Identity.  Do the same for the Id field in the Addresses entity.

Figure 2

5.    Now that the Entity Model is updated, you need to update the database.  Right click on the Designer and select Generate Database From Model.  This will bring up the Generate Database Wizard.  The DDL to create the two tables with the Identity field will be automatically generated.  Click the Finish button to create the script.  You'll get the following message warning you that you are going to overwrite the script that already exists.  Click the Yes button to generate the script.

Figure 3

6.    The OrderDB.edmx.sql file will be overwritten and should open in Visual Studio.  You now need to execute the sql file against the database.  To do this simply right click anywhere in the file and select Execute SQL from the pop-up menu.

Figure 4

You'll be prompted to connect to the database.  Once you log in the script will execute.

The next step is to add a web form to the application that will allow the user to maintain the list of UserAccount records.

1.    Right click on the OrderSystem project in the Solution Explorer and select AddàNew Item… from the pop-up menu.

2.    Select the Web Form template and change the name to Users.aspx.  Click the Add button.

3.    The HTML view of the web form should appear in Visual Studio.  Add the following code between the div tags.

<table>
  <tr>
    <td>Select A User:</td>
    <td><asp:DropDownList runat=server ID="ddlUsers" AutoPostBack="True">
        </asp:DropDownList> </td>
  </tr>        
  <tr>
    <td>First Name:</td>
    <td><asp:TextBox runat="server" ID="txtFirstName"></asp:TextBox></td>
  </tr>
  <tr>
    <td>Last Name:</td>
    <td><asp:TextBox runat="server" ID="txtLastName"></asp:TextBox></td>
  </tr>
  <tr>
    <td>Inserted:</td>
    <td><asp:Label runat="server" ID="lblInserted"></asp:Label> </td>
  </tr>
  <tr>
    <td>Updated:</td>
    <td><asp:Label runat="server" ID="lblUpdated"></asp:Label> </td>
  </tr>
</table>    
<asp:Button runat=server ID="btnSave" Text="Save" />
<asp:Button ID="btnDelete" runat="server" Text="Delete" />

This code uses a HTML table to format the controls on the web form.  If you switch to Design view the form should look like the following image.

Figure 5

The first task we'll do is to load the drop down list in the page load event with the list of records in the UserAccounts table.  We'll also add an extra entry in the list to allow the user to select the option of creating a new user.

1.    Double click on the web form in Design view to create the Page_Load event in the code behind.

2.    Add the following code to the Page_Load event.

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

3.    The LoadUserDropDownList is a custom method that you must create.

private void LoadUserDropDownList()
{
  using (OrderDBContainer db = new OrderDBContainer())
  {              
    ddlUsers.DataSource = from u in db.UserAccounts
                       orderby u.LastName
                 select new { Name = u.LastName + ", " + u.FirstName, Id = u.Id };
 
    ddlUsers.DataTextField = "Name";
    ddlUsers.DataValueField = "Id";
    ddlUsers.DataBind();
 
    ddlUsers.Items.Insert(0, new ListItem("Create New User", ""));
  }
}

This method creates and an instance of the OrderDBContainer class which was created when you created the OrderDB.edmx file.  This object acts similar to a connection object in ADO.NET.  You use the OrderDBContainer to "connect" to the database and manipulate the entities defined within it.  The drop down list's DataSource source property is set to the results of a LINQ query.  The Entity Framework will translate this syntax into a SQL statement.  The syntax for writing LINQ queries takes some time to get used to because it's backwards from SQL.  The FROM clause comes first and the SELECT clause comes last.   In this example, I'm selecting all the records from the UserAccounts table and ordering them by their last name.  In the select clause I'm creating a dynamically generated object with two properties called Name and Id.  The Name is what will be displayed to the user in the drop down list.  I'm concatenating the Last and First name and separating them by a comma. 

The DataTextField is then set to "Name" which is the property in the dynamically created object.  The DataValueField is then set to "Id".  The next line binds the data to the drop down list.  The call to the database doesn't actually get made until this line is executed.  The last line adds a new item to the list in the first position.  The text of the item is "Create New User" and this will be used to determine if the user is adding or updating an existing user.

Set this page as the startup page and run the project.  There are no records in the table yet so all you'll see is the "Create New User" entry in the drop down list.  If you were to turn on SQL Server Profiler you would see the SQL statement that the Entity Framework executed against the database to retrieve the records.

SELECT 
[Project1].[Id] AS [Id], 
[Project1].[C1] AS [C1]
FROM ( SELECT 
      [Extent1].[Id] AS [Id], 
      [Extent1].[LastName] AS [LastName], 
      [Extent1].[LastName] + N', ' + [Extent1].[FirstName] AS [C1]
      FROM [dbo].[UserAccounts] AS [Extent1]
)  AS [Project1]
ORDER BY [Project1].[LastName] ASC

The next step will be to add the code to allow the user to create new or update records in the table.

1.    Switch to Design view and double click on the Save button to create the button click event handler.

2.    Add the following code to the click event handler.

using (OrderDBContainer db = new OrderDBContainer())
{
  UserAccount userAccount = new UserAccount();
  userAccount.FirstName = txtFirstName.Text;
  userAccount.LastName = txtLastName.Text;
  userAccount.AuditFields.UpdateDate = DateTime.Now;
 
  if (ddlUsers.SelectedItem.Value == "")
  {
    //Adding                    
    userAccount.AuditFields.InsertDate = DateTime.Now;
    db.UserAccounts.AddObject(userAccount);
  }
  else
  {
    //Updating
    userAccount.Id = Convert.ToInt32(ddlUsers.SelectedValue);
    userAccount.AuditFields.InsertDate = Convert.ToDateTime(lblInserted.Text);
 
    db.UserAccounts.Attach(userAccount);
    db.ObjectStateManager.ChangeObjectState(userAccount, System.Data.EntityState.Modified);
  }
 
  db.SaveChanges();
 
  lblInserted.Text = userAccount.AuditFields.InsertDate.ToString();
  lblUpdated.Text = userAccount.AuditFields.UpdateDate.ToString();
 
  //Reload the drop down list
  LoadUserDropDownList();
 
  //Select the one the user just saved.
  ddlUsers.Items.FindByValue(userAccount.Id.ToString()).Selected = true;
}

This code starts by instanciating the OrderDBContainer object and then creates a new instance of a UserAccount object.  The FirstName and LastName are set to the value entered by the user.  The UpdateDate is set to the current date\time.  The next line checks if the selected item in the Users drop down list is blank.  A blank value would signify that the user selected "Create New User".  If they are creating a new user then the InsertDate is set to the current date\time and the UserAccount object is added to the list of UserAccount objects associated with the OrderDBContainer.  This doesn't add the record to the database, it simply lets the OrderDBContainer know that this object should be added to the database.

If the user was updating a record rather than adding one then the "else" logic would be followed.  The Id is set to the Id of the selected item in the drop down list.  The InsertDate should not be changed so the value that is displayed in the label for the insert date is used.  Since we don't want to add this record we need to call the Attach method on the UserAccounts object.  Again this tells the OrderDBContainer that this object exists. You then have to tell the OrderDBContainer to update the record associated with this object.  To do that you call the ObjectStateManager.ChangeObjectState method and pass in the object to be updated and the Modified entity state enumeration value.

The db.SaveChanges() method actually executes either the INSERT or UPDATE statement against the database.  Once the record is added or updated then the labels on the screen are updated to reflect the audit dates and the drop down list is refreshed.  You should be able to run the project now and add a few records.

We still have to add the code so that when a user selects an item in the drop down list the system will retrieve the record and display the information on the web page.  This will be done in the SelectedIndexChanged event.

1.    Switch back to Design view and double click on the Users drop down list.  This should create the SelectedIndexChanged event handler.

2.    Add the following code.

if (ddlUsers.SelectedValue == "")
{
  txtFirstName.Text = "";
  txtLastName.Text = "";
  lblInserted.Text = "";
  lblUpdated.Text = "";
}
else
{
  //Get the user from the DB
  using (OrderDBContainer db = new OrderDBContainer())
  {
    int userAccountId = Convert.ToInt32(ddlUsers.SelectedValue);    
    List<UserAccount> userAccounts = (from u in db.UserAccounts
                                                       where u.Id == userAccountId
                                                       select u).ToList();
 
    if (userAccounts.Count() > 0)
    {
      UserAccount userAccount = userAccounts[0];
      txtFirstName.Text = userAccount.FirstName;
      txtLastName.Text = userAccount.LastName;
      lblInserted.Text = userAccount.AuditFields.InsertDate.ToString();
      lblUpdated.Text = userAccount.AuditFields.UpdateDate.ToString();
    }
    else
    {
      //Error: didn't find user.
      txtFirstName.Text = "";
      txtLastName.Text = "";
      lblInserted.Text = "";
      lblUpdated.Text = "";
    }
  }
}

This code uses another LINQ query to retrieve a single record from the database based on the Id of the selected item in the drop down list.  If the record is found then the userAccounts count property would be greater than one.  You can then access the object by using the indexer.  The textboxes and labels are set to the properties of the object.

If you run the project now you should be able to pull up the records that were added previously and then update them.

The last step is to add the code to the Delete button's click event to delete a record.

1.    Switch back to Design view and double click on the Delete button to generate the click event handler.

2.    Add the following code.

if (ddlUsers.SelectedItem.Value != "")            
{
  using (OrderDBContainer db = new OrderDBContainer())    
  {
    UserAccount userAccount = new UserAccount();
    userAccount.Id = Convert.ToInt32(ddlUsers.SelectedValue);
    db.UserAccounts.Attach(userAccount);
    db.ObjectStateManager.ChangeObjectState(userAccount, System.Data.EntityState.Deleted);
    db.SaveChanges();
 
    LoadUserDropDownList();
    txtFirstName.Text = "";
    txtLastName.Text = "";
    lblInserted.Text = "";
    lblUpdated.Text = "";
  }
}

This code creates an instance of a UserAccount object and sets its Id property to the value selected in the drop down list.  To delete a record you still need to attach it to the OrderDBContainer and tell the ObjectStateManager what to do with the object when the SaveChanges method is called.  Once the record is deleted the drop down list is reloaded so the user is removed and the textboxes and labels are cleared.

So there you have it, you can now create, read, update and delete records using the Entity Framework 4 and you don't need to write a single SQL statement.

In the next article I'll show you how to perform the CRUD operations using stored procedures rather than rely on the Entity Framework to generate the SQL needed to perform the operations.     Good luck on your project and happy coding.