要插入5万多条数据
第一、使用Dapper,遥遥无期
public static int InsertByTran(List<Model.LidarPointDeformation> list, string lidarcode)
{
string connectionString = DataBaseApp.GetBusinessDbStringByLidarcode(lidarcode);
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
int record = 0;
using (var trans = connection.BeginTransaction())
{
try
{
var sql = "INSERT INTO LidarPointDeformation(id, pointId, row, col, reffile, deformation, lidarcode, monitoringtime) VALUES (@id,@pointId,@row,@col,@reffile,@deformation,@lidarcode,@monitoringtime)";
record = connection.Execute(sql, list, trans, 60, CommandType.Text);
}
catch (DataException e)
{
trans.Rollback();
throw e;
}
trans.Commit();
}
return record;
}
}
第二、使用sql语句拼接,但是最多只支持1000条,差强人意
public static void InsertBySQL(List<Model.LidarPointDeformation> list, string lidarcode)
{
try
{
string connectionString = DataBaseApp.GetBusinessDbStringByLidarcode(lidarcode);
using (IDbConnection connection = new SqlConnection(connectionString))
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
string basesql = "INSERT INTO LidarPointDeformation(id, pointId, row, col, reffile, deformation, lidarcode, monitoringtime) VALUES";
StringBuilder sb = new StringBuilder();
int count = 0;
for (int i = 0; i < list.Count; i++)
{
var item = list[i];
string contractsql = String.Format(" ('{0}',{1},{2},{3},'{4}',{5},'{6}','{7}'),", item.id, item.pointId, item.row, item.col, item.reffile, item.deformation, item.lidarcode, item.monitoringtime);
sb.Append(contractsql);
if (i % 990 == 1)
{
sb.Remove(sb.Length - 1, 1);
connection.Execute(basesql + sb.ToString());
sb.Clear();
continue;
}
else if (i == list.Count - 1)
{
sb.Remove(sb.Length - 1, 1);
connection.Execute(basesql + sb.ToString());
}
else
{
continue;
}
}
}
}
catch (Exception e)
{
throw;
}
}
第三、找了很久,说是用DapperExtensions,效率很不错
/// <summary>
/// 最后确定使用DapperExtensions,效率高很多
/// </summary>
/// <param name="list"></param>
/// <param name="lidarcode"></param>
public static void InsertByDapperExtension(List<Model.LidarPointDeformation> list, string lidarcode)
{
string connectionString = DataBaseApp.GetBusinessDbStringByLidarcode(lidarcode);
using (IDbConnection connection = new SqlConnection(connectionString))
{
connection.Open();
InsertBatch<Model.LidarPointDeformation>(connection, list);
}
}
/// <summary>
/// 批量插入
/// </summary>
public static void InsertBatch<T>(IDbConnection conn, IEnumerable<T> entityList, IDbTransaction transaction = null) where T : class
{
var tblName = string.Format("dbo.{0}", typeof(T).Name);
var tran = (SqlTransaction)transaction;
using (var bulkCopy = new SqlBulkCopy(conn as SqlConnection, SqlBulkCopyOptions.TableLock, tran))
{
try
{
bulkCopy.BatchSize = entityList.Count();
bulkCopy.DestinationTableName = tblName;
var table = new DataTable();
DapperExtensions.Sql.ISqlGenerator sqlGenerator = new SqlGeneratorImpl(new DapperExtensionsConfiguration());
var classMap = sqlGenerator.Configuration.GetMap<T>();
var props = classMap.Properties.Where(x => x.Ignored == false).ToArray();
foreach (var propertyInfo in props)
{
bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyInfo.PropertyType) ?? propertyInfo.PropertyInfo.PropertyType);
}
var values = new object[props.Count()];
foreach (var itemm in entityList)
{
for (var i = 0; i < values.Length; i++)
{
values[i] = props[i].PropertyInfo.GetValue(itemm, null);
}
table.Rows.Add(values);
}
bulkCopy.WriteToServer(table);
}
catch (Exception e)
{
throw e;
}
}
}