一次性插入上万条数据的写法
1.
///
<summary>
/// DataTable批量添加(有事务)
/// </summary>
/
// <param name="Table">数据源</param>
///
<param name="Mapping">定义数据源和目标源列的关系集合</param>
/// <param name="DestinationTableName">目标表</param>
public static
bool MySqlBulkCopy(DataTable Table, SqlBulkCopyColumnMapping[] Mapping,
string DestinationTableName) {
bool Bool
= true;
using (SqlConnection con
= new SqlConnection(ConnectionString)) {
con.Open();
using (SqlTransaction Tran
= con.BeginTransaction()) {
using (SqlBulkCopy Copy
= new SqlBulkCopy(con,SqlBulkCopyOptions.KeepIdentity,Tran)) {
Copy.DestinationTableName = DestinationTableName;//指定目标表
if (Mapping
!= null) {
//如果有数据
foreach (SqlBulkCopyColumnMapping Map
in Mapping) {
Copy.ColumnMappings.Add(Map); } }
try { Copy.WriteToServer(Table);//批量添加
Tran.Commit();//提交事务 }
catch { Tran.Rollback();
//回滚事务 Bool
= false; } } } }
return Bool; }
2.MSSQL使用SqlBulkCopy,传数据源，表名，列影射，我导入150万几秒完成
/// <summary>
/// 使用SqlBulkCopy批量插入，只限SQLServer
/// 缺点，没有返回行数
/// </summary>
/// <param name="table">填充的DataTable,支持其它数据源，请看重载</param>
/// <param name="tableName">数据库对应表名</param>
/// <param name="columns">插入表对应的列名集合</param>
public void SqlBulkCopyInsert(DataTable table, string tableName, string[] columns)
{
SqlBulkCopy sbc = new SqlBulkCopy("接连字符串");
sbc.DestinationTableName = tableName;
foreach (string col in columns)
{
sbc.ColumnMappings.Add(col, col);
}
sbc.WriteToServer(table);
}
3.其它数据库，将数据查到一个datatable,往table填充数据,再在adpt.Update(table)
/// <summary>
/// 多行插入,Connection/Command/DataAdapter看你连接的数据库类型
/// 进行相应的替换即可
/// </summary>
/// <param name="ds">填充数据后的数据集</param>
/// <returns>受影响行数</returns>
public int MultyInsert(DataSet ds)
{
int result = 0;
IDbConnection con = new OracleConnection("连接字符串");
con.Open();
IDbCommand cmd = new OracleCommand();
cmd.CommandText = "Insert into Member(UserName,Password) values(@name,@password)";
IDbDataParameter namePar = cmd.CreateParameter();
namePar.ParameterName = "@name";
namePar.SourceColumn = "UserName";
namePar.SourceVersion = DataRowVersion.Original;
namePar.DbType = DbType.String;
cmd.Parameters.Add(namePar);
IDbDataParameter passPar = cmd.CreateParameter();
passPar.ParameterName = "@pass";
passPar.DbType = DbType.String;
passPar.SourceColumn = "Password";
passPar.SourceVersion = DataRowVersion.Original;
cmd.Parameters.Add(passPar);
IDbDataAdapter adpt = new OracleDataAdapter();
adpt.InsertCommand = cmd;
try
{
result = adpt.Update(ds);
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
return result;
}