/// <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>
/// 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);
}
}