SqlBulkCopy 结合事务 批量保存数据到数据库

        /// <summary>
        /// 保存到数据库
        /// </summary>
        /// <param name="mDCCampaignList"></param>
        /// <param name="mDCAdgroupList"></param>
        /// <param name="mDCKeywordList"></param>
        private void SaveToServer(IList<DCCampaign> mDCCampaignList, IList<DCADGroup> mDCAdgroupList, IList<DCKeyword> mDCKeywordList)
        {
            using (SqlConnection conn = new DBHelper().GetSqlConnection())
            {
                conn.Open();
                using (SqlTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
                        {
                            //設置一个批，写入多少条记录
                            bulkCopy.BatchSize = 10000;
                            bulkCopy.BulkCopyTimeout = 60;
                            ////設置 NotifyAfter 属性，以便在每拷贝 10000 条记录至数据表後，呼叫事件处理函数    
                            //bulkCopy.NotifyAfter = 10000;
                            //bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                            //設置要写入的数据库
                            bulkCopy.DestinationTableName = "dbo.DC_Campaign";
                            DataTable dt = mDCCampaignList.ToDataTable();
                            bulkCopy.WriteToServer(dt);
                        }

                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
                        {
                            //設置一个批，写入多少条记录
                            bulkCopy.BatchSize = 10000;
                            bulkCopy.BulkCopyTimeout = 60;
                            ////設置 NotifyAfter 属性，以便在每拷贝 10000 条记录至数据表後，呼叫事件处理函数    
                            //bulkCopy.NotifyAfter = 10000;
                            //bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                            //設置要写入的数据库
                            bulkCopy.DestinationTableName = "dbo.DC_ADGroup";
                            DataTable dt = mDCAdgroupList.ToDataTable();
                            bulkCopy.WriteToServer(dt);
                        }

                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran))
                        {
                            //設置一个批，写入多少条记录
                            bulkCopy.BatchSize = 100000;
                            bulkCopy.BulkCopyTimeout = 60;
                            ////設置 NotifyAfter 属性，以便在每拷贝 10000 条记录至数据表後，呼叫事件处理函数    
                            //bulkCopy.NotifyAfter = 10000;
                            //bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
                            //設置要写入的数据库
                            bulkCopy.DestinationTableName = "dbo.DC_Keyword";
                            DataTable dt = mDCKeywordList.ToDataTable();
                            bulkCopy.WriteToServer(dt);
                        }

                        //事务提交
                        tran.Commit();
                    }
                    catch(Exception e)
                    {
                        //事务回滚
                        tran.Rollback();
                        throw e;
                    }
                    finally
                    {
                        tran.Dispose();
                    }
                }
            }
        }

 

转载于:https://www.cnblogs.com/xust/articles/3069316.html