Biegral Blog

//批量导入功能
    public string BulkCopytoSQL(DataTable DataSoure, string dataTableName )
    {
        string  result = "成功";
        try
        {
            //额外添加列
            string workersNo = Request.Cookies["EmployeeNo"].Value;
            DataSoure.Columns.Add(new DataColumn("操作工号"));
            //DataSoure.Columns.Add(new DataColumn("录入日期"));

            //DataSoure.Rows.Count;
            for (int i = 0; i < DataSoure.Rows.Count; i++)
            {
                DataSoure.Rows[i]["操作工号"] = workersNo;
                //DataSoure.Rows[i]["录入日期"] = DateTime.Now.Date.ToString("yyyy-MM-dd HH:mm:ss");
            }


            //GridView GridViewOutPut = new GridView();
            //GridViewOutPut.DataSource = DataSoure;
            //GridViewOutPut.DataBind();
            ////换成自己的excel导出格式
            //Export(GridViewOutPut, "application/ms-excel", "测试批量导入的脚本", "", "测试批量导入的脚本" + ".xls", "", "", this);

            //声明数据库连接
            SqlConnection conn = new SqlConnection(new DBOperator().ConnectString);

            conn.Open();
            //声明SqlBulkCopy ,using释放非托管资源
            using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn))
            {
                //一次批量的插入的数据量
                sqlBC.BatchSize = 1000;
                //超时之前操作完成所允许的秒数，如果超时则事务不会提交 ，数据将回滚，所有已复制的行都会从目标表中移除
                //sqlBC.BulkCopyTimeout = 60;

                //設定 NotifyAfter 属性，以便在每插入10000 条数据时，呼叫相应事件。  
                //sqlBC.NotifyAfter = 10000;
                //sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); 

                //设置要批量写入的表
                sqlBC.DestinationTableName = dataTableName;

                //自定义的datatable和数据库的字段进行对应
                //sqlBC.ColumnMappings.Add("id", "tel");
                //sqlBC.ColumnMappings.Add("name", "neirong"); 
                
                sqlBC.ColumnMappings.Add("B8码", "MeterNo");
                sqlBC.ColumnMappings.Add("用户号", "ClientNo");
                sqlBC.ColumnMappings.Add("表册号", "BookNo");
                sqlBC.ColumnMappings.Add("抄表顺序号", "SequenceNo");
                sqlBC.ColumnMappings.Add("操作工号", "ImportEmployee");
                
                //批量写入
                sqlBC.WriteToServer(DataSoure);
            }
            conn.Close();

            
        }
        catch (Exception e)
        {
            result = "失败" + e.ToString();
        }

        return result;


    }