SQLBulkCopy使用实例--读取Excel写入数据库/将 Excel 文件转成 DataTable

MS SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表可以在不同服务器上）。

SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。相比其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句）， SqlBulkCopy 提供明显的性能优势。（经测试数据量越大性能越明显，至少是批量insert语句的25倍以上。）

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是数据源不限于 SQL Server，可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。

SqlBulkcopy对象主要作为作为了提高批量插入的效率，所以在默认情况下批量数据的插入并不会引发insert触发器的触发。这可以通过使用SqlBulkCopy (String, SqlBulkCopyOptions) 指定enum SqlBulkCopyOptions为FireTriggers解决。读取Excel写入数据库/将 Excel 文件转成 DataTable

注意：使用SqlBulkcopy时在inserted表中是有多条记录的，且一次调用SqlBulkcopy只会有一次触发器的运行。

实例--读取Excel写入数据库/将 Excel 文件转成 DataTable  

源代码：

#region 读取Excel

        /// <summary>
        /// 将 Excel 文件转成 DataTable 后,再把 DataTable中的数据写入表Products
        /// </summary>
        /// <param name="serverMapPathExcelAndFileName"></param>
        /// <param name="excelFileRid"></param>
        /// <returns></returns>
        public static int WriteExcelToDataBase(string excelFileName)
        {
            int rowsCount = 0;
            OleDbConnection objConn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFileName+ ";" + "Extended Properties=Excel 8.0;");
            objConn.Open();
            try
            {
                DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string sheetName = string.Empty;
                for (int j = 0; j < schemaTable.Rows.Count; j++)
                {
                    sheetName = schemaTable.Rows[j][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
                    DataTable excelDataTable = DbHelperSQL.ExcelToDataTable(excelFileName, sheetName, true);
                    if (excelDataTable.Columns.Count > 1)
                    {
                        　 SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
                            sqlbulkcopy.DestinationTableName = "Products";//数据库中的表名

                            sqlbulkcopy.WriteToServer(excelDataTable);
                            sqlbulkcopy.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                objConn.Close();
                objConn.Dispose();
            }
            return rowsCount;

        }

 /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="Path"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataSet ExcelToDS(string Path, string sheetName)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";  //HDR=Yes;
            DataSet ds = null;
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                OleDbDataAdapter myCommand = null;
                try
                {
                    conn.Open();
                    string strExcel = "";
                    strExcel = "select * from [" + sheetName + "$]";
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    ds = new DataSet();
                    myCommand.Fill(ds, "table1");
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    myCommand.Dispose();
                    conn.Close();
                }
                return ds;
            }
        }

        /// <summary>
        /// 将 Excel 文件转成 DataTable
        /// </summary>
        /// <param name="serverMapPathExcel">Excel文件及其路径</param>
        /// <param name="strSheetName">工作表名,如:Sheet1</param>
        /// <param name="isTitleOrDataOfFirstRow">True 第一行是标题,False 第一行是数据</param>
        /// <returns>DataTable</returns>
        public static DataTable ExcelToDataTable(string serverMapPathExcel, string strSheetName, bool isTitleOrDataOfFirstRow)
        {

            string HDR = string.Empty;//如果第一行是数据而不是标题的话, 应该写: "HDR=No;"
            if (isTitleOrDataOfFirstRow)
            {
                HDR = "YES";//第一行是标题
            }
            else
            {
                HDR = "NO";//第一行是数据
            }
            //源的定义
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + serverMapPathExcel + ";" + "Extended Properties='Excel 8.0;HDR=" + HDR + ";IMEX=1';";

            //Sql语句
            //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法
            string strExcel = "select * from   [" + strSheetName + "]";
            //定义存放的数据表
            DataSet ds = new DataSet();

            //连接数据源
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                try
                {
                    conn.Open();
                    //适配到数据源
                    OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn);
                    
                    adapter.Fill(ds, strSheetName);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return ds.Tables[strSheetName];
        }
        #endregion

      使用SqlBulkCopy类进行数据插入其原理是采用了SQL Server的BCP协议进行数据的批量复制。通常 我们先要建好一个DataTable（最好是通过DataAdapter来灌数据得到，因为这样出来的DataTable就已经有跟数据表相同的列定义， 可以免去之后Mapping Column的步骤），把要插入的数据加进这个DataTable中，然后用SqlBulkCopy的实例来插入到数据库中。经过测 试，SqlBulkCopy方法比直接用Sql语句插入数据的效率高出将近25倍。