现有一XML文件,其中包含443676条信息,主要用于记录点的XY坐标。XML文件如下图所示:
现在需要将XML文件中的坐标信息导入SqlServer,数据表[TPoint]如下图所示,其中Id为自增主键,X和Y为float类型字段:
实现代码如下:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Xml;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 导入数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnImport_Click(object sender, EventArgs e)
{
DataTable dataTable = GetDataTable(@"C:\Users\DSF\Desktop\point.xml");
Insert(dataTable);
}
/// <summary>
/// 读取XML,生成DataTable
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
private DataTable GetDataTable(string filePath)
{
// 加载XML
XmlDocument document = new XmlDocument();
document.Load(filePath);
// 获取子节点
XmlNode root = document.SelectSingleNode("root");
XmlNodeList childs = root.ChildNodes;
// 创建数据列
DataTable dataTable = new DataTable("TPoint");
dataTable.Columns.Add("Id", typeof(int));
dataTable.Columns.Add("X", typeof(double));
dataTable.Columns.Add("Y", typeof(double));
// 读取XY坐标
foreach (XmlNode child in childs)
{
DataRow dataRow = dataTable.NewRow();
dataRow[1] = double.Parse(child.SelectSingleNode("x").InnerText);
dataRow[2] = double.Parse(child.SelectSingleNode("y").InnerText);
dataTable.Rows.Add(dataRow);
}
return dataTable;
}
/// <summary>
/// 批量插入数据库
/// </summary>
/// <param name="dataTable"></param>
private void Insert(DataTable dataTable)
{
using (SqlBulkCopy bulk = new SqlBulkCopy("Data Source=DSF-PC;Initial Catalog=Test;User ID=sa;Password=123456"))
{
bulk.DestinationTableName = dataTable.TableName;
bulk.BatchSize = dataTable.Rows.Count;
bulk.WriteToServer(dataTable);
}
}
}
}
批量插入数据时应选用SqlBulkCopy,其速度较快,程序运行结果如下图所示: