public static YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogViewCollection GetMaterialTrackingLogViewCollectionByBatchIdMasterAccessionNo(string materialTrackingBatchId, string masterAccessionNo) { YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogViewCollection result = new YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogViewCollection(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "Select * from ViewMaterialTrackingLog where MaterialTrackingBatchId = @MaterialTrackingBatchId and MasterAccessionNo = @MasterAccessionNo"; cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@MaterialTrackingBatchId", System.Data.SqlDbType.VarChar).Value = materialTrackingBatchId; cmd.Parameters.Add("@MasterAccessionNo", System.Data.SqlDbType.VarChar).Value = masterAccessionNo; using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.ProductionConnectionString)) { cn.Open(); cmd.Connection = cn; using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogView materialTrackingLogView = new YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogView(); YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new Persistence.SqlDataReaderPropertyWriter(materialTrackingLogView, dr); propertyWriter.WriteProperties(); result.Add(materialTrackingLogView); } } } return(result); }
public static YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogViewCollection GetMaterialTrackingLogViewCollectionByBatchId(string materialTrackingBatchId) { YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogViewCollection result = new YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogViewCollection(); string sql = "SELECT " + "mtb.MaterialTrackingBatchId AS MaterialTrackingBatchId, " + "mtl.MaterialTrackingLogId AS MaterialTrackingLogId, " + "ao.MasterAccessionNo AS MasterAccessionNo, " + "ao.ClientAccessionNo AS ClientAccessionNo, " + "ao.PLastName AS PLastName, " + "ao.PFirstName AS PFirstName, " + "mtl.MaterialType AS MaterialType, " + " mtl.MaterialId AS MaterialId, " + "mtl.LogDate AS LogDate, " + "(CASE " + "WHEN " + "(mtl.MaterialType = 'Slide') " + "THEN " + "(SELECT " + "tblSlideOrder.Label " + "FROM " + "tblSlideOrder " + "WHERE " + "(tblSlideOrder.SlideOrderId = mtl.MaterialId)) " + "ELSE 'None' " + "END) AS `MaterialLabel`, " + "mtl.LoggedBy AS `LoggedBy` " + "FROM " + "tblMaterialTrackingBatch mtb " + "JOIN tblMaterialTrackingLog mtl ON mtb.MaterialTrackingBatchId = mtl.MaterialTrackingBatchId " + "Left JOIN tblAccessionOrder ao ON mtl.MasterAccessionNo = ao.MasterAccessionNo " + "where mtb.MaterialTrackingBatchId = @MaterialTrackingBatchId order by mtl.LogDate desc"; MySqlCommand cmd = new MySqlCommand(); cmd.CommandText = sql; cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.AddWithValue("@MaterialTrackingBatchId", materialTrackingBatchId); using (MySqlConnection cn = new MySqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString)) { cn.Open(); cmd.Connection = cn; using (MySqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogView materialTrackingLogView = new YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogView(); YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new Persistence.SqlDataReaderPropertyWriter(materialTrackingLogView, dr); propertyWriter.WriteProperties(); result.Add(materialTrackingLogView); } } } return(result); }
public static YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogViewCollection GetMaterialTrackingLogViewCollectionByBatchIdMasterAccessionNo(string materialTrackingBatchId, string masterAccessionNo) { YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogViewCollection result = new YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogViewCollection(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "Select * from ViewMaterialTrackingLog where MaterialTrackingBatchId = @MaterialTrackingBatchId and MasterAccessionNo = @MasterAccessionNo"; cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@MaterialTrackingBatchId", System.Data.SqlDbType.VarChar).Value = materialTrackingBatchId; cmd.Parameters.Add("@MasterAccessionNo", System.Data.SqlDbType.VarChar).Value = masterAccessionNo; using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.ProductionConnectionString)) { cn.Open(); cmd.Connection = cn; using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogView materialTrackingLogView = new YellowstonePathology.Business.MaterialTracking.Model.MaterialTrackingLogView(); YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new Persistence.SqlDataReaderPropertyWriter(materialTrackingLogView, dr); propertyWriter.WriteProperties(); result.Add(materialTrackingLogView); } } } return result; }