SqlBulkCopy类数据导入(自己测试例子)

来源：互联网发布：考驾照模拟软件编辑：程序博客网时间：2024/05/29 18:11

public static readonly string ConnData = "Data Source=192.168.0.100;Initial Catalog=DBName;User ID=sa;Password=123456";

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("ScheduleKey", typeof(Guid));
                dt.Columns.Add("AdminUserKey", typeof(Guid));
                dt.Columns.Add("ScheduleRuleKey", typeof(Guid));
                dt.Columns.Add("eBayProfileKey", typeof(Guid));
                dt.Columns.Add("LaunchTimeSite", typeof(DateTime));
                dt.Columns.Add("CreationDate", typeof(DateTime));
                for (int i = 0; i < 7000; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr["ScheduleKey"] = Guid.NewGuid();
                    dr["AdminUserKey"] = Guid.NewGuid();
                    dr["ScheduleRuleKey"] = Guid.NewGuid();
                    dr["eBayProfileKey"] = Guid.NewGuid();
                    dr["LaunchTimeSite"] = DateTime.Now;
                    dr["CreationDate"] = DateTime.Now;
                    dt.Rows.Add(dr);
                }

                SqlBulkCopy(dt);
            }
        }

        #region   批量插入数据到数据库
        DateTime startTime;
        private bool SqlBulkCopy(DataTable dt)
        {

            try
            {
                startTime = DateTime.Now;
                //数据批量导入sqlserver,创建实例     SqlBulkCopyOptions.UseInternalTransaction采用事务   复制失败自动回滚
                System.Data.SqlClient.SqlBulkCopy sqlbulk = new System.Data.SqlClient.SqlBulkCopy(ConnData, SqlBulkCopyOptions.UseInternalTransaction);
                sqlbulk.SqlRowsCopied +=
                new SqlRowsCopiedEventHandler(OnRowsCopied); //订阅复制完成后的方法,参数是 sqlbulk.NotifyAfter的值
                sqlbulk.NotifyAfter = dt.Rows.Count;

                //目标数据库表名
                sqlbulk.DestinationTableName = "pa_ScheduleShadow";
                //数据集字段索引与数据库字段索引映射
                sqlbulk.ColumnMappings.Add(0, "ScheduleKey");
                sqlbulk.ColumnMappings.Add(1, "AdminUserKey");
                sqlbulk.ColumnMappings.Add(2, "ScheduleRuleKey");
                sqlbulk.ColumnMappings.Add(3, "eBayProfileKey");
                sqlbulk.ColumnMappings.Add(4, "LaunchTimeSite");
                sqlbulk.ColumnMappings.Add(5, "CreationDate");
                //导入
                sqlbulk.WriteToServer(dt);
                sqlbulk.Close();
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                dt.Dispose();
            }
        }

        //复制完成后的处理事件
        private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args)
        {
            lblCounter.Text += args.RowsCopied.ToString() + " 条记录已导入";
            TimeSpan copyTime = DateTime.Now - startTime;
            lblCounter.Text += "  花费时间:" + copyTime.Seconds.ToString() + "." +
            copyTime.Milliseconds.ToString() + " 秒";
        }
        #endregion