.Net json获取到大量数据插入sqlserver的方法

一直用旧的插入方式插入数据库，发现体验不好，耗时较慢，需改造。
改造前：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;
            }
        }复制