public static void DeleteSlotRange(int id, SlotRange range)
{
string errMsg = "";
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
byte[] timestamp = range.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * " +
"FROM SlotRange S " +
"WHERE S.id_slotRange=@id AND S.timestamp=@timestamp;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
cmd = new SqlCommand("DELETE FROM Slot WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE FROM MachineSlotRange WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.ExecuteNonQuery();
cmd = new SqlCommand("DELETE FROM SlotRange WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.ExecuteNonQuery();
}
else
{
rdr.Close();
errMsg += " " + Messages.recommencerDelete;
Console.WriteLine("Cross modify");
throw new GrException(Messages.recommencerDelete);
}
transaction.Commit();
}
catch (SqlException sqlError)
{
Console.WriteLine(sqlError);
transaction.Rollback();
throw new GrException(sqlError, Messages.errProd);
}
db.Close();
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
}
public static void UpdateSlotRange(SlotRange range)
{
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
byte[] timestamp = range.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * FROM SlotRange R " +
"WHERE R.[id_slotRange]=@id AND R.timestamp=@timestamp;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
cmd = new SqlCommand("SELECT CASE WHEN DATEDIFF(d, startRes, GETDATE ()) >= 0 AND DATEDIFF(d, GETDATE(), endRes) >= 0 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END locked " +
"FROM SlotRange where id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
rdr = cmd.ExecuteReader();
Boolean isLocked = false;
if (rdr.Read())
{
isLocked = rdr.GetBoolean(rdr.GetOrdinal("locked")); ;
}
rdr.Close();
cmd = new SqlCommand("UPDATE SlotRange SET startRes=@startRes, endRes=@endRes, " +
"name=@name, id_course=@idCourse WHERE id_slotRange=@id;", db, transaction);
cmd.Parameters.Add("@startRes", SqlDbType.DateTime).Value = range.StartRes;
cmd.Parameters.Add("@endRes", SqlDbType.DateTime).Value = range.EndRes;
cmd.Parameters.Add("@name", SqlDbType.Char).Value = range.Name;
cmd.Parameters.Add("@idCourse", SqlDbType.Int).Value = range.IdCourse;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
cmd.ExecuteNonQuery();
if (!isLocked)
{
SqlCommand cmd2 = new SqlCommand("DELETE FROM Slot WHERE id_slotRange=@id;", db, transaction);
cmd2.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
cmd2.ExecuteNonQuery();
//do slots
InsertAllSlots(range, range.id_slotRange, db, transaction);
}
//do machines
//we update only as long as we keep at least as many machines as there already are in the database for this slotrange
SqlCommand cmd3 = new SqlCommand("SELECT COUNT(id_slotRange) as cpt " +
"FROM [GR-Calcul].[dbo].[MachineSlotRange] WHERE id_slotRange=@id;", db, transaction);
cmd3.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
SqlDataReader rdr2 = cmd3.ExecuteReader();
int nMachines = 0;
if (rdr2.Read())
{
nMachines = rdr2.GetInt32(rdr2.GetOrdinal("cpt"));
}
rdr2.Close();
if (range.Machines.Count >= nMachines)
{
SqlCommand cmd4 = new SqlCommand("DELETE FROM MachineSlotRange WHERE id_slotRange=@id;", db, transaction);
cmd4.Parameters.Add("@id", SqlDbType.Int).Value = range.id_slotRange;
cmd4.ExecuteNonQuery();
foreach (var machine in range.Machines)
{
InsertMachines(machine, range.id_slotRange, db, transaction);
}
}
transaction.Commit();
}
else
{
rdr.Close();
Console.WriteLine("Cross modify");
throw new GrException(Messages.recommencerEdit);
}
}
catch (Exception ex)
{
transaction.Rollback();
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
throw new GrException(ex, Messages.errProd);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
}