Entity Framework 4 - Part 3 - Execute Stored Procedures Using the Entity Framework 4

This article demonstrates the usage of the Entity Framework 4 to execute stored procedures to create, read, update, and delete records in the database created in Part 1. After a short introduction, he examines the creation of the stored procedures and Web Forms, addition of the Stored Procedures to the Entity Model including adding, updating, and deleting records. He also shows how to retrieve a single record from the database.

Introduction

This article will demonstrate how to use the Entity Framework 4 to execute stored procedures that 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, and creates the same web page as in Part 2. The web page functions the same except that it executes stored procedures rather than rely on the Entity Framework to create the dynamic SQL to access the database. You'll need to download Visual Studio 2010 RC 1 from Microsoft's site in order for the sample code to work. 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 image. 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.

Step 1: Create the Stored Procedures

The first step is to create the stored procedures in the database. The first stored procedure is used to select all the records from the UserAccounts table. CREATE PROCEDURE dbo.UserAccounts_SelectAll AS SET NOCOUNT ON SELECT Id, FirstName, LastName, AuditFields_InsertDate, AuditFields_UpdateDate FROM UserAccounts RETURN The next procedure will select a single record from the UserAccounts table using the Id in the where clause. CREATE PROCEDURE dbo.UserAccounts_SelectById ( @Id int ) AS SET NOCOUNT ON SELECT Id, FirstName, LastName, AuditFields_InsertDate, AuditFields_UpdateDate FROM UserAccounts WHERE Id = @Id RETURN The third procedure inserts a record into the UserAccounts table. This takes all the fields as parameters and returns the Id of the inserted record. CREATE PROCEDURE dbo.UserAccounts_Insert ( @FirstName nvarchar(50), @LastName nvarchar(50), @AuditFields_InsertDate datetime, @AuditFields_UpdateDate datetime ) AS INSERT INTO UserAccounts (FirstName, LastName, AuditFields_InsertDate, AuditFields_UpdateDate) VALUES (@FirstName, @LastName, @AuditFields_InsertDate, @AuditFields_UpdateDate) SELECT SCOPE_IDENTITY() AS Id The fourth procedure updates a record in the UserAccounts table. CREATE PROCEDURE dbo.UserAccounts_Update ( @Id int, @FirstName nvarchar(50), @LastName nvarchar(50), @AuditFields_UpdateDate datetime ) AS SET NOCOUNT ON UPDATE UserAccounts SET FirstName = @FirstName, LastName = @LastName, AuditFields_UpdateDate = @AuditFields_UpdateDate WHERE Id = @Id RETURN The fifth procedure deletes a record from the UserAccounts table. CREATE PROCEDURE dbo.UserAccounts_Delete ( @Id int ) AS SET NOCOUNT ON DELETE FROM UserAccounts WHERE Id = @Id RETURN You should create all of these stored procedures in the OrderSystem database before moving to step 2.

Step 2: Add the Stored Procedures to the Entity Model

Open the OrderDB.edmx file in you project. This assumes you created a web application as described in Part 1 of this series. When you open the OrderDB.edmx file the entities will appear in the designer and the Model Browser window will show you the database with the Entities, Complex Types, and Associations listed in a folder structure. Right click on the OrderDB.edmx node in the Model Browser and select Update Model From Database… from the pop-up menu. The Update Wizard should appear and it should recognize the five stored procedures added in step 1. Check the box next to the Stored Procedure node to check all the stored procedures and then click the Finish button. The stored procedures will now appear under the Stored Procedures in the Model Browser. Right click on the UserAccounts_SelectAll stored procedure in the Model Browser and select Add Function Import… from the pop-up menu. The Add Function Import dialog should appear. This allows you to add a method to the OrdersDBContainer class that will execute this stored procedure. You need to choose the return type when the stored procedure is executed. It would be nice if you could return a list of UserAccount entities but complex type properties aren't supported by the framework. To get around this issue you need to create a new complex type that represents the return set from the stored procedure. Since the SelectAll and SelectById stored procedures both return the same fields we'll create a single complex type that both return. Click the Get Column Information button. This will determine the fields that are returned by the stored procedure. Once the fields are listed, click on the Create New Complex Type button. When you click the button the Complex option will automatically be selected and the name of the complex type that will be created is added to the drop down list next to the Complex type option. By default the name of the complex type is the name of the stored procedure with the word Result appended to it. Since we'll share this complex type with two stored procedures we'll make the name generic. Change the name to UserAccounts_Select_Result. Now click the OK button. You should see the UserAccounts_SelectAll function under the Function Imports folder in the Model Browser. You should also see the new UserAccounts_Select_Result complex type under the Complex Types node. Now right click on the UserAccounts_SelectById procedure in the Model Browser. Select Add Function Import again and select Complex type for the return type and select the UserAccounts_Select_Result complex type. Click the OK button. Now you need to associate the Insert, Update, and Delete stored procedures with the UserAccount Entity. To do this you should right click on the UserAccount entity in the designer. Select Stored Procedure Mapping from the pop-up menu. You should see Mapping Details window. Click on

Step 3: Create the Web Form

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 UsersSP.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.

Step 4: Selecting Records to Load a Drop Down List

[ Back To Top ] 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_SelectAll() 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", "")); } } Notice the from clause in this LINQ query. It is using the db.UserAccounts_SelectAll method on the OrderDBContainer. This will execute the stored procedure. The drop down list's DataSource source property is set to the results of the LINQ query. 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 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.

Step 5: Adding and Updating Records

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 the update stored procedure. 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. If you run SQL Profiler you can see that the insert stored procedure being called. exec [dbo].[UserAccounts_Insert] @FirstName=N'Vince', @LastName=N'Varallo', @AuditFields_InsertDate='2010-03-08 18:14:42.4564241', @AuditFields_UpdateDate='2010-03-08 18:14:42.4564241'

Step 6: Retrieving a Single Record

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); var userAccounts = from u in db.UserAccounts_SelectById(userAccountId) select u; txtFirstName.Text = ""; txtLastName.Text = ""; lblInserted.Text = ""; lblUpdated.Text = ""; foreach (UserAccounts_Select_Result userAccount in userAccounts) { txtFirstName.Text = userAccount.FirstName; txtLastName.Text = userAccount.LastName; lblInserted.Text = userAccount.AuditFields_InsertDate.ToString(); lblUpdated.Text = userAccount.AuditFields_UpdateDate.ToString(); } } } This code calls the UserAccounts_SelectById method which in turn executes the stored procedure. The textboxes and labels are set to the properties of the object that was returned. If you run the project now you should be able to pull up the records that were added previously and then update them.

Step 7: Deleting Records

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. using (OrderDBContainer db = new OrderDBContainer()) { if (ddlUsers.SelectedItem.Value != "") { 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.

Summary

Part 2 of the series explained how to insert, update, delete, and select records using the Entity Framework and now this article explained how to use stored procedures and the Entity Framework to perform the same operations. In the next article I'll explore a pattern for using the Entity Framework in a 3 layered environment.

SharePoint Object Model - A beginner Overview

Introduction

SharePoint provides a solid framework for the .Net developers to write code against and extend sharepoint functionality. As sharepoint is done on ASP.NET 2.0 and have a power code base of .Net Class libraries, a lot of developers can now make use of them and create excellent applications utilizing sharepoint features and libraries. In this tutorial i will explain on how to open sharepoint site using Visual Studio and then connect with a document library inside sharepoint and get the creation date of the document library. This feature is not given in the sharepoint out of the box so we need to do custom development to find out this information.

Using the code

Lets get directly to the code. The first thing we need to do is to setup our development environment. This is a bit tricky. We can do couple of things.

• Setup of development tools where sharepoint portal is installed
• Create virtual pc of sharepoint and install sharepoint on that
• copy sharepoint dlls to your development pc and reference those dlls in the visual studio but keep in mind that debugging will be very difficult

OK now in my scenario i used a virtual pc and sharepoint VHD boot it up and opened the visual studio. I referenced Microsoft.SharePoint dll and referenced it inside the code behind as well.

Collapse

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SharePoint;

I dropped a couple of drop down lists on the form and a text box where the user will give its portal name. Once typing portal address the user will click connect. This event will populate the drop down with all the sites created on the portal.

Collapse

private void cmdConnect_Click(object sender, EventArgs e)
       {
           cboLists.Items.Clear();
           SPSite mysite = new SPSite(txtURL.Text.Trim());
           foreach (SPWeb myweb in mysite.AllWebs)
           {
               cboSites.Items.Add(myweb.Title);
           }
       }

As you can see from above, i have used SPSite class to open a site and passed the url of the site as constructor. Once opened this site class has a web collection class. I looped through all the webs created and listed them in the drop down.

Now the user will select the site where the document library exists. On the selected index changed, i have populated next drop down with all the lists and libraries.

Collapse

        private void cboSites_SelectedIndexChanged(object sender, EventArgs e)
       {
           mysite2 = new SPSite(txtURL.Text.Trim());
           myweb2 = mysite2.AllWebs[cboSites.SelectedItem.ToString()];
           foreach (SPList lst in myweb2.Lists)
           {
               cboLists.Items.Add(lst.Title);
           }          
       }

What happened just now is the most intresting part. Here we opened the site, got the web from the user selection of the drop down and put it in SPWeb class. SPWeb class now contains all the document libraries and lists you have in your site. As you can see we are looping through the list collection in the spweb.We are adding all the lists and libraries in the drop down as we loop.

Now once the user selects the library, he will see the creation date of the library in the label control.

Collapse

private void cboLists_SelectedIndexChanged(object sender, EventArgs e)
       {
           SPList mylist = myweb2.Lists[cboLists.SelectedItem.ToString()];
           DateTime datecreated = mylist.Created;
           lblDate.Text = datecreated.ToShortDateString();
       }

As can be seen, we got the list in SPList class and utilized its created property to find out the list creation date.

Points of Interest

The main point of interest part here is to see how to connect to a site, open its web and iterate through its list and libraries. Then to see how to get the properties of the lists and libraries. SPSite, SPWeb and SPList are the major classes we used to achieve our goal. In my next writing i wil explain how to go beyond and access individual list items.

Hence the object models looks like

SPSite

SPWeb

SPList

SPListItem

Entity Framework 4 - Part 2: Perform CRUD Operations Using the Entity Framework 4

This article demonstrates the usage of the Entity Framework 4 to create, read, update, and delete records in the database which was created in Part 1 of this series. After a short introduction, he discusses the various step involved in the modification of the database, creation of a web form, the selection records to load a drop down list, and the adding, updating, deletion and retrieval of records from the database with the help of relevant source code and screen shots.

Introduction

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. 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.

Step 1: Modify the Database Generated in Part 1

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.

Step 2: Create the Web Form

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. &lt;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

Step 3: Selecting Records to Load a Drop Down List

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

Step 4: Adding and Updating Records

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.

Step 5: Retrieving a Single Record

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 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.

Step 6: Deleting Records

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.

Summary

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.