public static Customer GetCustomerWithOrders(string id = "1")
{
var customer = new Customer
{
Name = "Customer " + id,
PrimaryAddress = new CustomerAddress
{
AddressLine1 = id + " Humpty Street",
City = "Humpty Doo",
State = "Northern Territory",
Country = "Australia"
},
Orders = new[]
{
new Order {LineItem = "Line 1", Qty = 1, Cost = 1.99m},
new Order {LineItem = "Line 2", Qty = 2, Cost = 2.99m},
}.ToList(),
};
return customer;
}
private Customer AddCustomerWithOrders()
{
var customer = new Customer
{
Name = "Customer 1",
PrimaryAddress = new CustomerAddress
{
AddressLine1 = "1 Australia Street",
Country = "Australia"
},
Orders = new[]
{
new Order {LineItem = "Line 1", Qty = 1, Cost = 1.99m},
new Order {LineItem = "Line 2", Qty = 2, Cost = 2.99m},
}.ToList(),
};
db.Save(customer, references: true);
return customer;
}
public async Task Can_Save_and_Load_References_Async()
{
var customer = new Customer
{
Name = "Customer 1",
PrimaryAddress = new CustomerAddress
{
AddressLine1 = "1 Humpty Street",
City = "Humpty Doo",
State = "Northern Territory",
Country = "Australia"
},
Orders = new[] {
new Order { LineItem = "Line 1", Qty = 1, Cost = 1.99m },
new Order { LineItem = "Line 2", Qty = 2, Cost = 2.99m },
}.ToList(),
};
await db.SaveAsync(customer);
Assert.That(customer.Id, Is.GreaterThan(0));
Assert.That(customer.PrimaryAddress.CustomerId, Is.EqualTo(0));
await db.SaveReferencesAsync(customer, customer.PrimaryAddress);
Assert.That(customer.PrimaryAddress.CustomerId, Is.EqualTo(customer.Id));
await db.SaveReferencesAsync(customer, customer.Orders);
Assert.That(customer.Orders.All(x => x.CustomerId == customer.Id));
var dbCustomer = await db.LoadSingleByIdAsync<Customer>(customer.Id);
dbCustomer.PrintDump();
Assert.That(dbCustomer.PrimaryAddress, Is.Not.Null);
Assert.That(dbCustomer.Orders.Count, Is.EqualTo(2));
}
public void Run()
{
//Setup SQL Server Connection Factory
var dbFactory = OrmLiteTestBase.CreateSqlServerDbFactory();
//var dbFactory = new OrmLiteConnectionFactory(
// @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Database1.mdf;Integrated Security=True;User Instance=True",
// SqlServerOrmLiteDialectProvider.Instance);
//Use in-memory Sqlite DB instead
//var dbFactory = new OrmLiteConnectionFactory(
// ":memory:", false, SqliteOrmLiteDialectProvider.Instance);
//Non-intrusive: All extension methods hang off System.Data.* interfaces
using (IDbConnection db = dbFactory.OpenDbConnection())
{
//Re-Create all table schemas:
db.DropTable<OrderDetail>();
db.DropTable<Order>();
db.DropTable<Customer>();
db.DropTable<Product>();
db.DropTable<Employee>();
db.CreateTable<Employee>();
db.CreateTable<Product>();
db.CreateTable<Customer>();
db.CreateTable<Order>();
db.CreateTable<OrderDetail>();
db.Insert(new Employee { Id = 1, Name = "Employee 1" });
db.Insert(new Employee { Id = 2, Name = "Employee 2" });
var product1 = new Product { Id = 1, Name = "Product 1", UnitPrice = 10 };
var product2 = new Product { Id = 2, Name = "Product 2", UnitPrice = 20 };
db.Save(product1, product2);
var customer = new Customer {
FirstName = "Orm",
LastName = "Lite",
Email = "*****@*****.**",
PhoneNumbers =
{
{ PhoneType.Home, "555-1234" },
{ PhoneType.Work, "1-800-1234" },
{ PhoneType.Mobile, "818-123-4567" },
},
Addresses =
{
{ AddressType.Work, new Address { Line1 = "1 Street", Country = "US", State = "NY", City = "New York", ZipCode = "10101" } },
},
CreatedAt = DateTime.UtcNow,
};
db.Insert(customer);
var customerId = db.GetLastInsertId(); //Get Auto Inserted Id
customer = db.QuerySingle<Customer>(new { customer.Email }); //Query
Assert.That(customer.Id, Is.EqualTo(customerId));
//Direct access to System.Data.Transactions:
using (IDbTransaction trans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
var order = new Order {
CustomerId = customer.Id,
EmployeeId = 1,
OrderDate = DateTime.UtcNow,
Freight = 10.50m,
ShippingAddress = new Address { Line1 = "3 Street", Country = "US", State = "NY", City = "New York", ZipCode = "12121" },
};
db.Save(order); //Inserts 1st time
order.Id = (int)db.GetLastInsertId(); //Get Auto Inserted Id
var orderDetails = new[] {
new OrderDetail {
OrderId = order.Id,
ProductId = product1.Id,
Quantity = 2,
UnitPrice = product1.UnitPrice,
},
new OrderDetail {
OrderId = order.Id,
ProductId = product2.Id,
Quantity = 2,
UnitPrice = product2.UnitPrice,
Discount = .15m,
}
};
db.Insert(orderDetails);
order.Total = orderDetails.Sum(x => x.UnitPrice * x.Quantity * x.Discount) + order.Freight;
db.Save(order); //Updates 2nd Time
trans.Commit();
}
}
}
public async Task Can_Save_References_Async()
{
var customer = new Customer
{
Name = "Customer 1",
PrimaryAddress = new CustomerAddress
{
AddressLine1 = "1 Humpty Street",
City = "Humpty Doo",
State = "Northern Territory",
Country = "Australia"
},
Orders = new[] {
new Order { LineItem = "Line 1", Qty = 1, Cost = 1.99m },
new Order { LineItem = "Line 2", Qty = 2, Cost = 2.99m },
}.ToList(),
};
try
{
await db.SaveAsync(customer, references:true);
Assert.That(customer.Id, Is.GreaterThan(0));
}
catch (Exception ex)
{
throw ex;
}
}
public async Task Can_load_only_included_references_async()
{
var customer = new Customer
{
Name = "Customer 1",
PrimaryAddress = new CustomerAddress
{
AddressLine1 = "1 Humpty Street",
City = "Humpty Doo",
State = "Northern Territory",
Country = "Australia"
},
Orders = new[] {
new Order { LineItem = "Line 1", Qty = 1, Cost = 1.99m },
new Order { LineItem = "Line 2", Qty = 2, Cost = 2.99m },
}.ToList(),
};
await db.SaveAsync(customer);
Assert.That(customer.Id, Is.GreaterThan(0));
await db.SaveReferencesAsync(customer, customer.PrimaryAddress);
Assert.That(customer.PrimaryAddress.CustomerId, Is.EqualTo(customer.Id));
await db.SaveReferencesAsync(customer, customer.Orders);
Assert.That(customer.Orders.All(x => x.CustomerId == customer.Id));
// LoadSelectAsync overload 1
var dbCustomers = await db.LoadSelectAsync<Customer>(db.From<Customer>().Where(q => q.Id == customer.Id), include: new[] { "PrimaryAddress" });
Assert.That(dbCustomers.Count, Is.EqualTo(1));
Assert.That(dbCustomers[0].Name, Is.EqualTo("Customer 1"));
Assert.That(dbCustomers[0].Orders, Is.Null);
Assert.That(dbCustomers[0].PrimaryAddress, Is.Not.Null);
// LoadSelectAsync overload 2
dbCustomers = await db.LoadSelectAsync<Customer>(q => q.Id == customer.Id, include: new[] { "PrimaryAddress" });
Assert.That(dbCustomers.Count, Is.EqualTo(1));
Assert.That(dbCustomers[0].Name, Is.EqualTo("Customer 1"));
Assert.That(dbCustomers[0].Orders, Is.Null);
Assert.That(dbCustomers[0].PrimaryAddress, Is.Not.Null);
// LoadSelectAsync overload 3
dbCustomers = await db.LoadSelectAsync(db.From<Customer>().Where(x => x.Id == customer.Id), include: new[] { "PrimaryAddress" });
Assert.That(dbCustomers.Count, Is.EqualTo(1));
Assert.That(dbCustomers[0].Name, Is.EqualTo("Customer 1"));
Assert.That(dbCustomers[0].Orders, Is.Null);
Assert.That(dbCustomers[0].PrimaryAddress, Is.Not.Null);
// LoadSingleById overload 1
var dbCustomer = await db.LoadSingleByIdAsync<Customer>(customer.Id, include: new[] { "PrimaryAddress" });
Assert.That(dbCustomer.Name, Is.EqualTo("Customer 1"));
Assert.That(dbCustomer.Orders, Is.Null);
Assert.That(dbCustomer.PrimaryAddress, Is.Not.Null);
// LoadSingleById overload 2
dbCustomer = await db.LoadSingleByIdAsync<Customer>(customer.Id, include: x => new { x.PrimaryAddress });
Assert.That(dbCustomer.Name, Is.EqualTo("Customer 1"));
Assert.That(dbCustomer.Orders, Is.Null);
Assert.That(dbCustomer.PrimaryAddress, Is.Not.Null);
// Invalid field name
try
{
dbCustomers = await db.LoadSelectAsync<Customer>(q => q.Id == customer.Id, include: new[] { "InvalidOption1", "InvalidOption2" });
Assert.Fail();
}
catch (System.ArgumentException)
{
}
catch (System.Exception)
{
Assert.Fail();
}
try
{
dbCustomer = await db.LoadSingleByIdAsync<Customer>(customer.Id, include: new[] { "InvalidOption1", "InvalidOption2" });
Assert.Fail();
}
catch (System.ArgumentException)
{
}
catch (System.Exception)
{
Assert.Fail();
}
}
public async Task Can_run_Customer_Orders_UseCase()
{
LogManager.LogFactory = new ConsoleLogFactory();
using (IDbConnection db = OpenDbConnection())
{
//Re-Create all table schemas:
RecreateTables(db);
await db.InsertAsync(new Employee { Id = 1, Name = "Employee 1" });
await db.InsertAsync(new Employee { Id = 2, Name = "Employee 2" });
var product1 = new Product { Id = 1, Name = "Product 1", UnitPrice = 10 };
var product2 = new Product { Id = 2, Name = "Product 2", UnitPrice = 20 };
await db.SaveAsync(product1, product2);
var customer = new Customer {
FirstName = "Orm",
LastName = "Lite",
Email = "*****@*****.**",
PhoneNumbers =
{
{ PhoneType.Home, "555-1234" },
{ PhoneType.Work, "1-800-1234" },
{ PhoneType.Mobile, "818-123-4567" },
},
Addresses =
{
{ AddressType.Work, new Address { Line1 = "1 Street", Country = "US", State = "NY", City = "New York", ZipCode = "10101" } },
},
CreatedAt = DateTime.UtcNow,
};
var customerId = await db.InsertAsync(customer, selectIdentity: true); //Get Auto Inserted Id
customer = await db.SingleAsync<Customer>(new { customer.Email }); //Query
Assert.That(customer.Id, Is.EqualTo(customerId));
//Direct access to System.Data.Transactions:
using (IDbTransaction trans = db.OpenTransaction(IsolationLevel.ReadCommitted))
{
var order = new Order {
CustomerId = customer.Id,
EmployeeId = 1,
OrderDate = DateTime.UtcNow,
Freight = 10.50m,
ShippingAddress = new Address { Line1 = "3 Street", Country = "US", State = "NY", City = "New York", ZipCode = "12121" },
};
await db.SaveAsync(order); //Inserts 1st time
//order.Id populated on Save().
var orderDetails = new[] {
new OrderDetail {
OrderId = order.Id,
ProductId = product1.Id,
Quantity = 2,
UnitPrice = product1.UnitPrice,
},
new OrderDetail {
OrderId = order.Id,
ProductId = product2.Id,
Quantity = 2,
UnitPrice = product2.UnitPrice,
Discount = .15m,
}
};
await db.SaveAsync(orderDetails);
order.Total = orderDetails.Sum(x => x.UnitPrice * x.Quantity * x.Discount) + order.Freight;
await db.SaveAsync(order); //Updates 2nd Time
trans.Commit();
}
}
}
public void Run()
{
LogManager.LogFactory = new ConsoleLogFactory();
//var dbFactory = new OrmLiteConnectionFactory(
// @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\App_Data\Database1.mdf;Integrated Security=True;User Instance=True",
// SqlServerDialect.Provider);
//Use in-memory Sqlite DB instead
//var dbFactory = new OrmLiteConnectionFactory(
// ":memory:", false, SqliteDialect.Provider);
//If you are trying to get this to build as a standalone example, use one of the dbFactory methods
// above, instead of Config.OpenDbConnection in the using statement below.
//Non-intrusive: All extension methods hang off System.Data.* interfaces
using (IDbConnection db = Config.OpenDbConnection())
{
//Re-Create all table schemas:
RecreateTables(db);
db.Insert(new Employee { Id = 1, Name = "Employee 1" });
db.Insert(new Employee { Id = 2, Name = "Employee 2" });
var product1 = new Product { Id = 1, Name = "Product 1", UnitPrice = 10 };
var product2 = new Product { Id = 2, Name = "Product 2", UnitPrice = 20 };
db.Save(product1, product2);
var customer = new Customer {
FirstName = "Orm",
LastName = "Lite",
Email = "*****@*****.**",
PhoneNumbers =
{
{ PhoneType.Home, "555-1234" },
{ PhoneType.Work, "1-800-1234" },
{ PhoneType.Mobile, "818-123-4567" },
},
Addresses =
{
{ AddressType.Work, new Address { Line1 = "1 Street", Country = "US", State = "NY", City = "New York", ZipCode = "10101" } },
},
CreatedAt = DateTime.UtcNow,
};
var customerId = db.Insert(customer, selectIdentity: true); //Get Auto Inserted Id
customer = db.Single<Customer>(new { customer.Email }); //Query
Assert.That(customer.Id, Is.EqualTo(customerId));
#if NETCORE
var isolationLevel = base.Dialect == Dialect.Sqlite ? IsolationLevel.Serializable : IsolationLevel.ReadCommitted;
#else
var isolationLevel = IsolationLevel.ReadCommitted;
#endif
//Direct access to System.Data.Transactions:
using (IDbTransaction trans = db.OpenTransaction(isolationLevel))
{
var order = new Order {
CustomerId = customer.Id,
EmployeeId = 1,
OrderDate = DateTime.UtcNow,
Freight = 10.50m,
ShippingAddress = new Address { Line1 = "3 Street", Country = "US", State = "NY", City = "New York", ZipCode = "12121" },
};
db.Save(order); //Inserts 1st time
//order.Id populated on Save().
var orderDetails = new[] {
new OrderDetail {
OrderId = order.Id,
ProductId = product1.Id,
Quantity = 2,
UnitPrice = product1.UnitPrice,
},
new OrderDetail {
OrderId = order.Id,
ProductId = product2.Id,
Quantity = 2,
UnitPrice = product2.UnitPrice,
Discount = .15m,
}
};
db.Save(orderDetails);
order.Total = orderDetails.Sum(x => x.UnitPrice * x.Quantity * x.Discount) + order.Freight;
db.Save(order); //Updates 2nd Time
trans.Commit();
}
}
}