public static YellowstonePathology.Business.Client.Model.ClientSupplyOrderCollection GetClientSupplyOrderCollectionByFinal(bool final) { YellowstonePathology.Business.Client.Model.ClientSupplyOrderCollection result = new Client.Model.ClientSupplyOrderCollection(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT c.*," + " (Select cd.* " + " from tblClientSupplyOrderDetail cd where cd.clientSupplyOrderId = c.clientSupplyOrderId for xml path('ClientSupplyOrderDetail'), type) ClientSupplyOrderDetailCollection" + " from tblClientSupplyOrder c where c.OrderDate >= dateadd(mm, -3, getdate()) and c.OrderFinal = @Final order by c.OrderDate desc for xml path('ClientSupplyOrder'), root('ClientSupplyOrderCollection')"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add("@Final", SqlDbType.Bit).Value = final; XElement collectionElement = PhysicianClientGateway.GetXElementFromCommand(cmd); if (collectionElement != null) { List<XElement> clientSupplyOrderList = collectionElement.Elements("ClientSupplyOrder").ToList(); foreach (XElement clientSupplyOrderElement in clientSupplyOrderList) { YellowstonePathology.Business.Client.Model.ClientSupplyOrder clientSupplyOrder = BuildClientSupplyOrder(clientSupplyOrderElement); result.Add(clientSupplyOrder); } } return result; }
private static YellowstonePathology.Business.Client.Model.ClientSupplyOrderCollection BuildClientSupplyOrderCollection(SqlCommand cmd) { Client.Model.ClientSupplyOrderCollection result = new Client.Model.ClientSupplyOrderCollection(); using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString)) { cn.Open(); cmd.Connection = cn; using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.KeyInfo)) { while (dr.Read()) { Client.Model.ClientSupplyOrder clientSupplyOrder = new YellowstonePathology.Business.Client.Model.ClientSupplyOrder(); Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientSupplyOrder, dr); sqlDataReaderPropertyWriter.WriteProperties(); result.Add(clientSupplyOrder); } if (dr.IsClosed == false) { dr.NextResult(); while (dr.Read()) { YellowstonePathology.Business.Client.Model.ClientSupplyOrderDetail clientSupplyOrderDetail = new YellowstonePathology.Business.Client.Model.ClientSupplyOrderDetail(); Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientSupplyOrderDetail, dr); sqlDataReaderPropertyWriter.WriteProperties(); foreach (Client.Model.ClientSupplyOrder clientSupplyOrder in result) { if (clientSupplyOrder.ClientSupplyOrderId == clientSupplyOrderDetail.clientsupplyorderid) { clientSupplyOrder.ClientSupplyOrderDetailCollection.Add(clientSupplyOrderDetail); break; } } } } } } return result; }