由于之前的项目说最好要有日志功能,正好之前看过几篇这方面的文章就弄了点东西。
这是EF日志受启发很大的一个原文:
http://www.cnblogs.com/GuZhenYin/p/5556732.html
下面说开发经历~
由于之前有一个开发了一半的.net core的项目M,这个项目的框架都是由一个大牛来搭起来的。期中有几个比较好的功能,一个是报错拦截和日志记录功能。但是现在开发的项目C是没有上面的两个功能的,然后项目C的前辈说最好C也能实现这几个功能,正好我又看了上面的那个文章,就想着来自己搞一下~。
(下面说的内容如果没有看过上面EF日志的文章可能会看不懂)
最初:
一开始我想先把项目的SQL日志给完成了,但是项目C使用的ORM是dapper,之前文章里面用的是EF(这里不得不吐槽一下,一开始项目C都是没有ORM的,就有一个SqlHelper简单封装了一下,其实几乎就是ado.net了),我“天真”的以为直接下一个DatabaseLogger然后在Global.asax里面注入一下就可以了,但是注入好之后发现根本没有起任何作用。最后在一个Chloe.ORM的作者的提醒下发现了这个错误原因:
这个注入的方法是EF空间下的,明显就是EF的功能,对dapper当然没有用了。然后查了一下发现dapper原生的是没有这个功能的,只能自己写~(囧),然后我就对照着Chloe.ORM的写法,自己在dapper的源码里面修改了几个方法(只要修改sqlmapper.cs里面的代码就可以了)
private static int ExecuteImpl(this IDbConnection cnn, ref CommandDefinition command) { object param = command.Parameters; IEnumerable multiExec = GetMultiExec(param); Identity identity; CacheInfo info = null; IDbCommandInterceptor[] log = DbInterception.GetInterceptors(); IDbCommand cmd = null; DbCommandInterceptionContext<IDataReader> dbCommandInterceptionContext = new DbCommandInterceptionContext<IDataReader>(); if (multiExec != null) { #if ASYNC if((command.Flags & CommandFlags.Pipelined) != 0) { // this includes all the code for concurrent/overlapped query return ExecuteMultiImplAsync(cnn, command, multiExec).Result; } #endif bool isFirst = true; int total = 0; bool wasClosed = cnn.State == ConnectionState.Closed; try { if (wasClosed) cnn.Open(); using (cmd = command.SetupCommand(cnn, null)) { foreach (var l in log) { l.ReaderExecuting(cmd, dbCommandInterceptionContext); } string masterSql = null; foreach (var obj in multiExec) { if (isFirst) { masterSql = cmd.CommandText; isFirst = false; identity = new Identity(command.CommandText, cmd.CommandType, cnn, null, obj.GetType(), null); info = GetCacheInfo(identity, obj, command.AddToCache); } else { cmd.CommandText = masterSql; // because we do magic replaces on "in" etc cmd.Parameters.Clear(); // current code is Add-tastic } info.ParamReader(cmd, obj); total += cmd.ExecuteNonQuery(); } } command.OnCompleted(); } catch (Exception e) { dbCommandInterceptionContext.Exception = e; foreach (var l in log) { l.ReaderExecuted(cmd, dbCommandInterceptionContext); } } finally { foreach (var l in log) { l.ReaderExecuted(cmd, dbCommandInterceptionContext); } if (wasClosed) cnn.Close(); } return total; } // nice and simple if (param != null) { identity = new Identity(command.CommandText, command.CommandType, cnn, null, param.GetType(), null); info = GetCacheInfo(identity, param, command.AddToCache); } return ExecuteCommand(cnn, ref command, param == null ? null : info.ParamReader); }
private static IEnumerable<T> QueryImpl<T>(this IDbConnection cnn, CommandDefinition command, Type effectiveType) { object param = command.Parameters; var identity = new Identity(command.CommandText, command.CommandType, cnn, effectiveType, param?.GetType(), null); var info = GetCacheInfo(identity, param, command.AddToCache); List<T> temp = new List<T>(); IDbCommand cmd = null; IDataReader reader = null; IDbCommandInterceptor[] log = DbInterception.GetInterceptors(); bool wasClosed = cnn.State == ConnectionState.Closed; DbCommandInterceptionContext<IDataReader> dbCommandInterceptionContext = new DbCommandInterceptionContext<IDataReader>(); try { cmd = command.SetupCommand(cnn, info.ParamReader); foreach (var l in log) l.ReaderExecuting(cmd, dbCommandInterceptionContext); if (wasClosed) cnn.Open(); reader = ExecuteReaderWithFlagsFallback(cmd, wasClosed, CommandBehavior.SequentialAccess | CommandBehavior.SingleResult); wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader // with the CloseConnection flag, so the reader will deal with the connection; we // still need something in the "finally" to ensure that broken SQL still results // in the connection closing itself var tuple = info.Deserializer; int hash = GetColumnHash(reader); if (tuple.Func == null || tuple.Hash != hash) { if (reader.FieldCount == 0) //https://code.google.com/p/dapper-dot-net/issues/detail?id=57 return null; tuple = info.Deserializer = new DeserializerState(hash, GetDeserializer(effectiveType, reader, 0, -1, false)); if (command.AddToCache) SetQueryCache(identity, info); } var func = tuple.Func; var convertToType = Nullable.GetUnderlyingType(effectiveType) ?? effectiveType; while (reader.Read()) { object val = func(reader); if (val == null || val is T) { temp.Add((T)val); //return (T)val; } else { temp.Add((T)Convert.ChangeType(val, convertToType, CultureInfo.InvariantCulture)); } } while (reader.NextResult()) { } // happy path; close the reader cleanly - no // need for "Cancel" etc reader.Dispose(); reader = null; command.OnCompleted(); return temp; } catch(Exception e) { dbCommandInterceptionContext.Exception = e; foreach (var l in log) l.ReaderExecuted(cmd, dbCommandInterceptionContext); return new List<T>(); } finally { foreach (var l in log) l.ReaderExecuted(cmd, dbCommandInterceptionContext); if (reader != null) { if (!reader.IsClosed) try { cmd.Cancel(); } catch { /* don't spoil the existing exception */ } reader.Dispose(); } if (wasClosed) cnn.Close(); cmd?.Dispose(); } }
private static T QueryRowImpl<T>(IDbConnection cnn, Row row, ref CommandDefinition command, Type effectiveType) { object param = command.Parameters; var identity = new Identity(command.CommandText, command.CommandType, cnn, effectiveType, param?.GetType(), null); var info = GetCacheInfo(identity, param, command.AddToCache); IDbCommand cmd = null; IDataReader reader = null; IDbCommandInterceptor[] log = DbInterception.GetInterceptors(); bool wasClosed = cnn.State == ConnectionState.Closed; DbCommandInterceptionContext<IDataReader> dbCommandInterceptionContext = new DbCommandInterceptionContext<IDataReader>(); try { cmd = command.SetupCommand(cnn, info.ParamReader); foreach (var l in log) l.ReaderExecuting(cmd, dbCommandInterceptionContext); if (wasClosed) cnn.Open(); reader = ExecuteReaderWithFlagsFallback(cmd, wasClosed, (row & Row.Single) != 0 ? CommandBehavior.SequentialAccess | CommandBehavior.SingleResult // need to allow multiple rows, to check fail condition : CommandBehavior.SequentialAccess | CommandBehavior.SingleResult | CommandBehavior.SingleRow); wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader T result = default(T); if (reader.Read() && reader.FieldCount != 0) { // with the CloseConnection flag, so the reader will deal with the connection; we // still need something in the "finally" to ensure that broken SQL still results // in the connection closing itself var tuple = info.Deserializer; int hash = GetColumnHash(reader); if (tuple.Func == null || tuple.Hash != hash) { tuple = info.Deserializer = new DeserializerState(hash, GetDeserializer(effectiveType, reader, 0, -1, false)); if (command.AddToCache) SetQueryCache(identity, info); } var func = tuple.Func; object val = func(reader); if (val == null || val is T) { result = (T)val; } else { var convertToType = Nullable.GetUnderlyingType(effectiveType) ?? effectiveType; result = (T)Convert.ChangeType(val, convertToType, CultureInfo.InvariantCulture); } if ((row & Row.Single) != 0 && reader.Read()) ThrowMultipleRows(row); while (reader.Read()) { } } else if ((row & Row.FirstOrDefault) == 0) // demanding a row, and don't have one { ThrowZeroRows(row); } while (reader.NextResult()) { } // happy path; close the reader cleanly - no // need for "Cancel" etc reader.Dispose(); reader = null; command.OnCompleted(); return result; } catch (Exception e) { dbCommandInterceptionContext.Exception = e; foreach (var l in log) l.ReaderExecuted(cmd, dbCommandInterceptionContext); return default(T); } finally { foreach (var l in log) l.ReaderExecuted(cmd, dbCommandInterceptionContext); if (reader != null) { if (!reader.IsClosed) try { cmd.Cancel(); } catch { /* don't spoil the existing exception */ } reader.Dispose(); } if (wasClosed) cnn.Close(); cmd?.Dispose(); } }
这个就是在dapper数据操作的时候记录日志的,为了体现DI的思想,还要新增一个类
public static class DbInterception { static volatile List<IDbCommandInterceptor> _interceptors = new List<IDbCommandInterceptor>(); static readonly object _lockObject = new object(); public static void Add(IDbCommandInterceptor interceptor) { lock (_lockObject) { List<IDbCommandInterceptor> newList = _interceptors.ToList(); newList.Add(interceptor); newList.TrimExcess(); _interceptors = newList; } } public static void Remove(IDbCommandInterceptor interceptor) { lock (_lockObject) { List<IDbCommandInterceptor> newList = _interceptors.ToList(); newList.Remove(interceptor); newList.TrimExcess(); _interceptors = newList; } } public static IDbCommandInterceptor[] GetInterceptors() { return _interceptors.ToArray(); } }
这样子当要注入日志操作的时候只要在Global.asax中向DbInterception插入实现IDbCommandInterceptor的类就可以了。
由于篇幅过长,关于程序报错的日志处理就稍后发表了。