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