C#使用SqlBulkCopy批量导数据

案例：将数据库数据导入到另一个库

ImportDataUtil：

public class ImportDataUtil
    {
        const int PAGE_SIZE = 500;
        private SqlConnection _sourceConn = null;
        private SqlConnection _descConn = null;
        public ImportDataUtil(string sourceConnStr,string descConnStr)
        {
            _sourceConn = new SqlConnection(sourceConnStr);
            _descConn = new SqlConnection(descConnStr);
            if (_sourceConn.State != System.Data.ConnectionState.Open)
            {
                _sourceConn.Open();
            }
            if (_descConn.State != System.Data.ConnectionState.Open)
            {
                _descConn.Open();
            }
        }


        public void ImportTables()
        {
            var tableNameList = GetTableNames();
            Console.WriteLine($"共{tableNameList.Count}个表");
            for (int i = 0; i < tableNameList.Count; i++)
            {
                var tableName = tableNameList[i];
                Console.WriteLine($"{tableName}开始导数据");
                ImportTable(tableName);
                Console.WriteLine($"{tableName}结束导数据");
            }
        }
        private void ImportTable(string tableName)
        {
            DataTable table = null;
            var rowCount = GetRowCount( tableName);
            Console.WriteLine($"{tableName}表共{rowCount}条记录");
            var pageCount = rowCount / PAGE_SIZE + 1;
            for (int pageIndex = 0; pageIndex < pageCount; pageIndex++)
            {
                table = GetSourceTable(tableName, PAGE_SIZE, pageIndex);
                SqlBulkCopy bulkCopy = new SqlBulkCopy(_descConn);//,SqlBulkCopyOptions.KeepIdentity 自增列保留原值
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.BatchSize = table.Rows.Count;
                bulkCopy.WriteToServer(table);
            }
        }

        private  List<string> GetTableNames()
        {
            string sql = "select name from sysobjects where  OBJECTPROPERTY(id, N'IsUserTable') = 1;";
            return _sourceConn.Query<string>(sql).ToList();
        }
        private  int GetRowCount( string tableName)
        {
            string sql = $"SELECT COUNT(ID) FROM {tableName}";
            return _sourceConn.ExecuteScalar<int>(sql);
        }
        private  DataTable GetSourceTable( string tableName, int pageSize, int pageIndex)
        {
            var table = new DataTable();
            var command = _sourceConn.CreateCommand();
            command.CommandText = $"SELECT * FROM {tableName}";//Account_Permission
            command.CommandType = System.Data.CommandType.Text;
            var adapter = new SqlDataAdapter(command);
            adapter.Fill(table);
            return table;
        }

    }

使用：

        static void Main(string[] args)
        {
            string sourceConnStr = "Data Source = 192.168.0.116,30705; uid = sa; pwd = KI68oecJc0NpXwscxybK; Initial Catalog=test3; Pooling=true; Max Pool Size=1000; Min Pool Size=5; Connection Timeout=28800";
            string descConnStr = "Data Source = 192.168.0.116,30705; uid = sa; pwd = KI68oecJc0NpXwscxybK; Initial Catalog=test2; Pooling=true; Max Pool Size=1000; Min Pool Size=5; Connection Timeout=28800";
            ImportDataUtil importUtil = new ImportDataUtil(sourceConnStr,descConnStr);
            importUtil.ImportTables();

            Console.ReadKey();
        }

参考文章：

https://www.cnblogs.com/jiekzou/p/6145550.html

https://www.cnblogs.com/zhaoshujie/p/9691010.html