コード例 #1
0
 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;
        }
コード例 #3
0
        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));
        }
コード例 #4
0
        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();
                }
            }
        }
コード例 #5
0
        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;
            }
        }
コード例 #6
0
        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();
            }
        }
コード例 #7
0
        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();
                }
            }
        }
コード例 #8
0
        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();
                }
            }
        }