public BusinessType Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <BusinessType>(My.Table_BusinessType.SelectSingle, new { businessTypeId = id }));
}
}
public IEnumerable <AppUser> Get()
{
using (var db = My.ConnectionFactory())
{
return(db.Query <AppUser>(My.Table_AppUser.Select).ToList());
}
}
public IEnumerable <Township> Get()
{
using (var db = My.ConnectionFactory())
{
return(db.Query <Township>(My.Table_Township.Select).ToList());
}
}
public Language Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <Language>(My.Table_Language.SelectSingle, new { languageId = id }));
}
}
public IEnumerable <Position> Get()
{
using (var db = My.ConnectionFactory())
{
return(db.Query <Position>(My.Table_Position.Select).ToList());
}
}
public IEnumerable <ProductType> Get()
{
using (var db = My.ConnectionFactory())
{
return(db.Query <ProductType>(My.Table_ProductType.Select).ToList());
}
}
private void Execute_Many(object sender, EventArgs e)
{
My.Database.Reset();
var sql = My.SqlText.Proc_Invoice_Insert;
var parameters = new List <DynamicParameters>();
for (var i = 0; i < 3; i++)
{
var p = new DynamicParameters();
p.Add("@Kind", InvoiceKind.WebInvoice, DbType.Int32, ParameterDirection.Input);
p.Add("@Code", "Many_Insert_" + (i + 1), DbType.String, ParameterDirection.Input);
p.Add("@RowCount", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
parameters.Add(p);
}
using (var connection = My.ConnectionFactory())
{
connection.Open();
connection.Execute(sql,
parameters,
commandType: CommandType.StoredProcedure
);
var rowCount = parameters.Sum(x => x.Get <int>("@RowCount"));
My.Result.Show(rowCount);
}
}
private void Many(object sender, EventArgs e)
{
My.Database.Reset();
using (var connection = My.ConnectionFactory())
{
connection.Open();
var list = new List <InvoiceContrib>
{
new InvoiceContrib {
InvoiceID = 1, Code = "Update_Many_1"
},
new InvoiceContrib {
InvoiceID = 2, Code = "Update_Many_2"
},
new InvoiceContrib {
InvoiceID = 3, Code = "Update_Many_3"
}
};
var isSuccess = connection.Update(list);
My.Result.Show(isSuccess);
}
}
public IndustryType Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <IndustryType>(My.Table_IndustryType.SelectSingle, new { industryTypeId = id }));
}
}
public IEnumerable <AccountHead> Get()
{
using (var db = My.ConnectionFactory())
{
return(db.Query <AccountHead>(My.Table_AccountHead.Select).ToList());
}
}
public AccountHead Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <AccountHead>(My.Table_AccountHead.SelectSingle, new { accountHeadId = id }));
}
}
private void Many(object sender, EventArgs e)
{
My.Database.Reset(includeDetail: false, includeItems: false);
using (var connection = My.ConnectionFactory())
{
connection.Open();
var list = new List <InvoiceContrib>
{
new InvoiceContrib {
InvoiceID = 1
},
new InvoiceContrib {
InvoiceID = 2
},
new InvoiceContrib {
InvoiceID = 3
}
};
var isSuccess = connection.Delete(list);
My.Result.Show(isSuccess);
}
}
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);
}
}
public MemberType Get(Guid?id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <MemberType>(My.Table_MemberType.SelectSingle, new { memberTypeId = id }));
}
}
public City Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <City>(My.Table_City.SelectSingle, new { cityId = id }));
}
}
public IEnumerable <FiscalYear> Get()
{
using (var db = My.ConnectionFactory())
{
return(db.Query <FiscalYear>(My.Table_FiscalYear.Select).ToList());
}
}
private List <Invoice> GetInvoiceWithItems()
{
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();
return(invoices);
}
}
public FiscalYear Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <FiscalYear>(My.Table_FiscalYear.SelectSingle, new { fscalYearId = id }));
}
}
public ProductType Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <ProductType>(My.Table_ProductType.SelectSingle, new { productTypeId = id }));
}
}
public void Relation_OneToMany(object sender, EventArgs e)
{
My.Database.Reset(10000);
var invoices = GetInvoiceWithItems();
invoices.ForEach(x =>
{
x.Code += "z";
x.Items.ForEach(y => y.Code += "z");
});
using (var connection = My.ConnectionFactory())
{
connection.Open();
var clock = new Stopwatch();
clock.Start();
connection.BulkUpdate(invoices, x => x.Items);
clock.Stop();
My.Result.Show(clock, invoices.Count + invoices.Sum(x => x.Items.Count));
}
}
public IEnumerable <Language> Get()
{
using (var db = My.ConnectionFactory())
{
return(db.Query <Language>(My.Table_Language.Select).ToList());
}
}
public void Single(object sender, EventArgs e)
{
My.Database.Reset();
Invoice invoice;
using (var connection = My.ConnectionFactory())
{
invoice = connection.QueryFirst <Invoice>(My.SqlText.Invoice_Select_ByID, new { InvoiceID = 1 });
invoice.Code = "Bulk_Update_0";
}
using (var connection = My.ConnectionFactory())
{
connection.Open();
var clock = new Stopwatch();
clock.Start();
connection.BulkUpdate(invoice);
clock.Stop();
My.Result.Show(clock, 1);
}
}
public Person Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
var persons = db.Query <Person, City, Township, City, Person>($@"SELECT * FROM dbo.Person
LEFT OUTER JOIN dbo.City AS homeCity ON dbo.Person.homeCityId = homeCity.cityId
LEFT OUTER JOIN dbo.Township ON dbo.Person.homeTownshipId = dbo.Township.townshipId
LEFT OUTER JOIN dbo.City AS nativeCity ON dbo.Person.nativeCityId = nativeCity.cityId WHERE personId='{id.ToString()}'", (person, homeCity, homeTownship, nativeCity) =>
{
person.homeCity = homeCity;
person.homeTownship = homeTownship;
person.nativeCity = nativeCity;
return(person);
}, splitOn: "cityId,townshipId,cityId").ToArray();
if (persons.Length == 1)
{
PersonBusinessController personBusinessController = new PersonBusinessController();
PersonLanguageController personLanguageController = new PersonLanguageController();
var p = persons[0];
p.personBusiness = personBusinessController.Get(p.personId.ToString()).ToArray();
p.personLanguage = personLanguageController.Get(p.personId.ToString()).ToArray();
personBusinessController.Dispose();
personLanguageController.Dispose();
return(p);
}
else
{
return(null);
}
}
}
public void Many(object sender, EventArgs e)
{
My.Database.Reset(10000);
List <Invoice> invoices;
using (var connection = My.ConnectionFactory())
{
invoices = connection.Query <Invoice>(My.SqlText.Invoice_Select).ToList();
for (var i = 0; i < invoices.Count; i++)
{
invoices[i].Code = "Bulk_Update_" + i;
}
}
using (var connection = My.ConnectionFactory())
{
connection.Open();
var clock = new Stopwatch();
clock.Start();
connection.BulkUpdate(invoices);
clock.Stop();
My.Result.Show(clock, invoices.Count);
}
}
public Position Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <Position>(My.Table_Position.SelectSingle, new { positionId = id }));
}
}
public void Relation_OneToOne(object sender, EventArgs e)
{
My.Database.Reset(10000);
var invoices = GetInvoiceWithDetail();
invoices.ForEach(x =>
{
x.Code += "z";
x.Detail.Detail += "z";
});
using (var connection = My.ConnectionFactory())
{
connection.Open();
var clock = new Stopwatch();
clock.Start();
connection.BulkUpdate(invoices, x => x.Detail);
clock.Stop();
My.Result.Show(clock, invoices.Count * 2);
}
}
public AppUser Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <AppUser>(My.Table_AppUser.SelectSingle, new { userId = id }));
}
}
public IEnumerable <City> Get()
{
using (var db = My.ConnectionFactory())
{
return(db.Query <City>(My.Table_City.Select).ToList());
}
}
public Township Get(Guid id)
{
using (var db = My.ConnectionFactory())
{
return(db.QuerySingle <Township>(My.Table_Township.SelectSingle, new { townshipId = id }));
}
}
public IEnumerable <Business> Get()
{
using (var db = My.ConnectionFactory())
{
var businesses = db.Query <Business, IndustryType, Township, City, BusinessType, Business>($@"{My.Table_Business.Select}
LEFT OUTER JOIN dbo.IndustryType ON dbo.Business.industryTypeId = dbo.IndustryType.industryTypeId
LEFT OUTER JOIN dbo.Township ON dbo.Business.townshipId = dbo.Township.townshipId
LEFT OUTER JOIN dbo.City ON dbo.Business.cityId = dbo.City.cityId
LEFT OUTER JOIN dbo.BusinessType ON dbo.Business.businessTypeId = dbo.BusinessType.businessTypeId",
(business, industryType, township, city, businessType) =>
{
if (business.industryTypeId != null)
{
business.industryType = industryType;
}
if (business.cityId != null)
{
business.city = city;
}
if (business.townshipId != null)
{
business.township = township;
}
if (business.businessTypeId != null)
{
business.businessType = businessType;
}
return(business);
}, splitOn: "industryTypeId,townshipId,cityId,businessTypeId").ToList();
return(businesses);
}
}
