public static List <Lemma <int, string> > GetIntLemmata(SqlConnection conn, SqlTransaction trans, string proc, bool addEmpty) { List <Lemma <int, string> > list = new List <Lemma <int, string> >(); if (addEmpty) { list.Add(new Lemma <int, string>(0, "")); } try { using (SqlDataReader reader = DB.GetDataReader(conn, trans, proc, CommandType.StoredProcedure)) { while (reader.Read()) { list.Add(new Lemma <int, string>(reader.GetInt32("id"), reader.GetString("name"))); } } } catch (Exception ex) { Common.Log.Error(ex); } return(list); }
public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid analResId) { using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_analysis_result", CommandType.StoredProcedure, new SqlParameter("@id", analResId))) { if (!reader.HasRows) { throw new Exception("Error: Analysis result with id " + analResId.ToString() + " was not found"); } reader.Read(); Id = reader.GetGuid("id"); AnalysisId = reader.GetGuid("analysis_id"); NuclideId = reader.GetGuid("nuclide_id"); Activity = reader.GetDoubleNullable("activity"); ActivityUncertaintyABS = reader.GetDoubleNullable("activity_uncertainty_abs"); ActivityApproved = reader.GetBoolean("activity_approved"); UniformActivity = reader.GetDoubleNullable("uniform_activity"); UniformActivityUnitId = reader.GetInt32("uniform_activity_unit_id"); DetectionLimit = reader.GetDoubleNullable("detection_limit"); DetectionLimitApproved = reader.GetBoolean("detection_limit_approved"); Accredited = reader.GetBoolean("accredited"); Reportable = reader.GetBoolean("reportable"); InstanceStatusId = reader.GetInt32("instance_status_id"); CreateDate = reader.GetDateTime("create_date"); CreateId = reader.GetGuid("create_id"); UpdateDate = reader.GetDateTime("update_date"); UpdateId = reader.GetGuid("update_id"); } NuclideName = GetNuclideName(conn, trans); Dirty = false; }
public static void PopulateComboBoxes(SqlConnection conn, string procedure, SqlParameter[] sqlParams, params ComboBox[] cbn) { List <Lemma <Guid, string> > list = new List <Lemma <Guid, string> >(); list.Add(new Lemma <Guid, string>(Guid.Empty, "")); using (SqlDataReader reader = DB.GetDataReader(conn, null, procedure, CommandType.StoredProcedure, sqlParams)) { while (reader.Read()) { list.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"))); } } foreach (ComboBox cb in cbn) { object o = cb.SelectedValue; cb.DataSource = new List <Lemma <Guid, string> >(list); cb.DisplayMember = "Name"; cb.ValueMember = "Id"; if (o != null) { cb.SelectedValue = o; } } }
public void Populate(SqlConnection conn, SqlTransaction trans) { if (Id == Guid.Empty) { return; } using (SqlDataReader reader = DB.GetDataReader(conn, trans, @" select a.number, am.name as 'analysis_method_name', l.name as 'laboratory_name', ws.id as 'workflow_status_id', ws.name as 'workflow_status_name' from analysis a inner join analysis_method am on am.id = a.analysis_method_id inner join laboratory l on l.id = a.laboratory_id inner join workflow_status ws on ws.id = a.workflow_status_id where a.id = @aid ", CommandType.Text, new SqlParameter("@aid", Id))) { reader.Read(); Number = reader.GetInt32("number"); AnalysisMethodName = reader.GetString("analysis_method_name"); LaboratoryName = reader.GetString("laboratory_name"); WorkflowStatusId = reader.GetInt32("workflow_status_id"); WorkflowStatusName = reader.GetString("workflow_status_name"); } }
public static bool HasRequiredFields(SqlConnection conn, SqlTransaction trans, Guid sampleId) { string query = "select number, sample_type_id, project_sub_id, laboratory_id, reference_date from sample where id = @id"; using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@id", sampleId))) { if (!reader.HasRows) { return(false); } reader.Read(); if (!DB.IsValidField(reader["number"]) || !DB.IsValidField(reader["sample_type_id"]) || !DB.IsValidField(reader["project_sub_id"]) || !DB.IsValidField(reader["laboratory_id"]) || !DB.IsValidField(reader["reference_date"])) { return(false); } if (!Utils.IsValidGuid(reader["sample_type_id"]) || !Utils.IsValidGuid(reader["project_sub_id"]) || !Utils.IsValidGuid(reader["laboratory_id"])) { return(false); } } return(true); }
public void Populate(SqlConnection conn, SqlTransaction trans) { if (Id == Guid.Empty) { return; } using (SqlDataReader reader = DB.GetDataReader(conn, trans, @" select s.number, st.name as 'sample_type_name', sc.name as 'sample_component_name', l.name as 'laboratory_name' from sample s inner join sample_type st on st.id = s.sample_type_id left outer join sample_component sc on sc.id = s.sample_component_id inner join laboratory l on l.id = s.laboratory_id where s.id = @sid ", CommandType.Text, new SqlParameter("@sid", Id))) { reader.Read(); Number = reader.GetInt32("number"); SampleTypeName = reader.GetString("sample_type_name"); SampleComponentName = reader.GetString("sample_component_name"); LaboratoryName = reader.GetString("laboratory_name"); } }
public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid sampParamId) { using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_sample_parameter", CommandType.StoredProcedure, new SqlParameter("@id", sampParamId))) { if (!reader.HasRows) { throw new Exception("Error: Sample parameter with id " + sampParamId.ToString() + " was not found"); } reader.Read(); Id = reader.GetGuid("id"); SampleId = reader.GetGuid("sample_id"); SampleParameterNameId = reader.GetGuid("sample_parameter_name_id"); Value = reader.GetString("value"); CreateDate = reader.GetDateTime("create_date"); CreateId = reader.GetGuid("create_id"); UpdateDate = reader.GetDateTime("update_date"); UpdateId = reader.GetGuid("update_id"); } Name = GetParameterName(conn, trans); Type = GetParameterType(conn, trans); Dirty = false; }
private void UpdateCurrentPreparations(TreeNode tnode) { if (tnode.Level != 1 || tnode.Tag == null) { return; } tnode.ToolTipText = ""; try { List <Guid> prepList = tnode.Tag as List <Guid>; string query = "select number from preparation where id in (" + String.Join(",", prepList.Select(x => "'" + x.ToString() + "'").ToArray()) + ")"; List <object> prepNums = new List <object>(); using (SqlConnection conn = DB.OpenConnection()) { using (SqlDataReader reader = DB.GetDataReader(conn, null, query, CommandType.Text)) while (reader.Read()) { prepNums.Add(mSample.Number + "/" + reader.GetString("number")); } } tnode.ToolTipText = "Connected preparations: " + String.Join(", ", prepNums); } catch {} }
public static void PopulateSampleTypePrepMeth(SqlConnection conn, SqlTransaction trans, TreeNode tnode, ListBox lb, ListBox lbInherited) { Guid sampleTypeId = Guid.Parse(tnode.Name); string query = @" select pm.id, pm.name from preparation_method pm inner join sample_type_x_preparation_method stpm on stpm.preparation_method_id = pm.id inner join sample_type st on stpm.sample_type_id = st.id and st.id = @sample_type_id order by name"; lb.Items.Clear(); using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@sample_type_id", sampleTypeId))) { while (reader.Read()) { Lemma <Guid, string> st = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")); lb.Items.Add(st); } } lbInherited.Items.Clear(); while (tnode.Parent != null) { tnode = tnode.Parent; sampleTypeId = Guid.Parse(tnode.Name); using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@sample_type_id", sampleTypeId))) { while (reader.Read()) { Lemma <Guid, string> st = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")); lbInherited.Items.Add(st); } } } }
public void Populate(SqlConnection conn, SqlTransaction trans) { if (Id == Guid.Empty) { return; } using (SqlDataReader reader = DB.GetDataReader(conn, trans, @" select p.number, pm.name as 'preparation_method_name', l.name as 'laboratory_name', ws.id as 'workflow_status_id', ws.name as 'workflow_status_name' from preparation p inner join preparation_method pm on pm.id = p.preparation_method_id inner join laboratory l on l.id = p.laboratory_id inner join workflow_status ws on ws.id = p.workflow_status_id where p.id = @pid ", CommandType.Text, new SqlParameter("@pid", Id))) { reader.Read(); Number = reader.GetInt32("number"); PreparationMethodName = reader.GetString("preparation_method_name"); LaboratoryName = reader.GetString("laboratory_name"); WorkflowStatusId = reader.GetInt32("workflow_status_id"); WorkflowStatusName = reader.GetString("workflow_status_name"); } }
public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid analId) { using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_analysis", CommandType.StoredProcedure, new SqlParameter("@id", analId))) { if (!reader.HasRows) { throw new Exception("Error: Analysis with id " + analId.ToString() + " was not found"); } reader.Read(); Id = reader.GetGuid("id"); Number = reader.GetInt32("number"); AssignmentId = reader.GetGuid("assignment_id"); LaboratoryId = reader.GetGuid("laboratory_id"); PreparationId = reader.GetGuid("preparation_id"); AnalysisMethodId = reader.GetGuid("analysis_method_id"); WorkflowStatusId = reader.GetInt32("workflow_status_id"); SpecterReference = reader.GetString("specter_reference"); ActivityUnitId = reader.GetGuid("activity_unit_id"); ActivityUnitTypeId = reader.GetGuid("activity_unit_type_id"); SigmaActivity = reader.GetDouble("sigma_act"); SigmaMDA = reader.GetDouble("sigma_mda"); NuclideLibrary = reader.GetString("nuclide_library"); MDALibrary = reader.GetString("mda_library"); InstanceStatusId = reader.GetInt32("instance_status_id"); Comment = reader.GetString("comment"); CreateDate = reader.GetDateTime("create_date"); CreateId = reader.GetGuid("create_id"); UpdateDate = reader.GetDateTime("update_date"); UpdateId = reader.GetGuid("update_id"); } Results.Clear(); List <Guid> analResIds = new List <Guid>(); using (SqlDataReader reader = DB.GetDataReader(conn, trans, "select id from analysis_result where analysis_id = @id and instance_status_id <= @instance_status_level", CommandType.Text, new SqlParameter("@id", analId), new SqlParameter("@instance_status_level", InstanceStatus.Active))) { while (reader.Read()) { analResIds.Add(reader.GetGuid("id")); } } foreach (Guid analResId in analResIds) { AnalysisResult ar = new AnalysisResult(); ar.LoadFromDB(conn, trans, analResId); Results.Add(ar); } ImportFile = String.Empty; Dirty = false; }
public void LoadFromDB(SqlConnection conn, SqlTransaction trans, Guid prepId) { using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_preparation", CommandType.StoredProcedure, new SqlParameter("@id", prepId))) { if (!reader.HasRows) { throw new Exception("Error: Preparation with id " + prepId.ToString() + " was not found"); } reader.Read(); Id = reader.GetGuid("id"); SampleId = reader.GetGuid("sample_id"); Number = reader.GetInt32("number"); AssignmentId = reader.GetGuid("assignment_id"); LaboratoryId = reader.GetGuid("laboratory_id"); PreparationGeometryId = reader.GetGuid("preparation_geometry_id"); PreparationMethodId = reader.GetGuid("preparation_method_id"); WorkflowStatusId = reader.GetInt32("workflow_status_id"); Amount = reader.GetDoubleNullable("amount"); PrepUnitId = reader.GetInt32("prep_unit_id"); Quantity = reader.GetDoubleNullable("quantity"); QuantityUnitId = reader.GetInt32("quantity_unit_id"); FillHeightMM = reader.GetDoubleNullable("fill_height_mm"); Volume_l = reader.GetDoubleNullable("volume_l"); PreprocessingVolume_l = reader.GetDoubleNullable("preprocessing_volume_l"); InstanceStatusId = reader.GetInt32("instance_status_id"); Comment = reader.GetString("comment"); CreateDate = reader.GetDateTime("create_date"); CreateId = reader.GetGuid("create_id"); UpdateDate = reader.GetDateTime("update_date"); UpdateId = reader.GetGuid("update_id"); } // Load analyses Analyses.Clear(); List <Guid> analysisIds = new List <Guid>(); using (SqlDataReader reader = DB.GetDataReader(conn, trans, "select id from analysis where preparation_id = @id", CommandType.Text, new SqlParameter("@id", Id))) { while (reader.Read()) { analysisIds.Add(reader.GetGuid("id")); } } foreach (Guid aId in analysisIds) { Analysis a = new Analysis(); a.LoadFromDB(conn, trans, aId); Analyses.Add(a); } }
public static bool HasAccreditationForOrderLine(SqlConnection conn, SqlTransaction trans, Guid labId, Guid sampleTypeId, Guid sampleComponentId, Guid prepMethId, Guid analMethId) { object o = GetScalar(conn, trans, "select path from sample_type where id = @id", CommandType.Text, new SqlParameter("@id", sampleTypeId)); if (o == null || o == DBNull.Value) { return(false); } string findSampleTypePath = o.ToString().ToLower(); bool accredFound = false; string query = @" select a.*, st.id as 'sample_type_id', st.path as 'sample_type_path', sc.id as 'sample_component_id' from accreditation_term a inner join accreditation_term_x_laboratory al on a.id = al.accreditation_term_id and al.laboratory_id = @laboratory_id inner join accreditation_term_x_sample_type ast on a.id = ast.accreditation_term_id left outer join sample_type st on st.id = ast.sample_type_id inner join accreditation_term_x_preparation_method apm on a.id = apm.accreditation_term_id and apm.preparation_method_id = @preparation_method_id inner join accreditation_term_x_analysis_method aam on a.id = aam.accreditation_term_id and aam.analysis_method_id = @analysis_method_id left outer join accreditation_term_x_sample_type_x_sample_component ass on a.id = ass.accreditation_term_id and st.id = ass.sample_type_id left outer join sample_component sc on sc.id = ass.sample_component_id where a.instance_status_id = 1"; SqlCommand cmd = new SqlCommand(query, conn, trans); using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new[] { new SqlParameter("@laboratory_id", labId), new SqlParameter("@preparation_method_id", prepMethId), new SqlParameter("@analysis_method_id", analMethId) })) { while (reader.Read()) { Guid accredId = reader.GetGuid("id"); Guid sampTypeId = reader.GetGuid("sample_type_id"); Guid sampCompId = reader.GetGuid("sample_component_id"); string sampleTypePath = reader.GetString("sample_type_path").ToLower(); if (findSampleTypePath.StartsWith(sampleTypePath)) { if (sampCompId != Guid.Empty) { if (sampCompId == sampleComponentId) { accredFound = true; } } else { accredFound = true; } } } } return(accredFound); }
private void FormOrdersAssignUsers_Load(object sender, EventArgs e) { SqlConnection conn = null; try { conn = DB.OpenConnection(); DataTable dt = DB.GetDataTable(conn, null, "select id, name from cv_account where email is not NULL order by name", CommandType.Text); DataColumn assignedColumn = new DataColumn("Assigned", typeof(bool)); assignedColumn.DefaultValue = false; dt.Columns.Add(assignedColumn); gridUsers.DataSource = dt; gridUsers.ReadOnly = false; gridUsers.Columns["name"].ReadOnly = true; gridUsers.Columns["Assigned"].ReadOnly = false; gridUsers.Columns["id"].Visible = false; gridUsers.Columns["name"].HeaderText = "User"; List <Guid> assignedUserIds = new List <Guid>(); using (SqlDataReader reader = DB.GetDataReader(conn, null, "select account_id from assignment_x_account where assignment_id = @aid", CommandType.Text, new SqlParameter("@aid", mAssignmentId))) { while (reader.Read()) { assignedUserIds.Add(reader.GetGuid("account_id")); } } foreach (DataGridViewRow row in gridUsers.Rows) { Guid uid = Utils.MakeGuid(row.Cells["id"].Value); if (assignedUserIds.Contains(uid)) { row.Cells["Assigned"].Value = true; } } } catch (Exception ex) { Common.Log.Error(ex); MessageBox.Show(ex.Message); DialogResult = DialogResult.Abort; Close(); } finally { conn?.Close(); } }
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 static void GetOrderRequiredInventory(SqlConnection conn, SqlTransaction trans, Guid assignmentId, out int nSamples, out int nPreparations, out int nAnalyses) { nSamples = nPreparations = nAnalyses = 0; string query = @" select ( select sum(ast.sample_count) from assignment a inner join assignment_sample_type ast on ast.assignment_id = a.id where a.id = @aid ) as 'nsamples', ( select sum(apm.preparation_method_count * ast.sample_count) as 'npreparations' from assignment a inner join assignment_sample_type ast on ast.assignment_id = a.id inner join assignment_preparation_method apm on apm.assignment_sample_type_id = ast.id and apm.preparation_laboratory_id = a.laboratory_id where a.id = @aid ) as 'npreparations', ( select sum(aam.analysis_method_count * apm.preparation_method_count * ast.sample_count) as 'nanalyses' from assignment a inner join assignment_sample_type ast on ast.assignment_id = a.id inner join assignment_preparation_method apm on apm.assignment_sample_type_id = ast.id left outer join assignment_analysis_method aam on aam.assignment_preparation_method_id = apm.id where a.id = @aid ) as 'nanalyses' "; using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@aid", assignmentId))) { if (reader.HasRows) { reader.Read(); if (IsValidField(reader["nsamples"])) { nSamples = reader.GetInt32("nsamples"); } if (IsValidField(reader["npreparations"])) { nPreparations = reader.GetInt32("npreparations"); } if (IsValidField(reader["nanalyses"])) { nAnalyses = reader.GetInt32("nanalyses"); } } } }
public static void PopulatePrepMethAnalMeths(SqlConnection conn, SqlTransaction trans, Guid preparationMethodId, ListBox lb) { lb.Items.Clear(); using (SqlDataReader reader = DB.GetDataReader(conn, trans, "csp_select_analysis_methods_for_preparation_method", CommandType.StoredProcedure, new SqlParameter("@preparation_method_id", preparationMethodId))) { while (reader.Read()) { Lemma <Guid, string> n = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")); lb.Items.Add(n); } } }
public static void PopulateSampleComponents(SqlConnection conn, Guid sampleTypeId, ListBox lb) { lb.Items.Clear(); using (SqlDataReader reader = DB.GetDataReader(conn, null, "csp_select_sample_components_for_sample_type", CommandType.StoredProcedure, new SqlParameter("@sample_type_id", sampleTypeId))) { while (reader.Read()) { Lemma <Guid, string> sampleComponent = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")); lb.Items.Add(sampleComponent); } } }
private void FormSampleParameter_Load(object sender, EventArgs e) { lblTypeInfo.Text = ""; SampleParameterName spn = new SampleParameterName(); spn.Id = Guid.Empty; spn.Name = ""; spn.Type = ""; cboxSampleParameterNames.Items.Add(spn); SqlConnection conn = null; try { conn = DB.OpenConnection(); SqlDataReader reader = DB.GetDataReader(conn, null, "select id, name, type from sample_parameter_name order by name", CommandType.Text); while (reader.Read()) { spn = new SampleParameterName(); spn.Id = reader.GetGuid("id"); spn.Name = reader.GetString("name"); spn.Type = reader.GetString("type"); cboxSampleParameterNames.Items.Add(spn); } } catch (Exception ex) { Common.Log.Error(ex); MessageBox.Show(ex.Message); DialogResult = DialogResult.Abort; Close(); } finally { conn?.Close(); } cboxSampleParameterNames.DisplayMember = "Name"; cboxSampleParameterNames.ValueMember = "Id"; if (mSPId != Guid.Empty) { SampleParameter p = mSample.Parameters.Find(x => x.Id == mSPId); cboxSampleParameterNames.Text = p.Name; cboxSampleParameterNames.Enabled = false; tbSampleParameterValue.Text = p.Value; lblTypeInfo.Text = "Parameter type: " + p.Type; } }
public static void PopulateOrderYears(SqlConnection conn, ComboBox cbox) { List <string> years = new List <string>(); years.Add(""); using (SqlDataReader reader = DB.GetDataReader(conn, null, "select distinct year(create_date) as 'year' from assignment order by year desc", CommandType.Text)) { while (reader.Read()) { years.Add(reader.GetString("year")); } } cbox.DataSource = years; }
public static void PopulateOrderWorkflowStatus(SqlConnection conn, ComboBox cbox) { List <Lemma <int, string> > stats = new List <Lemma <int, string> >(); stats.Add(new Lemma <int, string>(0, "")); using (SqlDataReader reader = DB.GetDataReader(conn, null, "csp_select_workflow_status", CommandType.StoredProcedure)) { while (reader.Read()) { stats.Add(new Lemma <int, string>(reader.GetInt32("id"), reader.GetString("name"))); } } cbox.DataSource = stats; }
public static void PopulateSampleTypePrepMeth(SqlConnection conn, Guid sampleTypeId, Guid labId, ComboBox cbox) { List <Lemma <Guid, string> > list = new List <Lemma <Guid, string> >(); string query = @" select pm.id, pm.name_short as 'name' from preparation_method pm inner join sample_type_x_preparation_method stpm on stpm.preparation_method_id = pm.id inner join laboratory_x_preparation_method lxpm on lxpm.preparation_method_id = pm.id and lxpm.laboratory_id = @laboratory_id inner join sample_type st on stpm.sample_type_id = st.id and st.id = @sample_type_id where pm.instance_status_id <= 1 order by name"; using (SqlDataReader reader = DB.GetDataReader(conn, null, query, CommandType.Text, new SqlParameter("@sample_type_id", sampleTypeId), new SqlParameter("@laboratory_id", labId))) { while (reader.Read()) { Lemma <Guid, string> st = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")); list.Add(st); } } while (true) { sampleTypeId = DB.GetSampleTypeParentId(conn, null, sampleTypeId); using (SqlDataReader reader = DB.GetDataReader(conn, null, query, CommandType.Text, new SqlParameter("@sample_type_id", sampleTypeId), new SqlParameter("@laboratory_id", labId))) { while (reader.Read()) { Lemma <Guid, string> st = new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name")); list.Add(st); } } if (sampleTypeId == Guid.Empty) { break; } } cbox.DisplayMember = "name"; cbox.ValueMember = "id"; cbox.DataSource = list; }
public static void PopulateAccreditationTermNuclides(SqlConnection conn, SqlTransaction trans, Guid accredId, ListBox lb) { lb.Items.Clear(); string query = @" select n.id, n.name from nuclide n inner join accreditation_term_x_nuclide atxn on n.id = atxn.nuclide_id and atxn.accreditation_term_id = @accreditation_term_id order by n.name"; using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@accreditation_term_id", accredId))) { while (reader.Read()) { lb.Items.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"))); } } }
public static List <Lemma <double, string> > GetSigmaValues(SqlConnection conn, SqlTransaction trans, bool MDA) { List <Lemma <double, string> > lst = new List <Lemma <double, string> >(); lst.Add(new Lemma <double, string>(0d, "")); using (SqlDataReader reader = DB.GetDataReader(conn, trans, MDA ? "csp_select_sigma_mda_values" : "csp_select_sigma_values", CommandType.StoredProcedure)) { while (reader.Read()) { lst.Add(new Lemma <double, string>(reader.GetDouble(0), reader.GetString(1))); } } return(lst); }
public static void PopulateAccreditationTermAnalysisMethods(SqlConnection conn, SqlTransaction trans, Guid accredId, ListBox lb) { lb.Items.Clear(); string query = @" select am.id, am.name from analysis_method am inner join accreditation_term_x_analysis_method atxam on am.id = atxam.analysis_method_id and atxam.accreditation_term_id = @accreditation_term_id order by am.name"; using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@accreditation_term_id", accredId))) { while (reader.Read()) { lb.Items.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"))); } } }
public static void PopulateAccreditationTermLaboratories(SqlConnection conn, SqlTransaction trans, Guid accredId, ListBox lb) { lb.Items.Clear(); string query = @" select l.id, l.name from laboratory l inner join accreditation_term_x_laboratory atxl on l.id = atxl.laboratory_id and atxl.accreditation_term_id = @accreditation_term_id order by l.name"; using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@accreditation_term_id", accredId))) { while (reader.Read()) { lb.Items.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"))); } } }
public static void PopulateProjectMain(SqlConnection conn, SqlTransaction trans, Guid accountId, int instanceStatusLevel, params ComboBox[] cbn) { List <Lemma <Guid, string> > list = new List <Lemma <Guid, string> >(); list.Add(new Lemma <Guid, string>(Guid.Empty, "")); string query; if (!Utils.IsValidGuid(Common.LabId)) { query = @" select distinct pm.id, pm.name from project_main pm inner join project_sub ps on ps.project_main_id = pm.id inner join project_sub_x_account psxa on psxa.project_sub_id = ps.id and psxa.account_id = @aid where pm.instance_status_id <= @isl order by pm.name "; } else { query = "select id, name from project_main where instance_status_id <= @isl order by name"; } using (SqlDataReader reader = DB.GetDataReader(conn, trans, query, CommandType.Text, new SqlParameter("@aid", accountId), new SqlParameter("@isl", instanceStatusLevel) )) { while (reader.Read()) { list.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"))); } } foreach (ComboBox cb in cbn) { object o = cb.SelectedValue; cb.DataSource = new List <Lemma <Guid, string> >(list); cb.DisplayMember = "Name"; cb.ValueMember = "Id"; if (o != null) { cb.SelectedValue = o; } } }
private void FormSampParamName_Load(object sender, EventArgs e) { SqlConnection conn = null; try { conn = DB.OpenConnection(); cboxSampParamType.Items.Clear(); using (SqlDataReader reader = DB.GetDataReader(conn, null, "select * from sample_parameter_type order by name", CommandType.Text)) { while (reader.Read()) { cboxSampParamType.Items.Add(reader.GetString("name")); } } if (mSampParamNameId != Guid.Empty) { using (SqlDataReader reader = DB.GetDataReader(conn, null, "select * from sample_parameter_name where id = @id", CommandType.Text, new SqlParameter("@id", mSampParamNameId))) { if (!reader.HasRows) { throw new Exception("FormSampParamName_Load: No sample parameter name found with id: " + mSampParamNameId.ToString()); } reader.Read(); cboxSampParamType.Text = reader.GetString("type"); tbParamName.Text = reader.GetString("name"); } cboxSampParamType.Enabled = false; } } catch (Exception ex) { Common.Log.Error(ex); MessageBox.Show(ex.Message); DialogResult = DialogResult.Abort; Close(); } finally { conn?.Close(); } }
public List <Guid> GetSampleTypeIdsForSampleType(SqlConnection conn, SqlTransaction trans, string sampleTypeRoot) { List <Guid> ids = new List <Guid>(); using (SqlDataReader astReader = DB.GetDataReader(conn, null, "csp_select_assignment_sample_types_for_sample_type_name", CommandType.StoredProcedure, new SqlParameter("@assignment_id", Id), new SqlParameter("@sample_type_name", sampleTypeRoot))) { while (astReader.Read()) { Guid stId = astReader.GetGuid("sample_type_id"); ids.Add(stId); } } return(ids); }
private static void AddSampleTypeComponentsAscending(SqlConnection conn, Guid sampleTypeId, TreeNode tnode, List <Lemma <Guid, string> > comps) { using (SqlDataReader reader = DB.GetDataReader(conn, null, "csp_select_sample_components_for_sample_type", CommandType.StoredProcedure, new SqlParameter("@sample_type_id", sampleTypeId))) { while (reader.Read()) { comps.Add(new Lemma <Guid, string>(reader.GetGuid("id"), reader.GetString("name"))); } } if (tnode.Parent != null) { Guid parentId = Guid.Parse(tnode.Parent.Name); AddSampleTypeComponentsAscending(conn, parentId, tnode.Parent, comps); } }