// ReSharper restore UnusedMember.Global

        // ReSharper disable once UnusedMember.Local
        static void testSqlServer(ReflectedTable <Order> orders)
        {
            var sqlTest           = new SqlServerSource("TestDb1");
            var sqlCustomersTable = sqlTest.Tables.Get <Customer>();
            var cl2 = sqlCustomersTable.ToList();

            cl2[0].ZipCode++;
            cl2[0].Update();
            var newCustomer = new Customer {
                Name    = "Delete Me",
                Address = "123 You're dead",
                City    = "Hell",
                State   = "MI",
                ZipCode = 666
            };

            sqlCustomersTable.Insert(newCustomer);
            newCustomer.Delete();

            var sqlDataTableNames = sqlTest.GetDataTableNames();

            if (sqlDataTableNames.Contains("Orders"))
            {
                sqlTest.Table <Order>().DropDataTable();
            }
            sqlTest.Table <Order>().CreateDataTable();
            foreach (var o in orders.ToEnumerable())
            {
                sqlTest.Table <Order>().Insert(o);
            }
            sqlTest.Table <Order>().DropDataTable();

            // copying a hierarchy set from a one source to the other requires mapping new ID's!
        }
Example #2
0
 internal JoinSet(string onField, object onId, ReflectedTable <T> table) : base(null, null, table)
 {
     this._onId    = onId;
     this._onField = onField;
 }
        // ReSharper disable once UnusedParameter.Local
        static void Main(string[] args)
        {
            // simplify connection creation by referring file path and immediately start using data
            // ReSharper disable once UnusedVariable
            // ReSharper disable once StringLiteralTypo
            var srcNew = new DataFileSource(StartupPath + @"\testDB.accdb");

            var src = new DataFileSource(StartupPath + @"\testDB.mdb")
            {
                ReuseConnection = true
            };

            // Run a query into a data-set, reflection not involved yet
            var dataSet = src.GetDataSet("select * from customers");

            dataSet.Dispose();

            // We can use linq to a data-set-wrapper and then assign anonymous types instead of defining reflect-able tables
            var wrappedSet          = src.GetDataSetWrapper("select * from customers");
            var linqEnumFromDataSet = from line in wrappedSet
                                      select new {
                CustomerName = (string)line["CustomerName"],
                City         = (string)line["City"]
            };

            foreach (var linqLine in linqEnumFromDataSet)
            {
                Debug.WriteLine(linqLine.CustomerName + " from " + linqLine.City);
            }

            // We can access tables by defining record classes
            var customers  = src.Table <Customer>();
            var orders     = src.Tables.Get <Order>();
            var orderLines = src.Table <OrderLine>();

            // Run a static function over the table class to get a list of records and join-lists populated with data
            var customersList = ReflectedTable <Customer> .ToListFillJoins(src);

            var wasSameTableInstanceUtilized = customersList[0].AtTable == customers; // should be true

            // We can utilize these instances for quick select queries, enumerations as well as joins
            var jerome = customers.LikeList("%jer%").FirstOrDefault();

            // the line instances can be used to modify the data
            if (jerome != null)
            {
                jerome.ZipCode++;
                jerome.Update();

                // test sql insert.
                var newOrder = orders.Insert(new Order {
                    customerID = jerome.ID, OrderShipped = true
                });                                                           // insert
                var newOrderCopy = orders.Get(newOrder.ID);                   // query
                Debug.Assert(newOrder.customerID == newOrderCopy.customerID); // verify data
                Debug.Assert(newOrder.OrderShipped == newOrderCopy.OrderShipped);

                // Query into enumerable without table definition directly on data source. Reflection is performed to identify returned fields
                // this query uses a simple class definition without any attributes
                foreach (var detailAndPrice in src.SelectUnattributed <DetailAndPrice>("OrderLines", null))
                {
                    Debug.WriteLine(
                        "detail: " + detailAndPrice.Detail + "\t ,price:" + detailAndPrice.Price);
                }

                // Using an attributed reflected class - define the join relationship as a parameter to the function to return a list of results
                var selectJoinList = src.SelectList <CustomSelectJoinLine>(
                    "(Customers LEFT JOIN Orders ON Customers.ID = Orders.customerID)" +
                    "LEFT JOIN OrderLines ON Orders.ID = OrderLines.orderID", null);

                // Get a list from an sql query built by a tool. The line type of the returned list is defined by a class
                var customerAndSumList = src.SelectList <CustomerAndSum>(
                    @"SELECT DISTINCTROW Customers.CustomerName, Sum(OrderLines.Price) AS [Sum Of Price]
FROM (Customers LEFT JOIN Orders ON Customers.[ID] = Orders.[customerID]) LEFT JOIN OrderLines ON Orders.[ID] = OrderLines.[orderID]
GROUP BY Customers.CustomerName;");

                // test select-unattributed-list, no where
                var detailAndPriceList = src.SelectUnattributedList <DetailAndPrice>("OrderLines", null);

                // Run queries utilizing reflection and hierarchies defined by JoinSet classes
                // in the following test, data-readers are enumerated and not cached as memory lists
                foreach (var c in customers.ToEnumerable())
                {
                    foreach (var o in c.OrdersSet)
                    {
                        foreach (var ol in o.OrderLinesSet)
                        {
                            Debug.WriteLine(
                                c.Name + " bought " + ol.Detail + ":" + ol.Price);
                        }
                    }
                }

                // test table-join with different syntax
                foreach (var joinTableLine in src.Join <Customer, Order>("customerID"))
                {
                    Debug.WriteLine(
                        joinTableLine.l.Name + " bought on " +
                        joinTableLine.r.OrderDate);
                }
                foreach (var joinTableLine in src.Table <Customer>().JoinEn <Order>(-1, "customerID", null, null))
                {
                    Debug.WriteLine(
                        joinTableLine.l.Name + " bought on " +
                        joinTableLine.r.OrderDate);
                }

                // reuse join sets to perform group
                foreach (var joinedGroupedLine in src.Join <Customer, Order, OrderLine>(
                             "customerID", "orderID").Group(
                             "Customers.ID", "CustomerName", SqlFunction.Sum, "Price", null))
                {
                    Debug.WriteLine(joinedGroupedLine.extraField + " owes: " +
                                    joinedGroupedLine.functionField);
                }

                // run enumeration of hierarchy join-set relationships when several threads are involved
                var testThreads = new Thread[10];
                for (var i = 0; i < testThreads.Length; i++)
                {
                    testThreads[i] = new Thread(() => testHirarchySetQueries(src))
                    {
                        Name = "thread " + i
                    };
                }

                // ReSharper disable once ForCanBeConvertedToForeach
                for (var i = 0; i < testThreads.Length; i++)
                {
                    testThreads[i].Start();
                }
                // ReSharper disable once ForCanBeConvertedToForeach
                for (var i = 0; i < testThreads.Length; i++)
                {
                    testThreads[i].Join();
                }

                // the same test over several threads in a 2000 access data file
                testThreads = new Thread[10];
                for (var i = 0; i < testThreads.Length; i++)
                {
                    testThreads[i] = new Thread(() => testHirarchySetQueries(src))
                    {
                        Name = "thread " + i
                    };
                }

                // ReSharper disable once ForCanBeConvertedToForeach
                for (var i = 0; i < testThreads.Length; i++)
                {
                    testThreads[i].Start();
                }
                // ReSharper disable once ForCanBeConvertedToForeach
                for (var i = 0; i < testThreads.Length; i++)
                {
                    testThreads[i].Join();
                }

                // test source clone, and get data set over custom sql
                var srcClone = src.Clone();
                var dataSet2 = srcClone.GetDataSet("select * from customers");
                srcClone.Dispose();
                dataSet2.Dispose();

                // Use indexing to query for a record and update a record
                var someone = customers[customerAndSumList[0].CustomerName];
                someone.ZipCode++;
                customers[someone.Name] = someone;

                // easy code to insert new records into tables, doesn't keep an in memory reference
                orderLines.Insert(new OrderLine {
                    orderID = newOrder.ID,
                    Detail  = "test123",
                    Price   = 23.4m
                });
                orderLines.Insert(new OrderLine {
                    orderID = newOrder.ID,
                    Detail  = "delete123",
                    Price   = 23.4m
                });

                // utilize the ReflectedTableLine fill join mechanism to fill the empty list with data from storage
                newOrder.FillJoin("OrderLines");

                // delete new order and related order lines
                src.Delete("OrderLines", "orderID", newOrder.ID);
                newOrder.Delete();
            }

            // test fill joins to fill in-memory lists with data
            foreach (var c in customersList)
            {
                c.FillJoins();
                foreach (var o in c.Orders)
                {
                    o.FillJoins();
                }
            }

            // at this point should have an in memory tree of all customers->orders->order-lines
            var editOrderLine = customersList.Last().Orders.Last().OrderLines.LastOrDefault();

            if (editOrderLine != null)
            {
                editOrderLine.Price = 44.21m;
                orderLines.Update(editOrderLine); // use table instance to call update
            }
            else
            {
                var lastOrder = customersList.Last().Orders.Last();
                orderLines.Insert(new OrderLine {
                    orderID = lastOrder.ID,
                    Detail  = "stack of papers",
                    Price   = 43.22m
                });
            }

            // test access to xlsx file, reuse the same reflected table classes
            var xlsxTest                      = new DataFileSource(StartupPath + @"\Customers.xlsx");
            var xlsxCustomersTable            = xlsxTest.Tables.Get <Customer>();
            var xlsxCustomersTable2ndInstance = xlsxTest.Table <Customer>(); // alternative syntax

            Debug.Assert(xlsxCustomersTable == xlsxCustomersTable2ndInstance);
            var xlsxCustomersList = xlsxCustomersTable.ToList();

            xlsxCustomersList[0].ZipCode++;
            xlsxCustomersList[0].Update();

            // create table, drop table and copy records from one source to the other
            var dataTableNames = xlsxTest.GetDataTableNames();

            if (dataTableNames.Contains("Orders"))
            {
                xlsxTest.Table <Order>().DropDataTable();
            }

            xlsxTest.Table <Order>().CreateDataTable();
            foreach (var o in orders.ToEnumerable())
            {
                xlsxTest.Table <Order>().Insert(o);
            }

            // test export to excel
            if (File.Exists(StartupPath + @"\Customers1.xlsx"))
            {
                File.Delete(StartupPath + @"\Customers1.xlsx");
            }
            var newXlsxTest = new DataFileSource(StartupPath + @"\Customers1.xlsx");

            newXlsxTest.Table <Customer>().CreateDataTable();
            newXlsxTest.Dispose();
            //xlsxTest.Table<Order>().DropDataTable();

            // test access to local sql server express database
            // testSqlServer(orders);
        }