private void MultiMapping_OneToMany(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Select_WithItem;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoiceDictionary = new Dictionary <int, Invoice>();
var invoices = connection.Query <Invoice, InvoiceItem, Invoice>(
sql,
(invoice, invoiceItem) =>
{
Invoice invoiceEntry;
if (!invoiceDictionary.TryGetValue(invoice.InvoiceID, out invoiceEntry))
{
invoiceEntry = invoice;
invoiceEntry.Items = new List <InvoiceItem>();
invoiceDictionary.Add(invoiceEntry.InvoiceID, invoiceEntry);
}
invoiceEntry.Items.Add(invoiceItem);
return(invoiceEntry);
},
splitOn: "InvoiceItemID")
.Distinct()
.ToList();
My.Result.Show(invoices);
}
}
private void Anonymous(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Select_ByID;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoice = connection.QueryFirstOrDefault(sql, new { InvoiceID = 1 });
My.Result.Show(invoice);
}
}
private void StronglyTyped(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Select;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = connection.Query <Invoice>(sql).ToList();
My.Result.Show(invoices);
}
}
private void StronglyTyped(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Select_ByID;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoice = connection.QuerySingle <Invoice>(sql, new { InvoiceID = 1 });
My.Result.Show(invoice);
}
}
private void Insert_Single(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Insert;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql, new { Kind = InvoiceKind.WebInvoice, Code = "Single_Insert_1" });
My.Result.Show(affectedRows);
}
}
private void Delete_Single(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Delete;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql, new { InvoiceID = 1 });
My.Result.Show(affectedRows);
}
}
private void Read(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Select_Then_Item_Select;
using (var connection = My.ConnectionFactory())
{
connection.Open();
using (var multi = connection.QueryMultiple(sql, new { InvoiceID = 1 }))
{
var invoice = multi.Read <Invoice>().First();
var invoiceItems = multi.Read <InvoiceItem>().ToList();
My.Result.Show(invoice, invoiceItems);
}
}
}
private void MultiType(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Select;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = new List <Invoice>();
using (var reader = connection.ExecuteReader(sql))
{
var storeInvoiceParser = reader.GetRowParser <StoreInvoice>();
var webInvoiceParser = reader.GetRowParser <WebInvoice>();
while (reader.Read())
{
Invoice invoice;
switch ((InvoiceKind)reader.GetInt32(reader.GetOrdinal("Kind")))
{
case InvoiceKind.StoreInvoice:
invoice = storeInvoiceParser(reader);
break;
case InvoiceKind.WebInvoice:
invoice = webInvoiceParser(reader);
break;
default:
throw new Exception(ExceptionMessage.GeneralException);
}
invoices.Add(invoice);
}
}
My.Result.Show(invoices);
}
}
private void Update_Many(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Update;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql,
new[]
{
new { InvoiceID = 1, Code = "Many_Update_1" },
new { InvoiceID = 2, Code = "Many_Update_2" },
new { InvoiceID = 3, Code = "Many_Update_3" }
});
My.Result.Show(affectedRows);
}
}
private void StoredProcedure_Many(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Proc_Invoice_Insert;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var affectedRows = connection.Execute(sql,
new[]
{
new { Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_1" },
new { Kind = InvoiceKind.WebInvoice, Code = "Many_Insert_2" },
new { Kind = InvoiceKind.StoreInvoice, Code = "Many_Insert_3" }
},
commandType: CommandType.StoredProcedure
);
My.Result.Show(affectedRows);
}
}
private void MultiMapping_OneToOne(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Invoice_Select_WithDetail;
using (var connection = My.ConnectionFactory())
{
connection.Open();
var invoices = connection.Query <Invoice, InvoiceDetail, Invoice>(
sql,
(invoice, invoiceDetail) =>
{
invoice.Detail = invoiceDetail;
return(invoice);
},
splitOn: "InvoiceID")
.Distinct()
.ToList();
My.Result.Show(invoices);
}
}
