SqlBulkCopy 批量插入数据库

 　　　  /// <summary>

        /// 批量插入  注：DT的tableName为要更新的数据库表名,DT的列名和数据库一致

        /// </summary>

        /// <param name="dt"></param>

        /// <remarks></remarks>

        public void  tsSqlBulkCopy(DataTable dt)

        {

            try

            {

                using (SqlBulkCopy sqlCopy = new SqlBulkCopy(this.connection, SqlBulkCopyOptions.FireTriggers, this.transaction))

                {

                    string sql = "select * from " + dt.TableName + " where 1=-1";

                    DataTable DBTable = ExecuteDataTable(sql);

                    List<string> DBList = new List<string>();

                    for (int i = ; i <= DBTable.Columns.Count - ; i++)

                    {

                        DBList.Add(DBTable.Columns[i].ColumnName);

                    }

                    List<string> ParList = new List<string>();

                    for (int i = ; i <= dt.Columns.Count - ; i++)

                    {

                        ParList.Add(dt.Columns[i].ColumnName);

                    }

                    //添加对应列

                    foreach (string item in DBList)

                    {

                        if (ParList.Contains(item))

                        {

                            sqlCopy.ColumnMappings.Add(item, item);

                        }

                    }

                    sqlCopy.DestinationTableName = dt.TableName;

                    sqlCopy.BatchSize = dt.Rows.Count;

                    sqlCopy.WriteToServer(dt);

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }