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