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

自定义梅林固件Dnsmasq

360这个孙子东西,真的太烦了。。

家里的大人又搞不懂,总是莫名其叫的装上这些恶心的软件!

那么就从源头来干掉这烦人的东西吧!!

调整路由器配置

  1. 系统管理 -> 系统设置
  2. 启用 Enable JFFS custom scripts and configs 选项
  3. 启用 启用 SSH 选项
  4. 前往 智能网络卫士 -> DNS Filtering
  5. 禁用 Enable DNS-based Filtering

调整DHCP服务器配置

  1. 内部网络(LAN) -> DHCP 服务器
  2. 设置 DNS Server 1 为你路由器的地址
  3. 如果 Advertise router's IP in addition to user-specified DNS 启用,所有自定义的DNS地址将附加到客户端租用地址时给予的地址列表中。因此,如果你希望能够解析名称而不指定路由器地址作为名称服务器来执行,请关闭此设置。
  4. 关闭 Forward local domain queries to upstream DNS 以防止您的私人DNS解析请求传递到Inertnet。

编辑dnsmasq配置选项

连接到您的路由器

# 为dnsmasq创建配置文件
touch /jffs/configs/dnsmasq.conf.add

# 编辑配置文件
vi /jffs/configs/dnsmasq.conf.add

# 写入如下内容
address=/.360.cn/127.0.0.1

最后一步

# 重启dnsmasq
service restart_dnsmasq

写在最后

梅林WIKI:https://github.com/RMerl/asuswrt-merlin/wiki

find / -name dnsmasq.conf*

Win10 Hyper-V Centos7 网络配置

创建虚拟网络

Hyper-V的网络支持三种不同的模式,这里选用外部模式,这样可以得到一个独立的IP分配。

创建外部虚拟网卡

配置网络

为配置文件增加HWADDR(mac地址),并将ONBOOT设置为yes。

# 查看网卡mac地址
ip link

# 编辑配置
# vi /etc/sysconfig/network-scripts/ifcfg-eth0
TYPE="Ethernet"
HWADDR=00:15:5d:c7:96:02
BROWSER_ONLY="no"
BOOTPROTO="dhcp"
DEFROUTE="yes"
IPV4_FAILURE_FATAL="no"
IPV6INIT="no"
IPV6_AUTOCONF="yes"
IPV6_DEFROUTE="yes"
IPV6_FAILURE_FATAL="no"
IPV6_ADDR_GEN_MODE="stable-privacy"
NAME="eth0"
UUID="becabf8b-fc8d-40ea-a3b9-0cecfbb4a9b7"
DEVICE="eth0"
ONBOOT="yes"

重启网络服务

systemctl restart network
systemctl status network