C#使用SqlBulkCopy批量导数据
时间：2021-07-01 10:21:17
帮助过：16人阅读
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
C#使用SqlBulkCopy批量导数据
标签：open ref tin objects 使用 state names htm sys
