Entity Framework 4.0-1: Call the Stored Procedure

This article describes how to call a stored procedure to perform crud operations on data in the SQL Server database. The database used in this article is still the first article in this series, "using model-first ".
The web page used is created in the second article "using Entity Framework for crud operations, you can click [Code] to download the link to download the project file. Use vs2010 to open it.

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_SelectAllAS      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 choose 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 selectall
The fields returned by the selectbyid stored procedure are the same. 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 Create new complex
Type button, complex is automatically selected
Option. The names of complex types are also added to the following boxes on the right. By default, the name of the complex type is stored procedure name plus "result ". Because I will use this complex type in the return values of multiple stored procedures, we recommend that you use a common name. Here, I change the name to "useraccounts_select_result" and click OK.

 

 

Then you will see the function imports in the Model Browser
A useraccounts_selectall method is added to the folder, and a useraccounts_select_result type is added to the complex type node.

 

 

Right-click the useraccounts_selectbyid stored procedure in the Model Browser and select Add function.
Import, 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, the detailed ing window appears.

 

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. In result
<Add result binding> input ID under the column bindings node. The returned ID is the "select
Scope_identity () as ID ". The following is the result after binding is completed:

 

 

Map A new stored procedure to <select update
Function> replace it 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 to <Select Delete
Function> replace it 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.

 

Datatextfield attribute is set to name, datavaluefield
Set to id. These are all created in the LINQ query. After the configuration is complete, it is bound. When binding, you can call the database query operation. Finally, drop down
"Crate new user." is added to the List to differentiate update and addition 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. Finally, update drop.
Down
List and select the useraccout. Here dB. savechanges () is actually executed in the database to add or update the statement. When you want to add data to a database, open SQL
Profiler 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 drop
Get down the data source of list!