MiniProfiler使用點滴記錄-2017年6月23日11:08:23

1.看似針對同樣一段查詢表ef達式,重覆執行卻沒有被記錄下來。其實這是正常情況，因為ef並沒有重覆去執行 相同sql查詢。

2.MiniProfiler結合MVC過濾器進行 攔截記錄Sql,示例代碼：

using Mobile360.Core;
using Mobile360.Core.Interfaces;
using Mobile360.Core.Models;
using Mobile360.Web.Common;
using Newtonsoft.Json.Linq;
using StackExchange.Profiling;
using StackExchange.Profiling.Storage;
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Helpers;
using System.Web.Mvc;

namespace Mobile360.Web
{
    [AttributeUsage(AttributeTargets.Method, AllowMultiple = true, Inherited = false)]
    public class OperationHandlerAttribute : FilterAttribute,IActionFilter, IExceptionFilter 
    {
        private IRepository repo;

        /// <summary>
        /// 模塊描述
        /// </summary>
        public string ModuleName { get; set; }

        /// <summary>
        /// 方法名稱
        /// </summary>
        public string ActionName { get; set; }

        /// <summary>
        /// 方法描述
        /// </summary>
        public string ActionDescription { get; set; }

        /// <summary>
        /// 控制器名稱
        /// </summary>
        public string ControllerName { get; set; }

        /// <summary>
        /// 方法參數
        /// </summary>
        public string ActionParameters { get; set; }

        /// <summary>
        /// 訪問時間
        /// </summary>
        public DateTime AccessDate { get; set; }
 
        /// <summary>
        /// 操作備註
        /// </summary>
        public string OperationRemark { get; set; }

        /// <summary>
        /// 是否記錄入庫
        /// </summary>
        public bool IsLog { get; set; } 

        /// <summary>
        /// 操作人id
        /// </summary>
        public int OperatorId { get; set; }

        /// <summary>
        /// 操作人名
        /// </summary>
        public string OperatorName { get; set; }
 

        public OperationHandlerAttribute()
        {
            this.AccessDate = DateTime.Now;
            this.IsLog = true;
            this.repo = DependencyResolver.Current.GetService<IRepository>();
        }

        /// <summary>
        /// 操作日誌記錄
        /// </summary>
        /// <param name="option">操作動作描述</param>
        /// <param name="remark">其他備註</param>
        public OperationHandlerAttribute(string actionDescription , string remark = "")
        {
            this.AccessDate = DateTime.Now;
            this.IsLog = true;
            //this.ModuleName = moduleName; 
            this.OperationRemark = remark;
            this.ActionDescription = actionDescription;
            this.repo = DependencyResolver.Current.GetService<IRepository>();
        }
        void IActionFilter.OnActionExecuting(ActionExecutingContext filterContext)
        {
            if (this.IsLog)
            {
                MiniProfiler.Start();
                 
                this.OperatorName =  filterContext.HttpContext.User.Identity.Name;  

                this.ActionName = filterContext.ActionDescriptor.ActionName; 
                this.ControllerName = filterContext.ActionDescriptor.ControllerDescriptor.ControllerName; 
                IDictionary<string, object> dic = filterContext.ActionParameters; 
                var parameters = new System.Text.StringBuilder();
                foreach (var item in dic)
                {
                    parameters.Append(item.Key + "=" +  Json.Encode(item.Value) + "|");
                }
                this.ActionParameters = parameters.ToString(); 
                  
            }
        }

        void IActionFilter.OnActionExecuted(ActionExecutedContext context)
        {
            if (this.IsLog)
            {
                MiniProfiler.Stop(); 
                string efSqlStr2Json = MiniProfiler.Current.Root.CustomTimingsJson; 

                AuditLog log = new AuditLog();
                log.AuditAccount = string.IsNullOrEmpty(this.OperatorName)?"(未登錄用戶)": this.OperatorName;
                log.Action = this.ActionName;
                log.ActionDescription = this.ActionDescription;
                log.Controller = this.ControllerName;
                log.Parameters = this.ActionParameters;
                log.StartTime = this.AccessDate;
                log.SqlQuery = efSqlStr2Json; 
                log.EndTime = DateTime.Now;
                log.Result = true;
                log.IP = IPHelper.GetRealIP();

                repo.Insert<AuditLog>(log); 
                repo.SaveChanges();
            }
        }



        #region IExceptionFilter 成員
        void IExceptionFilter.OnException(ExceptionContext context)
        {
            if (ConfigurationManager.AppSettings["IsDev"] == "true")
            {
                throw new Exception(context.Exception.Message, context.Exception);
            }

            SystemLog slog = new SystemLog();
            slog.Action = this.ActionName;
            slog.Level = (int)SystemLogType.ERROR;
            slog.LoginAccount = this.OperatorName;
            slog.Message = BuildExceptionInfo(context);
            slog.OccurTime = DateTime.Now;

            repo.Insert<SystemLog>(slog); 
            repo.SaveChanges();


            JObject jsonResult = new JObject(); //返回的json數據
            jsonResult.Add(new JProperty("Code", -1));
            jsonResult.Add(new JProperty("Msg", "系統發生異常，請查看內部日誌"));
            ContentResult cr = new ContentResult();
            cr.Content = jsonResult.ToString();
            cr.ContentType = "application/json";
            context.Result = cr;
            context.ExceptionHandled = true;
        }

        private string BuildExceptionInfo(ExceptionContext context)
        {
            var sb = new StringBuilder();
            var req = context.HttpContext.Request;
            sb.AppendLine(String.Format("處理對“{0}”的“{1}”請求時發生了異常", req.RawUrl, req.HttpMethod));
            sb.AppendLine("以下是參數的信息：");
            this.AppendRequestLine(sb, req.QueryString);
            this.AppendRequestLine(sb, req.Form);
            sb.AppendLine("以下是異常的信息：");
            sb.AppendLine(context.Exception.ToString());
            //sb.AppendLine(context.Exception.StackTrace.ToString());

            return sb.ToString();
        }

        private void AppendRequestLine(StringBuilder sb, NameValueCollection coll)
        {
            for (int i = 0; i < coll.Count; i++)
            {
                sb.AppendFormat("{0}: {1}", coll.Keys[i], coll[i]);
                sb.AppendLine();
            }
        }

        #endregion 

    }
}

3.以上的miniprofiler並不能 攔截到 sql語句查詢，需要使用 minprofiler 封裝的ado.net對象。

 /// <summary>
        /// 執行自定義SQL(創建、更新、刪除操作)
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public async Task<int> ExecuteSqlCommandAsync(string commandText, params object[] parameters)
        {
            var connection1 = this.Database.Connection;//使用EF的sql連接對象。統一管理。
            if (connection1 != null)
            {
                DbCommand command = new SqlCommand();
                ProfiledDbCommand prcommand = new ProfiledDbCommand(command, connection1, MiniProfiler.Current);
                prcommand.CommandType = CommandType.Text;
                prcommand.CommandText = commandText;
                prcommand.Parameters.AddRange(parameters);
                prcommand.Connection = connection1;

                if (connection1.State == ConnectionState.Closed)
                    connection1.Open();

                return await prcommand.ExecuteNonQueryAsync();
            }
            return 0;
        }

ProfiledDbCommand，

ProfiledDbConnection等對象都是MiniProfiler的對象。這樣才能抓到 Sql語句。

4.由於miniprofiler是用來性能調優的，用來做審計日誌記錄（包括哪個用戶最終生成的sql查詢）看似並不合適，非常耗性能。
所以，我們並沒有準備去使用它來獲取Sql語句。

運用在

Application_BeginRequest和

Application_EndRequest
期間用EF6.0版本以上才有的 攔截器介面

DbCommandInterceptor

攔截的所有sql語句作為一次請求的sql查詢語句 來作為嘗試，不知道這樣有啥劣勢不？  希望有嘗試過的 前輩 指點。