MVC5 Entity Framework Learning Elastic connections and command interception

So far, applications have been running on local IIS Express. If you want someone to access your application over the Internet, you must deploy it to a Web server and deploy the database to the database server

This article will teach you how to use the very valuable two features of the entity Framework 6 when deploying your application to a cloud environment: elastic connections (Automatic retry of instantaneous errors) and command interception (captures all SQL query statements sent to the database and logs them to the log).

1. Enable Elastic connections

When you deploy an application to Windows Azure, the appropriate database department should also be deployed to the Windows Azure SQL database. Instantaneous connection errors are more likely to occur when connecting a cloud database service than connecting the Web server and the database directly in the same datacenter, even if the cloud Web server and the Cloud database server are in the same datacenter, network connectivity issues such as load balancing still occur.

Typically, cloud servers are shared by users, which means that your application's responsiveness can be affected by other users, and your access to the database can be limited by limiting access to the database if you are accessing it over a service level agreement. Then the database service throws an exception.

When you access the cloud server most of the connectivity problems are instantaneous and they will try to solve themselves in a short time, so when you try to perform database operations, the error is usually instantaneous, when you wait a while to retry, the operation may be executed successfully. You can use automatic retries to resolve transient errors to improve the user experience. The connection recovery feature of the Entity Framework 6 automatically retries the failed SQL query.

For a particular database service, you must configure the elastic connection function correctly:

• You must know which exceptions are likely to be instantaneous, and you should retry those errors caused by network connections, not those caused by bugs in the program.
• You must wait for the appropriate time before retrying the failed operation, which should wait longer when retrying the bulk operation
• You must set an appropriate number of retries, and you may have multiple retries in an online application

You can manually configure these settings for any database environment supported by the entity Framework provider, but it is sufficient for your application to use the default configuration of Windows Azure SQL database.

To enable connection recovery, you only need to create a new class in your assembly and inherit the Dbconfiguration class, and configure the execution policy of the SQL database in that class, which is the retry policy in EF.

In the Dal folder, add a class that is named SchoolConfiguration.cs and replace it with the following code

Using system.data.entity;using system.data.entity.sqlserver;namespace contosouniversity.dal{public    class Schoolconfiguration:dbconfiguration    {public        schoolconfiguration ()        {            setexecutionstrategy (" System.Data.SqlClient ", () = new Sqlazureexecutionstrategy ());}}    

The Entity framework automatically runs classes derived from Dbconfiguration, and you can use the Dbconfiguration class to configure related jobs, otherwise it can only be configured in Web. config.

Open StudentController.cs, add System.Data.Entity.Infrastructure namespace

Using System.Data.Entity.Infrastructure;

Replace DataException with retrylimitexceededexception exception

catch (retrylimitexceededexception/* dex */) {    //log the error (uncomment DEX variable name and add a line here to WRI Te a log.    Modelstate.addmodelerror ("", "unable to save changes. Try again, and if the problem persists see your system administrator. ");

Instead of using dataexception to try to catch exceptions that could be transient errors and send a friendly retry message to the user, the retry policy is now enabled, and the error that continues to fail multiple retries will trigger the retrylimitexceededexception exception.

2. Enable command interceptionNow that you have enabled the retry policy, how do you test to verify that it works as expected? Forcing a transient error is not easy, especially if your app is running on local IIS, even if you are using unit tests. To test the elastic connection feature, you need to intercept the Entity Framework's query command sent to the SQL database and replace the SQL database with the exception type, which is the transient error.

You can also follow the best practices (log the latency and success or failure of all calls to external services) such as database services to implement query interception, EF6 provides a dedicated log interface (dedicated Logging API) to log logs. In this article, you will learn how to use the block functionality of the Entity Framework, including logging and simulating transient errors.

Create a logging interface and classThe best way to log records is by using an interface instead of calling the hard-coded method of the System.Diagnostice.Trace or logging class. This makes it easier to change the logging mechanism later on, so in this article we will create an interface and implement it.

Create a new folder and name it logging

In the logging folder, create a new class and name it ILogger.cs, replacing it with the following code

<pre name= "code" class= "CSharp" >using system;using system.collections.generic;using system.linq;using        System.web;namespace contosouniversity.logging{public interface ILogger {void information (string message);        void information (string fmt, params object[] vars);        void information (Exception Exception, String fmt, params object[] vars);        void Warning (String message);        void Warning (String fmt, params object[] vars);        void Warning (Exception Exception, String fmt, params object[] vars);        void Error (String message);        void Error (String fmt, params object[] vars);        void Error (Exception Exception, String fmt, params object[] vars);        void Traceapi (String componentname, String method, TimeSpan TimeSpan);        void Traceapi (String componentname, String method, TimeSpan TimeSpan, String properties);    void Traceapi (String componentname, String method, TimeSpan TimeSpan, String fmt, params object[] vars); }}

The interface provides three trace levels to indicate the relative importance of the log, one of which is designed to provide deferred information for external service calls such as database queries. The log method provides overloads with exception parameters to ensure that exception information, including stack trace information and internal exceptions, can be reliably logged by the class that implements the interface.

The Traceapi method allows you to track the delay time of each invocation of an external service (such as a SQL database)

In the logging folder, create a new class and name it Logger.cs, replacing it with the following code

Using system;using system.collections.generic;using system.diagnostics;using system.linq;using System.Text;using System.web;namespace contosouniversity.logging{public class Logger:ilogger {public void information (Strin        G message) {trace.traceinformation (message);        } public void information (string fmt, params object[] vars) {trace.traceinformation (FMT, VARs); } public void information (Exception Exception, String fmt, params object[] vars) {trace.t        Raceinformation (Formatexceptionmessage (Exception, FMT, VARs));        public void Warning (String message) {trace.tracewarning (message);        } public void Warning (string fmt, params object[] vars) {trace.tracewarning (FMT, VARs); } public void Warning (Exception Exception, String fmt, params object[] vars) {trace.tracewarning (Formatexceptionmessage (Exception, FMT, VARs));        The public void Error (String message) {trace.traceerror (message);        } public void Error (string fmt, params object[] vars) {trace.traceerror (FMT, VARs); ' public void Error ' (Exception Exception, String fmt, params object[] vars) {Trace.traceerror (for        Matexceptionmessage (Exception, FMT, VARs)); The public void Traceapi (string componentname, String method, TimeSpan TimeSpan) {Traceapi (Compone        Ntname, method, TimeSpan, "");        public void Traceapi (string componentname, String method, TimeSpan TimeSpan, String fmt, params object[] vars) {Traceapi (ComponentName, Method, TimeSpan, String.)        Format (FMT, VARs));            public void Traceapi (string componentname, String method, TimeSpan TimeSpan, string properties) { String message = String.Concat ("Component:", ComponentName, "; Method: ", method,"; Timespan: ", TimeSpan. ToString (), ";            Properties: ", properties);        Trace.traceinformation (message);            } private static string Formatexceptionmessage (Exception Exception, String fmt, object[] vars) { Simple exception Formatting:for A more comprehensive version see//Http://code.msdn.microsoft.com/windows            AZURE/FIX-IT-APP-FOR-BUILDING-CDD80DF4 var sb = new StringBuilder (); Sb. Append (String.            Format (FMT, VARs)); Sb.            Append ("Exception:"); Sb. Append (Exception.            ToString ()); Return SB.        ToString (); }    }}

The above implementation uses system.diagnostics for tracking, which is a built-in feature of. NET that makes it easier to build and use tracking information. You can use a variety of listeners for System.Diagnostics to track and log logs, such as writing them to blob storage in Windows Azure.

In a production environment you might want to consider tracking packages instead of System.diagnostics,ilogger interfaces that can be relatively easy to switch to different tracking mechanisms depending on your needs.

Creating Interceptor Classes

Next you will create two classes, one for simulating transient errors and the other for logging, and these two classes are called every time the entity Framework sends a query command to the database. These interceptor classes must derive from the Dbcommandinterceptor class, and the methods you override need to be called automatically when the query command executes, in which you can examine or record the query commands that will be sent to the database, and you can modify them before the query commands are sent to the database. They do not even send them to the database for querying and return results directly to the entity Framework.

In order to create an interceptor class that can record every query command sent to the database, you need to make a new class in the Dal folder and name it SchoolInterceptorLogging.cs, replacing it with the following code

Using contosouniversity.logging;using system;using system.collections.generic;using System.Data.Common;using System.data.entity.infrastructure.interception;using system.diagnostics;using System.Linq;using System.Web; Namespace contosouniversity.dal{public class Schoolinterceptorlogging:dbcommandinterceptor {private Ilogg        ER _logger = new logger ();        Private readonly Stopwatch _stopwatch = new Stopwatch (); public override void Scalarexecuting (DbCommand command, dbcommandinterceptioncontext<object> Interceptioncontext) {base.            Scalarexecuting (command, Interceptioncontext); _stopwatch.        Restart (); } public override void scalarexecuted (DbCommand command, dbcommandinterceptioncontext<object> Interceptioncon Text) {_stopwatch.            Stop (); if (interceptioncontext.exception! = null) {_logger. Error (interceptioncontext.exception, "Error executing command: {0}", CommanD.commandtext); } else {_logger. Traceapi ("SQL Database", "schoolinterceptor.scalarexecuted", _stopwatch.            Elapsed, "Command: {0}:", Command.commandtext); } base.        scalarexecuted (command, Interceptioncontext); } public override void Nonqueryexecuting (DbCommand command, dbcommandinterceptioncontext<int> Interceptioncon Text) {base.            Nonqueryexecuting (command, Interceptioncontext); _stopwatch.        Restart (); } public override void nonqueryexecuted (DbCommand command, dbcommandinterceptioncontext<int> Interceptioncont EXT) {_stopwatch.            Stop (); if (interceptioncontext.exception! = null) {_logger.            Error (interceptioncontext.exception, "Error executing command: {0}", Command.commandtext); } else {_logger. Traceapi ("SQL Database", "schoolinterceptor.nonqueryexecuted", _stopwatch.            Elapsed, "Command: {0}:", Command.commandtext); } base.        nonqueryexecuted (command, Interceptioncontext); } public override void Readerexecuting (DbCommand command, dbcommandinterceptioncontext<dbdatareader> intercep Tioncontext) {base.            Readerexecuting (command, Interceptioncontext); _stopwatch.        Restart (); } public override void readerexecuted (DbCommand command, dbcommandinterceptioncontext<dbdatareader> Intercept Ioncontext) {_stopwatch.            Stop (); if (interceptioncontext.exception! = null) {_logger.            Error (interceptioncontext.exception, "Error executing command: {0}", Command.commandtext); } else {_logger. Traceapi ("SQL Database", "schoolinterceptor.readerexecuted", _stopwatch.            Elapsed, "Command: {0}:", Command.commandtext); } base. readerexecuted (Command, InterceptionconText); }    }}

For a successful query command, the above code writes the delay information to the log, and for the exception it creates an error log.

Create a new class in the Dal folder and name it SchoolInterceptorTransientErrors.cs, which generates a dummy transient error when you enter "Throw" in the search box, replacing the framework's auto-generated code with the following code

Using contosouniversity.logging;using system;using system.collections.generic;using System.Data.Common;using System.data.entity.infrastructure.interception;using system.data.sqlclient;using System.Linq;using System.reflection;using system.web;namespace contosouniversity.dal{public class Schoolinterceptortransienterrors:        dbcommandinterceptor {private int _counter = 0;        Private ILogger _logger = new Logger (); public override void Readerexecuting (DbCommand command, dbcommandinterceptioncontext<dbdatareader>            Interceptioncontext) {bool throwtransienterrors = false; if (command. Parameters.count > 0 && command. Parameters[0].                value.tostring () = = "Throw") {throwtransienterrors = true; Command. Parameters[0].                Value = "an"; Command. PARAMETERS[1].            Value = "an"; } if (Throwtransienterrors && _counter < 4) {_loggeR.information ("Returning transient error for command: {0}", Command.commandtext);                _counter++;            Interceptioncontext.exception = Createdummysqlexception (); }} private SqlException createdummysqlexception () {///The instance of SQL Server you att            Empted to connect to does not support encryption var sqlerrornumber = 20; var sqlerrorctor = typeof (SqlError). GetConstructors (BindingFlags.Instance | BindingFlags.NonPublic). Where (c = c.getparameters (). Count () = = 7).            Single ();            var sqlError = Sqlerrorctor.invoke (new object[] {sqlerrornumber, (byte) 0, (byte) 0, "", "", "", 1});            var errorcollection = Activator.CreateInstance (typeof (Sqlerrorcollection), true); var addmethod = typeof (Sqlerrorcollection). GetMethod ("Add", BindingFlags.Instance |            BindingFlags.NonPublic);            Addmethod.invoke (ErrorCollection, new[] {sqlError}); var sqlexceptionctor = typeof(SqlException). GetConstructors (BindingFlags.Instance | BindingFlags.NonPublic). Where (c = c.getparameters (). Count () = = 4).            Single (); var sqlException = (sqlException) sqlexceptionctor.invoke (new object[] {"Dummy", errorcollection, NULL, Guid.NewGuid ()}            );        return sqlException; }    }}

The above code simply overrides the Readerexecuting method, which can return multiple rows of data through a query command. If you want to see elastic connectivity for other query types, you need to override the Nonqueryexecuting and Scalarexecuting methods as you did in the log blocker.

When you run the project and enter "Throw" as the search condition on the student page, the method creates a virtual SQL database exception with an error codenamed 20. The current accepted error codes for instantaneous connection errors are 64, 233, 10053, 10054, 10060, 10928, 10929, 40197, 40501, 40613, but in the new version of the database The error code may be inconsistent with the above.

The above code returns an exception to the Entity Framework instead of executing the query and returning the result of the query, which occurs 4 times, then the program resumes normal and the query command is sent to the database.

Because the log records all events, you can see that the Entity Framework has performed four queries before the final success, and the only difference you can feel in the application is that the time it takes to render the page becomes longer.

The retry count for the Entity framework is configurable, and the code above specifies that the retry count is 4 times because this is the default value for the SQL database execution policy setting. If you modify the execution policy, you must modify the above code to specify how many transient errors should be generated. You can also modify the code to produce more exceptions so that the Entity Framework throws a retrylimitexceededexception exception.

The value you enter in the search box is saved in the command. Parameters[0] and command. PARAMETERS[1] (one for storing the last name, one for storing the name). When "Throw" is entered, the value is replaced with "an" and the corresponding student information is queried.

It is only easy to test the elastic connection function by modifying the input parameters. You can also generate transient connection errors for query or update commands by using your own code.

Open Global.asax, add namespace

Using contosouniversity.dal;using System.Data.Entity.Infrastructure.Interception;

Add the following code in the Application_Start method

protected void Application_Start () {    arearegistration.registerallareas ();    Filterconfig.registerglobalfilters (globalfilters.filters);    Routeconfig.registerroutes (routetable.routes);    Bundleconfig.registerbundles (bundletable.bundles);    Dbinterception.add (New Schoolinterceptortransienterrors ());    Dbinterception.add (New schoolinterceptorlogging ());}

The code above will enable interceptors when the Entity Framework sends query commands to the database, noting that you have created different interceptor classes for transient error impersonation and logging, and you can enable or disable them individually.

You can add interceptors anywhere in the code using the Dbinterception.add method, and not necessarily in the Application_Start method. Another alternative is to put the above code in the Dbconfiguration class you created earlier and configure the execution policy.

public class schoolconfiguration:dbconfiguration{public    schoolconfiguration ()    {        setexecutionstrategy ("System.Data.SqlClient", () = new Sqlazureexecutionstrategy ());        Dbinterception.add (New Schoolinterceptortransienterrors ());        Dbinterception.add (New schoolinterceptorlogging ());}    }

No matter where you put this code, note that for the same interceptor, the Dbinterception.add method can only have one, otherwise it will produce multiple interceptor instances. For example, if you add two logging interceptors, each SQL query statement produces two identical log records.

Interceptors are executed in the order in which they are registered (the order in which the Dbinterception.add method is called). Depending on what you want to do, the order of execution of the interceptor may be important. For example, an interceptor might modify the SQL command obtained from the CommandText property, and the other interceptor gets the modified SQL command instead of the original SQL command.

You have completed the code that simulates the transient connection error and can throw a transient connection error by entering a specific value in the program. As an alternative, you can write code that always produces instantaneous connection errors instead of checking for specific parameters to throw instantaneous connection errors, and then adding interceptors when you want to generate instantaneous connection errors. If you do this, add the interceptor after the database initialization is complete. In other words, at least one database operation, such as querying the entity set, should be performed before the instantaneous connection error begins. The Entity framework performs multiple query operations during database initialization, but they are not executed in the same transaction, so errors that occur during initialization can cause the database context to enter an inconsistent state.

3. Test logging and elastic connection capabilities

Press F5 to run, click to select Students Item Card

Open the Visual Studio Output window to view the outputs

You can see the SQL query statements that are actually sent to the database, where you can see that the Entity Framework executes some query statements and commands to check the database version and migrate historical data at initialization time, followed by the number of students in the query, and finally the SQL statement that queries all student data.

Open the Students page, enter "Throw" in the Search box, click Search

You can notice that the browser appears to have a delay of several seconds, because the Entity Framework is retrying the query. The first retry occurs very quickly, and then increases the wait time before each retry, which is called exponential backoff (exponential backoff)

When the student data being searched is displayed, look at the Output window and you will see that the same query was tried 5 times and the first 4 executions returned a transient error exception. For each transient error, you can see the log information that you defined when you generated the Schoolinterceptortransienterrors class and captured the transient error in the schoolinterceptorlogging.

The parameterized query is used here.

SELECT TOP (3) [Project1]. [ID] as [id], [Project1]. [LastName] As [LastName], [Project1]. [Firstmidname] As [Firstmidname], [Project1]. [EnrollmentDate] As [EnrollmentDate] from (SELECT [project1].[ ID] as [id], [Project1]. [LastName] As [LastName], [Project1]. [Firstmidname] As [Firstmidname], [Project1]. [EnrollmentDate] As [EnrollmentDate], Row_number () over (ORDER by [project1].[ LastName] ASC) as [Row_number] from (SELECT [extent1].[ ID] as [id], [Extent1]. [LastName] As [LastName], [Extent1]. [Firstmidname] As [Firstmidname], [Extent1]. [EnrollmentDate] As [enrollmentdate] from [dbo]. [Student] As [Extent1] WHERE (CAST (CHARINDEX (UPPER (@p__linq__0), UPPER ([extent1].[ LastName]) (as int)) > 0) OR (CAST (CHARINDEX (UPPER (@p__linq__1), UPPER ([extent1].[ Firstmidname])) as int)) > 0) as [Project1]) as [Project1] WHERE [Project1]. [Row_number] > 0 ORDER by [Project1]. [LASTNAME] ASC 

The values of the parameters are not recorded in the log, and if you wish to record them, you need to modify the logging related code to record the values in the Parameters property of the DbCommand object in the Interceptor method.

Note You cannot repeat this test unless you rerun the application. If you want to be able to repeatedly test the elastic connection functionality while the app is running, you need to modify the code in the Schoolinterceptortransienterrors class to reset the error counter.

To see the difference between using execution policy (retry policy) and not using execution policy, comment out the setexecutionstrategy row in the SchoolConfiguration.cs class, rerun the project, and enter "Throw" on the student page and query.

When the query is first executed, the debugger catches it and throws it when the first exception occurs

Finally, uncomment the Setexecutionstrategy line in the SchoolConfiguration.cs class.

Project Source: https://github.com/johnsonz/MvcContosoUniversity

The END

MVC5 Entity Framework Learning Elastic connections and command interception