利用Bulk Insert将Excel中的大批量数据入库

原创，转载请注明出处。
小弟摸索了二天，终于搞定，不敢独享。

第一次使用微软的SqlBulkCopy，其实它也是调用Bulk insert的，只不过微软封装了它，不过它不能指定映射文件，常出错，会说类型不匹配。一咬牙决定自己动手做。以下是具体的操作：

第一步：使用Bcp工具先生成一个XML格式化文档，它能映射欲插入的数据与数据库的表的对应关系。有关Bcp工具的使用请在CMD中键入Bcp /?。

    在CMD中敲入命令： bcp  数据库名.表名 format nul -c -x -f  生成文档的路径   -T 。 先存为：dataformat.xml。
 如果你不想自己敲，我已经写好C#代码：
 //author:MeBack_黯然消魂

/// <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\"", "=\",\"");/*分隔符默认为\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秒。

注意：Web服务器与SqlServer必须在同一机器上，如不在同一机器，须设共享文件夹解决。

转载于:https://www.cnblogs.com/MeBack/archive/2007/10/31/944478.html