private SmartCollection<Sample> GetSamples(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, string searchString, Identification identification) { try { SmartCollection<Sample> resultList = new SmartCollection<Sample>(); dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "uspGetSamples"; dbCommand.Parameters.Clear(); dbCommand.Parameters.Add("@SearchString", System.Data.SqlDbType.NVarChar, 100).Value = searchString; DataTable returnDT = dbConnection.ExecuteQuery(dbCommand); foreach (DataRow row in returnDT.Rows) { Sample sample = new Sample(); sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]); sample.Description = row["Description"].ToString(); sample.ReceivedDate = row["ReceivedDate"] != DBNull.Value ? Convert.ToDateTime(row["ReceivedDate"]) : new DateTime?(); sample.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : new Int32(); sample.ClientName = row["ClientName"].ToString(); if (sample.ClientId > -1) { //sample.Client = new Client() { ClientId = (int)sample.ClientId, ClientName = sample.ClientName }; using (ClientDAO clientDao = new ClientDAO()) { sample.Client = clientDao.GetClient((int)sample.ClientId); } } sample.Status = row["Status"] != DBNull.Value ? (EnumSampleStatus)row["Status"] : EnumSampleStatus.InProgress; sample.PONumber = row["PONumber"].ToString(); sample.FormulationId = row["FormulationID"].ToString(); sample.LotNumber = row["LotNumber"].ToString(); sample.ProjectNumber = row["ProjectNumber"].ToString(); sample.StorageLocationId = row["StorageLocationID"] != DBNull.Value ? Convert.ToInt32(row["StorageLocationID"]) : new Int32(); sample.StorageLocationName = row["StorageLocationName"].ToString(); sample.StorageLocationConditions = row["StorageLocationConditions"].ToString(); sample.StorageLocationCode = row["StorageLocationCode"].ToString(); if (sample.StorageLocationId > -1) sample.StorageLocation = new StorageLocation() { StorageLocationId = sample.StorageLocationId, Active = true, Description = row["StorageLocationName"].ToString(), Conditions = row["StorageLocationConditions"].ToString(), LocationCode = row["StorageLocationCode"].ToString() }; sample.RequestedStorageId = row["RequestedStorageID"] != DBNull.Value ? Convert.ToInt32(row["RequestedStorageID"]) : new Int32(); sample.RequestedStorageName = row["RequestedStorageName"].ToString(); if (sample.RequestedStorageId > -1) sample.RequestedStorageLocation = new StorageLocation() { StorageLocationId = sample.RequestedStorageId, Active = true, Description = row["RequestedStorageName"].ToString(), Conditions = "" }; sample.DosageId = row["DosageID"] != DBNull.Value ? Convert.ToInt32(row["DosageID"]) : new Int32(); sample.DosageName = row["DosageName"] != DBNull.Value ? row["DosageName"].ToString() : null; if (sample.DosageId > -1) sample.Dosage = new Dosage() { DosageId = sample.DosageId, Active = true, DosageName = row["DosageName"].ToString() }; sample.Containers = row["Containers"] != DBNull.Value ? Convert.ToInt32(row["Containers"]) : new Int32(); sample.ContainerDescription = row["ContainerDescription"].ToString(); sample.VolumeAmount = row["VolumeAmount"] != DBNull.Value ? Convert.ToDecimal(row["VolumeAmount"]) : new Decimal(); sample.VolumeUOMId = row["VolumeUOMID"] != DBNull.Value ? Convert.ToInt32(row["VolumeUOMID"]) : new Int32(); sample.VolumeUOM = row["VolumeUOM"].ToString(); sample.TimepointStudyYN = row["TimepointStudyYN"] != DBNull.Value ? Convert.ToBoolean(row["TimepointStudyYN"]) : false; sample.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false; sample.CompoundedBy = row["CompoundedBy"].ToString(); sample.CompoundedDate = row["CompoundedDate"] != DBNull.Value ? Convert.ToDateTime(row["CompoundedDate"]) : new DateTime?(); sample.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32(); sample.CreatedUser = row["CreatedUser"].ToString(); sample.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null; sample.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32(); sample.ModifiedUser = row["ModifiedUser"].ToString(); sample.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null; sample.SampleAnalytes = this.GetSampleAnalytes((int)sample.ARLNumber); sample.SampleCharges = this.GetSampleCharges((int)sample.ARLNumber); sample.SampleDocuments = this.GetSampleDocuments((int)sample.ARLNumber); sample.SampleNotes = this.GetSampleNotes((int)sample.ARLNumber, identification); sample.SampleTests = this.GetSampleTests((int)sample.ARLNumber, false, identification); resultList.Add(sample); } returnDT = null; return resultList; } catch { throw; } }
public Client DoGetClient(int id, Guid userToken) { logger.Log(LogLevel.Trace, AppLib.GetCaller(logger)); using (ClientDAO dao = new ClientDAO()) { return dao.GetClient(id); } }
public SmartCollection<Order> SearchOrders(string searchString, IEnumerable<ISearchItem> searchItems, Identification identification) { try { SmartCollection<Order> resultList = new SmartCollection<Order>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = @" select distinct orders.id,orders.parentid,orders.ponumber, orders.received_date,orders.start_date, orders.due_date,orders.report_date, orders.status, (users.firstname + ' ' + users.lastname) as modifieduser, (users2.firstname + ' ' + users2.lastname) as createduser, orders.modified_by, orders.modified_date, orders.created_by, orders.created_date, (select sum(amount) from orders_charges where parentid = orders.id and delete_date IS NULL) as ChargesTotal, (select sum(orders_samples_tests.item_price) from orders_samples_tests where orders_samples_tests.parentid = orders.id and orders_samples_tests.delete_date IS NULL and orders_samples_tests.status < 7 ) as TestsTotal from orders LEFT JOIN orders_samples as samples ON samples.parentid = orders.id LEFT JOIN orders_samples_containers as containers ON containers.parentid = samples.id LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id LEFT JOIN list.departments as dept ON dept.departmentid = tests.departmentid LEFT JOIN customers ON customers.id = orders.parentid LEFT JOIN [User] AS users ON orders.modified_by = users.UserID LEFT JOIN [User] as users2 ON orders.created_by = users2.UserID " + SysLib.BuildSearchAllWhereClause(searchString, searchItems); if (identification.ClientYN) { sql += " and customers.id = @companyId "; DbCommand.Parameters.Add("@companyId", System.Data.SqlDbType.Int).Value = identification.ClientId; } sql += " AND orders.delete_date IS NULL "; sql += "ORDER BY orders.modified_date DESC "; DbCommand.CommandText = sql; DataTable customerDT = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in customerDT.Rows) { Order order = new Order(); order.Id = Convert.ToInt32(row["Id"]); order.ParentId = row["parentid"] != DBNull.Value ? (int)row["parentid"] : 0; order.Status = row["status"] != DBNull.Value ? (EnumOrderStatus)row["status"] : EnumOrderStatus.Open; order.PoNumber = row["ponumber"].ToString(); order.OrderTotal = Convert.ToDecimal(row["ChargesTotal"] != DBNull.Value ? (decimal)row["ChargesTotal"] : 0) + Convert.ToDecimal(row["TestsTotal"] != DBNull.Value ? (decimal)row["TestsTotal"] : 0); if (row["received_date"] != DBNull.Value) order.ReceivedDate = (DateTime)row["received_date"]; if (row["start_date"] != DBNull.Value) order.StartDate = (DateTime)row["start_date"]; if (row["due_date"] != DBNull.Value) order.DueDate = (DateTime)row["due_date"]; if (row["report_date"] != DBNull.Value) order.ReportDate = (DateTime)row["report_date"]; order.CreatedUser = row["createduser"].ToString(); order.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0; order.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime)row["created_date"] : (DateTime)SqlDateTime.Null; order.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0; order.ModifiedUser = row["modifieduser"].ToString(); order.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime)row["modified_date"] : (DateTime)SqlDateTime.Null; order.Samples = this.GetSamples(ref dbConnection, ref dbCommand, "", identification); order.SampleTests = this.GetSampleTests(ref dbConnection, ref dbCommand, order.Id, true, identification); using (ClientDAO dao = new ClientDAO()) { order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId); } resultList.Add(order); } customerDT = null; } } else { throw new Exception("Unable to Connect"); } } return resultList; } catch { throw; } }
public SmartCollection<Order> SearchModuleOrders(int? customerId, DateTime? orderReceivedStartDate, DateTime? orderReceivedEndDate, int? testId, int? analyteId) { try { var searchCustomers = customerId != null && customerId > 0; var searchDates = orderReceivedStartDate != null || orderReceivedEndDate != null; var searchTests = testId != null && testId > 0; var serachAnalytes = analyteId != null && analyteId > 0; var result = new SmartCollection<Order>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = @" select distinct orders.id, orders.parentid, customers.customer_name, orders.received_date, orders.status from orders LEFT JOIN customers ON customers.id = orders.parentid "; if (searchTests) { sql += @" LEFT JOIN orders_samples as samples ON samples.parentid = orders.id LEFT JOIN orders_samples_tests as tests ON tests.parentid = orders.id "; } if (serachAnalytes) { // NOTE: joining to seperate order_samples / order_samples_tests table aliases // is so searching tests and analytes can be be done independently or concurrently sql += @" LEFT JOIN orders_samples as samples2 ON samples2.parentid = orders.id LEFT JOIN orders_samples_tests as tests2 ON tests2.parentid = orders.id LEFT JOIN orders_samples_analytes as analytes ON analytes.parentid = tests2.id "; } sql += @" WHERE orders.delete_date IS NULL "; if (searchCustomers) { sql += @" AND customers.id = @customerId "; } if (searchDates) { sql += @" AND (received_date >= @orderReceivedStartDate and received_date <= @orderReceivedEndDate) "; } if (searchTests) { sql += @" AND tests.testid = @testId "; } if (serachAnalytes) { sql += @" AND analytes.analyteid = @analyteId "; } sql += @" ORDER BY orders.received_date DESC "; DbCommand.CommandText = sql; if (searchCustomers) { DbCommand.Parameters.AddWithValue("@customerId", customerId); } if (searchDates) { DbCommand.Parameters.AddWithValue("@orderReceivedStartDate", DateEx.GetStartOfDay(orderReceivedStartDate.Value)); DbCommand.Parameters.AddWithValue("@orderReceivedEndDate", DateEx.GetEndOfDay(orderReceivedEndDate.Value)); } if (searchTests) { DbCommand.Parameters.AddWithValue("@testId", testId); } if (serachAnalytes) { DbCommand.Parameters.AddWithValue("@analyteId", analyteId); } var reader = DbConnection.ExecuteReader(DbCommand); result.AddRange(AutoMap.MapReaderToList<Order>(reader)); foreach (var order in result) { using (var dao = new ClientDAO()) order.Client = dao.GetClient(ref dbConnection, ref dbCommand, order.ParentId); } } } else { throw new Exception("Unable to Connect"); } } return result; } catch { throw; } }
public Sample GetSample(int? arlNumber, Identification identification) { try { Sample sample = new Sample(); DbCommand.CommandType = CommandType.StoredProcedure; DbCommand.CommandText = "uspGetSample"; DbCommand.Parameters.Clear(); DbCommand.Parameters.Add("@ARLNumber", System.Data.SqlDbType.Int).Value = arlNumber; DataTable returnDT = DbConnection.ExecuteQuery(dbCommand); if (returnDT.Rows.Count > 0) { DataRow row = returnDT.Rows[0]; sample.ARLNumber = Convert.ToInt32(row["ARLNumber"]); sample.Description = row["Description"].ToString(); sample.ReceivedDate = row["ReceivedDate"] != DBNull.Value ? Convert.ToDateTime(row["ReceivedDate"]) : new DateTime?(); sample.ClientId = row["ClientID"] != DBNull.Value ? Convert.ToInt32(row["ClientID"]) : new Int32(); sample.ClientName = row["ClientName"].ToString(); if (sample.ClientId > -1) { //sample.Client = new Client() { ClientId = (int)sample.ClientId, ClientName = sample.ClientName }; using (ClientDAO clientDao = new ClientDAO()) { sample.Client = clientDao.GetClient((int)sample.ClientId); } } sample.Status = row["Status"] != DBNull.Value ? (EnumSampleStatus)row["Status"] : EnumSampleStatus.InProgress; sample.PONumber = row["PONumber"].ToString(); sample.FormulationId = row["FormulationID"].ToString(); sample.LotNumber = row["LotNumber"].ToString(); sample.ProjectNumber = row["ProjectNumber"].ToString(); sample.StorageLocationId = row["StorageLocationID"] != DBNull.Value ? Convert.ToInt32(row["StorageLocationID"]) : new Int32(); sample.StorageLocationName = row["StorageLocationName"].ToString(); sample.StorageLocationConditions = row["StorageLocationConditions"].ToString(); sample.StorageLocationCode = row["StorageLocationCode"].ToString(); if (sample.StorageLocationId > -1) sample.StorageLocation = new StorageLocation() { StorageLocationId = sample.StorageLocationId, Active = true, Description = row["StorageLocationName"].ToString(), Conditions = row["StorageLocationConditions"].ToString(), LocationCode = row["StorageLocationCode"].ToString() }; sample.RequestedStorageId = row["RequestedStorageID"] != DBNull.Value ? Convert.ToInt32(row["RequestedStorageID"]) : new Int32(); sample.RequestedStorageName = row["RequestedStorageName"].ToString(); if (sample.RequestedStorageId > -1) sample.RequestedStorageLocation = new StorageLocation() { StorageLocationId = sample.RequestedStorageId, Active = true, Description = row["RequestedStorageName"].ToString(), Conditions = "" }; sample.DosageId = row["DosageID"] != DBNull.Value ? Convert.ToInt32(row["DosageID"]) : new Int32(); sample.DosageName = row["DosageName"] != DBNull.Value ? row["DosageName"].ToString() : null; if (sample.DosageId > -1) sample.Dosage = new Dosage() { DosageId = sample.DosageId, Active = true, DosageName = row["DosageName"].ToString() }; sample.Containers = row["Containers"] != DBNull.Value ? Convert.ToInt32(row["Containers"]) : new Int32(); sample.ContainerDescription = row["ContainerDescription"].ToString(); if (sample.ContainerDescription != null) sample.Container = new Container() { Description = sample.ContainerDescription }; sample.VolumeAmount = row["VolumeAmount"] != DBNull.Value ? Convert.ToDecimal(row["VolumeAmount"]) : new Decimal(); sample.VolumeUOMId = row["VolumeUOMID"] != DBNull.Value ? Convert.ToInt32(row["VolumeUOMID"]) : new Int32(); sample.VolumeUOM = row["VolumeUOM"].ToString(); if (sample.VolumeUOMId != null) sample.VolumeUnitOfMeasure = new UnitOfMeasure() { UomId = sample.VolumeUOMId, Uom = sample.VolumeUOM }; sample.TimepointStudyYN = row["TimepointStudyYN"] != DBNull.Value ? Convert.ToBoolean(row["TimepointStudyYN"]) : false; sample.GMPYN = row["GMPYN"] != DBNull.Value ? Convert.ToBoolean(row["GMPYN"]) : false; sample.CompoundedBy = row["CompoundedBy"].ToString(); sample.CompoundedDate = row["CompoundedDate"] != DBNull.Value ? Convert.ToDateTime(row["CompoundedDate"]) : new DateTime?(); sample.CreatedBy = row["CreatedBy"] != DBNull.Value ? Convert.ToInt32(row["CreatedBy"]) : new Int32(); sample.CreatedUser = row["CreatedUser"].ToString(); sample.CreatedDate = row["CreatedDate"] != DBNull.Value ? (DateTime)row["CreatedDate"] : (DateTime)SqlDateTime.Null; sample.ModifiedBy = row["ModifiedBy"] != DBNull.Value ? Convert.ToInt32(row["ModifiedBy"]) : new Int32(); sample.ModifiedUser = row["ModifiedUser"].ToString(); sample.ModifiedDate = row["ModifiedDate"] != DBNull.Value ? (DateTime)row["ModifiedDate"] : (DateTime)SqlDateTime.Null; sample.SampleAnalytes = this.GetSampleAnalytes((int)sample.ARLNumber); sample.SampleCharges = this.GetSampleCharges((int)sample.ARLNumber); sample.SampleDocuments = this.GetSampleDocuments((int)sample.ARLNumber); sample.SampleNotes = this.GetSampleNotes((int)sample.ARLNumber, identification); sample.SampleTests = this.GetSampleTests((int)sample.ARLNumber, false, identification); } returnDT = null; return sample; } catch { throw; } }
public SmartCollection<InventoryItem> SearchInventory(string searchString, IEnumerable<ISearchItem> searchItems, Identification identification) { try { SmartCollection<InventoryItem> resultList = new SmartCollection<InventoryItem>(); using (DbConnection = new MsSqlPersistence(DbConnectionSettings)) { if (DbConnection.IsConnected()) { using (DbCommand) { string sql = @" SELECT DISTINCT " + baseInventorySql + SysLib.BuildSearchAllWhereClause(searchString, searchItems); sql += " AND inventory.delete_date IS NULL "; sql += "ORDER BY inventory.modified_date DESC "; DbCommand.CommandText = sql; var table = DbConnection.ExecuteQuery(DbCommand); foreach (DataRow row in table.Rows) { InventoryItem item = new InventoryItem(); item.Id = Convert.ToInt32(row["Id"]); item.Active = Convert.ToBoolean(row["analyte_active"]); item.ItemType = row["item_type"] != DBNull.Value ? (EnumInventoryType)row["item_type"] : EnumInventoryType.Standard; item.LabNumber = row["lab_number"] != DBNull.Value ? Convert.ToInt32(row["lab_number"]) : 0; item.ClientId = Convert.ToInt32(row["customerid"]); item.AnalyteId = Convert.ToInt32(row["analyteid"]); if (item.AnalyteId > 0) item.AnalyteItem = new Analyte() { AnalyteId = item.AnalyteId, AnalyteName = row["analytename"].ToString(), Active = Convert.ToBoolean(row["analyte_active"]), ControlledYN = Convert.ToBoolean(row["analyte_controlled"]), AntibioticYN = Convert.ToBoolean(row["analyte_antibiotic"]) }; item.StorageId = row["storageid"] != DBNull.Value ? Convert.ToInt32(row["storageid"]) : 0; if (item.StorageId > 0) item.StorageLocation = new StorageLocation() { StorageLocationId = item.StorageId, Active = Convert.ToBoolean(row["storage_active"]), Description = row["storage_description"].ToString(), Conditions = row["storage_conditions"].ToString() }; item.PoNumber = row["po_number"].ToString(); item.Manufacturer = row["manufacturer"].ToString(); item.Lot = row["lot"].ToString(); item.Cas = row["cas"].ToString(); item.Catalog = row["catalog"].ToString(); item.Weight = row["weight"].ToString(); item.VolumeAmount = row["volume_amount"].ToString(); item.OrderedFor = row["ordered_for"].ToString(); item.Potency = Convert.ToDecimal(row["potency"] != DBNull.Value ? (decimal)row["potency"] : 0); item.Cost = Convert.ToDecimal(row["cost"] != DBNull.Value ? (decimal)row["cost"] : 0); item.ClientCost = Convert.ToDecimal(row["customer_cost"] != DBNull.Value ? (decimal)row["customer_cost"] : 0); item.ReceivedDate = row["received_date"] != DBNull.Value ? (DateTime?)row["received_date"] : null; item.DestroyedDate = row["destroyed_date"] != DBNull.Value ? (DateTime?)row["destroyed_date"] : null; item.ExpirationDate = row["expiration_date"] != DBNull.Value ? (DateTime?)row["expiration_date"] : null; item.CreatedUser = row["createduser"].ToString(); item.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0; item.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime?)row["created_date"] : null; item.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0; item.ModifiedUser = row["modifieduser"].ToString(); item.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime?)row["modified_date"] : null; using (ClientDAO dao = new ClientDAO()) { item.Client = dao.GetClient(ref dbConnection, ref dbCommand, item.ClientId); } resultList.Add(item); } } } else { throw new Exception("Unable to Connect"); } } return resultList; } catch { throw; } }
public InventoryItem GetInventoryItemRecord(ref MsSqlPersistence dbConnection, ref SqlCommand dbCommand, int? id, string useSql = null) { try { InventoryItem item = new InventoryItem(); string sql = useSql != null ? useSql : "SELECT " + baseInventorySql + @" WHERE inventory.id = @ID "; dbCommand.Parameters.Clear(); dbCommand.CommandText = sql; dbCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int).Value = id; var table = dbConnection.ExecuteQuery(dbCommand); if (table.Rows.Count == 1) { DataRow row = table.Rows[0]; item.Id = Convert.ToInt32(row["Id"]); item.Active = Convert.ToBoolean(row["analyte_active"]); item.ItemType = row["item_type"] != DBNull.Value ? (EnumInventoryType)row["item_type"] : EnumInventoryType.Standard; item.LabNumber = row["lab_number"] != DBNull.Value ? Convert.ToInt32(row["lab_number"]) : 0; item.ClientId = Convert.ToInt32(row["customerid"]); item.AnalyteId = Convert.ToInt32(row["analyteid"]); if (item.AnalyteId > 0) item.AnalyteItem = new Analyte() { AnalyteId = item.AnalyteId, AnalyteName = row["analytename"].ToString(), Active = Convert.ToBoolean(row["analyte_active"]), ControlledYN = Convert.ToBoolean(row["analyte_controlled"]), AntibioticYN = Convert.ToBoolean(row["analyte_antibiotic"]) }; item.StorageId = row["storageid"] != DBNull.Value ? Convert.ToInt32(row["storageid"]) : 0; if (item.StorageId > 0) item.StorageLocation = new StorageLocation() { StorageLocationId = item.StorageId, Active = Convert.ToBoolean(row["storage_active"]), Description = row["storage_description"].ToString(), Conditions = row["storage_conditions"].ToString() }; item.PoNumber = row["po_number"].ToString(); item.Manufacturer = row["manufacturer"].ToString(); item.Lot = row["lot"].ToString(); item.Cas = row["cas"].ToString(); item.Catalog = row["catalog"].ToString(); item.Weight = row["weight"].ToString(); item.VolumeAmount = row["volume_amount"].ToString(); item.OrderedFor = row["ordered_for"].ToString(); item.Potency = Convert.ToDecimal(row["potency"] != DBNull.Value ? (decimal)row["potency"] : 0); item.Cost = Convert.ToDecimal(row["cost"] != DBNull.Value ? (decimal)row["cost"] : 0); item.ClientCost = Convert.ToDecimal(row["customer_cost"] != DBNull.Value ? (decimal)row["customer_cost"] : 0); item.ReceivedDate = row["received_date"] != DBNull.Value ? (DateTime?)row["received_date"] : null; item.DestroyedDate = row["destroyed_date"] != DBNull.Value ? (DateTime?)row["destroyed_date"] : null; item.ExpirationDate = row["expiration_date"] != DBNull.Value ? (DateTime?)row["expiration_date"] : null; item.CreatedUser = row["createduser"].ToString(); item.CreatedBy = row["created_by"] != DBNull.Value ? (int)row["created_by"] : 0; item.CreatedDate = row["created_date"] != DBNull.Value ? (DateTime?)row["created_date"] : null; item.ModifiedBy = row["modified_by"] != DBNull.Value ? (int)row["modified_by"] : 0; item.ModifiedUser = row["modifieduser"].ToString(); item.ModifiedDate = row["modified_date"] != DBNull.Value ? (DateTime?)row["modified_date"] : null; using (ClientDAO dao = new ClientDAO()) { item.Client = dao.GetClient(ref dbConnection, ref dbCommand, item.ClientId); } } return item; } catch { throw; } }