/// <summary>
/// 往数据库中批量插入数据
/// </summary>
/// <param name="sourceDt">数据源表</param>
/// <param name="targetTable">服务器上目标表</param>
/// <param name="err">错误信息</param>
public static void BulkToDB(DataTable sourceDt, string targetTable, out string err)
{
err = "";
using (SqlConnection conn = SqlFactoryCreate.CreateConnection(SqlFactoryCreate.DbConfigStr.SqlServer))
{
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn); //用其它源的数据有效批量加载sql server表中
bulkCopy.DestinationTableName = targetTable; //服务器上目标表的名称
bulkCopy.BatchSize = sourceDt.Rows.Count; //每一批次中的行数
//DataTable列名与数据库列名的映射
bulkCopy.ColumnMappings.Add("Area", "Area");
bulkCopy.ColumnMappings.Add("Street", "Street");
bulkCopy.ColumnMappings.Add("SecID", "SecID");
bulkCopy.ColumnMappings.Add("SecName", "SecName");
bulkCopy.ColumnMappings.Add("SchedulingDate", "SchedulingDate");
bulkCopy.ColumnMappings.Add("TimeIntervalID", "TimeIntervalID");
bulkCopy.ColumnMappings.Add("VehicleID", "VehicleID");
bulkCopy.ColumnMappings.Add("RegName", "RegName");
bulkCopy.ColumnMappings.Add("RoadCode", "RoadCode");
bulkCopy.ColumnMappings.Add("RoadName", "RoadName");
bulkCopy.ColumnMappings.Add("VehicleJobTypeName", "VehicleJobTypeName");
bulkCopy.ColumnMappings.Add("PlanCount", "PlanCount");
bulkCopy.ColumnMappings.Add("SinglePlanCount", "SinglePlanCount");
bulkCopy.ColumnMappings.Add("DriverAndTelephone", "DriverAndTelephone");
bulkCopy.ColumnMappings.Add("OnDutyAndTelephone", "OnDutyAndTelephone");
bulkCopy.ColumnMappings.Add("ShiftLeaderAndTelephone", "ShiftLeaderAndTelephone");
bulkCopy.ColumnMappings.Add("ProjectID", "ProjectID");
try
{
conn.Open();
if (sourceDt != null && sourceDt.Rows.Count != 0)
{
//将提供的数据源中的所有行复制到目标表中
bulkCopy.WriteToServer(sourceDt);
}
}
catch (Exception ex)
{
string json = JsonConvert.SerializeObject(sourceDt);
err = $"往数据库中批量插入数据失败{ex.Message},请求参数:{json}";
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
if (bulkCopy != null)
{
bulkCopy.Close();
}
}
}
}
/// <summary>
/// 生成DataTable
/// </summary>
/// <param name="vsList"></param>
/// <returns></returns>
private static DataTable GetDataTable(List<VehicleScheduling> vsList)
{
DataTable dt = new DataTable();
dt.Columns.Add("Area", typeof(string));
dt.Columns.Add("Street", typeof(string));
dt.Columns.Add("SecID", typeof(string));
dt.Columns.Add("SecName", typeof(string));
dt.Columns.Add("SchedulingDate", typeof(DateTime));
dt.Columns.Add("TimeIntervalID", typeof(int));
dt.Columns.Add("VehicleID", typeof(int));
dt.Columns.Add("RegName", typeof(string));
dt.Columns.Add("RoadCode", typeof(string));
dt.Columns.Add("RoadName", typeof(string));
dt.Columns.Add("VehicleJobTypeName", typeof(string));
dt.Columns.Add("PlanCount", typeof(int));
dt.Columns.Add("SinglePlanCount", typeof(int));
dt.Columns.Add("DriverAndTelephone", typeof(string));
dt.Columns.Add("OnDutyAndTelephone", typeof(string));
dt.Columns.Add("ShiftLeaderAndTelephone", typeof(string));
dt.Columns.Add("ProjectID", typeof(int));
for (int i = 0; i < vsList.Count; i++)
{
DataRow dr = dt.NewRow();
dr["ProjectID"] = vsList[i].ProjectID;
dr["Area"] = vsList[i].Area;
dr["Street"] = vsList[i].Street;
dr["SecID"] = vsList[i].SecID;
dr["SecName"] = vsList[i].SecName;
dr["SchedulingDate"] = vsList[i].SchedulingDate;
dr["TimeIntervalID"] = vsList[i].TimeIntervalID;
dr["VehicleID"] = vsList[i].VehicleID;
dr["RegName"] = vsList[i].RegName;
dr["RoadCode"] = vsList[i].RoadCode;
dr["RoadName"] = "";
dr["VehicleJobTypeName"] = vsList[i].VehicleJobTypeName;
dr["PlanCount"] = vsList[i].PlanCount;
dr["SinglePlanCount"] = vsList[i].SinglePlanCount;
dr["DriverAndTelephone"] = vsList[i].DriverAndTelephone;
dr["OnDutyAndTelephone"] = vsList[i].OnDutyAndTelephone;
dr["ShiftLeaderAndTelephone"] = vsList[i].ShiftLeaderAndTelephone;
dt.Rows.Add(dr);
}
return dt;
}
调用–
/// <summary>
/// 作者:kevin
/// 创建时间:2022年01月05日
/// 名称:批量添加数据
/// </summary>
public Tuple<string, List<BatchVehicleSchedulingResponse>> BatchAddOrUpdateVehicleScheduling(int ProjectID, BatchVehicleScheduling model)
{
List<BatchVehicleSchedulingResponse> failList = new List<BatchVehicleSchedulingResponse>();
//过滤当前需要的数据
List<VehicleScheduling> vslst = _alarmService.VehicleScheduling.Where(w => w.ProjectID == ProjectID && w.State != (int)VehicleSchedulingState.删除).ToList();
string msg = "";
List<VehicleScheduling> vsList = new List<VehicleScheduling>();
if (model.SchedulingDateList != null && model.SchedulingDateList.Any())
{
foreach (var item in model.SchedulingDateList)
{
//不存在则添加
var sc = vslst.Find(f =>
f.VehicleID == model.VehicleID &&
f.TimeIntervalID == model.TimeIntervalID &&
f.RoadCode == model.RoadCode &&
f.SchedulingDate.Date == Convert.ToDateTime(item).Date);
if (sc == null)
{
VehicleScheduling vs = new VehicleScheduling();
DataHandleHepler.CopyModel<BatchVehicleScheduling, VehicleScheduling>(model, vs);
vs.SchedulingDate = Convert.ToDateTime(item);
vs.ProjectID = ProjectID;
vsList.Add(vs);
}
else
{
failList.Add(new BatchVehicleSchedulingResponse()
{
Id = sc.Id,
SchedulingDate = sc.SchedulingDate,
TimeIntervalID = sc.TimeIntervalID,
VehicleID = sc.VehicleID,
RegName = sc.RegName,
RoadCode = sc.RoadCode,
ProjectID = sc.ProjectID
});
}
}
if (vsList.Count > 0)
{
DataTable dt = GetDataTable(vsList);
DataHandleHepler.BulkToDB(dt, "HL.VehicleScheduling", out msg);
}
}
else
{
msg = "请选择日期";
}
if (msg.Length == 0)
{
msg = $"操作完成,总记录{model.SchedulingDateList.Count()}条,成功{vsList.Count}条,失败{failList.Count}条";
}
return new Tuple<string, List<BatchVehicleSchedulingResponse>>(msg, failList);
}