Reputation: 35
These are my classes:
public class InvoiceModel
{
public int Id { get; set; }
public CustomerModel Customer { get; set; }
public string DateCreated { get; set; }
public ObservableCollection<InvoiceItemModel> Items { get; set; }
public ObservableCollection<InvoicePaymentModel> Payments { get; set; }
}
public class CustomerModel
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public ObservableCollection<PhoneNumberModel> PhoneNumbers { get; set; }
}
public class PhoneNumberModel
{
public int CustomerId { get; set; }
public string PhoneNumber { get; set; }
}
public class InvoiceItemModel
{
public int Id { get; set; }
public int InvoiceId { get; set; }
public int ProductId { get; set; }
public long SellPrice { get; set; }
public int Count { get; set; }
}
public class InvoicePaymentModel
{
public int InvoiceId { get; set; }
public string DateCreated { get; set; }
public double PayAmount { get; set; }
}
I also have these tables in SQL Server: Customers, PhoneNumbers, Invoices, InvoiceItems and InvoicePayments which you can guess the columns.
How can I map these classes using Dapper?
I know I can use SplitOn
for map Customer in Invoices, but I'm looking for a solution to do all these in one go (Least overhead on server and client). I know I should use QueryMultiple
but I don't know how.
I use C# and SQL Server 2017
I use ObservableCollection
because I use Caliburn Micro.
EDIT
I am fetching a set of InvoiceModel
from DB (ObservableCollection<InvoiceModel>
)
Upvotes: 0
Views: 648
Reputation: 143
Use this syntax
var model = new InvoiceModel
{
Customer = new CustomerModel
{
PhoneNumbers = new ObservableCollection<PhoneNumberModel>()
}
};
var sql = "Your Select Query";
using (var connection = new SqlConnection("Your Connection String"))
{
connection.Open();
var parameters = new DynamicParameters();
//If you have parameteres add like here
//parameters.Add("@Id", 1, DbType.Int32);
using (var multi = connection.QueryMultiple(sql, parameters))
{
model = multi.Read<InvoiceModel>().FirstOrDefault();
model.Customer = multi.Read<CustomerModel>().FirstOrDefault();
var items = multi.Read<InvoiceItemModel>().ToList();
var payments = multi.Read<InvoicePaymentModel>().ToList();
model.Items = new ObservableCollection<InvoiceItemModel>(items);
model.Payments = new ObservableCollection<InvoicePaymentModel>(payments);
}
}
EDIT :
To Load ObservableCollection<InvoiceModel>
Use This Syntax
using (var connection = new SqlConnection("Your Connection string"))
{
connection.Open();
var sqlCommand = "select * from InvoiceModels as ic inner JOIN" +
" CustomerModel AS cm on ic.CustomerId = cm.Id ";
var invoices = connection.Query(sqlCommand, new[] { typeof(InvoiceModel), typeof(CustomerModel), }, obj =>
{
var invoiceModel = obj[0] as InvoiceModel;
invoiceModel.Customer = obj[1] as CustomerModel;
return invoiceModel;
}).ToList();
var obserInvoices = new ObservableCollection<InvoiceModel>(invoices);
for (int i = 0; i < obserInvoices.Count; i++)
{
var phoneNumbers = connection.Query<PhoneNumberModel>
("Select * from PhoneNumberModel Where CustomerModelId=@Id", new { @Id = obserInvoices[i].CustomerId }).ToList();
if (phoneNumbers.Any())
obserInvoices[i].Customer.PhoneNumbers = new ObservableCollection<PhoneNumberModel>(phoneNumbers);
var invoiceItemModel = connection.Query<InvoiceItemModel>
("Select * from InvoiceItemModel Where InvoiceModelId=@Id", new { @Id = obserInvoices[i].Id }).ToList();
if (invoiceItemModel.Any())
obserInvoices[i].Items = new ObservableCollection<InvoiceItemModel>(invoiceItemModel);
var invoicePaymentModel = connection.Query<InvoicePaymentModel>
("Select * from InvoicePaymentModel Where InvoiceModelId=@Id", new { @Id = obserInvoices[i].Id }).ToList();
if (invoicePaymentModel.Any())
obserInvoices[i].Payments = new ObservableCollection<InvoicePaymentModel>(invoicePaymentModel);
}
}
Upvotes: 1