log4net保存到資料庫系列五、新增資料庫欄位

園子裡面有很多關於log4net保存到資料庫的帖子,但是要動手操作還是比較不易，從頭開始學習log4net資料庫日誌一、WebConfig中配置log4net 

• 一、WebConfig中配置log4net
• 二、獨立配置文件中配置log4net
• 三、代碼中xml配置log4net
• 四、完整代碼配置log4net
• 五、新增資料庫欄位

下麵先一點一點來學習如果進行配置

1.下載，請參考log4net保存到資料庫系列一：WebConfig中配置log4net

2.資料庫腳本

CREATE TABLE [dbo].[Logs] (
    [ID]         INT            IDENTITY (1, 1) NOT NULL,
    [Level]      NVARCHAR (MAX) NULL,
    [Logger]     NVARCHAR (MAX) NULL,
    [Message]    NVARCHAR (MAX) NULL,
    [Date]       DATETIME       NOT NULL,
    [Exception]  NVARCHAR (MAX) NULL,
    [ModName]    NVARCHAR (MAX) NULL,
    [UserName]   NVARCHAR (MAX) NULL,
    [Mac]        NVARCHAR (MAX) NULL,
    [IP]         NVARCHAR (MAX) NULL,
    [Url]        NVARCHAR (MAX) NULL,
    [ActionName] NVARCHAR (MAX) NULL,
    [Name]       NVARCHAR (MAX) NULL
);

3.消息類

 public class JimMessage : IRequiresSessionState
    {
        public JimMessage()
        {
            IP = Util.getLocalIP();
            Mac = Util.getLocalMac();
            Url = System.Web.HttpContext.Current.Request.Url.AbsoluteUri;
        }
        public string IP { get; set; }
        public string Mac { get; set; }

        public string Url { get; set; }

        public string ModName { get; set; }    

        public string ActionName { get; set; }

        public string Message { get; set; }

        public string UserName { get; set; }

        public string Name { get; set; }
    }

 4.自定義欄位解析類

   public class MyLayout : PatternLayout
    {
        public MyLayout()
        {
            this.AddConverter("property", typeof(MyMessagePatternConverter));//指定自定義欄位的解析類
        }
    }

public class MyMessagePatternConverter : PatternLayoutConverter//解析自定義屬性，用反射獲取屬性名稱
    {
        protected override void Convert(System.IO.TextWriter writer, log4net.Core.LoggingEvent loggingEvent)
        {
            if (Option != null)
            {
                WriteObject(writer, loggingEvent.Repository, LookupProperty(Option, loggingEvent));
            }
            else
            {
                WriteDictionary(writer, loggingEvent.Repository, loggingEvent.GetProperties());
            }
        }

        /// <summary>
        /// 通過反射獲取傳入的日誌對象的某個屬性的值
        /// </summary>
        /// <param name="property"></param>
        /// <returns></returns>
        private object LookupProperty(string property, log4net.Core.LoggingEvent loggingEvent)
        {
            object propertyValue = string.Empty;

            PropertyInfo propertyInfo = loggingEvent.MessageObject.GetType().GetProperty(property);
            if (propertyInfo != null)
                propertyValue = propertyInfo.GetValue(loggingEvent.MessageObject, null);

            return propertyValue;
        }
    }

5..測試代碼

ILoggerRepository rep = LogManager.CreateRepository(Guid.NewGuid().ToString());

AdoNetAppender adoNetAppender = new AdoNetAppender();
adoNetAppender.BufferSize = -1;
adoNetAppender.ConnectionType = "System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";
adoNetAppender.ConnectionString = "data source=.;initial catalog=test;persist security info=True;user id=sa;password=Jiehui@2016;MultipleActiveResultSets=True;";
adoNetAppender.CommandText = "INSERT INTO Logs ([Date],[Level],[Logger],[Message],[Exception],[UserName],[ModName],[Mac],[IP],[Url],[ActionName],[Name]) VALUES (@log_date, @log_level, @logger, @Message, @exception,@UserName,@ModName,@Mac,@IP,@Url,@ActionName,@Name)";
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@log_date", DbType = System.Data.DbType.DateTime, Layout = new log4net.Layout.RawTimeStampLayout() });
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@log_level", DbType = System.Data.DbType.String, Size = 50, Layout = new Layout2RawLayoutAdapter(new PatternLayout("%level")) });
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@logger", DbType = System.Data.DbType.String, Size = 255, Layout = new Layout2RawLayoutAdapter(new PatternLayout("%logger")) });      
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@exception", DbType = System.Data.DbType.String, Size = 4000, Layout = new Layout2RawLayoutAdapter(new ExceptionLayout()) });

PatternLayout layout = new MyLayout() {ConversionPattern = "%property{UserName}"};
layout.ActivateOptions();  
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@UserName", DbType = System.Data.DbType.String, Size = 4000, Layout = new Layout2RawLayoutAdapter( layout)});

layout = new MyLayout() { ConversionPattern = "%property{ModName}" };
layout.ActivateOptions();
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@ModName", DbType = System.Data.DbType.String, Size = 4000, Layout = new Layout2RawLayoutAdapter(layout) });

layout = new MyLayout() { ConversionPattern = "%property{Mac}" };
layout.ActivateOptions();
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@Mac", DbType = System.Data.DbType.String, Size = 4000, Layout = new Layout2RawLayoutAdapter(layout) });

layout = new MyLayout() { ConversionPattern = "%property{IP}" };
layout.ActivateOptions();
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@IP", DbType = System.Data.DbType.String, Size = 4000, Layout = new Layout2RawLayoutAdapter(layout) });

layout = new MyLayout() { ConversionPattern = "%property{Url}" };
layout.ActivateOptions();
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@Url", DbType = System.Data.DbType.String, Size = 4000, Layout = new Layout2RawLayoutAdapter(layout) });

layout = new MyLayout() { ConversionPattern = "%property{ActionName}" };
layout.ActivateOptions();
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@ActionName", DbType = System.Data.DbType.String, Size = 4000, Layout = new Layout2RawLayoutAdapter(layout) });

layout = new MyLayout() { ConversionPattern = "%property{Name}" };
layout.ActivateOptions();
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@Name", DbType = System.Data.DbType.String, Size = 4000, Layout = new Layout2RawLayoutAdapter(layout) });

layout = new MyLayout() { ConversionPattern = "%property{Message}" };
layout.ActivateOptions();
adoNetAppender.AddParameter(new AdoNetAppenderParameter { ParameterName = "@Message", DbType = System.Data.DbType.String, Size = 4000, Layout = new Layout2RawLayoutAdapter(layout) });

adoNetAppender.ActivateOptions();

BasicConfigurator.Configure(rep, adoNetAppender);

ILog log = LogManager.GetLogger(rep.Name, "NoBufferingTest");
JimMessage message = new JimMessage();
message.Message = "審核成功";
message.UserName = "金朝錢";
message.Url = Request.Url.ToString();
message.ModName = "其他入庫";
message.ActionName = "審核";
message.Name = "Bill20150505";

log.Debug(message);

 6.測試成功