c#几种数据库的大数据批量插入（SqlServer、Oracle、SQLite和MySql）

C#几种数据库的大数据批量插入

在C#开发中，我们经常需要将大量数据批量插入到数据库中。本攻略将讲解如何在C#中实现SqlServer、Oracle、SQLite和MySql几种数据库的大数据批量插入。

SqlServer

使用SqlBulkCopy可以实现大数据批量插入到SqlServer中。具体步骤如下：

1. 创建SqlBulkCopy对象并设置目标表名和连接字符串。

string connectionString = "Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True";
SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";

1. 设置需要进行批量插入的列映射。

SqlBulkCopyColumnMapping columnMappingId = new SqlBulkCopyColumnMapping("Id", "Id");
SqlBulkCopyColumnMapping columnMappingName = new SqlBulkCopyColumnMapping("Name", "Name");
bulkCopy.ColumnMappings.Add(columnMappingId);
bulkCopy.ColumnMappings.Add(columnMappingName);

1. 开始批量插入。

bulkCopy.WriteToServer(dataTable);

完整示例代码如下：

string connectionString = "Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True";
SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";
SqlBulkCopyColumnMapping columnMappingId = new SqlBulkCopyColumnMapping("Id", "Id");
SqlBulkCopyColumnMapping columnMappingName = new SqlBulkCopyColumnMapping("Name", "Name");
bulkCopy.ColumnMappings.Add(columnMappingId);
bulkCopy.ColumnMappings.Add(columnMappingName);
bulkCopy.WriteToServer(dataTable);

Oracle

使用OracleBulkCopy可以实现大数据批量插入到Oracle中。具体步骤如下：

1. 创建OracleBulkCopy对象并设置目标表名和连接字符串。

string connectionString = "Data Source=MyDataSource;User Id=myUsername;Password=myPassword";
OracleBulkCopy bulkCopy = new OracleBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";

1. 设置需要进行批量插入的列映射。

OracleBulkCopyColumnMapping columnMappingId = new OracleBulkCopyColumnMapping("Id", "Id");
OracleBulkCopyColumnMapping columnMappingName = new OracleBulkCopyColumnMapping("Name", "Name");
bulkCopy.ColumnMappings.Add(columnMappingId);
bulkCopy.ColumnMappings.Add(columnMappingName);

1. 开始批量插入。

bulkCopy.WriteToServer(dataTable);

完整示例代码如下：

string connectionString = "Data Source=MyDataSource;User Id=myUsername;Password=myPassword";
OracleBulkCopy bulkCopy = new OracleBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";
OracleBulkCopyColumnMapping columnMappingId = new OracleBulkCopyColumnMapping("Id", "Id");
OracleBulkCopyColumnMapping columnMappingName = new OracleBulkCopyColumnMapping("Name", "Name");
bulkCopy.ColumnMappings.Add(columnMappingId);
bulkCopy.ColumnMappings.Add(columnMappingName);
bulkCopy.WriteToServer(dataTable);

SQLite

使用SQLiteDataAdapter可以实现大数据批量插入到SQLite中。具体步骤如下：

1. 创建SQLiteDataAdapter对象并设置目标表名和连接字符串。

string connectionString = "Data Source=mydb.sqlite;Version=3;";
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("SELECT * FROM MyTable", connectionString);

1. 创建SQLiteCommandBuilder对象。

SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(dataAdapter);

1. 批量插入。

dataAdapter.Update(dataTable);

完整示例代码如下：

string connectionString = "Data Source=mydb.sqlite;Version=3;";
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("SELECT * FROM MyTable", connectionString);
SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(dataAdapter);
dataAdapter.Update(dataTable);

MySql

使用MySqlBulkCopy可以实现大数据批量插入到MySql中。具体步骤如下：

1. 创建MySqlBulkCopy对象并设置目标表名和连接字符串。

string connectionString = "Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";
MySqlBulkCopy bulkCopy = new MySqlBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";

1. 设置需要进行批量插入的列映射。

MySqlBulkCopyColumnMapping columnMappingId = new MySqlBulkCopyColumnMapping("Id", "Id");
MySqlBulkCopyColumnMapping columnMappingName = new MySqlBulkCopyColumnMapping("Name", "Name");
bulkCopy.ColumnMappings.Add(columnMappingId);
bulkCopy.ColumnMappings.Add(columnMappingName);

1. 开始批量插入。

bulkCopy.WriteToServer(dataTable);

完整示例代码如下：

string connectionString = "Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";
MySqlBulkCopy bulkCopy = new MySqlBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";
MySqlBulkCopyColumnMapping columnMappingId = new MySqlBulkCopyColumnMapping("Id", "Id");
MySqlBulkCopyColumnMapping columnMappingName = new MySqlBulkCopyColumnMapping("Name", "Name");
bulkCopy.ColumnMappings.Add(columnMappingId);
bulkCopy.ColumnMappings.Add(columnMappingName);
bulkCopy.WriteToServer(dataTable);

以上就是在C#中实现SqlServer、Oracle、SQLite和MySql几种数据库的大数据批量插入的完整攻略。下面是完整示例代码。

// SqlServer
string connectionString = "Data Source=.;Initial Catalog=MyDatabase;Integrated Security=True";
SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";
SqlBulkCopyColumnMapping columnMappingId = new SqlBulkCopyColumnMapping("Id", "Id");
SqlBulkCopyColumnMapping columnMappingName = new SqlBulkCopyColumnMapping("Name", "Name");
bulkCopy.ColumnMappings.Add(columnMappingId);
bulkCopy.ColumnMappings.Add(columnMappingName);
bulkCopy.WriteToServer(dataTable);

// Oracle
string connectionString = "Data Source=MyDataSource;User Id=myUsername;Password=myPassword";
OracleBulkCopy bulkCopy = new OracleBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";
OracleBulkCopyColumnMapping columnMappingId = new OracleBulkCopyColumnMapping("Id", "Id");
OracleBulkCopyColumnMapping columnMappingName = new OracleBulkCopyColumnMapping("Name", "Name");
bulkCopy.ColumnMappings.Add(columnMappingId);
bulkCopy.ColumnMappings.Add(columnMappingName);
bulkCopy.WriteToServer(dataTable);

// SQLite
string connectionString = "Data Source=mydb.sqlite;Version=3;";
SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter("SELECT * FROM MyTable", connectionString);
SQLiteCommandBuilder commandBuilder = new SQLiteCommandBuilder(dataAdapter);
dataAdapter.Update(dataTable);

// MySql
string connectionString = "Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;";
MySqlBulkCopy bulkCopy = new MySqlBulkCopy(connectionString);
bulkCopy.DestinationTableName = "MyTable";
MySqlBulkCopyColumnMapping columnMappingId = new MySqlBulkCopyColumnMapping("Id", "Id");
MySqlBulkCopyColumnMapping columnMappingName = new MySqlBulkCopyColumnMapping("Name", "Name");
bulkCopy.ColumnMappings.Add(columnMappingId);
bulkCopy.ColumnMappings.Add(columnMappingName);
bulkCopy.WriteToServer(dataTable);