MiniProfiler usage record-June 23, 2017 11:08:23, miniprofiler-2017

MiniProfiler usage record-June 23, 2017 11:08:23, miniprofiler-2017

1. It seems that the ef statements for the same query table are repeated but not recorded. In fact, this is normal, because ef does not repeatedly execute the same SQL query.

 

2. Use MiniProfiler and MVC filters to intercept and record SQL statements. Example code:

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. collections. generic; using System. collections. specialized; using System. configuration; using System. linq; using System. text; using System. web; using System. web. helpers; using Syst Em. web. mvc; namespace Mobile360.Web {[AttributeUsage (AttributeTargets. method, AllowMultiple = true, Inherited = false)] public class OperationHandlerAttribute: FilterAttribute, IActionFilter, IExceptionFilter {private IRepository repo; /// <summary> /// module description /// </summary> public string ModuleName {get; set ;} /// <summary> /// method name /// </summary> public string ActionName {get; set ;}/// <summa Ry >/// method description /// </summary> public string ActionDescription {get; set ;} /// <summary> /// controller name /// </summary> public string ControllerName {get; set ;} /// <summary> /// method parameter /// </summary> public string ActionParameters {get; set ;} /// <summary> /// access time /// </summary> public DateTime AccessDate {get; set ;} /// <summary> /// operation remarks /// </summary> public string OperationRemark {get; set ;} /// <Summary> /// whether to record the data in the database // </summary> public bool IsLog {get; set ;} /// <summary> /// operator id /// </summary> public int OperatorId {get; set ;} /// <summary> /// operation name // </summary> public string OperatorName {get; set;} public OperationHandlerAttribute () {this. accessDate = DateTime. now; this. isLog = true; this. repo = DependencyResolver. current. getService <IRepository> () ;}/// <summary> /// Operation Log // </summary> /// <param name = "option"> operation Action Description </param> /// <param name = "remark"> others remarks </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 <IRepositor Y> ();} 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. actionPara Meters; 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 )? "(User not logged on)": 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 member void IExceptionFilter. onException (ExceptionContext context) {if (ConfigurationManager. deleetask[ "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 (); // The returned json data jsonResult. add (new JProperty ("Code",-1); jsonResult. add (new JProperty ("Msg", "system exception, please check internal logs"); 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 ("an exception occurred when processing the" {1} "request for" {0} "", req. rawUrl, req. httpMethod); sb. appendLine ("The following is the parameter information:"); this. appendRequestLine (sb, req. queryString); this. appendRequestLine (sb, req. form); sb. appendLine ("the following information is abnormal:"); 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. The preceding miniprofiler cannot intercept SQL statement queries. You must use the ado.net object encapsulated by minprofiler.

/// <Summary> /// execute custom SQL statements (create, update, and delete operations) /// </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; // use the SQL Connection object of EF. Unified management. 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，

Objects such as ProfiledDbConnection are all MiniProfiler objects. In this way, the SQL statement can be captured.

4. Because miniprofiler is used for performance tuning, it seems inappropriate to use audit log records (including the user's final SQL query), which is very performance-consuming.
Therefore, we are not prepared to use it to obtain SQL statements.

Used in

Application_BeginRequest and

Application_EndRequest
Interceptor interfaces available only in EF6.0 and later versions

DbCommandInterceptor

All the SQL statements intercepted are used as the SQL query statement for one request. Do you have any disadvantages? Hope you can give some advice from your predecessors.