public ActionResult Delete(int id, Room room)
{
try
{
RoomModel.DeleteRoom(id, room);
return RedirectToAction("Index");
}
catch (GrException gex)
{
ModelState.AddModelError("", gex.UserMessage);
// get new data in case it changed in the mean time
return View(RoomModel.GetRoom(id));
}
}
public ActionResult Create(Room room)
{
if (ModelState.IsValid)
{
try
{
RoomModel.CreateRoom(room);
return RedirectToAction("Index");
}
catch (GrException gex)
{
ModelState.AddModelError("", gex.UserMessage);
return View(room);
}
}
else
{
ModelState.AddModelError("", Messages.invalidData);
return View(room);
}
}
public static void CreateRoom(Room room)
{
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
try
{
SqlCommand cmd = new SqlCommand("INSERT INTO Room " +
"(name) " +
"VALUES (@name);", db, transaction);
cmd.Parameters.Add("@name", SqlDbType.Char).Value = room.Name;
cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlException ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
transaction.Rollback();
throw new GrException(ex, Messages.errProd);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
}
public static void UpdateRoom(Room room)
{
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
byte[] timestamp = room.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * " +
"FROM Room R " +
"WHERE R.id_room=@id AND R.timestamp=@timestamp;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = room.ID;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
cmd = new SqlCommand("UPDATE Room " +
"SET name=@Name WHERE id_room=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = room.ID;
cmd.Parameters.Add("@Name", SqlDbType.Char).Value = room.Name;
cmd.ExecuteNonQuery();
}
else
{
rdr.Close();
System.Diagnostics.Debug.WriteLine("Cross modify");
throw new GrException(Messages.recommencerEdit);
}
transaction.Commit();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
transaction.Rollback();
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
}
public static List<Room> ListRooms()
{
List<Room> list = new List<Room>();
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
try
{
SqlCommand cmd = new SqlCommand("SELECT [id_room],[name], [timestamp] FROM [Room] R;", db, transaction);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int id_course = rdr.GetInt32(rdr.GetOrdinal("id_room"));
string name = rdr.GetString(rdr.GetOrdinal("name"));
Room room = new Room(id_course, name);
byte[] buffer = new byte[100];
rdr.GetBytes(rdr.GetOrdinal("timestamp"), 0, buffer, 0, 100);
room.setTimestamp(buffer);
list.Add(room);
}
rdr.Close();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw new GrException(ex, Messages.errProd);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
return list;
}
public static Room GetRoom(int id)
{
Room room = null;
try
{
SqlConnection db = new SqlConnection(connectionString);
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
SqlCommand cmd = new SqlCommand("SELECT [name], [timestamp] FROM [Room] R WHERE R.id_room=@id;", db, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
string name = rdr.GetString(rdr.GetOrdinal("name"));
room = new Room(id, name);
byte[] buffer = new byte[100];
rdr.GetBytes(rdr.GetOrdinal("timestamp"), 0, buffer, 0, 100);
room.setTimestamp(buffer);
}
rdr.Close();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
throw new GrException(ex, Messages.errProd);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
if (ex is GrException) throw ex;
throw new GrException(ex, Messages.errProd);
}
return room;
}
