public void DataUpdate()
{
var table = SqlCoreHelper.ExecuteDataSetText("select Id,FieldD from BulkTest", null).Tables[0];
var tableTwo = SqlCoreHelper.ExecuteDataSetText("select Id,FieldD from BulkTestTwo", null).Tables[0];
foreach (DataRow item in table.Rows)
{
item["FieldD"] = "111";
}
foreach (DataRow item in tableTwo.Rows)
{
item["FieldD"] = "222";
}
table.TableName = "BulkTest";
tableTwo.TableName = "BulkTestTwo";
Stopwatch sw = new Stopwatch();
sw.Start();
SqlBulkCopyHelper.BulkUpdateTables(new List <DataTable>()
{
table, tableTwo
});
sw.Stop();
string bb = sw.Elapsed.TotalSeconds.ToString();
Console.WriteLine(bb);
}
public void DataEdit()
{
var dataList = CommonHelper.GetTestListDto(5000);
var dataListTwo = CommonHelper.GetTestListDtoTwo(5000);
var table = SqlBulkCopyHelper.ListToTable(dataList, "BulkTest");
var tableTwo = SqlBulkCopyHelper.ListToTable(dataListTwo, "BulkTestTwo");
var tableUpdate = SqlCoreHelper.ExecuteDataSetText("select top 5000 Id,FieldD from BulkTest order by FieldU asc", null).Tables[0];
var tableTwoUpdate = SqlCoreHelper.ExecuteDataSetText("select top 5000 Id,FieldD from BulkTestTwo order by FieldU asc", null).Tables[0];
foreach (DataRow item in tableUpdate.Rows)
{
item["FieldD"] = "555";
}
foreach (DataRow item in tableTwoUpdate.Rows)
{
item["FieldD"] = "666";
}
tableUpdate.TableName = "BulkTest";
tableTwoUpdate.TableName = "BulkTestTwo";
Stopwatch sw = new Stopwatch();
sw.Start();
SqlBulkCopyHelper.BulkEditTables(new List <DataTable>()
{
table, tableTwo
}, new List <DataTable>()
{
tableUpdate, tableTwoUpdate
});
sw.Stop();
string bb = sw.Elapsed.TotalSeconds.ToString();
Console.WriteLine(bb);
}
public void DataInsert()
{
var dataList = CommonHelper.GetTestListDto(5000);
var dataListTwo = CommonHelper.GetTestListDtoTwo(5000);
var table = SqlBulkCopyHelper.ListToTable(dataList, "BulkTest");
var tableTwo = SqlBulkCopyHelper.ListToTable(dataListTwo, "BulkTestTwo");
Stopwatch sw = new Stopwatch();
sw.Start();
SqlBulkCopyHelper.BulkInsertTables(new List <DataTable>()
{
table, tableTwo
}).Wait();
sw.Stop();
string bb = sw.Elapsed.TotalSeconds.ToString();
Console.WriteLine(bb);
}
/// <summary>
/// 数据列表转成DataTable
/// </summary>
/// <typeparam name="TModel"></typeparam>
/// <param name="modelList"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataTable ListToTable <TModel>(List <TModel> modelList, string tableName = default, List <string> tableFields = default)
{
Type modelType = typeof(TModel);
if (string.IsNullOrEmpty(tableName))
{
tableName = modelType.Name;
}
DataTable dt = new DataTable(tableName);
var columns = GetTableColumns(tableName);
var mappingProps = new List <PropertyInfo>();
var props = modelType.GetProperties();
if (tableFields != default)
{
columns = columns.Where(o => tableFields.Contains(o.Name)).ToList();
props = props.Where(o => tableFields.Contains(o.Name)).ToArray();
}
for (int i = 0; i < columns.Count; i++)
{
var column = columns[i];
PropertyInfo mappingProp = props.Where(a => a.Name == column.Name).FirstOrDefault();
Type dataType = default;
if (mappingProp == default)
{
dataType = SqlBulkCopyHelper.SqlTypeString2CsharpType(column.Type);
}
else
{
mappingProps.Add(mappingProp);
if (column.IsNull == 0)
{
dataType = mappingProp.PropertyType;
}
else
{
dataType = Nullable.GetUnderlyingType(mappingProp.PropertyType) ?? mappingProp.PropertyType;
}
}
if (dataType.IsEnum)
{
dataType = typeof(int);
}
var dataColumn = new DataColumn(column.Name, dataType);
if (column.IsNull == 0)
{
if (dataType == typeof(string))
{
dataColumn.DefaultValue = string.IsNullOrEmpty(column.Default) ? string.Empty : column.Default;
}
if (dataType == typeof(int) || dataType == typeof(int?))
{
dataColumn.DefaultValue = string.IsNullOrEmpty(column.Default) ? 0 : int.Parse(column.Default);
}
if (dataType == typeof(decimal) || dataType == typeof(decimal?))
{
dataColumn.DefaultValue = string.IsNullOrEmpty(column.Default) ? 0 : decimal.Parse(column.Default);
}
if (dataType == typeof(double) || dataType == typeof(double?))
{
dataColumn.DefaultValue = string.IsNullOrEmpty(column.Default) ? 0 : double.Parse(column.Default);
}
if (dataType == typeof(bool) || dataType == typeof(bool?))
{
dataColumn.DefaultValue = string.IsNullOrEmpty(column.Default) ? false : bool.Parse(column.Default);
}
if (dataType == typeof(DateTime) || dataType == typeof(DateTime?))
{
dataColumn.DefaultValue = DateTime.Now;
}
if (dataType == typeof(Guid) || dataType == typeof(Guid?))
{
dataColumn.DefaultValue = Guid.Empty;
}
}
dt.Columns.Add(dataColumn);
}
foreach (var model in modelList)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < mappingProps.Count; i++)
{
PropertyInfo prop = mappingProps[i];
object value = prop.GetValue(model);
if (prop.PropertyType.IsEnum)
{
if (value != null)
{
value = (int)value;
}
}
dr[prop.Name] = value ?? DBNull.Value;
}
dt.Rows.Add(dr);
}
return(dt);
}