Introduction to the Entity Framework 6 using MVC5 (ix)--using asynchronous and stored procedures for ASP.net MVC applications

using asynchronous and stored procedures for asp.net MVC applications

This is the translation of the Microsoft Official tutorial Getting started with Entity Framework 6 Code The 5 series, this is Nineth: using asynchronous and stored procedures for ASP.net MVC applications

Original: Async and Stored procedures with the Entity Framework in a asp.net MVC application

In the previous tutorial, you learned how to use the synchronous programming model to read and update data. In this tutorial you will see how to implement the asynchronous programming model. Because you can better use server resources, asynchronous code can help your application perform better.

In this tutorial you will also see how to use stored procedures to insert, update, and delete entities.

The following illustration shows the page you are going to write:

Why to use troublesome asynchronous code

A Web server has only a limited number of available threads, and in the case of high load, all threads may be in use. When this occurs, the server will not be able to process the new request until the thread is freed. In the case of synchronizing code, multiple threads may be associated, but in fact they are not doing any work but are waiting for IO to complete. With asynchronous code, when a process is waiting for IO to complete, its threads can be freed up by the server for processing other requests. Therefore, asynchronous code can use server resources more efficiently, and the server can handle more traffic without delay.

In earlier versions of the. NET, writing and testing asynchronous code is a complex, error-prone, and difficult to debug task. In. Net 4.5, writing, testing, and debugging asynchronous code becomes simpler, and you should always use asynchronous code unless you have reason to not. Asynchronous code can cost a small amount of money, but the loss of performance for low traffic is negligible. For high flow scenarios, potential performance hints are huge.

For more information about asynchronous programming, see Use the. NET 4.5 ' s async support to avoid blocking calls. Create a system controller

Create a system controller in the same way that you created the other controller before, but this time we chose to use the asynchronous controller action option.

In the following code, the highlighted section shows the difference between the asynchronous method and the synchronization method:

Public async task<actionresult> Index ()
{
    var departments = db. Departments.include (d => d.administrator);
    Return View (await departments. Tolistasync ());
}

We applied four changes to enable the Entity Framework database to execute an asynchronous query: The method uses the Async keyword, which tells the compiler to generate parts of the callback method body and automatically create the Task<actionresult> return object. The return type is changed from ActionResult to Task<actionresult>. The task<t> type indicates that a task in progress has the result of a type T. The await keyword is applied to the Web service invocation. When the compiler sees this keyword, the method is divided into two parts in the background. The first part ends with the asynchronous operation starting, and the second part is put into a callback method when the operation completes. An asynchronous version of the ToList extension method was invoked.

Why only modify departments. ToList statement rather than departments= db. Departments statement. The reason is that only queries or statements executed by the sent database can use asynchronous execution. Departments=db. The Departments statement sets a query, but the query does not execute until the ToList method is invoked. Therefore, only the ToList method is executed asynchronously.

In the details method and the HttpGet edit and Delete methods, the Find method is a method that causes the query to be sent to the database for retrieval, so the method can be executed asynchronously.

Public async task<actionresult> Details (int? id)
{
    if (id = null)
    {return
        new Httpstatuscoderesult (httpstatuscode.badrequest);
    }
    Department Department = await db. Departments.findasync (ID);
    if (department = = null)
    {return
        httpnotfound ();
    }
    Return View (department);
}

In the Create,httppost Edit and Deleteconfirmed methods, the SaveChanges method causes the command to execute, rather like db. The Department.add (Department) method simply causes the entity to modify in memory.

Public async task<actionresult> Create (Department Department)
{
    if (modelstate.isvalid)
    {
        db. Departments.add (department);
    Await DB. Savechangesasync ();
        Return redirecttoaction ("Index");
    }

Open the views\department\index.cshtml and replace the original with the following code:

@model ienumerable<contosouniversity.models.department> @{viewbag.title = "departments";}  
The code modifies the caption and moves the Dean column to the right, along with the name of the dean.

In the Create, delete, details, and edit views, change the title of the Instructorid field to "Dean", similar to the way you changed the system name field to "department" in the course view.

Use the following code in creating and editing views:

<label class= "Control-label col-md-2" for= "Instructorid" >Administrator</label>
 
Use the following code in the delete and detailed view:

<dt>
    Administrator
</dt>
 
Run the application and click on the System tab.


The program runs normally, just like any other controller. But in this controller, all SQL queries are executed asynchronously.

Some things to note when you use asynchronous programming in the Entity Framework: Asynchronous code is not thread-safe. In other words, do not use the same context instance to perform multiple operations in parallel. If you want to take advantage of the performance benefits of asynchronous code, make sure that all of the library packages that you are using (such as pagination), and any Entity Framework methods that are used in the package, such as database queries, also use asynchronous execution. stored procedures for inserts, updates, and deletes

Some developers and DBAs prefer to use stored procedures to access databases. In earlier versions of the Entity Framework, you can use the method of executing the original SQL query to retrieve data to execute the stored procedure, but you cannot use stored procedures for update operations. In Entity Framework 6, you can easily configure code-I to use stored procedures. In Dal\schoolcontext.cs, add the highlighted code to the Onmodelcreating method.

protected override void Onmodelcreating (Dbmodelbuilder modelbuilder)
{
    modelbuilder.conventions.remove< Pluralizingtablenameconvention> ();
    Modelbuilder.entity<course> ()
        . Hasmany (c => c.instructors). Withmany (i => i.courses)
        . Map (t => t.mapleftkey ("CourseID")
            . Maprightkey ("Instructorid")
            . ToTable ("Courseinstructor"));
    Modelbuilder.entity<department> (). Maptostoredprocedures ();
}
 
This code instructs the Entity Framework to use stored procedures for INSERT, UPDATE, and delete operations on department entities. In the Package management console, enter the following command:

Add-migration DEPARTMENTSP
 
Turn on the migrations\< timestamp >_departmentsp.cs, see the code in the Up method, and you will see the stored procedures for inserting, updating, and deleting:

public override void Up () {createstoredprocedure ("dbo.") Department_insert ", p => new {Name = P.string (maxlength:50), BUDG ET = P.decimal (precision:19, Scale:4, Storetype: "Money"), StartDate = P.datetime (), Ins Tructorid = P.int (),}, Body: @ "INSERT [dbo]." [Department]
              ([Name], [Budget], [StartDate], [Instructorid]) VALUES (@Name, @Budget, @StartDate, @InstructorID) DECLARE @DepartmentID int SELECT @Departme Ntid = [DepartmentID] from [dbo].
              [Department] WHERE @ @ROWCOUNT > 0 and [DepartmentID] = scope_identity () SELECT t0.
              [DepartmentID] from [dbo]. [Department] As t0 WHERE @ @ROWCOUNT > 0 and T0.

            [DepartmentID] = @DepartmentID "); Createstoredprocedure ("dbo.")
Department_update ", p => new {                DepartmentID = P.int (), Name = P.string (maxlength:50), Budget = P.decimal (Precision:19, Scale:4, Storetype: "Money"), StartDate = P.datetime (), Instructorid = P. Int (),}, Body: @ "UPDATE [dbo]."
              [Department] SET [Name] = @Name, [Budget] = @Budget, [StartDate] = @StartDate, [Instructorid] = @InstructorID WHERE ([Dep

            Artmentid] = @DepartmentID) "); Createstoredprocedure ("dbo.") 
            Department_delete ", p => new {DepartmentID = P.int (),}, Body: @ "DELETE [dbo]."
              [Department]

WHERE ([DepartmentID] = @DepartmentID) "); }
In the Package Manager console, enter the following command:
Update-database
Run the application under the Debug model, click the System tab, and then click Create. Enter related data for a new system and click Create.
View the log in the Output window in vs.


Code one creates a stored procedure using the default name. If you are using an existing database, you may need to customize the name of the stored procedure, and for information about how to do so, see the Entity Framework Code insert/update/delete Stored procedures.

If you want to customize the stored procedure, you can edit the up method in the scaffolding code in the migration to create the stored procedure. When you use this method, your changes are made automatically when you apply the migration or when you deploy to a production environment.

If you want to modify a stored procedure that has been created in a previous migration, you can use the add-migration command to generate a blank migration, and then manually write code to call the Alterstoredprocedure method. deploy to Windows Azure

Slightly... Summary

In this tutorial, you see how to improve server efficiency. Insert, update, and delete operations by writing asynchronous code execution and using stored procedures. In the following tutorial, you will see how to prevent data loss when multiple users try to edit the same record. Author Information

Tom Dykstra-Tom Dykstra is a senior programmer and writer for the Microsoft Web platform and tools team.