public static YellowstonePathology.Business.Client.Model.ClientCollection GetClientCollectionByClientGroupId(int clientGroupId) { YellowstonePathology.Business.Client.Model.ClientCollection result = new Client.Model.ClientCollection(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "select c.* " + "from tblClientGroup cg " + "join tblClientGroupClient cgc on cg.ClientGroupId = cgc.ClientGroupId " + "join tblclient c on cgc.ClientId = c.ClientId " + "where cgc.ClientGroupId = @ClientGroupId order by c.ClientName"; cmd.Parameters.Add("@ClientGroupId", SqlDbType.Int).Value = clientGroupId; 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; }
private static YellowstonePathology.Business.Client.Model.ClientCollection BuildClientCollection(SqlCommand cmd) { Client.Model.ClientCollection result = new Client.Model.ClientCollection(); 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.Client client = new YellowstonePathology.Business.Client.Model.Client(); Persistence.SqlDataReaderPropertyWriter sqlDataReaderPropertyWriter = new Persistence.SqlDataReaderPropertyWriter(client, dr); sqlDataReaderPropertyWriter.WriteProperties(); result.Add(client); } 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(); foreach (Client.Model.Client client in result) { if (client.ClientId == clientLocation.ClientId) { client.ClientLocationCollection.Add(clientLocation); break; } } } } } } return result; }