連接彈性和命令攔截的 ASP.NET MVC 應用程式中的實體框架

   最近悟出來一個道理，在這兒分享給大家：學歷代表你的過去，能力代表你的現在，學習代表你的將來。

   十年河東十年河西，莫欺少年窮

   學無止境，精益求精

   上篇博客我們學習了EF 之 MVC 排序，查詢，分頁 Sorting, Filtering, and Paging For MVC About EF，本節繼續學習

   標題中的：連接彈性(微軟解釋：瞬態錯誤自動重試連接)和命令攔截(捕捉所有 SQL 查詢發送到資料庫，以便登錄或改變它們)

   上網查了大量的資料，網友們基本都是直接翻譯原文：Connection Resiliency and Command Interception with the Entity Framework in an ASP.NET MVC Application

    在解釋連接彈性之前，我們來看一段代碼：

        /// <summary>
        /// 釋放資料庫資源 斷開連接
        /// </summary>
        /// <param name="disposing"></param>
        protected override void Dispose(bool disposing)
        {
            db.Dispose();
            base.Dispose(disposing);
        }

   上述代碼意思是SQL操作執行後，及時斷開資料庫連接，釋放資料庫資源

   SQL操作的過程：SQL操作-->執行時發生異常-->執行Dispose-->斷開連接，釋放資源。在本次操作中，程式和資料庫連接了一次，因為發生異常，及時釋放了資料庫資源，這樣的執行過程看似沒問題，但是用戶體驗不太好。

   如果SQL本身沒有什麼問題，由於斷開了資料庫連接，用戶得不到數據結果，豈不是用戶體驗差嗎？

   我們再來看看微軟的解讀：連接彈性(微軟解釋：瞬態錯誤自動重試連接的次數)

   微軟的意思是，在執行一個SQL的過程中，如果第一次執行錯誤，還可以通過代碼控制來實現重連，進行第二次資料庫連接，同理，如果第二次數據連接依然發生異常，還會執行第三次資料庫連接等等，而在資料庫訪問策略中，這樣的重試連接預設是四次。

   回到剛纔的話題：如果SQL語句本身沒有什麼問題，SQL第一次執行失敗，那麼第二次就可能成功，這樣就提高了用戶體驗。

   在此：舉一些例子，例如SQL執行過程中突然斷網，訪問的資源臨時被占用等導致的執行失敗都是可以嘗試重連的。

   OK，關於連接彈性的說明就到這兒，下麵我們探討下命令攔截，首先看微軟的解釋<捕捉所有 SQL 查詢發送到資料庫，以便登錄或改變它們>

   看完微軟的解釋，相信你和我一樣也是丈二的和尚，摸不著頭腦。而本人的理解是這樣的，當然，我的理解也可能不對，希望大家在評論區指出，謝謝。

   我的理解如下：

   EF代碼很少使用SQL語句，在我們寫EF時，基本都用Linq To Sql代替了，而我們訪問資料庫的最基本單元就是SQL語句，那麼你書寫的linq To Sql 會轉化成什麼樣的SQL語句呢？如果我們能看到這些SQL語句，我們就可以根據這些SQL語句做一些改變，從而提高程式的效率。

   例如：下麵的EF代碼語句：

        private StudentContext db = new StudentContext();
        /// <summary>
        /// 簡單分頁演示
        /// </summary>
        /// <param name="page">頁碼</param>
        /// <returns></returns>
        public ActionResult Index2(int page = 1)//查詢所有學生數據
        {
            return View(db.Students.OrderBy(item=>item.Id).ToPagedList(page,9));
        }

   上述代碼是個簡單的分頁程式，如果你看不懂，請參照我的上篇博客：EF 之 MVC 排序，查詢，分頁 Sorting, Filtering, and Paging For MVC About EF

   那麼上述代碼在執行的過程中會生成什麼樣的SQL語句呢？

   在程式運行的輸出視窗中，我們可以看到如上輸出，其輸出的完整SQL如下：

SELECT TOP (9) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Sex] AS [Sex], 
    [Extent1].[StudentNum] AS [StudentNum]
    FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Sex] AS [Sex], [Extent1].[StudentNum] AS [StudentNum], row_number() OVER (ORDER BY [Extent1].[Id] ASC) AS [row_number]
        FROM [dbo].[Student] AS [Extent1]
    )  AS [Extent1]
    WHERE [Extent1].[row_number] > 0
    ORDER BY [Extent1].[Id] ASC

   那麼，我們怎樣才能捕捉到這些SQL語句呢？

   在MVC EF 預設的輸出視窗中，這些SQL語句是不會輸出的，我們需要增加一個‘捕捉器’來捕捉這些SQL語句。

   綜上所言，我們就基本瞭解了連接彈性和命令攔截的概念和基本意思。註：如有個人理解不對的地方，謹防誤人子弟，希望大家在評論區指出，小弟拜謝

   那麼，我們需要寫什麼代碼來達到連接彈性和命令攔截的功效呢？

   如下<大家也可參考：Connection Resiliency and Command Interception with the Entity Framework in an ASP.NET MVC Application>

   首先：如何啟用彈性連接

   在我們的EF項目中創建一個名稱為：Configuration 的文件夾，在文件夾中首先添加一個資料庫重連類：

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.SqlServer;
using System.Linq;
using System.Web;

namespace EF_Test.Configuration

{ public class StudentConfiguration : DbConfiguration { /// <summary> /// 需要引入命名空間：using System.Collections.Generic;和using System.Data.Entity.SqlServer; /// </summary> public StudentConfiguration() { //設置 SQL 資料庫執行策略 預設重連四次 SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy()); } } }

   上文中提到，如果不是SQL本身的異常，我們重新連接資料庫，可能會得到我們想要的結果。例如查詢數據時，突然斷網，第一次查詢失敗，在資料庫重連後，第二次查詢成功，系統將查詢結果反饋給客戶，提高了客戶體驗。

   但是，如果您寫的SQL本身就是錯誤的，那無論重連幾次數據都將是無用之功，這時，我們可以通過如下代碼來捕獲SQL執行異常：

   在控制器代碼中引用：using System.Data.Entity.Infrastructure;

            try
            {
                //有異常的SQL操作，SQL語句本身異常
            }
            catch (RetryLimitExceededException /* dex */)
            {
                //Log the error (uncomment dex variable name and add a line here to write a log.
                ModelState.AddModelError("", "Unable to save changes. Try again, and if the problem persists see your system administrator.");
            }

   至此：資料庫彈性連接的啟用就完成了，下麵我們繼續命令攔截：

   如何啟用命令攔截：

   首先在項目中創建文件夾：ILogger

   1、創建日誌介面和類：在日誌記錄文件夾中，創建一個名為ILogger.cs的類文件︰

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

    }

   2、在日誌記錄文件夾中，創建一個名為Logger.cs的類文件︰

    public class Logger : ILogger
    {

        public void Information(string 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.TraceInformation(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));
        }

        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(FormatExceptionMessage(exception, fmt, vars));
        }

        public void TraceApi(string componentName, string method, TimeSpan timespan)
        {
            TraceApi(componentName, 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/windowsazure/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();
        }
    }

   3、在日誌文件夾中創建攔截器類

using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;
using System.Data.Entity.SqlServer;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Reflection;
using System.Linq;

namespace EF_Test.ILogger
{
    public class StudentInterceptorLogging : DbCommandInterceptor
    {
        private ILogger _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> interceptionContext)
        {
            _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> interceptionContext)
        {
            base.NonQueryExecuting(command, interceptionContext);
            _stopwatch.Restart();
        }

        public override void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            _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> interceptionContext)
        {
            base.ReaderExecuting(command, interceptionContext);
            _stopwatch.Restart();
        }
        public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            _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);
        }
    }
}

   4、創建記錄SQL錯誤的攔截器類

using System;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure.Interception;
using System.Data.Entity.SqlServer;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Reflection;
using System.Linq;

namespace EF_Test.ILogger
{
    public class StudentInterceptorTransientErrors : 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 attempted 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;
        }
    }
}

   至此，整個攔截器就建立完畢。

   如果正確的使攔截器發揮作用呢？我們還需在全局應用文件中添加如下代碼：

   代碼如下：

        protected void Application_Start()
        {
           // Database.SetInitializer<StudentContext>(new DropCreateDatabaseIfModelChanges<StudentContext>());  

            AreaRegistration.RegisterAllAreas();
            WebApiConfig.Register(GlobalConfiguration.Configuration);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
            //
            DbInterception.Add(new StudentInterceptorTransientErrors());
            DbInterception.Add(new StudentInterceptorLogging());
        }

   當然，我們如果不想寫在全局應用文件中，我們可以在資料庫重連策略類中添加，如下：

        public StudentConfiguration()
        {
            //設置 SQL 資料庫執行策略 預設重連四次
            SetExecutionStrategy("System.Data.SqlClient", () => new SqlAzureExecutionStrategy());
            //註冊攔截器  using System.Data.Entity.Infrastructure.Interception;
            DbInterception.Add(new StudentInterceptorTransientErrors());
            DbInterception.Add(new StudentInterceptorLogging());
        }

   下麵是我的文件代碼目錄結構：

   運行程式，測試下我們的攔截器及輸出的SQL語句：

   程式效果圖為：

   上述SQL語句其實就是一個簡單的分頁SQL語句。

   我們輸入學號進行查詢，看看會輸出什麼樣的SQL語句：

   輸出的SQL語句為：

   我們把SQL語句放入資料庫中執行，如下：

   至此：本節內容也就講完了，謝謝！

    @陳卧龍的博客