ado.net mysql 批量插入_ado.net 批量添加 更新 删除

自曾列就别往下看 别折腾了

使用 SqlBulkCopy ，dataTable 必须跟sql里面的表字段完全一样

下面在sqlserver中演示

mysql 请google MySqlBulkLoader

oracle 请google OracleBulkCopy

表结构

DROP TABLE [dbo].[Product]

GO

CREATE TABLE [dbo].[Product]([Id] varchar(36) NOT NULL,[Name] varchar(255) NOT NULL,[Price] decimal(18,4) NOT NULL)GO

ALTER TABLE [dbo].[Product] ADD PRIMARY KEY ([Id])GO

批量添加

public static void Insert(string connectionString, List dataList, string destinationTableName, int batchSize = 0)

{

DataTable dataTable=ConvertToDataTable(dataList);

Insert(connectionString, dataTable, destinationTableName, batchSize);

}public static void Insert(string connectionString, DataTable dataTable, string destinationTableName, int batchSize = 0)

{using (SqlConnection connection = newSqlConnection(connectionString))

{if (connection.State !=ConnectionState.Open)

{

connection.Open();

}using (SqlTransaction transaction =connection.BeginTransaction())

{using (SqlBulkCopy bulkCopy = newSqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))

{

bulkCopy.BatchSize=batchSize;

bulkCopy.DestinationTableName=destinationTableName;try{

bulkCopy.WriteToServer(dataTable);

transaction.Commit();

}catch(Exception ex)

{

Console.WriteLine(ex.Message);

transaction.Rollback();

}

}

}

}

}

批量添加测试代码

public static voidInsert()

{

List products = new List();for (int i = 0; i < 100000; i++)

{

Product product= newProduct

{

Id=Guid.NewGuid().ToString(),

Name= $"商品{i}",

Price= (decimal)i

};

products.Add(product);

}

Stopwatch stopwatch= newStopwatch();

stopwatch.Start();

Insert(SqLiteHelper.SqlServerConnection, products,"Product");

stopwatch.Stop();

Console.WriteLine("耗时：" +stopwatch.ElapsedMilliseconds);

}

批量更新

public static void Update(string connectionString, List list, stringdestinationTableName)

{var dt =ConvertToDataTable(list);using (SqlConnection connection = newSqlConnection(connectionString))

{if (connection.State !=ConnectionState.Open)

{

connection.Open();

}using (SqlTransaction transaction =connection.BeginTra