ASP. NET 4.0 and Entity Framework 4-Article 3-using Entity Framework to call stored procedures

Summary

This article describes how to call a stored procedure to perform CRUD operations on data in the SQL Server database. The databases used in this article are still created in the First article "Create a database using Model-First Development" in this series, the Web page used is created in the second article "using Entity Framework for CRUD operations". You can click [Code] to download the project file from the link and open it with VS2010.

Step 1. Create a stored procedure

First, you need to create a stored procedure. The first stored procedure is used to query the UserAccount records. The Code is as follows:

CREATE PROCEDURE dbo.UserAccounts_SelectAll     AS      SET NOCOUNT ON       SELECT Id, FirstName, LastName, AuditFields_InsertDate,              AuditFields_UpdateDate        FROM UserAccounts       RETURN

The following stored procedure uses Id to select a record in the UserAccount table. The Code is as follows:

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 stored procedure is to insert a record into the UserAccounts table. The Code is as follows:

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 stored procedure is to update the data in the UserAccount table. The Code is as follows:

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 last stored procedure is used to delete records in the UserAccount table. The Code is as follows:

CREATE PROCEDURE dbo.UserAccounts_Delete(      @Id int     )     AS      SET NOCOUNT ON       DELETE         FROM UserAccounts       WHERE Id = @Id       RETURN

Before proceeding to the next step, you must create the above stored procedures in the OrderSystem database.

Step 2: Add a stored procedure to an object

Open the OrderDB. edmx file in the project. The designer appears, and the database entity, complex attributes, and relationships are displayed in the Model Browser. Right-click OrderDB. edmx and select Update Mode From DataBase. The Update Wizard is displayed. The five stored procedures created earlier are displayed.

Expand the Stored Procedures node, select all Stored Procedures, and click Finish. In this case, the Model Browser will have five more stored procedure objects.

Right-click the UserAccounts_SelectAll stored procedure in the Model Browser and select Add Function Import. Then, you can Add a method to the OrdersDBContainer class to execute the UserAccounts_SelectAll stored procedure.

Select the type of the returned value after the stored procedure is executed. When we want to return a list of useraccounts, but the Framework does not provide this type, It is very convenient to use this method to create. We can first create a complex type to indicate the type of the stored procedure return value. Because the SelectAll and SelectById stored procedures return the same fields, you only need to create a complex type.

Click Get Column Information to check the fields returned by the stored procedure. After checking the returned fields, click the Create New Complex Type button. The Complex option is automatically selected, and the names of Complex types are added to the following boxes on the right. By default, the name of the complex type is stored procedure name plus "Result ". Because the complex type will be used in the return values of multiple stored procedures, we recommend that you use a common name. Here, you should change the name to "UserAccounts_Select_Result" and click OK.

At this time, you will see a UserAccounts_SelectAll method in the Function Imports folder of the model browser, and a UserAccounts_Select_Result type is added under the complex type node.

Right-click the UserAccounts_SelectById stored procedure in the Model Browser and select Add Function Import. Then, select the UserAccounts_Select_Result type as the return value type of the stored procedure, and click OK.

The following is a Stored Procedure for using insert, update, and delete In UserAccount. Right-click the UserAccount entity in the designer and select Stored Procedure Mapping. A detailed ing window is displayed.

Click <Select Insert Function> In the first row and Select UserAccounts_Insert from the drop-down list. The parameter list is displayed in the grid. We must map object attributes to stored procedure parameters. Because the stored procedure returns the Id of the newly created record, you must select the attribute to which the Id is assigned. <Add Result Binding> input Id under the Result Column Bindings node. The returned Id is "SELECT SCOPE_IDENTITY () AS Id" in the stored procedure. The following is the Result after Binding is completed:

Map A new stored procedure and replace <Select Update Function> with UserAccounts_Update. Other attributes are automatically mapped. Only AuditFields_UpdateDate must be automatically mapped to AuditFields. UpdateDate.

The last step is to map the Delete stored procedure and replace <Select Delete Function> with the UserAccounts_Delete stored procedure. The Id parameter is mapped to the Id attribute of the object.

Do not forget to save the above operation results. OrdersDBContainer can now use those stored procedures to perform CRUD operations on UserAccount records.

Step 3: create a Web form

Create a form in the program to manage UserAccount data.

1. Right-click the project file and choose Add-> New Item ..

2. Select the Web Form template, change the name to UsersSP. aspx, and click "Add ".

3. Add the following code between the div of UserSP. aspx:

<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" />

Here, the table is used to make a simple layout for the form. Go to the Design view and you will see the form as follows:

Step 4: load data to the Drop Down List

To load a page, load the UserAccount Name and Id data to the Drop Down List. Load more detailed information when selecting a specific item.

1. Double-click Degsin view (F7) and add the Page_Load event in the background code.

2. The code for processing the Page_Load event is as follows:

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

3. The code for the LoadUserDropDownList method in the Page_Load method is as follows:

private void LoadUserDropDownList()        {           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", ""));            }        }

Pay attention to the from Statement of Linq, which calls the UserAccounts_SelectAll method of OrderDBContainer. This method will execute the stored procedure.

The DataTextField attribute is set to Name and the DataValueField is set to Id, which are created in the Linq query. After the configuration is complete, it is bound. When binding, you can call the database query operation. Finally, add "Crate New User." To the Drop Down List to differentiate between update and add operations.

Currently, there is no data in the database, and the Drop Down List contains only "Crete New User.

Step 5: Add and update data

The following describes how to add and update data in a table.

1. Go to the design view and double-click the Save button to create the click event for this button.

2. The code for handling click events is as follows:

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;            }        }

The code first creates the OrderDBContainer object, then creates the UserAccount object, and fills in the attributes of the UserAccount object with the input value. The update date uses the current system time, and then determines whether the update operation or add operation is performed. The last step is to update the value of the Drop Down List and select the UserAccout in the previous operation. Here db. SaveChanges () is actually executed in the database to add or update the statement. When you want to add data to the database, open SQL Profiler and you will see that the Insert stored procedure is called.

exec [dbo].[UserAccounts_Insert] @FirstName=N'Lloyd', @LastName=N'Sheng', @AuditFields_InsertDate='2010-04-26 18:14:42.4564241', @AuditFields_UpdateDate='2010-04-26 18:14:42.4564241'

Step 6: query data

The following describes how to display detailed information when you select a Drop Down List item.

1. Double-click the Drop Down List in the view designer to create the SelectedIndexChanged method for the Drop Down List.

2. The code for compiling the SelectedIndexChanged method is as follows:

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();    }  }}

Based on the Id selected in the Drop Down List, the Code calls the UserAccounts_SelectById method to query and display a piece of data from the database.

Step 7: delete data

The last step is to delete data. 1. Go to the view designer and double-click "Delete. 2. Add the following code:

if 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 = "";  }}

The code first creates a UserAccount object, sets its Id as the Id of the selected item, attaches the UserAccount to the UserAccount collection, and sets its status to delete. Call the SaveChanges operation to delete the data and refresh the data source in the Drop Down List!

Summary

The second article tells you how to use Entity Framework for CRUD operations. This article describes how to use stored procedures and Entity Framework for CRUD operations.

In the next article, I will show you how to use the Entity Framework in a three-tier structure.

 

Code: http://files.cnblogs.com/lloydsheng/OrderSystem3.zip

Permanent link to this article: http://lloydsheng.com/2010/04/aspnet40-entityframework4-execute-stored-procedures-using-entity-framework4.html