Author: Jalena

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>

Visual Studio Uninstaller

最近环境有问题,删除个vs2013总是不干净,原来微软是提供了专用卸载工具的!

This executable is designed to clean up and delete Preview, RC and final releases of Visual Studio 2013 and Visual Studio 2015, as well as early previews of Visual Studio 2017. It is designed to be used as a final resort to clean up a system of remaining artifacts from a non-successful installation, instead of having to reimage the machine.

WARNING: running this application may stop earlier remaining installations of Visual Studio 2012 and earlier from working, because Visual Studio 2012 and below share MSI upgrade code with Visual Studio 2013 and above.

Download: https://github.com/Microsoft/VisualStudioUninstaller/releases