private void BuildClient(YellowstonePathology.Business.Client.Model.Client client)
 {
     using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
     {
         cn.Open();
         this.m_SQLCommand.Connection = cn;
         using (SqlDataReader dr = this.m_SQLCommand.ExecuteReader(CommandBehavior.KeyInfo))
         {
             while (dr.Read())
             {
                 Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(client, dr);
                 sqlDataReaderPropertyWriter.WriteProperties();
             }
             if (dr.IsClosed == false)
             {
                 dr.NextResult();
                 while (dr.Read())
                 {
                     YellowstonePathology.Business.Client.Model.ClientLocation clientLocation = new YellowstonePathology.Business.Client.Model.ClientLocation();
                     Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientLocation, dr);
                     sqlDataReaderPropertyWriter.WriteProperties();
                     client.ClientLocationCollection.Add(clientLocation);
                 }
             }
         }
     }
 }

        private static YellowstonePathology.Business.Test.AbsoluteCD4Count.AbsoluteCD4CountTestOrder BuildReportOrderAbsoluteCD4Count(string reportNo)
        {
            YellowstonePathology.Business.Test.AbsoluteCD4Count.AbsoluteCD4CountTestOrder reportAbsoluteCD4Count = null;
            using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Select * " +
                    "from tblAbsoluteCD4CountTestOrder ro " +
                    "join tblPanelSetOrder pso on ro.ReportNo = pso.ReportNo " +
                    "where ro.ReportNo = @ReportNo";
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@ReportNo", SqlDbType.VarChar).Value = reportNo;
                cmd.Connection = cn;

                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        reportAbsoluteCD4Count = new YellowstonePathology.Business.Test.AbsoluteCD4Count.AbsoluteCD4CountTestOrder();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(reportAbsoluteCD4Count, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                    }
                }
            }
            return reportAbsoluteCD4Count;
        }

        public static YellowstonePathology.Business.ClientOrder.Model.ClientOrder BuildClientOrder(SqlDataReader dr)
        {
            YellowstonePathology.Business.ClientOrder.Model.ClientOrder clientOrder = null;
            Nullable<int> panelSetId = null;
            while (dr.Read())
            {
                if (dr["PanelSetId"] != DBNull.Value)
                {
                    panelSetId = Convert.ToInt32(dr["PanelSetId"].ToString());
                }
            }

            clientOrder = YellowstonePathology.Business.ClientOrder.Model.ClientOrderFactory.GetClientOrder(panelSetId);
            dr.NextResult();

            while (dr.Read())
            {
                YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientOrder, dr);
                propertyWriter.WriteProperties();
            }

            dr.NextResult();
            while (dr.Read())
            {
                YellowstonePathology.Business.Client.Model.ClientLocation clientLocation = new YellowstonePathology.Business.Client.Model.ClientLocation();
                YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter(clientLocation, dr);
                propertyWriter.WriteProperties();
                clientOrder.ClientLocation = clientLocation;
            }

            return clientOrder;
        }

        public static YellowstonePathology.Business.ReportDistribution.Model.TestOrderReportDistributionCollection GetReportDistributionCollectionByDateRangeTumorRegistry(DateTime startDate, DateTime endDate, string distributionType)
        {
            YellowstonePathology.Business.ReportDistribution.Model.TestOrderReportDistributionCollection result = new YellowstonePathology.Business.ReportDistribution.Model.TestOrderReportDistributionCollection();
            string sql = "Select * from tblTestOrderReportDistribution where TimeOfLastDistribution between @StartDate and @EndDate and DistributionType = @DistributionType";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate;
            cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endDate;
            cmd.Parameters.Add("@DistributionType", SqlDbType.VarChar).Value = distributionType;
            cmd.CommandType = CommandType.Text;

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        YellowstonePathology.Business.ReportDistribution.Model.TestOrderReportDistribution testOrderReportDistribution = new YellowstonePathology.Business.ReportDistribution.Model.TestOrderReportDistribution();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(testOrderReportDistribution, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        result.Add(testOrderReportDistribution);
                    }
                }
            }
            return result;
        }

        public YellowstonePathology.Business.Search.PathologistSearchResult PathologistAliquotOrderIdSearch(string aliquotOrderId, int panelSetId)
        {
            SqlCommand cmd = new SqlCommand("pPathologistAliquotOrderIdSearch_4");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@AliquotOrderId", SqlDbType.VarChar).Value = aliquotOrderId;
            cmd.Parameters.Add("@PanelSetId", SqlDbType.Int).Value = panelSetId;

            YellowstonePathology.Business.Search.PathologistSearchResult result = null;

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        result = new Search.PathologistSearchResult();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderProperyWriter = new Persistence.SqlDataReaderPropertyWriter(result, dr);
                        sqlDataReaderProperyWriter.WriteProperties();
                    }
                }
            }

            return result;
        }

        public static Test.ThinPrepPap.AcidWashList GetAcidWashList(DateTime startDate)
        {
            Test.ThinPrepPap.AcidWashList result = new Test.ThinPrepPap.AcidWashList();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select pso.MasterAccessionNo, po.ReportNo, po.OrderDate, po.Accepted, a.PLastName, a.PFirstName, a.PMiddleInitial from tblPanelOrder po join tblPanelSetOrder pso on po.ReportNo = pso.ReportNo " +
                " join tblAccessionOrder a on pso.MasterAccessionNo = a.MasterAccessionNo where po.PanelId = 39 and po.OrderDate >= @StartDate  order by po.OrderDate Desc";
            cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate;

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        Test.ThinPrepPap.AcidWashListItem acidWashLIstItem = new Test.ThinPrepPap.AcidWashListItem();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(acidWashLIstItem, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        result.Add(acidWashLIstItem);
                    }
                }
            }
            return result;
        }

        public static YellowstonePathology.Business.Client.PhysicianClientDistributionCollection GetPhysicianClientDistributionCollection(int physicianId, int clientId)
        {
            YellowstonePathology.Business.Client.PhysicianClientDistributionCollection result = new Client.PhysicianClientDistributionCollection();
            string sql = "Select c.ClientId, c.ClientName, ph.PhysicianId, ph.DisplayName [PhysicianName], c.DistributionType, c.Fax [FaxNumber], c.LongDistance " +
                "from tblPhysicianClient pc " +
                "join tblPhysicianClientDistribution pcd on pc.PhysicianClientId = pcd.PhysicianClientId " +
                "join tblPhysicianClient pc2 on pcd.DistributionId = pc2.PhysicianClientId " +
                "join tblClient c on pc2.ClientId = c.ClientId " +
                "join tblPhysician ph on pc2.Physicianid = ph.PhysicianId " +
                "where pc.ClientId = @ClientId and pc.PhysicianId = @PhysicianId";

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add("@PhysicianId", SqlDbType.Int).Value = physicianId;
            cmd.Parameters.Add("@ClientId", SqlDbType.Int).Value = clientId;
            cmd.CommandType = CommandType.Text;

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        string distributionType = dr["DistributionType"].ToString();
                        YellowstonePathology.Business.Client.PhysicianClientDistribution physicianClientDistribution = YellowstonePathology.Business.Client.PhysicianClientDistributionFactory.GetPhysicianClientDistribution(distributionType);
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(physicianClientDistribution, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        result.Add(physicianClientDistribution);
                    }
                }
            }
            return result;
        }

        public static YellowstonePathology.Business.Test.AccessionOrderView GetAccessionOrderView(string masterAccessionNo)
        {
            #if MONGO
            return AccessionOrderGatewayMongo.GetAccessionOrderView(masterAccessionNo);
            #else
            YellowstonePathology.Business.Test.AccessionOrderView result = null;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Select * from tblAccessionOrder where MasterAccessionNo = @MasterAccessionNo";
            cmd.Parameters.Add("@MasterAccessionNo", SqlDbType.VarChar).Value = masterAccessionNo;
            cmd.CommandType = CommandType.Text;

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        result = new Test.AccessionOrderView();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(result, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                    }
                }
            }
            return result;
            #endif
        }

        private void BuildClientSupplyOrder(YellowstonePathology.Business.Client.Model.ClientSupplyOrder clientSupplyOrder)
        {
            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                this.m_SQLCommand.Connection = cn;
                using (SqlDataReader dr = this.m_SQLCommand.ExecuteReader(CommandBehavior.KeyInfo))
                {
                    while (dr.Read())
                    {
                        Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientSupplyOrder, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                    }

                    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();
                        clientSupplyOrder.ClientSupplyOrderDetailCollection.Add(clientSupplyOrderDetail);
                    }
                }
            }
        }

        public static YellowstonePathology.Business.User.SystemUserCollection GetSystemUserCollection()
        {
            /*SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select su.UserId, su.Active, su.UserName, su.FirstName, su.LastName, su.Initials, su.Signature, su.DisplayName, su.EmailAddress, su.NationalProviderId, (select sr.* from tblSystemUserRole sr where sr.UserId = su.UserId " +
                "for xml Path('SystemUserRole'), type) [SystemUserRoleCollection] from tblSystemUser su order by su.UserName for xml Path('SystemUser'), root('SystemUserCollection')";
            cmd.CommandType = System.Data.CommandType.Text;
            YellowstonePathology.Business.User.SystemUserCollection systemUserCollection = Persistence.SqlCommandHelper.ExecuteCollectionCommand<YellowstonePathology.Business.User.SystemUserCollection>(cmd);
            return systemUserCollection;*/

            Type t = typeof(YellowstonePathology.Business.User.SystemUserCollection);
            ConstructorInfo ci = t.GetConstructor(BindingFlags.Instance | BindingFlags.NonPublic, null, new Type[0], null);
            YellowstonePathology.Business.User.SystemUserCollection systemUserCollection = (YellowstonePathology.Business.User.SystemUserCollection)ci.Invoke(null);

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select UserId, Active, UserName, FirstName, LastName, Initials, Signature, DisplayName, " +
                "EmailAddress, NationalProviderId from tblSystemUser order by UserName " +
                "select * from tblSystemUserRole";
            cmd.CommandType = System.Data.CommandType.Text;
            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        YellowstonePathology.Business.User.SystemUser systemUser = new SystemUser();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(systemUser, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        systemUserCollection.Add(systemUser);
                    }
                    dr.NextResult();
                    while (dr.Read())
                    {
                        YellowstonePathology.Business.User.SystemUserRole systemUserRole = new SystemUserRole();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(systemUserRole, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        foreach (SystemUser systemUser in systemUserCollection)
                        {
                            if (systemUser.UserId == systemUserRole.UserID)
                            {
                                systemUser.SystemUserRoleCollection.Add(systemUserRole);
                                break;
                            }
                        }
                    }
                }
            }
            return systemUserCollection;
        }

 private void Build(YellowstonePathology.Business.Test.AliquotOrder aliquotOrder)
 {
     using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
     {
         cn.Open();
         this.m_SQLCommand.Connection = cn;
         using (SqlDataReader dr = this.m_SQLCommand.ExecuteReader())
         {
             while (dr.Read())
             {
                 YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(aliquotOrder, dr);
                 sqlDataReaderPropertyWriter.WriteProperties();
             }
         }
     }
 }

        public void BuildClientOrder()
        {
            while (this.m_DataReader.Read())
            {
                YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new Persistence.SqlDataReaderPropertyWriter(this.m_ClientOrder, this.m_DataReader);
                propertyWriter.WriteProperties();
            }

            this.m_DataReader.NextResult();
            while (this.m_DataReader.Read())
            {
                YellowstonePathology.Business.Client.Model.ClientLocation clientLocation = new YellowstonePathology.Business.Client.Model.ClientLocation();
                YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter(clientLocation, this.m_DataReader);
                propertyWriter.WriteProperties();
                this.m_ClientOrder.ClientLocation = clientLocation;
            }
        }

        private void Build(object o)
        {
            using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.ProductionConnectionString))
            {
                cn.Open();
                m_SQLCommand.Connection = cn;

                using (SqlDataReader dr = m_SQLCommand.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(o, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                    }
                }
            }
        }

        public YellowstonePathology.Business.Search.PathologistSearchResult PathologistAliquotOrderIdSearch(string aliquotOrderId, int panelSetIdHint)
        {
            SqlCommand cmd = new SqlCommand("pPathologistAliquotOrderIdSearch_5");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@AliquotOrderId", SqlDbType.VarChar).Value = aliquotOrderId;

            List<YellowstonePathology.Business.Search.PathologistSearchResult> resultList = new List<Search.PathologistSearchResult>();

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        YellowstonePathology.Business.Search.PathologistSearchResult result = new Search.PathologistSearchResult();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderProperyWriter = new Persistence.SqlDataReaderPropertyWriter(result, dr);
                        sqlDataReaderProperyWriter.WriteProperties();
                        resultList.Add(result);
                    }
                }
            }

            if(resultList.Count == 0)
            {
                return null;
            }
            else if(resultList.Count == 1)
            {
                return resultList[0];
            }
            else
            {
                foreach (YellowstonePathology.Business.Search.PathologistSearchResult item in resultList)
                {
                    if (item.PanelSetId == panelSetIdHint) return item;
                }
                return resultList[0];
            }
        }

        private object ExcuteCommandAndBuild(SqlCommand cmd, Type topLevelType)
        {
            object result = null;

            using (SqlConnection cn = new SqlConnection("Data Source=TestSQL;Initial Catalog=YPIData;Integrated Security=True"))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        result = Activator.CreateInstance(topLevelType);
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(result, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                    }
                }
            }

            return result;
        }

        public static Reports.POCRetensionReportData GetPOCRetensionReport(DateTime startDate, DateTime endDate)
        {
            Reports.POCRetensionReportData result = new Reports.POCRetensionReportData();
            SqlCommand cmd = new SqlCommand("prcPOCRetensionReport_1");
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add("@StartDate", System.Data.SqlDbType.DateTime).Value = startDate;
            cmd.Parameters.Add("@EndDate", System.Data.SqlDbType.DateTime).Value = endDate;

            using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.ProductionConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        result.StartDate = (DateTime)dr[0];
                    }
                    dr.NextResult();

                    while (dr.Read())
                    {
                        Reports.POCRetensionReportDataItem pocRetensionReportDataItem = new Reports.POCRetensionReportDataItem();
                        Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(pocRetensionReportDataItem, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        result.POCRetensionReportDataItems.Add(pocRetensionReportDataItem);
                    }
                }
            }

            if (result.POCRetensionReportDataItems.Count == 0)
            {
                Reports.POCRetensionReportDataItem pocRetensionReportDataItem = new Reports.POCRetensionReportDataItem();
                pocRetensionReportDataItem.Status = "No Cases Found.";
                result.POCRetensionReportDataItems.Add(pocRetensionReportDataItem);
            }

            return result;
        }

        public static YellowstonePathology.Business.Client.Model.ClientSupplyCollection GetClientSupplyCollection(string supplyCategory)
        {
            YellowstonePathology.Business.Client.Model.ClientSupplyCollection result = new Client.Model.ClientSupplyCollection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Select * from tblClientSupply where supplycategory = @SupplyCategory order by supplyname";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@SupplyCategory", SqlDbType.VarChar).Value = supplyCategory;

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        YellowstonePathology.Business.Client.Model.ClientSupply clientSupply = new Client.Model.ClientSupply();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientSupply, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        result.Add(clientSupply);
                    }
                }
            }

            return result;
        }

        public static Domain.ClientCollection GetClientsByProviderId(string objectId)
        {
            Domain.ClientCollection result = new Domain.ClientCollection();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "select c.* " +
               "from tblClient c " +
               "join tblPhysicianClient pc on c.ClientId = pc.ClientId " +
               "where pc.ProviderId = @ObjectId order by ClientName ";
            cmd.Parameters.Add("@ObjectId", SqlDbType.VarChar).Value = objectId;
            cmd.CommandType = CommandType.Text;

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.BaseData.SqlConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        YellowstonePathology.Business.Client.Model.Client client = new YellowstonePathology.Business.Client.Model.Client();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(client, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        result.Add(client);
                    }
                }
            }
            return result;
        }

        public static YellowstonePathology.Business.Client.Model.ClientGroupCollection GetClientGroupCollection()
        {
            #if MONGO
            return PhysicianClientGatewayMongo.GetClientGroupCollection();
            #else
            YellowstonePathology.Business.Client.Model.ClientGroupCollection result = new Client.Model.ClientGroupCollection();
            SqlCommand cmd = new SqlCommand("select * from tblClientGroup order by GroupName");
            cmd.CommandType = CommandType.Text;

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        YellowstonePathology.Business.Client.Model.ClientGroup clientGroup = new Client.Model.ClientGroup();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientGroup, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        result.Add(clientGroup);
                    }
                }
            }
            return result;
            #endif
        }

        public static YellowstonePathology.Business.Client.Model.ClientGroupClientCollection GetClientGroupClientCollectionByClientGroupId(int clientGroupId)
        {
            YellowstonePathology.Business.Client.Model.ClientGroupClientCollection result = new Client.Model.ClientGroupClientCollection();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "Select * from tblClientGroupClient where ClientGroupId = @ClientGroupId";
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@ClientGroupId", SqlDbType.Int).Value = clientGroupId;

            using (SqlConnection cn = new SqlConnection(YellowstonePathology.Business.Properties.Settings.Default.CurrentConnectionString))
            {
                cn.Open();
                cmd.Connection = cn;
                using (SqlDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        YellowstonePathology.Business.Client.Model.ClientGroupClient clientGroupClient = new Client.Model.ClientGroupClient();
                        YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientGroupClient, dr);
                        sqlDataReaderPropertyWriter.WriteProperties();
                        result.Add(clientGroupClient);
                    }
                }
            }
            return result;
        }