/// <summary>
/// delete a person from the db
/// </summary>
/// <param name="person">the person to delete</param>
public static void DeletePerson(Person2 person)
{
try
{
SqlConnection conn = new SqlConnection(ConnectionManager.GetConnectionString());
SqlTransaction transaction;
conn.Open();
transaction = conn.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
byte[] timestamp = person.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * " +
"FROM [" + person.pType.ToString() + "] " +
"WHERE id_person=@id AND timestamp=@timestamp;", conn, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = person.ID;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
cmd = new SqlCommand("DELETE FROM [" + person.pType.ToString() + "] " +
"WHERE id_person=@id;", conn, transaction);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = person.ID;
cmd.ExecuteNonQuery();
}
else
{
rdr.Close();
Console.WriteLine("Cross modify");
throw new GrException(Messages.recommencerDelete);
}
transaction.Commit();
}
catch (SqlException sqlError)
{
System.Diagnostics.Debug.WriteLine(sqlError.Message);
System.Diagnostics.Debug.WriteLine(sqlError.StackTrace);
transaction.Rollback();
throw new GrException(sqlError, Messages.errProd);
}
conn.Close();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
throw (ex is GrException) ? ex : new GrException(ex, Messages.errProd);
}
}
/// <summary>
/// Creates a person
/// </summary>
/// <param name="person">the person to create</param>
public static void CreatePerson(Person2 person)
{
try
{
SqlConnection db = new SqlConnection(ConnectionManager.GetConnectionString());
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.ReadUncommitted);
try
{
SqlCommand cmd = new SqlCommand("INSERT INTO [" + person.pType.ToString() + "] " +
"([email], [password], [firstname], [lastname], [username]) " +
"VALUES (@email, @password, @firstname, @lastname, @username);", db, transaction);
cmd.Parameters.Add("@email", SqlDbType.Char).Value = person.Email;
cmd.Parameters.Add("@password", SqlDbType.Char).Value = person.Password;
cmd.Parameters.Add("@firstname", SqlDbType.Char).Value = person.FirstName;
cmd.Parameters.Add("@lastname", SqlDbType.Char).Value = person.LastName;
cmd.Parameters.Add("@username", SqlDbType.Char).Value = person.Username;
cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (SqlException sqlError)
{
System.Diagnostics.Debug.WriteLine(sqlError.Message);
System.Diagnostics.Debug.WriteLine(sqlError.StackTrace);
transaction.Rollback();
throw new GrException(sqlError, (sqlError.Number > 50000) ? sqlError.Message : Messages.uniqueUserEmail);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
throw (ex is GrException) ? ex : new GrException(ex, Messages.errProd);
}
}
public ActionResult Delete(int id, Person2 person)
{
try
{
PersonModel.DeletePerson(person);
return RedirectToAction("Index");
}
catch (GrException gex)
{
ModelState.AddModelError("", gex.UserMessage);
// get updated data
Person person_ = PersonModel.getPerson(id, person.pType);
// update timestamp in case user really wants to delete this
ModelState.SetModelValue("Timestamp", new ValueProviderResult(person_.Timestamp, "", CultureInfo.InvariantCulture));
// show new values before user decided to really delete them
return View(person_.toPerson2());
}
}
public ActionResult Create(Person2 person)
{
if (ModelState.IsValid)
{
try
{
PersonModel.CreatePerson(person);
return RedirectToAction("Index");
}
catch (GrException gex)
{
ModelState.AddModelError("", gex.UserMessage);
return View();
}
}
else
{
// addinge extra error message here in case JS is deactivated on client.
ModelState.AddModelError("", Messages.invalidData);
return View();
}
}
/// <summary>
/// update a person
/// </summary>
/// <param name="person">the person object containing the new data to update</param>
public static void UpdatePerson(Person2 person)
{
try
{
SqlConnection db = new SqlConnection(ConnectionManager.GetConnectionString());
SqlTransaction transaction;
db.Open();
transaction = db.BeginTransaction(IsolationLevel.RepeatableRead);
try
{
byte[] timestamp = person.getByteTimestamp();
SqlCommand cmd = new SqlCommand("SELECT * " +
"FROM [" + person.pType.ToString() + "] P " +
"WHERE P.id_person=@id_person AND P.timestamp=@timestamp;", db, transaction);
cmd.Parameters.Add("@id_person", SqlDbType.Int).Value = person.ID;
cmd.Parameters.Add("@timestamp", SqlDbType.Binary).Value = timestamp;
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read())
{
rdr.Close();
cmd = new SqlCommand("UPDATE [" + person.pType.ToString() + "] " +
"SET [email]=@email, [firstname]=@firstname, [lastname]=@lastname, [username]=@username " +
"WHERE id_person=@id_person", db, transaction);
cmd.Parameters.Add("@email", SqlDbType.Char).Value = person.Email;
cmd.Parameters.Add("@firstname", SqlDbType.Char).Value = person.FirstName;
cmd.Parameters.Add("@lastname", SqlDbType.Char).Value = person.LastName;
cmd.Parameters.Add("@username", SqlDbType.Char).Value = person.Username;
cmd.Parameters.Add("@id_person", SqlDbType.Int).Value = person.ID;
cmd.ExecuteNonQuery();
}
else
{
rdr.Close();
System.Diagnostics.Debug.WriteLine("Cross modify");
throw new GrException(Messages.recommencerEdit);
}
transaction.Commit();
}
catch (SqlException sqlError)
{
System.Diagnostics.Debug.WriteLine(sqlError.Message);
System.Diagnostics.Debug.WriteLine(sqlError.StackTrace);
transaction.Rollback();
throw new GrException(sqlError, (sqlError.Number > 50000) ? sqlError.Message : Messages.uniqueUserEmail);
}
finally
{
db.Close();
}
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
throw (ex is GrException) ? ex : new GrException(ex, Messages.errProd);
}
}
public ActionResult Edit(int id, Person2 person)
{
// all but password !
if (ModelState.IsValidField("ID") &&
ModelState.IsValidField("pType") &&
ModelState.IsValidField("Timestamp") &&
ModelState.IsValidField("FirstName") &&
ModelState.IsValidField("LastName") &&
ModelState.IsValidField("Email") &&
ModelState.IsValidField("Username")
)
{
try
{
PersonModel.UpdatePerson(person);
return RedirectToAction("Index");
}
catch (GrException gex)
{
if (person.Username == HttpContext.User.Identity.Name)
{
ModelState.AddModelError("", Messages.editSameUser);
}
ModelState.AddModelError("", gex.UserMessage);
return View(person);
}
}
else
{
// addinge extra error message here in case JS is deactivated on client.
ModelState.AddModelError("", Messages.invalidData);
return View();
}
}