public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid astId) { if (!AssignmentSampleType.IdExists(conn, trans, astId)) { throw new Exception("Assignment sample type with id " + astId.ToString() + " was not found"); } PreparationMethods.Clear(); using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_assignment_sample_type", CommandType.StoredProcedure, new SqlParameter("@id", astId))) { if (!reader.HasRows) { throw new Exception("Assignment sample type with id " + astId.ToString() + " was not found"); } reader.Read(); Id = reader.GetGuid("id"); AssignmentId = reader.GetGuid("assignment_id"); SampleTypeId = reader.GetGuid("sample_type_id"); SampleComponentId = reader.GetGuid("sample_component_id"); SampleCount = reader.GetInt32("sample_count"); RequestedActivityUnitId = reader.GetGuid("requested_activity_unit_id"); RequestedActivityUnitTypeId = reader.GetGuid("requested_activity_unit_type_id"); ReturnToSender = reader.GetBoolean("return_to_sender"); Comment = reader.GetString("comment"); CreateDate = reader.GetDateTime("create_date"); CreateId = reader.GetGuid("create_id"); UpdateDate = reader.GetDateTime("update_date"); UpdateId = reader.GetGuid("update_id"); Dirty = false; } List <Guid> prepMethIds = new List <Guid>(); using (SqlDataReader reader = DB.GetDataReader(conn, trans, "select id from assignment_preparation_method where assignment_sample_type_id = @astId", CommandType.Text, new SqlParameter("@astId", astId))) { while (reader.Read()) { prepMethIds.Add(reader.GetGuid("id")); } } foreach (Guid apmId in prepMethIds) { AssignmentPreparationMethod apm = new AssignmentPreparationMethod(); apm.LoadFromDB(conn, trans, apmId); PreparationMethods.Add(apm); } }
public void StoreToDB(SqlConnection conn, SqlTransaction trans) { SqlCommand cmd = new SqlCommand("", conn, trans); if (!AssignmentSampleType.IdExists(conn, trans, Id)) { // Insert new ast cmd.CommandText = "csp_insert_assignment_sample_type"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@id", Id); cmd.Parameters.AddWithValue("@assignment_id", AssignmentId, Guid.Empty); cmd.Parameters.AddWithValue("@sample_type_id", SampleTypeId, Guid.Empty); cmd.Parameters.AddWithValue("@sample_component_id", SampleComponentId, Guid.Empty); cmd.Parameters.AddWithValue("@sample_count", SampleCount); cmd.Parameters.AddWithValue("@requested_activity_unit_id", RequestedActivityUnitId, Guid.Empty); cmd.Parameters.AddWithValue("@requested_activity_unit_type_id", RequestedActivityUnitTypeId, Guid.Empty); cmd.Parameters.AddWithValue("@return_to_sender", ReturnToSender); cmd.Parameters.AddWithValue("@comment", Comment, String.Empty); cmd.Parameters.AddWithValue("@create_date", DateTime.Now); cmd.Parameters.AddWithValue("@create_id", Common.UserId, Guid.Empty); cmd.Parameters.AddWithValue("@update_date", DateTime.Now); cmd.Parameters.AddWithValue("@update_id", Common.UserId, Guid.Empty); cmd.ExecuteNonQuery(); Dirty = false; } else { if (Dirty) { // Update existing ast cmd.CommandText = "csp_update_assignment_sample_type"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@id", Id); cmd.Parameters.AddWithValue("@assignment_id", AssignmentId, Guid.Empty); cmd.Parameters.AddWithValue("@sample_type_id", SampleTypeId, Guid.Empty); cmd.Parameters.AddWithValue("@sample_component_id", SampleComponentId, Guid.Empty); cmd.Parameters.AddWithValue("@sample_count", SampleCount); cmd.Parameters.AddWithValue("@requested_activity_unit_id", RequestedActivityUnitId, Guid.Empty); cmd.Parameters.AddWithValue("@requested_activity_unit_type_id", RequestedActivityUnitTypeId, Guid.Empty); cmd.Parameters.AddWithValue("@return_to_sender", ReturnToSender); cmd.Parameters.AddWithValue("@comment", Comment, String.Empty); cmd.Parameters.AddWithValue("@update_date", DateTime.Now); cmd.Parameters.AddWithValue("@update_id", Common.UserId, Guid.Empty); cmd.ExecuteNonQuery(); Dirty = false; } } foreach (AssignmentPreparationMethod apm in PreparationMethods) { apm.StoreToDB(conn, trans); } // Remove deleted prep methods from DB List <Guid> storedPrepMethIds = new List <Guid>(); using (SqlDataReader reader = DB.GetDataReader(conn, trans, "select id from assignment_preparation_method where assignment_sample_type_id = @id", CommandType.Text, new SqlParameter("@id", Id))) { while (reader.Read()) { storedPrepMethIds.Add(reader.GetGuid("id")); } } cmd.CommandText = "delete from assignment_preparation_method where id = @id"; cmd.CommandType = CommandType.Text; foreach (Guid apmId in storedPrepMethIds) { if (PreparationMethods.FindIndex(x => x.Id == apmId) == -1) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@id", apmId); cmd.ExecuteNonQuery(); } } }