关于MVC+dapper 记录SQL日志的问题

可以参考dapper源码，或者反编译一下dapper.dll，在dapper之上再封装一层拓展方法，正好最近我也在做这个，下面代码可以参考一下：

   using Dapper

   //封装的拓展类

    public static class DapperExtension

    {

        public static int CExecute(this IDbConnection cnn, string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)

        {

            var beginTime = DateTime.Now

            return  SqlMapper.Execute(cnn, sql, param, transaction, commandTimeout, commandType).ToLog(sql,param, beginTime)

        }

        public static int CExecute(this IDbConnection cnn, CommandDefinition command)

        {

            var beginTime = DateTime.Now

            return SqlMapper.Execute(cnn, command).ToLog(command.CommandText,command.Parameters,beginTime)

        }

        ……

        }

        

        //日志记录拓展方法

           private static T ToLog<T>(this T result, string sql, object param, DateTime beginTime, IDbConnection conn =null)

        {

            //日志记录要异步进行，防止日志记录时报错，影响业务流程

            Task.Run(()=> {

                var now = DateTime.Now

                var log = new LogModel()

                {

                    CreateTime = now,

                    SqlStr = sql,

                    ExcuteResult = result.ToJsonString(),

                    Timespan = (now - beginTime).TotalMilliseconds                    

                }

                if (param != null)

                {

                    foreach (var p in param.GetType().GetProperties())

                    {

                        log.SqlStr=log.SqlStr.Replace("@"+p.Name,p.GetValue(param).ToString())

                    }

                }

                //日志记录到MongoDB中

                MongoDbHelper.SetCollection(log, "Log_"+ now.ToString("yyyyMMdd"), "ExcuteLog")

            })

            return result

        } //调用示例：

        public int GetInfo(string sql, object param = null)

        {

             var connStr="...."//数据库连接字符串

            using (var conn = new MySqlConnection(connStr))

            {

                var result = conn.CExecute(sql, param)

                return result

            }

        }

项目有在ASP .NET MVC下作日志并写到数据库里面的的需求，于是在网上搜寻资料，发现解决方法五花八门，把别人的东西放到自己的机器上，发现问题不能解决，搞了将近两天，终于解决了这个问题，在此记录下来 。 一，首先先解决写到一般文件里的问题：解决步骤：1.首先下载个log4net.dll.并放到你的reference中2.在AssemblyInfo.cs最下面加入一行 [assembly: log4net.Config.XmlConfigurator(ConfigFile = "log4net.config", Watch = true)]，这里log4net.config是你将要 放置一些配置的地方。这个文件放在项目的根目录下。内容为：<?xml version="1.0"?><configuration><configSections><section name="log4net" type="System.Configuration.IgnoreHandlerSection"/></configSections><log4net><appender name="console" type="log4net.Appender.ConsoleAppender"></appender><root><level value="WARN" /><appender-ref ref="LogFileAppender" /></root><logger name="Test"><level value="DEBUG"/></logger><appender name="LogFileAppender" type="log4net.Appender.FileAppender" ><param name="File" value="D:/Documents and Settings/E518731/log.txt" /><param name="AppendToFile" value="true" /><layout type="log4net.Layout.PatternLayout"><param name="Header" value="[Header]/r/n"/><param name="Footer" value="[Footer]/r/n"/><param name="ConversionPattern" value="%d [%t] %-5p %c [%x] - %m%n"/></layout></appender></log4net></configuration>3.Controller中的代码如此写:public class HomeController : Controller { log4net.ILog log = log4net.LogManager.GetLogger("Test")public ActionResult Index() { log.Error("haha")return View()} }这样你就可以写log到某个file里面了. 二,解决写自定义log到数据库里面的问题1.首先下载个log4net.dll.并放到你的reference中2.在Global.asax的Application_Start方法中加入如下一行log4net.Config.XmlConfigurator.Configure()3.建立一个自定义的log对象public class LogContent { public string contentspublic int userId}4.在Web.config的configSections中加入如下一节<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />5.在configSections节外面加入如下<log4net><appender name="ADONetAppender" type="log4net.Appender.ADONetAppender"><bufferSize value="0" /><connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /><connectionString value="server=ch71dtbfxt12x/SQLEXPRESSuid=sapwd=passworddatabase=ATMS" /><commandText value="INSERT INTO Logs ([createdAt],[contents],[userId],[severity]) VALUES (@log_date, @contents,@userId,@level)" /><parameter><parameterName value="@log_date" /><dbType value="DateTime" /><layout type="log4net.Layout.RawTimeStampLayout" /></parameter><parameter><parameterName value="@contents" /><dbType value="String" /><size value="256" /><layout type="ATMS.ATMSWeb.Log4Net.MyLayout"><conversionPattern value="%Contents" /></layout></parameter><parameter><parameterName value="@userId"/><dbType value="Int32"/><layout type="ATMS.ATMSWeb.Log4Net.MyLayout"><conversionPattern value="%UserId" /></layout></parameter><parameter><parameterName value="@level" /><dbType value="String" /><size value="256" /><layout type="log4net.Layout.PatternLayout" value="%level" /></parameter></appender><root><level value="DEBUG" /><appender-ref ref="ADONetAppender" /></root></log4net>6.新建一个文件,包含如下:namespace ATMS.ATMSWeb.Log4Net { public class MyLayout : PatternLayout { public MyLayout() { this.AddConverter("Contents", typeof(ContentsPatternConverter))this.AddConverter("UserId", typeof(UserIdPatternConverter))} } public class ContentsPatternConverter : PatternLayoutConverter { override protected void Convert(TextWriter writer, LoggingEvent loggingEvent) { LogContent logMessage = loggingEvent.MessageObject as LogContentif (logMessage != null) writer.Write(logMessage.contents)} } public class UserIdPatternConverter : PatternLayoutConverter { override protected void Convert(TextWriter writer, LoggingEvent loggingEvent) { LogContent logMessage = loggingEvent.MessageObject as LogContentif (logMessage != null) writer.Write(logMessage.userId)} } } 7.记日志的时候,如下写法:private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType)log.Error(new LogContent {contents="ss",userId=4 })这下子你就可以把日至写到数据库表中了.具体的有些配置可能因人而异，比如数据库连接串的配置。有什么不明白的，欢迎留言

使用LogBACK日志系统，来解决。

下载logback的包，如果是maven就不必要下了。直接复制pom的引用

<dependency>

<groupId>org.slf4j</groupId>

<artifactId>jcl-over-slf4j</artifactId>

<version>1.7.7</version>

</dependency>

<dependency>

<groupId>ch.qos.logback</groupId>

<artifactId>logback-classic</artifactId>

<version>1.1.2</version>

</dependency>

然后配置logback的xml ，放入classpath目录就可以了

<configuration scan="true" scanPeriod="60 seconds" debug="false">

<!-- 输出目录 -->

<property name="LOG_HOME" value="D:/logs" />

<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">

<layout class="ch.qos.logback.classic.PatternLayout">

<!--格式化输出：%d表示日期，%thread表示线程名，%-5level：信息级别，%logger{40}：显示4个字符宽度，%msg：日志消息，%n：是换行符-->

<Pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{40} - %msg%n</Pattern>

</layout>

</appender>

<!-- 框架日志 -->

<appender name="framework_log" class="ch.qos.logback.core.rolling.RollingFileAppender">

<filter class="ch.qos.logback.classic.filter.LevelFilter">

<level>WARN</level>

</filter>

<!-- 滚动策略 -->

<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">

<fileNamePattern>${LOG_HOME}/framework/%d{yyyy-MM-dd}-%i.log</fileNamePattern>

<!-- 按日期和时间来滚动记录日志文件 -->

<!-- 触发策略 -->

<timeBasedFileNamingAndTriggeringPolicy class="ch.qos.logback.core.rolling.SizeAndTimeBasedFNATP">

<maxFileSize>100MB</maxFileSize>

</timeBasedFileNamingAndTriggeringPolicy>

</rollingPolicy>

<encoder>

<pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{40} - %msg%n</pattern>

</encoder>

</appender>

<!-- 框架日志 -->

<logger name="org.springframework" level="WARN" addtivity="false">

<appender-ref ref="framework_log" />

</logger>

<logger name="org.mybatis" level="WARN" addtivity="false">

<appender-ref ref="framework_log" />

</logger>

<root level="INFO">

<!-- <appender-ref ref="STDOUT" />-->

<!-- <appender-ref ref="framework_log" />-->

</root>

</configuration>

配置好就可以了。

---