Tuesday, April 26, 2011

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.

0

0 comments:

Post a Comment

Popular Posts

  • Data Profiling Task in SSIS
    What do we mean by Data Profiling task in SSIS? Data Profiling task  is a control flow component and is used to  analyze data  of a tabl...
  • SharePoint 2007 - Simple Task Dashboard
    example of creating a simple Task Dashboard. This is a pretty basic example of using all OOTB (out of the box) features and combining them...
  • (no title)
    COURSE VIDEOS: ANALYZING BIG DATA WITH TWITTER

Recent Posts

Unordered List

Text Widget

Blog Archive