利用Bulk Insert将Excel中的大批量数据入库
原创，转载请注明出处。//author:MeBack_黯然消魂
小弟摸索了二天，终于搞定，不敢独享。
第一次使用微软的SqlBulkCopy，其实它也是调用Bulk insert的，只不过微软封装了它，不过它不能指定映射文件，常出错，会说类型不匹配。一咬牙决定自己动手做。以下是具体的操作：
第一步：使用Bcp工具先生成一个XML格式化文档，它能映射欲插入的数据与数据库的表的对应关系。有关Bcp工具的使用请在CMD中键入Bcp /?。
在CMD中敲入命令： bcp 数据库名.表名 format nul -c -x -f 生成文档的路径 -T 。 先存为：dataformat.xml。
如果你不想自己敲，我已经写好C#代码：
/// <summary>
/// 创建BulkInsert，XML格式化文件
/// </summary>
/// <param name="server">服务器名</param>
/// <param name="user">用户名</param>
/// <param name="pwd">密码</param>
/// <param name="databaseName">数据库名</param>
/// <param name="tableName">表名</param>
/// <param name="xmlFormatFilePath">输入文件的路径</param>
/// <returns>Boolern</returns>
public static bool BuildBulkInsertXMLFile(string server, string user, string pwd, string databaseName, string tableName, string xmlFormatFilePath)
{
string cmd = Null;
cmd = string.Format("bcp {0}..{1} format nul -c -x -f \"{2}\" -S \"{3}\" -U \"{4}\" -P \"{5}\"", dataBaseName, tableName, xmlFormatFilePath, server, user, pwd);
TextReader tr = Null;
TextWriter tw = Null;
bool IsSuccess = false;
try
{
Unity.RunCommand(cmd);
IsSuccess = File.Exists(xmlFormatFilePath);
tr = new StreamReader(xmlFormatFilePath, Encoding.GetEncoding("gb2312"));
cmd = tr.ReadToEnd();
cmd = cmd.Replace("=\""", "=\",\"");/*分隔符默认为\t 容易出错,我们用逗号分开，当然这只是例子，用一种少见的字符作分隔符,比如韩国日本的字符。就不会出错了*/
tr.Close();
tr.Dispose();
tw = new StreamWriter(xmlFormatFilePath, false, Encoding.GetEncoding("gb2312"));
tw.Write(cmd);
tw.Close();
tw.Dispose();
return IsSuccess && cmd.Length > 0;
}
catch (Exception)
{
if (tw != null)
tw.Dispose();
if (tr != null)
tr.Dispose();
cmd = null;
return IsSuccess;
}
}
/// <summary>
/// 执行CMD命令
/// </summary>
/// <param name="sCommand"></param>
/// <returns></returns>
public static bool RunCommand(string sCommand)
{
//调用系统CMD，篇幅有限这部分代码不用写了，如果需要我补上，
}
第二步：将Excel的数据读入写到一个文件
因为Bulk插入的数据格式写CVS类似，所以我们自定义将它写给我们的dataformat.xml格式，
/// <summary>
/// 创建BulkInsert的数据文件
/// </summary>
/// <param name="dt">欲写值的表</param>
/// <param name="dataPath">储存的路径</param>
/// <returns>Boolern</returns>
public static bool BuildBulkDataFile(DataTable dt, string dataPath)
{
TextWriter tw = null;
try
{
if (null != dt)
return false;
else if (dt.Rows.Count < 1)
{
return false;
}
else
{
tw = new StreamWriter(dataPath, false, Encoding.GetEncoding("gb2312"));
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
if (c == 0)
tw.Write(dt.Rows[i][c]);
else
tw.Write("," + dt.Rows[i][c]);
tw.Flush();
}
tw.Write(tw.NewLine);
tw.Flush();
}
tw.Flush();
tw.Close();
tw.Dispose();
}
return File.Exists(dataPath);
}
catch (Exception)
{
if (tw != null)
{
tw.Dispose();
}
return false;
}
}
OK,现在我们有格式文件与数据文件，就可以调用Bulk Insert命令了
第三步：
/// <summary>
/// BulkInsert插入大批量数据
/// </summary>
/// <param name="dataBaseName">数据库名</param>
/// <param name="insertTableName">欲插入的表名</param>
/// <param name="xmlFormatFilePath">表的XMl格式化文件</param>
/// <param name="dataFilePath">数据文件</param>
/// <returns>Boolern</returns>
public static bool DataTranByBulkInsert(string dataBaseName, string insertTableName, string xmlFormatFilePath, string dataFilePath)
{
return SqlHelper.ExecuteSql(string.Format("BULK INSERT {0}..{1} FROM '{2}' with(FORMATFILE='{3}')", dataBaseName, insertTableName, dataFilePath, xmlFormatFilePath)) > 0; //SqlHelper为数据操作类，就这执行这条Sql语句，
}
OK,大功告成，据Microsoft称，Bulk Insert插入几万条数据就几秒钟，经测试一万条数据花了5秒。
