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;
}