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(); } } }