[.Net码农]BulkCopytoSQL做批量导入
来源:互联网 发布:网络模式nat 编辑:程序博客网 时间:2024/06/03 10:42
//批量导入功能 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; }