private void button1_Click(object sender, EventArgs e) { string queryMappingData = @"select xref.*, pkg.ServiceID, pkg.ServiceName, trmt.TreatmentID, trmt.TreatmentName from [dbo].[XrefServiceTreatment] as xref, [dbo].[Treatment] as trmt, [dbo].[Service] as pkg where xref.TreatmentID = trmt.TreatmentID and xref.ServiceID = pkg.ServiceID and xref.CompanyID = 1 order by pkg.ServiceID, pkg.ServiceName"; string qryGetPkgAndTreatments = @"select * from [dbo].[Service] where CompanyID = 1 select * from [dbo].[Treatment] where CompanyID = 1 " + queryMappingData; var FuncQryReadAllMappings = new Func<XrefServiceTreatment, Service, Treatment, XrefServiceTreatment>( (xref, pkg, trtmnt) => { xref.Service = pkg; xref.Treatment = trtmnt; return xref; }); ServiceTreatmentViewModel ptVM = new ServiceTreatmentViewModel(); using (var multi = this._con.QueryMultiple(qryGetPkgAndTreatments)) { ptVM.Services = multi.Read<Service>().ToList(); ptVM.Treatments = multi.Read<Treatment>().ToList(); ptVM.SvcTrtmntMappings = multi.Read(FuncQryReadAllMappings, "ServiceID,TreatmentID").ToList(); } //var resultList = this._con.Query<XrefServiceTreatment, Service, Treatment, XrefServiceTreatment>( // queryMappingData, (xref, pkg, trtmnt) => // { // xref.Service = pkg; // xref.Treatment = trtmnt; // return xref; // }, // splitOn: "ServiceID,TreatmentID" // ).AsQueryable(); this._con.Close(); this.dgMappedData.DataSource = ptVM.SvcTrtmntMappings; this.dgServices.DataSource = ptVM.Services; this.dgTreatments.DataSource = ptVM.Treatments; }
/// <summary> /// Get app Service and treatments data for a company /// </summary> /// <param name="companyID"></param> /// <returns></returns> public ServiceTreatmentViewModel GetAllSvcTrtmnts(Int64 companyID) { StringBuilder sbAllQueries = new StringBuilder(); ServiceTreatmentViewModel ViewModelData = new ServiceTreatmentViewModel(); string qryAllServices = @"select * from [dbo].[Service] where CompanyID = " + companyID; string qryAllTreatments = @"select * from [dbo].[Treatment] where CompanyID = " + companyID; string qryAllMappingData = @"select xref.*, pkg.ServiceID, pkg.ServiceName, trmt.TreatmentID, trmt.TreatmentName, trmt.TreatmentDuration from [DevTest].[dbo].[XrefServiceTreatment] as xref, [DevTest].[dbo].[Treatment] as trmt, [DevTest].[dbo].[Service] as pkg where xref.TreatmentID = trmt.TreatmentID and xref.ServiceID = pkg.ServiceID and xref.CompanyID = " + companyID + " order by pkg.ServiceID, pkg.ServiceName"; sbAllQueries.Append(qryAllServices); sbAllQueries.Append(qryAllTreatments); sbAllQueries.Append(qryAllMappingData); var FuncQry2ReadAllMappings = new Func<XrefServiceTreatment, Service, Treatment, XrefServiceTreatment>( (xref, pkg, trtmnt) => { xref.Service = pkg; xref.Treatment = trtmnt; return xref; }); using (var multi = this._con.QueryMultiple(sbAllQueries.ToString())) { ViewModelData.Services = multi.Read<Service>().ToList(); ViewModelData.Treatments = multi.Read<Treatment>().ToList(); ViewModelData.SvcTrtmntMappings = multi.Read(FuncQry2ReadAllMappings, "ServiceID,TreatmentID").ToList(); } //var resultList = this._con.Query<XrefServiceTreatment, Service, Treatment, XrefServiceTreatment>( // qryAllMappingData, (xref, pkg, trtmnt) => // { // xref.Service = pkg; // xref.Treatment = trtmnt; // return xref; // }, // splitOn: "ServiceID,TreatmentID" // ).AsQueryable(); return ViewModelData; }