private static void IgnoreIfNull(IList nonKeyFields, DbCapacity dbCapacity) { RemoveIfNull(nonKeyFields, dbCapacity.Load, "load"); RemoveIfNull(nonKeyFields, dbCapacity.Deliveries, "deliveries"); RemoveIfNull(nonKeyFields, dbCapacity.Shifts, "shifts"); RemoveIfNull(nonKeyFields, dbCapacity.Capacity, "capacity"); }
public void InsertCapacity(DbCapacity dbCapacity) { var special = new[] { $"(select id from {DepotTable} where name = @depot)", "depot_id" }; var keyFieldsMinusSpecial = new List <string> { "day", "shift" }; var nonKeyFields = new List <string> { "load", "deliveries", "shifts", "capacity" }; var allFields = keyFieldsMinusSpecial; IgnoreIfNull(nonKeyFields, dbCapacity); allFields.AddRange(nonKeyFields); var sql = $"merge into {CapacityTable} as target " + $"using (select {special[0]},{Utils.ToCsv(allFields, "@")}) as source({special[1]},{Utils.ToCsv(allFields)}) " + "on target.depot_id = source.depot_id and target.day = source.day and target.shift = source.shift " + (nonKeyFields.Any() ? $"when matched then update set {CreateUpdates(nonKeyFields, "target", "source")} " : string.Empty) + $"when not matched then insert({special[1]},{Utils.ToCsv(allFields)}) values ({special[0]},{Utils.ToCsv(allFields, "source.")});"; using var connection = new SqlConnection(_dbConnectionString); using var command = new SqlCommand(sql, connection); command.Parameters.AddWithValue("@depot", dbCapacity.Depot); command.Parameters.AddWithValue("@day", dbCapacity.Day); command.Parameters.AddWithValue("@shift", dbCapacity.Shift); if (dbCapacity.Load.HasValue) { command.Parameters.AddWithValue("@load", dbCapacity.Load.Value); } if (dbCapacity.Deliveries.HasValue) { command.Parameters.AddWithValue("@deliveries", dbCapacity.Deliveries.Value); } if (dbCapacity.Shifts.HasValue) { command.Parameters.AddWithValue("@shifts", dbCapacity.Shifts.Value); } if (dbCapacity.Capacity.HasValue) { command.Parameters.AddWithValue("@capacity", dbCapacity.Capacity.Value); } connection.Open(); command.ExecuteNonQuery(); }
public void InsertDateCapacity(DbCapacity dbCapacity) { var special = new[] { $"(select id from {DepotTable} where name = @depot)", "depot_id" }; var keyFieldsMinusSpecial = new List <string> { "date", "shift" }; var nonKeyFields = new List <string> { "load", "deliveries", "shifts", "capacity" }; var allFields = keyFieldsMinusSpecial; IgnoreIfNull(nonKeyFields, dbCapacity); allFields.AddRange(nonKeyFields); var sql = $"insert into {CapacityDateTable} ({special[1]},{Utils.ToCsv(allFields)}) values ({special[0]},{Utils.ToCsv(allFields, "@")})"; using var connection = new SqlConnection(_dbConnectionString); using var command = new SqlCommand(sql, connection); command.Parameters.AddWithValue("@depot", dbCapacity.Depot); command.Parameters.AddWithValue("@shift", dbCapacity.Shift); command.Parameters.AddWithValue("@date", dbCapacity.Date); if (dbCapacity.Load.HasValue) { command.Parameters.AddWithValue("@load", dbCapacity.Load.Value); } if (dbCapacity.Deliveries.HasValue) { command.Parameters.AddWithValue("@deliveries", dbCapacity.Deliveries.Value); } if (dbCapacity.Shifts.HasValue) { command.Parameters.AddWithValue("@shifts", dbCapacity.Shifts.Value); } if (dbCapacity.Capacity.HasValue) { command.Parameters.AddWithValue("@capacity", dbCapacity.Capacity.Value); } connection.Open(); command.ExecuteNonQuery(); }