SQL SERVER的批量插入与批量更新

       /// <summary>
        /// 批量插入数据,将dt批量插入到tableName
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public bool BulkCopy(DataTable dt, string tableName)
        {
            bool flag = false;
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        bulkCopy.BatchSize = 100000;
                        bulkCopy.BulkCopyTimeout = 3;
                        bulkCopy.DestinationTableName = tableName;  //批量插入到的表名
                        try
                        {
                            foreach (DataColumn col in dt.Columns)
                            {
                                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                            bulkCopy.WriteToServer(dt);
                            transaction.Commit();
                            flag = true;
                        }
                        catch (Exception ex)
                        {
                            flag = false;
                            transaction.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }

            }
            return flag;
        }

        /// <summary>
        /// 批量更新数据(每批次5000)
        /// </summary>
        /// <param name="table"></param>
        /// <param name="courseId"></param>
        /// <param name="SchemeId"></param>
        /// <returns></returns>
        public bool Update(DataTable table, int courseId, int SchemeId)
        {
            table.TableName = "1";
            DataSet ds = new DataSet();
            bool flag = false;
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand comm = conn.CreateCommand();
            comm.CommandTimeout = 20;
            comm.CommandType = CommandType.Text;
            comm.CommandText = "select Id, EmpId, SchemeId, CourseId, SubjectItemId, Salary, Calformal, CalformalEng, Status, SubtracSequence, IsChange, SortIndex from  HR_SA_DE_XXX where CourseId=" + courseId.ToString() + " and SchemeId = " + SchemeId.ToString() + "";
            SqlDataAdapter adapter = new SqlDataAdapter(comm);
            SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter);
            commandBulider.ConflictOption = ConflictOption.OverwriteChanges;
            try
            {
                conn.Open();
                //设置批量更新的每次处理条数 这就是批量更新
                adapter.UpdateBatchSize = 100000;
                adapter.SelectCommand.Transaction = conn.BeginTransaction();/开始事务
                adapter.Fill(ds);
                foreach (DataRow trow in ds.Tables[0].Rows)
                {
                    trow.BeginEdit();
                    DataRow[] myrow = table.Select("EmpId='" + trow["EmpId"] + "' and SchemeId=" + ConvertHelper.ToInt32(trow["SchemeId"]) + " and CourseId=" + ConvertHelper.ToInt32(trow["CourseId"]) + " and SubjectItemId='" + trow["SubjectItemId"].ToString() + "'");
                    if (myrow.Length > 0)
                    {
                        trow["Salary"] = myrow[0]["Salary"].ToString();
                        trow["Calformal"] = myrow[0]["Calformal"].ToString();
                        trow["CalformalEng"] = myrow[0]["CalformalEng"].ToString();
                        trow["SubtracSequence"] = ConvertHelper.ToInt32(myrow[0]["SubtracSequence"].ToString());
                        trow["IsChange"] = ConvertHelper.ToBool(myrow[0]["IsChange"]);
                        trow["SortIndex"] = ConvertHelper.ToInt32(myrow[0]["SortIndex"].ToString());
                    }
                    trow.EndEdit();
                }

                adapter.Update(ds);
                adapter.SelectCommand.Transaction.Commit();/提交事务
                flag = true;
            }
            catch (Exception ex)
            {
                flag = false;
                if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null)
                {
                    adapter.SelectCommand.Transaction.Rollback();
                }

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return flag;
        }