ASP.NET Web Forms Connection Resiliency and Command Interception
by Erik Reitan
In this tutorial, you will modify the Wingtip Toys sample application to support connection resiliency and command interception. By enabling connection resiliency, the Wingtip Toys sample application will automatically retry data calls when transient errors typical of a cloud environment occur. Also, by implementing command interception, the Wingtip Toys sample application will catch all SQL queries sent to the database in order to log or change them.
[!NOTE]
This Web Forms tutorial was based on Tom Dykstra’s following MVC tutorial:
Connection Resiliency and Command Interception with the Entity Framework in an ASP.NET MVC Application
What you’ll learn:
- How to provide connection resiliency.
- How to implement command interception.
Prerequisites
Before you start, make sure that you have the following software installed on your computer:
- Microsoft Visual Studio 2013 or Microsoft Visual Studio Express 2013 for Web. The .NET Framework is installed automatically.
The Wingtip Toys sample project, so that you can implement the functionality mentioned in this tutorial within the Wingtip Toys project. The following link provides download details:
Prior to completing this tutorial, consider reviewing the related tutorial series, Getting Started with ASP.NET 4.5 Web Forms and Visual Studio 2013. The tutorial series will help you become familiar with the WingtipToys project and code.
Connection resiliency
When you consider deploying an application to Windows Azure, one option to consider is deploying the database to Windows Azure SQL Database, a cloud database service. Transient connection errors are typically more frequent when you connect to a cloud database service than when your web server and your database server are directly connected together in the same data center. Even if a cloud web server and a cloud database service are hosted in the same data center, there are more network connections between them that can have problems, such as load balancers.
Also a cloud service is typically shared by other users, which means its responsiveness can be affected by them. And your access to the database might be subject to throttling. Throttling means the database service throws exceptions when you try to access it more frequently than is allowed in your Service Level Agreement (SLA).
Many or most connection problems that occur when you’re accessing a cloud service are transient, that is, they resolve themselves in a short period of time. So when you try a database operation and get a type of error that is typically transient, you could try the operation again after a short wait, and the operation might be successful. You can provide a much better experience for your users if you handle transient errors by automatically trying again, making most of them invisible to the customer. The connection resiliency feature in Entity Framework 6 automates that process of retrying failed SQL queries.
The connection resiliency feature must be configured appropriately for a particular database service:
- It has to know which exceptions are likely to be transient. You want to retry errors caused by a temporary loss in network connectivity, not errors caused by program bugs, for example.
- It has to wait an appropriate amount of time between retries of a failed operation. You can wait longer between retries for a batch process than you can for an online web page where a user is waiting for a response.
- It has to retry an appropriate number of times before it gives up. You might want to retry more times in a batch process that you would in an online application.
You can configure these settings manually for any database environment supported by an Entity Framework provider.
All you have to do to enable connection resiliency is create a class in your assembly that derives from the DbConfiguration class, and in that class set the SQL Database execution strategy, which in Entity Framework is another term for retry policy.
Implementing connection resiliency
- Download and open the WingtipToys sample Web Forms application in Visual Studio.
- In the Logic folder of the WingtipToys application, add a class file named WingtipToysConfiguration.cs.
Replace the existing code with the following code:
[!code-csharpMain]
1: using System.Data.Entity;
2: using System.Data.Entity.SqlServer;
3:4: namespace WingtipToys.Logic
5: {6: public class WingtipToysConfiguration : DbConfiguration
7: {8: public WingtipToysConfiguration()
9: {10: SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy());
11: }12: }13: }
The Entity Framework automatically runs the code it finds in a class that derives from DbConfiguration. You can use the DbConfiguration class to do configuration tasks in code that you would otherwise do in the Web.config file. For more information, see EntityFramework Code-Based Configuration.
- In the Logic folder, open the AddProducts.cs file.
Add a
[!code-csharpMain]usingstatement forSystem.Data.Entity.Infrastructureas shown highlighted in yellow:1: using System;
2: using System.Collections.Generic;
3: using System.Linq;
4: using System.Web;
5: using WingtipToys.Models;
6: using System.Data.Entity.Infrastructure;
Add a
catchblock to theAddProductmethod so that theRetryLimitExceededExceptionis logged as highlighted in yellow:[!code-csharpMain]
1: public bool AddProduct(string ProductName, string ProductDesc, string ProductPrice, string ProductCategory, string ProductImagePath)
2: {3: var myProduct = new Product();
4: myProduct.ProductName = ProductName;5: myProduct.Description = ProductDesc;6: myProduct.UnitPrice = Convert.ToDouble(ProductPrice);7: myProduct.ImagePath = ProductImagePath;8: myProduct.CategoryID = Convert.ToInt32(ProductCategory);9:10: using (ProductContext _db = new ProductContext())
11: {12: // Add product to DB.
13: _db.Products.Add(myProduct);14: try
15: {16: _db.SaveChanges();17: }18: catch (RetryLimitExceededException ex)
19: {20: // Log the RetryLimitExceededException.
21: WingtipToys.Logic.ExceptionUtility.LogException(ex, "Error: RetryLimitExceededException -> RemoveProductButton_Click in AdminPage.aspx.cs");
22: }23: }24: // Success.
25: return true;
26: }
By adding the RetryLimitExceededException exception, you can provide better logging or display an error message to the user where they can choose to try the process again. By catching the RetryLimitExceededException exception, the only errors likely to be transient will already have been tried and failed several times. The actual exception returned will be wrapped in the RetryLimitExceededException exception. In addition, you also added a general catch block. For more information about the RetryLimitExceededException exception, see Entity Framework Connection Resiliency / Retry Logic.
Command interception
Now that you’ve turned on a retry policy, how do you test to verify that it is working as expected? It’s not so easy to force a transient error to happen, especially when you’re running locally, and it would be especially difficult to integrate actual transient errors into an automated unit test. To test the connection resiliency feature, you need a way to intercept queries that Entity Framework sends to SQL Server and replace the SQL Server response with an exception type that is typically transient.
You can also use query interception in order to implement a best practice for cloud applications: log the latency and success or failure of all calls to external services such as database services.
In this section of the tutorial you’ll use the Entity Framework’s interception feature both for logging and for simulating transient errors.
Create a logging interface and class
A best practice for logging is to do it by using an interface rather than hard-coding calls to System.Diagnostics.Trace or a logging class. That makes it easier to change your logging mechanism later if you ever need to do that. So in this section, you’ll create the logging interface and a class to implement it.
Based on the above procedure, you have downloaded and opened the WingtipToys sample application in Visual Studio.
- Create a folder in the WingtipToys project and name it Logging.
In the Logging folder, create a class file named ILogger.cs and replace the default code with the following code:
[!code-csharpMain]
1: using System;
2:3: namespace WingtipToys.Logging
4: {5: public interface ILogger
6: {7: void Information(string message);
8: void Information(string fmt, params object[] vars);
9: void Information(Exception exception, string fmt, params object[] vars);
10:11: void Warning(string message);
12: void Warning(string fmt, params object[] vars);
13: void Warning(Exception exception, string fmt, params object[] vars);
14:15: void Error(string message);
16: void Error(string fmt, params object[] vars);
17: void Error(Exception exception, string fmt, params object[] vars);
18:19: void TraceApi(string componentName, string method, TimeSpan timespan);
20: void TraceApi(string componentName, string method, TimeSpan timespan, string properties);
21: void TraceApi(string componentName, string method, TimeSpan timespan, string fmt, params object[] vars);
22:23: }24: }
The interface provides three tracing levels to indicate the relative importance of logs, and one designed to provide latency information for external service calls such as database queries. The logging methods have overloads that let you pass in an exception. This is so that exception information including stack trace and inner exceptions is reliably logged by the class that implements the interface, instead of relying on that being done in each logging method call throughout the application.
The TraceApi methods enable you to track the latency of each call to an external service such as SQL Database. 3. In the Logging folder, create a class file named Logger.cs and replace the default code with the following code:
[!code-csharpMain]
1: using System;
2: using System.Diagnostics;
3: using System.Text;
4:
5: namespace WingtipToys.Logging
6: {
7: public class Logger : ILogger
8: {
9:
10: public void Information(string message)
11: {
12: Trace.TraceInformation(message);
13: }
14:
15: public void Information(string fmt, params object[] vars)
16: {
17: Trace.TraceInformation(fmt, vars);
18: }
19:
20: public void Information(Exception exception, string fmt, params object[] vars)
21: {
22: Trace.TraceInformation(FormatExceptionMessage(exception, fmt, vars));
23: }
24:
25: public void Warning(string message)
26: {
27: Trace.TraceWarning(message);
28: }
29:
30: public void Warning(string fmt, params object[] vars)
31: {
32: Trace.TraceWarning(fmt, vars);
33: }
34:
35: public void Warning(Exception exception, string fmt, params object[] vars)
36: {
37: Trace.TraceWarning(FormatExceptionMessage(exception, fmt, vars));
38: }
39:
40: public void Error(string message)
41: {
42: Trace.TraceError(message);
43: }
44:
45: public void Error(string fmt, params object[] vars)
46: {
47: Trace.TraceError(fmt, vars);
48: }
49:
50: public void Error(Exception exception, string fmt, params object[] vars)
51: {
52: Trace.TraceError(FormatExceptionMessage(exception, fmt, vars));
53: }
54:
55: public void TraceApi(string componentName, string method, TimeSpan timespan)
56: {
57: TraceApi(componentName, method, timespan, "");
58: }
59:
60: public void TraceApi(string componentName, string method, TimeSpan timespan, string fmt, params object[] vars)
61: {
62: TraceApi(componentName, method, timespan, string.Format(fmt, vars));
63: }
64: public void TraceApi(string componentName, string method, TimeSpan timespan, string properties)
65: {
66: string message = String.Concat("Component:", componentName, ";Method:", method, ";Timespan:", timespan.ToString(), ";Properties:", properties);
67: Trace.TraceInformation(message);
68: }
69:
70: private static string FormatExceptionMessage(Exception exception, string fmt, object[] vars)
71: {
72: var sb = new StringBuilder();
73: sb.Append(string.Format(fmt, vars));
74: sb.Append(" Exception: ");
75: sb.Append(exception.ToString());
76: return sb.ToString();
77: }
78: }
79: }
The implementation uses System.Diagnostics to do the tracing. This is a built-in feature of .NET which makes it easy to generate and use tracing information. There are many “listeners” you can use with System.Diagnostics tracing, to write logs to files, for example, or to write them to blob storage in Windows Azure. See some of the options, and links to other resources for more information, in Troubleshooting Windows Azure Web Sites in Visual Studio. For this tutorial, you’ll only look at logs in the Visual Studio Output window.
In a production application you might want to consider using tracing frameworks other than System.Diagnostics, and the ILogger interface makes it relatively easy to switch to a different tracing mechanism if you decide to do that.
Create interceptor classes
Next, you’ll create the classes that the Entity Framework will call into every time it is going to send a query to the database, one to simulate transient errors and one to do logging. These interceptor classes must derive from the DbCommandInterceptor class. In them, you write method overrides that are automatically called when the query is about to be executed. In these methods you can examine or log the query that is being sent to the database, and you can change the query before it’s sent to the database or return something to Entity Framework yourself without even passing the query to the database.
To create the interceptor class that will log every SQL query before it is sent to the database, create a class file named InterceptorLogging.cs in the Logic folder and replace the default code with the following code:
[!code-csharpMain]
1: using System;
2: using System.Data.Common;
3: using System.Data.Entity;
4: using System.Data.Entity.Infrastructure.Interception;
5: using System.Data.Entity.SqlServer;
6: using System.Data.SqlClient;
7: using System.Diagnostics;
8: using System.Reflection;
9: using System.Linq;
10: using WingtipToys.Logging;
11:12: namespace WingtipToys.Logic
13: {14: public class InterceptorLogging : DbCommandInterceptor
15: {16: private ILogger _logger = new Logger();
17: private readonly Stopwatch _stopwatch = new Stopwatch();
18:19: public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
20: {21: base.ScalarExecuting(command, interceptionContext);
22: _stopwatch.Restart();23: }24:25: public override void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
26: {27: _stopwatch.Stop();28: if (interceptionContext.Exception != null)
29: {30: _logger.Error(interceptionContext.Exception, "Error executing command: {0}", command.CommandText);
31: }32: else
33: {34: _logger.TraceApi("SQL Database", "Interceptor.ScalarExecuted", _stopwatch.Elapsed, "Command: {0}: ", command.CommandText);
35: }36: base.ScalarExecuted(command, interceptionContext);
37: }38:39: public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
40: {41: base.NonQueryExecuting(command, interceptionContext);
42: _stopwatch.Restart();43: }44:45: public override void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
46: {47: _stopwatch.Stop();48: if (interceptionContext.Exception != null)
49: {50: _logger.Error(interceptionContext.Exception, "Error executing command: {0}", command.CommandText);
51: }52: else
53: {54: _logger.TraceApi("SQL Database", "Interceptor.NonQueryExecuted", _stopwatch.Elapsed, "Command: {0}: ", command.CommandText);
55: }56: base.NonQueryExecuted(command, interceptionContext);
57: }58:59: public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
60: {61: base.ReaderExecuting(command, interceptionContext);
62: _stopwatch.Restart();63: }64: public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
65: {66: _stopwatch.Stop();67: if (interceptionContext.Exception != null)
68: {69: _logger.Error(interceptionContext.Exception, "Error executing command: {0}", command.CommandText);
70: }71: else
72: {73: _logger.TraceApi("SQL Database", "Interceptor.ReaderExecuted", _stopwatch.Elapsed, "Command: {0}: ", command.CommandText);
74: }75: base.ReaderExecuted(command, interceptionContext);
76: }77: }78: }
For successful queries or commands, this code writes an Information log with latency information. For exceptions, it creates an Error log. 2. To create the interceptor class that will generate dummy transient errors when you enter “Throw” in the Name textbox on the page named AdminPage.aspx, create a class file named InterceptorTransientErrors.cs in the Logic folder and replace the default code with the following code:
[!code-csharpMain]
1: using System;
2: using System.Data.Common;
3: using System.Data.Entity;
4: using System.Data.Entity.Infrastructure.Interception;
5: using System.Data.Entity.SqlServer;
6: using System.Data.SqlClient;
7: using System.Diagnostics;
8: using System.Reflection;
9: using System.Linq;
10: using WingtipToys.Logging;
11:
12: namespace WingtipToys.Logic
13: {
14: public class InterceptorTransientErrors : DbCommandInterceptor
15: {
16: private int _counter = 0;
17: private ILogger _logger = new Logger();
18:
19: public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
20: {
21: bool throwTransientErrors = false;
22: if (command.Parameters.Count > 0 && command.Parameters[0].Value.ToString() == "Throw")
23: {
24: throwTransientErrors = true;
25: command.Parameters[0].Value = "TransientErrorExample";
26: command.Parameters[1].Value = "TransientErrorExample";
27: }
28:
29: if (throwTransientErrors && _counter < 4)
30: {
31: _logger.Information("Returning transient error for command: {0}", command.CommandText);
32: _counter++;
33: interceptionContext.Exception = CreateDummySqlException();
34: }
35: }
36:
37: private SqlException CreateDummySqlException()
38: {
39: // The instance of SQL Server you attempted to connect to does not support encryption
40: var sqlErrorNumber = 20;
41:
42: var sqlErrorCtor = typeof(SqlError).GetConstructors(BindingFlags.Instance | BindingFlags.NonPublic).Where(c => c.GetParameters().Count() == 7).Single();
43: var sqlError = sqlErrorCtor.Invoke(new object[] { sqlErrorNumber, (byte)0, (byte)0, "", "", "", 1 });
44:
45: var errorCollection = Activator.CreateInstance(typeof(SqlErrorCollection), true);
46: var addMethod = typeof(SqlErrorCollection).GetMethod("Add", BindingFlags.Instance | BindingFlags.NonPublic);
47: addMethod.Invoke(errorCollection, new[] { sqlError });
48:
49: var sqlExceptionCtor = typeof(SqlException).GetConstructors(BindingFlags.Instance | BindingFlags.NonPublic).Where(c => c.GetParameters().Count() == 4).Single();
50: var sqlException = (SqlException)sqlExceptionCtor.Invoke(new object[] { "Dummy", errorCollection, null, Guid.NewGuid() });
51:
52: return sqlException;
53: }
54: }
55: }
This code only overrides the `ReaderExecuting` method, which is called for queries that can return multiple rows of data. If you wanted to check connection resiliency for other types of queries, you could also override the `NonQueryExecuting` and `ScalarExecuting` methods, as the logging interceptor does. Later, you will log in as the “Admin” and select the Admin link on the top navigation bar. Then, on the AdminPage.aspx page you will add a product named “Throw”. The code creates a dummy SQL Database exception for error number 20, a type known to be typically transient. Other error numbers currently recognized as transient are 64, 233, 10053, 10054, 10060, 10928, 10929, 40197, 40501, and 40613, but these are subject to change in new versions of SQL Database. The product will be renamed to “TransientErrorExample”, which you can follow in the code of the InterceptorTransientErrors.cs file.
The code returns the exception to Entity Framework instead of running the query and passing back results. The transient exception is returned four times, and then the code reverts to the normal procedure of passing the query to the database.
Because everything is logged, you'll be able to see that Entity Framework tries to execute the query four times before finally succeeding, and the only difference in the application is that it takes longer to render a page with query results. The number of times the Entity Framework will retry is configurable; the code specifies four times because that’s the default value for the SQL Database execution policy. If you change the execution policy, you’d also change the code here that specifies how many times transient errors are generated. You could also change the code to generate more exceptions so that Entity Framework will throw the RetryLimitExceededException exception. 3. In Global.asax, add the following using statements:
[!code-csharpMain]
1: using System.Data.Entity.Infrastructure.Interception;
Then, add the highlighted lines to the
Application_Startmethod:[!code-csharpMain]
1: void Application_Start(object sender, EventArgs e)
2: {3: // Code that runs on application startup
4: RouteConfig.RegisterRoutes(RouteTable.Routes);5: BundleConfig.RegisterBundles(BundleTable.Bundles);6:7: // Initialize the product database.
8: Database.SetInitializer(new ProductDatabaseInitializer());
9:10: // Create administrator role and user.
11: RoleActions roleActions = new RoleActions();
12: roleActions.createAdmin();13:14: // Add Routes.
15: RegisterRoutes(RouteTable.Routes);16:17: // Logging.
18: DbInterception.Add(new InterceptorTransientErrors());
19: DbInterception.Add(new InterceptorLogging());
20:21: }
These lines of code are what causes your interceptor code to be run when Entity Framework sends queries to the database. Notice that because you created separate interceptor classes for transient error simulation and logging, you can independently enable and disable them.
You can add interceptors using the DbInterception.Add method anywhere in your code; it doesn’t have to be in the Application_Start method. Another option, if you didn’t add interceptors in the Application_Start method, would be to update or add the class named WingtipToysConfiguration.cs and put the above code at the end of the constructor of the WingtipToysbConfiguration class.
Wherever you put this code, be careful not to execute DbInterception.Add for the same interceptor more than once, or you’ll get additional interceptor instances. For example, if you add the logging interceptor twice, you’ll see two logs for every SQL query.
Interceptors are executed in the order of registration (the order in which the DbInterception.Add method is called). The order might matter depending on what you’re doing in the interceptor. For example, an interceptor might change the SQL command that it gets in the CommandText property. If it does change the SQL command, the next interceptor will get the changed SQL command, not the original SQL command.
You’ve written the transient error simulation code in a way that lets you cause transient errors by entering a different value in the UI. As an alternative, you could write the interceptor code to always generate the sequence of transient exceptions without checking for a particular parameter value. You could then add the interceptor only when you want to generate transient errors. If you do this, however, don’t add the interceptor until after database initialization has completed. In other words, do at least one database operation such as a query on one of your entity sets before you start generating transient errors. The Entity Framework executes several queries during database initialization, and they aren’t executed in a transaction, so errors during initialization could cause the context to get into an inconsistent state.
Test logging and connection resiliency
- In Visual Studio, press F5 to run the application in debug mode, and then login as “Admin” using “Pa$$word” as the password.
- Select Admin from the navigation bar at the top.
- Enter a new product named “Throw” with appropriate description, price and image file.
- Press the Add Product button.
You’ll notice that the browser seems to hang for several seconds while Entity Framework is retrying the query several times. The first retry happens very quickly, then the wait increases before each additional retry. This process of waiting longer before each retry is called exponential backoff . - Wait until the page is no longer atttempting to load.
- Stop the project and look at the Visual Studio Output window to see the tracing output. You can find the Output window by selecting Debug -> Windows -> Output. You might have to scroll past several other logs written by your logger.
Notice that you can see the actual SQL queries sent to the database. You see some initial queries and commands that Entity Framework does to get started, checking the database version and migration history table.
Note that you can’t repeat this test unless you stop the application and restart it. If you wanted to be able to test connection resiliency multiple times in a single run of the application, you could write code to reset the error counter in InterceptorTransientErrors . 7. To see the difference the execution strategy (retry policy) makes, comment out the SetExecutionStrategy line in WingtipToysConfiguration.cs file in the Logic folder, run the Admin page in debug mode again, and add the product named “Throw” again.
This time the debugger stops on the first generated exception immediately when it tries to execute the query the first time.
8. Uncomment the SetExecutionStrategy line in the WingtipToysConfiguration.cs file.
Summary
In this tutorial you’ve seen how to modify a Web Forms sample application to support connection resiliency and command interception.
Next Steps
After you have reviewed connection resiliency and command interception in ASP.NET Web Forms, review the ASP.NET Web Forms topic Asynchronous Methods in ASP.NET 4.5. The topic will teach you the basics of building an asynchronous ASP.NET Web Forms application using Visual Studio.
)
|
|