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;
	}
}