I have SP which is returns multiple result set.
CREATE Procedure [dbo].[GetForm2Data]
(@Form2Id bigint)
as
Begin
Declare @IGMRefNo varchar(50)
Select @IGMRefNo = frm2.IGMReferenceNo
from IMP_Form2 frm2
left join M_Courier_MST Courier on frm2.CourierCoId=Courier.CourierCoId
where Form2Id = @Form2Id
Select frm2.Form2ID, frm2.Form2No, frm2.IGMReferenceNo, replace(CONVERT(varchar(11), frm2.IGMReferenceDate,105),' ','-') as IGMReferenceDate,
frm2.ECMReferenceDocNo,replace(CONVERT(varchar(11),frm2.ECMReferenceDocDate,105),' ','-') as ECMReferenceDocDate,
convert(decimal(18,2),frm2.ECMReferenceDocManifestWt) as ECMReferenceDocManifestWt, frm2.ECMReferenceNonDocNo,
replace(CONVERT(varchar(11),frm2.ECMReferenceNonDocDate,105),' ','-') as ECMReferenceNonDocDate,
convert(decimal(18,2),frm2.ECMReferenceNonDocManifestWt) as ECMReferenceNonDocManifestWt, isnull(frm2.ThokaNo,'') as ThokaNo,
isnull(frm2.MAWBNo,'') as MAWBNo, frm2.CourierCoId,Courier.CourierCoName, frm2.AirlineId,
frm2.FlightNumber,frm2.AirportofArrival,frm2.AirportofShipment,frm2.CountryofExportation,
replace(CONVERT(varchar(11),frm2.ActualDateofArrival,105),' ','-') as ActualDateofArrival,
case when len(ActualTimeofArrival) = 4 then convert(varchar(4), frm2.ActualTimeofArrival)
when len(ActualTimeofArrival) = 3 then '0' + convert(varchar(4), frm2.ActualTimeofArrival)
when len(ActualTimeofArrival) = 2 then '00' + convert(varchar(4), frm2.ActualTimeofArrival)
when len(ActualTimeofArrival) = 1 then '000' + convert(varchar(4), frm2.ActualTimeofArrival) End as ActualTimeofArrival,
frm2.TerminalCode, frm2.Status, convert(varchar(23),frm2.LastUpDtOn,121) as LastUpDtOn,isnull(frm2.CreatedBy,'') as CreatedBy,
isnull(replace(CONVERT(varchar(11),frm2.CreatedOn,105),' ','-'),'') as CreatedOn
from IMP_Form2 frm2
left join M_Courier_MST Courier on frm2.CourierCoId=Courier.CourierCoId
where Form2Id = @Form2Id
if @@ROWCOUNT =0 select 'No Record Exists'
Select Form2Type,ThokaNo, NumberofBags, NumberofPkgs, NumberofShps, convert(decimal(18,2),WeightofShps) as WeightofShps,
NumberofShpsTrans, NumberofPkgsTrans, convert(decimal(18,2),WeightofTrans) as WeightofTrans
from IMP_Form2Dtl where Form2Id = @Form2Id
if @@ROWCOUNT =0 select 'No Record Exists'
select a.MAWBNo, ActualNoofPackages, ActualWeight
from imp_form1dtl a
join imp_form1 b on a.Form1Id=b.Form1Id
where b.IgmNo=@IGMRefNo
if @@ROWCOUNT = 0 select 'No Record Exists'
End
and my logic to read this output is
What I have tried:
using (var db = new Models.DAL())
{
List<IMP_Form2> Form2hdr = new List<IMP_Form2>();
List<IMP_Form2Dtl> Form2dtl = new List<IMP_Form2Dtl>();
List<IMP_Form1Dtl> Form1dtl = new List<IMP_Form1Dtl>();
db.Database.Initialize(force: false);
SqlParameter param = new SqlParameter();
param.ParameterName = "@Form2Id";
param.SqlDbType = SqlDbType.Int;
param.Value = 2;
param.Direction = ParameterDirection.Input;
var cmd = db.Database.Connection.CreateCommand();
cmd.CommandText = "[dbo].[GetForm2Data]";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(param);
try
{
db.Database.Connection.Open();
var reader = cmd.ExecuteReader();
var form2hdr = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<IMP_Form2>(reader, "IMP_Form2", MergeOption.AppendOnly);
foreach (var item in form2hdr)
{
Form2hdr.Add(item);
}
VMForm2 result = new VMForm2();
result.lstform2 = Form2hdr;
reader.NextResult();
var form2dtl = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<IMP_Form2Dtl>(reader);
foreach (var item in form2dtl)
{
Form2dtl.Add(item);
}
result.lstform2dts = Form2dtl;
var form1dtl = ((IObjectContextAdapter)db)
.ObjectContext
.Translate<IMP_Form1Dtl>(reader);
foreach (var item in form1dtl)
{
Form1dtl.Add(item);
}
result.lstForm1dtls = Form1dtl;
return result;
}
finally
{
db.Database.Connection.Close();
}
}
}
and i have 3 different models to mapped this.But problem is some result set have columns which are not mapped in Models.
So i gives me error..So is there anything i can implement other that writing separate model for each SP