将execl里的数据批量导入数据库

本文将采用NPOI插件来读取execl文件里的数据，将数据加载到内存中的DataTable中

 /// <summary>
        /// 将Excel转换为DataTable
        /// </summary>
        /// <param name="extension"></param>
        /// <param name="stream"></param>
        /// <returns></returns>
        public static System.Data.DataTable GetExcelDataTable(string extension, System.IO.Stream stream) {
            NPOI.SS.UserModel.IWorkbook workBook;
            if (extension == ".xls")
                workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(stream);
            else if (extension == ".xlsx")
                workBook = new NPOI.XSSF.UserModel.XSSFWorkbook(stream);
            else
                throw new Exception("文件格式出错!");
            var sheet = workBook.GetSheetAt(0);
            var row = sheet.GetRow(0);
            System.Data.DataTable dt = new System.Data.DataTable(sheet.SheetName);
            foreach (var i in row) {
                var name = i.StringCellValue;
                if (string.IsNullOrEmpty(name))
                    break;
                dt.Columns.Add(name);
            }
            dt.PrimaryKey = new DataColumn[1] { dt.Columns[0] };
            var rowCount = sheet.LastRowNum + 1;
            for (var i = 1; i < rowCount; ++i) {
                row = sheet.GetRow(i);
                string[] cells = new string[dt.Columns.Count];
                for (var j = 0; j < cells.Length; ++j) {
                    var cell = row.GetCell(j);
                    if (cell != null)
                        cells[j] = cell.ToString();
                }
                if (dt.Rows.Find(cells[0]) == null) {
                    dt.Rows.Add(cells);
                }
            }
            return dt;
        }

上面的方法中，把execl表格第一列数据设为DataTable的主键，同时使用dt.Rows.Find()方法去除重复项，主要是为了避免后面合并数据发生意外

加载到内存中的DataTable之后，在业务层处理一下，把execl文件里的第一行数据(即DataTable中的列名)修改成后面创建的临时表的列名一一对应

public static async Task BatchImport(System.Data.DataTable dt) {
            dt.Columns["商品id"].ColumnName = "GOODSID";
            dt.Columns["商品名称"].ColumnName = "GOODSNAME";
            dt.Columns["商品主图"].ColumnName = "GOODSMAINIMG";
            dt.Columns["商品详情页链接地址"].ColumnName = "GOODSDETAIL";
            dt.Columns["商品一级类目"].ColumnName = "GOODSLEVEL";
            dt.Columns["推广链接"].ColumnName = "SHORTLINK2";
            dt.Columns["商品价格(单位：元)"].ColumnName = "GOODSPRICE";
            dt.Columns["商品月销量"].ColumnName = "GOODSMONTHSALE";
            dt.Columns["收入比率(%)"].ColumnName = "GOODSINCOME";
            dt.Columns["佣金"].ColumnName = "GOODSCOMMISSION";
            dt.Columns["卖家名称"].ColumnName = "SELLER";
            dt.Columns["卖家id"].ColumnName = "SELLERID";
            dt.Columns["店铺名称"].ColumnName = "STORENAME";
            dt.Columns["平台类型"].ColumnName = "PLATFORMTYPE";
            dt.Columns["优惠券id"].ColumnName = "COUPONID";
            dt.Columns["优惠券总量"].ColumnName = "COUPONTOTAL";
            dt.Columns["优惠券剩余量"].ColumnName = "COUPONSURPLUS";
            dt.Columns["优惠券面额"].ColumnName = "COUPONCONTENT";
            dt.Columns["优惠券开始时间"].ColumnName = "CONPONSTRATETIME";
            dt.Columns["优惠券结束时间"].ColumnName = "COUPONENDTIME";
            dt.Columns["优惠券链接"].ColumnName = "COUPONSHORTLINK";
            dt.Columns["商品优惠券推广链接"].ColumnName = "COUPONLINK";
            using (var con =await mssql.tbk.GetConnection()) {//一下执行的方法会放后面
                await mssql.tbk.CreateTmepTable(con);//创建临时表
                await mssql.tbk.BatchImport("#TMD", dt,con);//将DataTable中的数据导入到临时表(#TMD)
                await mssql.tbk.Combine(con);
                await mssql.tbk.DropTempTable(con);
            }  
        }

下面开始创建临时表，临时表的名字命名必须以#开头，结构要和execl文件的一模一样，字段不能多也不能少，否则导入数据时某些数据丢失

        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        public static async Task CreateTmepTable(sqlClient.SqlConnection<System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlParameter> con) {
            await con.ExecuteNonQueryAsync("CREATE TABLE #TMD([GOODSID] BIGINT NOT NULL PRIMARY KEY,[GOODSNAME][varchar](250) NOT NULL,[GOODSMAINIMG][varchar](1024) NOT NULL,[GOODSDETAIL][varchar](1024) NOT NULL,[GOODSLEVEL][varchar](50) NOT NULL,[SHORTLINK2][varchar](1024) NOT NULL,[GOODSPRICE][varchar](100) NOT NULL,[GOODSMONTHSALE][varchar](100) NOT NULL,[GOODSINCOME][varchar](100) NOT NULL,[GOODSCOMMISSION][varchar](100) NOT NULL,[SELLER][varchar](100) NOT NULL,[SELLERID][varchar](100) NOT NULL,[STORENAME][varchar](100) NOT NULL,[PLATFORMTYPE][varchar](50) NOT NULL,[COUPONID][varchar](100) NOT NULL,[COUPONTOTAL][varchar](100) NOT NULL,[COUPONSURPLUS][varchar](100) NOT NULL,[COUPONCONTENT][varchar](100) NOT NULL,[CONPONSTRATETIME][varchar](100) NOT NULL,[COUPONENDTIME][varchar](100) NOT NULL,[COUPONSHORTLINK][varchar](1024) NOT NULL,[COUPONLINK][varchar](1024) NOT NULL)");
        }

成功创建临时表之后，开始将DataTable数据导入临时表

/// <summary>
        /// 将execl数据批量导入临时表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static async Task BatchImport(string tableName,System.Data.DataTable dt,  sqlClient.SqlConnection<System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlParameter> con) {
            await con.InsertBulkCopyAsync(tableName,dt,System.Data.SqlClient.SqlBulkCopyOptions.Default);
        }

重点来了，重点来了，重点来了，如何将临时表的数据写到数据库主表(主表结构与临时表结构可相同可不相同)里呢？请看代码

/// <summary>
        /// 合并临时表数据到数据库主表
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        public static async Task Combine(sqlClient.SqlConnection<System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlParameter> con) {
            await con.ExecuteNonQueryAsync("MERGE INTO TMD ts USING #TMD  t ON ts.GOODSID=t.GOODSID WHEN MATCHED AND ts.GOODSID=t.GOODSID THEN UPDATE SET ts.GOODSNAME = t.GOODSNAME, ts.GOODSMAINIMG = t.GOODSMAINIMG, ts.GOODSDETAIL = t.GOODSDETAIL, ts.GOODSLEVEL = t.GOODSLEVEL, ts.SHORTLINK2 = t.SHORTLINK2, ts.GOODSPRICE = t.GOODSPRICE, ts.GOODSMONTHSALE = t.GOODSMONTHSALE, ts.GOODSINCOME = t.GOODSINCOME, ts.GOODSCOMMISSION = t.GOODSCOMMISSION, ts.SELLER = t.SELLER, ts.SELLERID = t.SELLERID, ts.STORENAME = t.STORENAME, ts.PLATFORMTYPE = t.PLATFORMTYPE, ts.COUPONID = t.COUPONID, ts.COUPONTOTAL = t.COUPONTOTAL, ts.COUPONSURPLUS = t.COUPONSURPLUS, ts.COUPONCONTENT = t.COUPONCONTENT, ts.CONPONSTRATETIME = t.CONPONSTRATETIME, ts.COUPONENDTIME = t.COUPONENDTIME, ts.COUPONSHORTLINK = t.COUPONSHORTLINK, ts.COUPONLINK = t.COUPONLINK WHEN NOT MATCHED THEN INSERT VALUES(t.GOODSID, t.GOODSNAME, t.GOODSMAINIMG, t.GOODSDETAIL, t.GOODSLEVEL, t.SHORTLINK2, t.GOODSPRICE, t.GOODSMONTHSALE, t.GOODSINCOME, t.GOODSCOMMISSION, t.SELLER, t.SELLERID, t.STORENAME, t.PLATFORMTYPE, t.COUPONID, t.COUPONTOTAL, t.COUPONSURPLUS, t.COUPONCONTENT, t.CONPONSTRATETIME, t.COUPONENDTIME, t.COUPONSHORTLINK, t.COUPONLINK,0);");
        }

上面方法中使用了merge into和using，Merge和using搭配用于特别是BI上数据统计和分析上 比如 要求子表中没有的数据那么父表中就要删除对应的数据 保证子表和父表的数据对应 如果按照常规的做法是 跑个作业 然后通过游标/表值函数/临时表等等循环的获取数据然后更新父表  这样是很浪费效率的  这时Merge派上用场了

merge的语法：

merge into 主表 T
using #临时表 D on T.关联字段=D.关联字段
when matched 
    then update set T.字段=D.字段。。。。。。。。。。。
when not matched  --为not matched时 不能为update(没有匹配成功 当然不能update了)
    then insert (D字段。。。)values(D.字段);

想了解更详情请自行查找

合并完数据之后删除临时表（临时表会在数据库连接断开时清除临时表，这一步也可省）

 /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="con"></param>
        /// <returns></returns>
        public static async Task DropTempTable(sqlClient.SqlConnection<System.Data.SqlClient.SqlConnection, System.Data.SqlClient.SqlParameter> con) {
            await con.ExecuteNonQueryAsync("drop table #TAOBAO_SELECTED");
        }

写到这里基本搞定批量导入数据的问题了，如果你有更好的办法实现几万行的execl文件导入数据库，请留言，指导指导下。