Postgresql pg_dump: 因为服务器版本不匹配而终止

之前在生产环境部署的Postgresql为9.6版本,但最近在备份数据的时候发现他所使用的客户端居然是9.2的版本。。

# 查看已安装的postgresql程序
yum list installed |grep postgresql

# 查看Yum已安装的源
yum repolist

# 引入postgresql对应版本的源
# http://yum.postgresql.org/repopackages.php
rpm -Uvh https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

# 查找需要的版本
yum search postgresql --enablerepo=pgdg96

# 安装对应版本的客户端
yum install -y postgresql96

# 查看执行程序路径
find / -name pg_dump -type f

# 替换低版本
ln -snf /usr/pgsql-9.6/bin/pg_dump /usr/bin/pg_dump

备份命令

su - postgres -c "pg_dump -U odoo -Fc -d Yunshang -f Yunshang.dump"

恢复命令

createdb -U odoo Yunshang
pg_restore -v -U odoo "F:\python\YunShangZhuangShi\Yunshang.dump"

DataTable 差集Except、交集Intersect、并集Union

使用方法

//获取第一个数据源DataTable
DataTable _dtSource = DBHelper.GetDataTable("select top 10  ksdid,user_id,user_pwd from ksd_user_info");

IEnumerable<DataRow> query = _dtSource.AsEnumerable().Where(t => t.Field<string>("user_id").StartsWith("66")).ToList();
//获取第二个数据源DataTable
DataTable _dt2 = query.CopyToDataTable();

//比较两个数据源的交集
IEnumerable<DataRow> query2 = _dtSource.AsEnumerable().Intersect(dt2.AsEnumerable(), DataRowComparer.Default);
//两个数据源的交集集合      
DataTable dt3 = query2.CopyToDataTable();


//获取两个数据源的并集
IEnumerable<DataRow> query2 = _dtSource.AsEnumerable().Union(dt2.AsEnumerable(), DataRowComparer.Default);
//两个数据源的并集集合
DataTable dt3 = query2.CopyToDataTable();


//获取两个数据源的差集
IEnumerable<DataRow> query2 = _dtSource.AsEnumerable().Except(dt2.AsEnumerable(), DataRowComparer.Default);
//两个数据源的差集集合
DataTable dt3 = query2.CopyToDataTable();

自定义比较器

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Kelun.Comparer
{
    /// <summary>
    /// 计量单位比较器
    /// </summary>
    class JldwComparer : IEqualityComparer<DataRow>
    {
        public bool Equals(DataRow x, DataRow y)
        {
            return (x.Field<string>("F_DWBH") == y.Field<string>("F_DWBH") &&
            x.Field<string>("F_DWMC") == y.Field<string>("F_DWMC") &&
            x.Field<string>("F_DWLX") == y.Field<string>("F_DWLX"));
        }

        public int GetHashCode(DataRow obj)
        {
            return obj.ToString().GetHashCode();
        }
    }
}

根据自定义条件获取差集

// 获取2个数据源的差集
//IEnumerable<DataRow> query = _dt.AsEnumerable().Except(_sdt.AsEnumerable(), DataRowComparer.Default);
IEnumerable<DataRow> query = _dt.AsEnumerable().Except(_sdt.AsEnumerable(), new JldwComparer());

int querynum = query.Count();

if (querynum > 0)
{
	var _saveDt = query.CopyToDataTable();
	SqlDbHelper.Insert(_saveDt, "HNKL_JLDW_ERP");
	var msg = string.Format("计量单位写入成功,共计{0}条", _saveDt.Rows.Count);
	Logger.Info(msg, null, "System");
}

Quartz.NET 3.x 例子

最近写项目需要用到定时任务,所以就找到了Quartz.NET,但网上的例子都是到处抄袭的,完全没有适合Quartz.NET 3.x 的有用信息,这里就将自己的使用例子帖一下。

  • IDE:VS2017
  • .Net : 4.5.2

写在前面

Quartz.NET 在3.x已经将插件分离了,所以如果要从xml直接加载文件,需要引入插件包

引入包

配置

app.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>

  <configSections>
    <section name="quartz" type="System.Configuration.NameValueSectionHandler, System, Version=1.0.5000.0,Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  </configSections>

  <quartz>
    <add key="quartz.scheduler.instanceName" value="QuartzScheduler"/>
    <add key="quartz.threadPool.type" value="Quartz.Simpl.SimpleThreadPool, Quartz"/>
    <add key="quartz.threadPool.threadCount" value="10"/>
    <!--******************************Plugin配置*********************************************-->
    <add key="quartz.plugin.jobInitializer.type" value="Quartz.Plugin.Xml.XMLSchedulingDataProcessorPlugin, Quartz.Plugins"/>
    <add key="quartz.plugin.jobInitializer.fileNames" value="quartz_jobs.xml"/>
  </quartz>

  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2"/>
  </startup>

</configuration>

quartz_jobs.xml

<?xml version="1.0" encoding="UTF-8"?>

<!-- This file contains job definitions in schema version 2.0 format -->

<job-scheduling-data xmlns="http://quartznet.sourceforge.net/JobSchedulingData" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0">

  <processing-directives>
    <overwrite-existing-data>true</overwrite-existing-data>
  </processing-directives>


  <schedule>

    <!--开始执行一个调度-->
    <job>
      <name>jldwjob</name>
      <group>kelun</group>
      <description>计量单位</description>
      <job-type>Kelun.JldwJob, Kelun</job-type><!--格式:实现了IJob接口的包含完整命名空间的类名,程序集名称-->
      <durable>true</durable>
      <recover>false</recover>
    </job>
    
    <trigger>
      <cron>
        <name>jlwd</name>
        <group>kelun</group>
        <job-name>jldwjob</job-name>
        <job-group>kelun</job-group>
        <!--<start-time>2018-01-22T00:00:00+08:00</start-time>-->
        <cron-expression>0/1 * * * * ?</cron-expression><!--每3秒执行一次-->
      </cron>
    </trigger>

  </schedule>
</job-scheduling-data>

接口实现

using Kelun.Log4Net;
using Quartz;
using System.Reflection;
using System.Threading.Tasks;

namespace Kelun
{
    class JldwJob : IJob
    {

        private static readonly IMyLog Logger = MyLogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public Task Execute(IJobExecutionContext context)
        {
            return Task.Run(() => 
            {
                Logger.Info("定时任务执行");
            });
        }
    }
}

启动Quartz

public MainForm()
{
    InitializeComponent();
    RunProgramAsync().GetAwaiter().GetResult();
}

private static async Task RunProgramAsync()
{
    try
    {
        StdSchedulerFactory factory = new StdSchedulerFactory();

        IScheduler scheduler = await factory.GetScheduler();

        //开启调度器
        await scheduler.Start();

        //创建一个作业
        //IJobDetail job = JobBuilder.Create<JldwJob>().WithIdentity("myJob", "group1").Build();

        //ITrigger trigger = TriggerBuilder.Create()
        //    .WithIdentity("myTrigger", "group1")
        //    .StartNow()     //现在开始
        //    .WithSimpleSchedule(x => x
        //        .WithIntervalInSeconds(1)   //触发时间,1秒一次
        //        .RepeatForever())
        //    .Build();

        //把作业,触发器加入调度器。
       //await scheduler.ScheduleJob(job, trigger);  

        //await scheduler.Shutdown();

    }
    catch (SchedulerException se)
    {
        Logger.Error("执行错误", se);
    }
}

禁用Quartz.NET日志输出

<logger name="Quartz">
	<level value="OFF" />
</logger>

Log4Net 写入数据库[扩展字段]

最近在写一个项目,需要将日志写入数据库,Log4Net的功能仅仅只能满足写入消息部分,若需要写入自定义信息就不行了,所以这里扩展了Log4Net的部分功能。

扩展字段

建立扩展字段的Entity类

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Kelun.Log4Net
{
    /// <summary>
    /// 扩展Log4Net字段
    /// </summary>
    class LogMessage
    {
        /// <summary>
        /// 订单号
        /// </summary>
        public string OrderNum { get; set; }

        /// <summary>
        /// 用户
        /// </summary>
        public string UserName { get; set; }

        public LogMessage(string orderNum, string userName)
        {
            OrderNum = orderNum;
            UserName = userName;
        }
    }
}

转换类

using log4net.Core;
using log4net.Layout.Pattern;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace Kelun.Log4Net
{
    /// <summary>
    /// Log4Net 扩展字段
    /// 转换自定义字段
    /// </summary>
    class ParemterContent
    {
        internal sealed class OrderNumParam : PatternLayoutConverter
        {
            protected override void Convert(TextWriter writer, LoggingEvent loggingEvent)
            {
                if (loggingEvent.MessageObject is LogMessage content)
                {
                    writer.Write(content.OrderNum);
                }
            }
        }

        internal sealed class UserNameParam : PatternLayoutConverter
        {
            protected override void Convert(TextWriter writer, LoggingEvent loggingEvent)
            {
                if (loggingEvent.MessageObject is LogMessage content)
                {
                    writer.Write(content.UserName);
                }
            }
        }
    }
}

Layout 扩展

using log4net.Layout;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Kelun.Log4Net
{
    class MyLoggerLayout : PatternLayout
    {
        public MyLoggerLayout()
        {
            this.AddConverter("OrderNum", typeof(ParemterContent.OrderNumParam));
            this.AddConverter("UserName", typeof(ParemterContent.UserNameParam));
        }
    }
}

扩展方法

接口

using log4net;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Kelun.Log4Net
{
    interface IMyLog : ILog
    {
        void Debug(object message, string OrderNum, string UserName);
        void Debug(object message, string OrderNum, string UserName,Exception t);

        void Info(object message, string OrderNum, string UserName);
        void Info(object message, string OrderNum, string UserName, Exception t);

        void Warn(object message, string OrderNum, string UserName);
        void Warn(object message, string OrderNum, string UserName, Exception t);

        void Error(object message, string OrderNum, string UserName);
        void Error(object message, string OrderNum, string UserName, Exception t);

    }
}

接口实现

using Kelun.Log4Net;
using log4net.Core;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Kelun.Log4Net
{
    class MyLogImpl : LogImpl, IMyLog
    {

        public static readonly Type ThisDeclaringType = typeof(MyLogImpl);

        public MyLogImpl(ILogger logger) : base(logger)
        {
            
        }

        #region 实现重写

        public void Debug(object message, string OrderNum, string UserName)
        {
            Debug(message, OrderNum, UserName, null);
        }

        public void Debug(object message, string OrderNum, string UserName, Exception t)
        {
            if (this.IsDebugEnabled)
            {
                LoggingEvent loggingEvent = new LoggingEvent(ThisDeclaringType, Logger.Repository, Logger.Name, Level.Debug, message, t);
                loggingEvent.Properties["UserName"] = UserName;
                loggingEvent.Properties["OrderNum"] = OrderNum;
                Logger.Log(loggingEvent);
            }
        }

        public void Error(object message, string OrderNum, string UserName)
        {
            Error(message, OrderNum, UserName, null);
        }

        public void Error(object message, string OrderNum, string UserName, Exception t)
        {
            if (this.IsErrorEnabled)
            {
                LoggingEvent loggingEvent = new LoggingEvent(ThisDeclaringType, Logger.Repository, Logger.Name, Level.Error, message, t);
                loggingEvent.Properties["UserName"] = UserName;
                loggingEvent.Properties["OrderNum"] = OrderNum;
                Logger.Log(loggingEvent);
            }
        }

        public void Info(object message, string OrderNum, string UserName)
        {
            Info(message, OrderNum, UserName, null);
        }

        public void Info(object message, string OrderNum, string UserName, Exception t)
        {
            if (this.IsInfoEnabled)
            {
                LoggingEvent loggingEvent = new LoggingEvent(ThisDeclaringType, Logger.Repository, Logger.Name, Level.Info, message, t);
                loggingEvent.Properties["UserName"] = UserName;
                loggingEvent.Properties["OrderNum"] = OrderNum;
                Logger.Log(loggingEvent);
            }
        }

        public void Warn(object message, string OrderNum, string UserName)
        {
            Warn(message, OrderNum, UserName, null);
        }

        public void Warn(object message, string OrderNum, string UserName, Exception t)
        {
            LoggingEvent loggingEvent = new LoggingEvent(ThisDeclaringType, Logger.Repository, Logger.Name, Level.Warn, message, t);
            loggingEvent.Properties["UserName"] = UserName;
            loggingEvent.Properties["OrderNum"] = OrderNum;
            Logger.Log(loggingEvent);
        }

        #endregion
    }
}

扩展Manager

using log4net.Core;
using System.Reflection;
using System;

namespace Kelun.Log4Net
{
    class MyLogManager
    {
        #region Static Member Variables
        /// <summary>
        /// The wrapper map to use to hold the <see cref="MyLogImpl"/> objects
        /// </summary>
        private static readonly WrapperMap s_wrapperMap = new WrapperMap(new WrapperCreationHandler(WrapperCreationHandler));
        #endregion

        #region Constructor
        private MyLogManager() { }
        #endregion


        #region Type Specific Manager Methods

        /// <summary>
        /// Returns the named logger if it exists
        /// </summary>
        /// <remarks>
        /// <para>If the named logger exists (in the default hierarchy) then it
        /// returns a reference to the logger, otherwise it returns
        /// <c>null</c>.</para>
        /// </remarks>
        /// <param name="name">The fully qualified logger name to look for</param>
        /// <returns>The logger found, or null</returns>
        public static IMyLog Exists(string name)
        {
            return Exists(Assembly.GetCallingAssembly(), name);
        }

        /// <summary>
        /// Returns the named logger if it exists
        /// </summary>
        /// <remarks>
        /// <para>If the named logger exists (in the specified domain) then it
        /// returns a reference to the logger, otherwise it returns
        /// <c>null</c>.</para>
        /// </remarks>
        /// <param name="domain">the domain to lookup in</param>
        /// <param name="name">The fully qualified logger name to look for</param>
        /// <returns>The logger found, or null</returns>
        public static IMyLog Exists(string domain, string name)
        {
            return WrapLogger(LoggerManager.Exists(domain, name));
        }

        /// <summary>
        /// Returns the named logger if it exists
        /// </summary>
        /// <remarks>
        /// <para>If the named logger exists (in the specified assembly's domain) then it
        /// returns a reference to the logger, otherwise it returns
        /// <c>null</c>.</para>
        /// </remarks>
        /// <param name="assembly">the assembly to use to lookup the domain</param>
        /// <param name="name">The fully qualified logger name to look for</param>
        /// <returns>The logger found, or null</returns>
        public static IMyLog Exists(Assembly assembly, string name)
        {
            return WrapLogger(LoggerManager.Exists(assembly, name));
        }

        /// <summary>
        /// Returns all the currently defined loggers in the default domain.
        /// </summary>
        /// <remarks>
        /// <para>The root logger is <b>not</b> included in the returned array.</para>
        /// </remarks>
        /// <returns>All the defined loggers</returns>
        public static IMyLog[] GetCurrentLoggers()
        {
            return GetCurrentLoggers(Assembly.GetCallingAssembly());
        }

        /// <summary>
        /// Returns all the currently defined loggers in the specified domain.
        /// </summary>
        /// <param name="domain">the domain to lookup in</param>
        /// <remarks>
        /// The root logger is <b>not</b> included in the returned array.
        /// </remarks>
        /// <returns>All the defined loggers</returns>
        public static IMyLog[] GetCurrentLoggers(string domain)
        {
            return WrapLoggers(LoggerManager.GetCurrentLoggers(domain));
        }

        /// <summary>
        /// Returns all the currently defined loggers in the specified assembly's domain.
        /// </summary>
        /// <param name="assembly">the assembly to use to lookup the domain</param>
        /// <remarks>
        /// The root logger is <b>not</b> included in the returned array.
        /// </remarks>
        /// <returns>All the defined loggers</returns>
        public static IMyLog[] GetCurrentLoggers(Assembly assembly)
        {
            return WrapLoggers(LoggerManager.GetCurrentLoggers(assembly));
        }

        /// <summary>
        /// Retrieve or create a named logger.
        /// </summary>
        /// <remarks>
        /// <para>Retrieve a logger named as the <paramref name="name"/>
        /// parameter. If the named logger already exists, then the
        /// existing instance will be returned. Otherwise, a new instance is
        /// created.</para>
        /// 
        /// <para>By default, loggers do not have a set level but inherit
        /// it from the hierarchy. This is one of the central features of
        /// log4net.</para>
        /// </remarks>
        /// <param name="name">The name of the logger to retrieve.</param>
        /// <returns>the logger with the name specified</returns>
        public static IMyLog GetLogger(string name)
        {
            return GetLogger(Assembly.GetCallingAssembly(), name);
        }

        /// <summary>
        /// Retrieve or create a named logger.
        /// </summary>
        /// <remarks>
        /// <para>Retrieve a logger named as the <paramref name="name"/>
        /// parameter. If the named logger already exists, then the
        /// existing instance will be returned. Otherwise, a new instance is
        /// created.</para>
        /// 
        /// <para>By default, loggers do not have a set level but inherit
        /// it from the hierarchy. This is one of the central features of
        /// log4net.</para>
        /// </remarks>
        /// <param name="domain">the domain to lookup in</param>
        /// <param name="name">The name of the logger to retrieve.</param>
        /// <returns>the logger with the name specified</returns>
        public static IMyLog GetLogger(string domain, string name)
        {
            return WrapLogger(LoggerManager.GetLogger(domain, name));
        }

        /// <summary>
        /// Retrieve or create a named logger.
        /// </summary>
        /// <remarks>
        /// <para>Retrieve a logger named as the <paramref name="name"/>
        /// parameter. If the named logger already exists, then the
        /// existing instance will be returned. Otherwise, a new instance is
        /// created.</para>
        /// 
        /// <para>By default, loggers do not have a set level but inherit
        /// it from the hierarchy. This is one of the central features of
        /// log4net.</para>
        /// </remarks>
        /// <param name="assembly">the assembly to use to lookup the domain</param>
        /// <param name="name">The name of the logger to retrieve.</param>
        /// <returns>the logger with the name specified</returns>
        public static IMyLog GetLogger(Assembly assembly, string name)
        {
            return WrapLogger(LoggerManager.GetLogger(assembly, name));
        }

        /// <summary>
        /// Shorthand for <see cref="LogManager.GetLogger(string)"/>.
        /// </summary>
        /// <remarks>
        /// Get the logger for the fully qualified name of the type specified.
        /// </remarks>
        /// <param name="type">The full name of <paramref name="type"/> will 
        /// be used as the name of the logger to retrieve.</param>
        /// <returns>the logger with the name specified</returns>
        public static IMyLog GetLogger(Type type)
        {
            return GetLogger(Assembly.GetCallingAssembly(), type.FullName);
        }

        /// <summary>
        /// Shorthand for <see cref="LogManager.GetLogger(string)"/>.
        /// </summary>
        /// <remarks>
        /// Get the logger for the fully qualified name of the type specified.
        /// </remarks>
        /// <param name="domain">the domain to lookup in</param>
        /// <param name="type">The full name of <paramref name="type"/> will 
        /// be used as the name of the logger to retrieve.</param>
        /// <returns>the logger with the name specified</returns>
        public static IMyLog GetLogger(string domain, Type type)
        {
            return WrapLogger(LoggerManager.GetLogger(domain, type));
        }

        /// <summary>
        /// Shorthand for <see cref="LogManager.GetLogger(string)"/>.
        /// </summary>
        /// <remarks>
        /// Get the logger for the fully qualified name of the type specified.
        /// </remarks>
        /// <param name="assembly">the assembly to use to lookup the domain</param>
        /// <param name="type">The full name of <paramref name="type"/> will 
        /// be used as the name of the logger to retrieve.</param>
        /// <returns>the logger with the name specified</returns>
        public static IMyLog GetLogger(Assembly assembly, Type type)
        {
            return WrapLogger(LoggerManager.GetLogger(assembly, type));
        }

        #endregion

        #region Extension Handlers

        /// <summary>
        /// Lookup the wrapper object for the logger specified
        /// </summary>
        /// <param name="logger">the logger to get the wrapper for</param>
        /// <returns>the wrapper for the logger specified</returns>
        private static IMyLog WrapLogger(ILogger logger)
        {
            return (IMyLog)s_wrapperMap.GetWrapper(logger);
        }

        /// <summary>
        /// Lookup the wrapper objects for the loggers specified
        /// </summary>
        /// <param name="loggers">the loggers to get the wrappers for</param>
        /// <returns>Lookup the wrapper objects for the loggers specified</returns>
        private static IMyLog[] WrapLoggers(ILogger[] loggers)
        {
            IMyLog[] results = new IMyLog[loggers.Length];
            for (int i = 0; i < loggers.Length; i++)
            {
                results[i] = WrapLogger(loggers[i]);
            }
            return results;
        }

        /// <summary>
        /// Method to create the <see cref="ILoggerWrapper"/> objects used by
        /// this manager.
        /// </summary>
        /// <param name="logger">The logger to wrap</param>
        /// <returns>The wrapper for the logger specified</returns>
        private static ILoggerWrapper WrapperCreationHandler(ILogger logger)
        {
            return new MyLogImpl(logger);
        }

        #endregion
    }
}

配置文件及数据库建表语句

配置文件

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
  </configSections>

  <log4net debug="true">

    <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender" >
      <file value="testlog.txt" />
      <appendToFile value="true" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="100" />
      <rollingStyle value="Date" />
      <datePattern value="yyyy-MM-dd"/>
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%-5level %date %logger - [%message] - %property{OrderNum} - %property{UserName %newline" />
      </layout>
    </appender>


    <!-- 写入SqlServer数据库 -->
    <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
      <!--缓存多少数据后写入数据库-->
      <bufferSize value="1" />

      <!--数据源连接类型-->
      <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />

      <!-- SQL连接字符串-->
      <!--<connectionString value="Data Source=JALENAPC;Initial Catalog=erp;integrated security=false;Persist Security Info=True;User ID=sa;Password=privacy" />-->
      <ConnectionStringName value="Kelun.Properties.Settings.ConnectionString"/>

      <!--SQL写入语句-->
      <commandText value="INSERT INTO [Loging] (logDate, logLevel, logLogger, logOrder, logMessage, logUser, logException) VALUES (@logDate, @logLevel, @logLogger, @logOrder, @logMessage ,@logUser, @logException)" />


      <!-- 写入参数 -->
      <parameter>
        <parameterName value="@logDate" />
        <dbType value="DateTime" />
        <layout type="log4net.Layout.RawTimeStampLayout" />
      </parameter>

      <parameter>
        <parameterName value="@logLevel" />
        <dbType value="String" />
        <size value="50" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%level" />
        </layout>
      </parameter>

      <parameter>
        <parameterName value="@logLogger" />
        <dbType value="String" />
        <size value="500" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%logger" />
        </layout>
      </parameter>

      <parameter>
        <parameterName value="@logOrder" />
        <dbType value="String" />
        <size value="20" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%property{OrderNum}" />
        </layout>
      </parameter>

      <parameter>
        <parameterName value="@logMessage" />
        <dbType value="String" />
        <size value="4000" />
        <!--<layout type="log4net.Layout.PatternLayout">-->
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%message" />
        </layout>
      </parameter>

      <parameter>
        <parameterName value="@logUser" />
        <dbType value="String" />
        <size value="50" />
        <layout type="log4net.Layout.PatternLayout">
          <conversionPattern value="%property{UserName}" />
        </layout>
      </parameter>

      <parameter>
        <parameterName value="@logException" />
        <dbType value="String" />
        <size value="2000" />
        <layout type="log4net.Layout.ExceptionLayout" />
      </parameter>

    </appender>

    <root>
      <level value="ALL"/>
      <appender-ref ref="AdoNetAppender" />
      <appender-ref ref="RollingFileAppender" />
    </root>

    <!--<logger name="MyLogger">
      <level value="ALL"/>
      <appender-ref ref="AdoNetAppender" />
    </logger>-->

  </log4net>
</configuration>

建表语句

create table Loging (
   id                   int                  identity,
   logDate              datetime             null,
   logLevel             varchar(50)          null,
   logLogger            varchar(500)         null,
   logOrder             varchar(20)          null,
   logMessage           text                 null,
   logUser              varchar(50)          null,
   logException         text                 null,
   constraint PK_LOGING primary key (id)
)

使用

AssemblyInfo.cs 增加如下内容

// 自动配置Log4Net
[assembly: log4net.Config.XmlConfigurator(ConfigFile = "log4net.config",Watch =true)]
private static readonly IMyLog logger = MyLogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

logger.Debug("只有消息的输出");
logger.Debug("日志内容", "OrderNum", "Jalena");
logger.Warn("Warn.......","OrderNum","Jalena");
logger.Error("Error...", "order..", "王麻子", new Exception("异常信息"));

最后来个数据库最终数据

C# SqlBulkCopy 批量写入数据

最近在写 一个项目,数据来自WebService,然后插入数据库。但在写入库的时候总是报错,所以记录下!

XMl转DataTable

/// <summary>
/// XMLNode转DataTable
/// </summary>
/// <param name="Data">XMLNode节点</param>
/// <param name="xpath">Xpath</param>
/// <returns></returns>
public static DataTable GetDataTable(XmlNode Data, string xpath)
{
	XmlNodeList xmlRows = Data.SelectNodes(xpath);
	if ((xmlRows == null) || (xmlRows.Count == 0)) return null;

	DataTable dt = new DataTable();
	foreach (XmlNode xmlColumn in xmlRows[0].ChildNodes)
	{
		if (xmlColumn.Name.ToLower() != "locators")
		{
			dt.Columns.Add(xmlColumn.Name);
		}
		else
		{
			foreach (XmlNode loc in xmlColumn.SelectSingleNode("locator").ChildNodes)
			{
				dt.Columns.Add(loc.Name);
			}
		}
	}

	foreach (XmlNode xmlRow in xmlRows)
	{
		DataRow dr = dt.NewRow();
		foreach (XmlNode xmlColumn in xmlRow.ChildNodes)
		{
			if (xmlColumn.Name.ToLower() != "locators")
			{
				dr[xmlColumn.Name] = xmlRow[xmlColumn.Name].InnerText;
			}
			else
			{
				foreach (XmlNode loc in xmlColumn.SelectSingleNode("locator").ChildNodes)
				{
					dr[loc.Name] = xmlRow.SelectSingleNode(@"locators/locator/" + loc.Name).InnerText;
				}
			}
		}
		dt.Rows.Add(dr);
	}
	dt.AcceptChanges();

	return dt;
}

获取XML所有的单据行封装成DataTable

/// <summary>
/// XMLNodeList 转 DataTable
/// </summary>
/// <param name="xmlNodeList">xmlNodeList</param>
/// <param name="xpath">xpath</param>
/// <returns></returns>
public static DataTable GetDataTable(XmlNodeList xmlNodeList,string xpath)
{
	if (xmlNodeList.Count == 0 || string.IsNullOrEmpty(xpath))
		return null;

	DataTable dataTable = new DataTable();
	foreach (XmlElement item in xmlNodeList[0].ChildNodes)
	{
		dataTable.Columns.Add(item.Name.ToLower());
	}

	foreach (XmlNode item in xmlNodeList)
	{
		DataRow dataRow = dataTable.NewRow();
		foreach (XmlElement xmlelement in item.ChildNodes)
		{
			dataRow[xmlelement.Name] = xmlelement.InnerText;
		}
		dataTable.Rows.Add(dataRow);
	}
	dataTable.AcceptChanges();
	return dataTable;
}

批量插入数据

使用SqlBulkCopy的时候,它不是按照DataColumn的名称来对照数据库列名称的,它是按照DataTable的顺序来对应数据库列。所以一定要注意。若DataTable的列与数据库列不对应,可以使用ColumnMappings来建立对应关系。

public static void Insert(DataTable dataTable,string TableName)
{
	try
	{
		using (SqlConnection Connection = new SqlConnection(connectionString))
		{
			if (Connection.State != ConnectionState.Open)
				Connection.Open();

			using(SqlTransaction transaction = Connection.BeginTransaction())
			{
				using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(Connection, SqlBulkCopyOptions.Default, transaction))
				{
					sqlBulkCopy.BatchSize = dataTable.Rows.Count;
					sqlBulkCopy.DestinationTableName = TableName;

					// 映射表关系
					foreach (DataColumn column in dataTable.Columns)
					{
						sqlBulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(column.ColumnName, column.ColumnName));
					}

					try
					{
						sqlBulkCopy.WriteToServer(dataTable);
						transaction.Commit();
					}
					catch (Exception)
					{
						transaction.Rollback();
						throw;
					}
					finally
					{
						Connection.Close();
					}
				}
			}
		}
	}
	catch (Exception)
	{
		throw;
	}
}

WebService 测试

引入Maven包

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
		 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
		 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>Jalena</groupId>
	<artifactId>WebServiceTest</artifactId>
	<version>1.0-SNAPSHOT</version>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.6.1</version>
				<configuration>
					<source>1.8</source>
					<target>1.8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>

	<dependencies>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
		</dependency>

		<dependency>
			<groupId>org.apache.axis</groupId>
			<artifactId>axis</artifactId>
			<version>1.4</version>
		</dependency>

		<dependency>
			<groupId>org.apache.axis</groupId>
			<artifactId>axis-jaxrpc</artifactId>
			<version>1.4</version>
		</dependency>

		<dependency>
			<groupId>wsdl4j</groupId>
			<artifactId>wsdl4j</artifactId>
			<version>1.6.3</version>
		</dependency>

		<dependency>
			<groupId>dom4j</groupId>
			<artifactId>dom4j</artifactId>
			<version>1.6.1</version>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.37</version>
		</dependency>

		<dependency>
			<groupId>javax</groupId>
			<artifactId>javaee-api</artifactId>
			<version>7.0</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>commons-logging</groupId>
			<artifactId>commons-logging</artifactId>
			<version>1.2</version>
		</dependency>

		<dependency>
			<groupId>commons-discovery</groupId>
			<artifactId>commons-discovery</artifactId>
			<version>0.5</version>
			<exclusions>
				<exclusion>
					<artifactId>commons-logging</artifactId>
					<groupId>commons-logging</groupId>
				</exclusion>
			</exclusions>
		</dependency>

		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-slf4j-impl</artifactId>
			<version>2.9.1</version>
		</dependency>


	</dependencies>

</project>

创建接口调用实例

测试类

import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import xjklAxiService.XjklAxiServiceServiceLocator;
import xjklAxiService.XjklAxiService_PortType;

import javax.xml.rpc.ServiceException;
import java.rmi.RemoteException;

public class XjklAxiServiceServiceTest {

	private final Logger logger = LoggerFactory.getLogger(getClass());
	private XjklAxiService_PortType service;

	@Before
	public void init(){
		XjklAxiServiceServiceLocator locator = new XjklAxiServiceServiceLocator();
		try {
			service = locator.getxjklAxiService();
		} catch (ServiceException e) {
			logger.warn(e.getMessage());
		}
	}

	@Test
	public void testQry(){
		try {
			String result = service.YJstockviewQry("##2018-03-01#2018-03-15#");
			logger.debug(result);
		} catch (RemoteException e) {
			logger.warn(e.getMessage());
		}
	}
}

Log4j2 配置

<?xml version="1.0" encoding="UTF-8"?>

<configuration debug="off" status="INFO"> <!-- 这个status是控制系统信息的输出级别 -->
	<Properties>
		<Property name="path">${log4j:configParentLocation}/../../logs</Property>
		<Property name="pattern" value="%d{DEFAULT} [%-5level] %c{1.}.%M()/%L - %msg%xEx%n"/>
	</Properties>

	<Appenders>
		<Console name="Console" target="SYSTEM_OUT">	<!-- 将日志信息从控制台输出 -->
			<ThresholdFilter level="trace" onMatch="ACCEPT" onMismatch="DENY" />
			<PatternLayout pattern="%highlight{${pattern}}" />
		</Console>

		<File name="debug" fileName="${path}/log.log" append="true">	<!-- 将日志信息写入日志文件 -->
			<Filters>
				<!--控制台只输出level及以上级别的信息(onMatch),其他的直接拒绝(onMismatch) -->
				<ThresholdFilter level="debug" onMatch="ACCEPT" onMismatch="DENY" />
			</Filters>
			<PatternLayout pattern="${pattern}" />
		</File>

		<RollingFile name="warn" fileName="${path}/warn.log" filePattern="${path}/warn-%d{yyyy-MM-dd}_%i.log">
			<PatternLayout pattern="${pattern}" />
			<SizeBasedTriggeringPolicy size="50MB" />
			<Filters>
				<!--控制台只输出level及以上级别的信息(onMatch),其他的直接拒绝(onMismatch) -->
				<ThresholdFilter level="warn" onMatch="ACCEPT" onMismatch="DENY" />
			</Filters>
		</RollingFile>
	</Appenders>

	<Loggers>
		<Logger name="TestWebService" level="TRACE"/>
		<Root level="debug">
			<AppenderRef ref="Console" />   <!-- 仅有上述的Appenders配置还不够,这里还不能少,少了就不会在控制台输出 -->
			<AppenderRef ref="warn" />
			<AppenderRef ref="debug" />  <!-- 仅有上述的Appenders配置还不够,这里还不能少,少了就不会写入文件,但会创建文件 -->
		</Root>
	</Loggers>

</configuration>