最近在写 一个项目，数据来自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;
}
}
