一直用旧的插入方式插入数据库,发现体验不好,耗时较慢,需改造。
改造前:10万条数据起码半小时以上
dynamic root = JsonConvert.DeserializeObject<dynamic>(json);
List<string> list = new List<string>();
for (int i = 0; i < root.value.Count; i++){
list.Add("inset into...");
}
int result = DbHelper.ExecuteSqlTran(list);
static int ExecuteSqlTran(List<String> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(StrConn))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch (Exception ex)
{
tx.Rollback();
return 0;
}
}
}
改造后:10万条数据约十分钟以下
dynamic root = JsonConvert.DeserializeObject<dynamic>(json);
//table的列名就是数据库的列名
DataTable table = new DataTable();
table.TableName = "表名";
table.Columns.Add("id", typeof(string));
table.Columns.Add("ItemCode", typeof(string));
foreach (var item in root.value){
table.Rows.Add(item.id, item.ItemCode)
}
int insert = DbHelper.BulkInsertJsonData("ItemMaster", table);
static int BulkInsertJsonData(DataTable table)
{
var result = 0;
try
{
using (SqlConnection connection = new SqlConnection(StrConn))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = table.TableName;
bulkCopy.WriteToServer(table);
result = table.Rows.Count;
}
}
return result;
}
catch (Exception)
{
return result;
}
}