Ejemplo n.º 1
0
        public JsonResult Create(Orders orders)
        {
            if (ModelState.IsValid)
            {
                db.Orders.Add(orders);
                db.SaveChanges();
            }

            var original = db.Orders.Where(c => c.OrderID == orders.OrderID)
                            .Include(c => c.Customers)
                            .Include(c => c.Employees)
                            .Include(c => c.Shippers)
                            .FirstOrDefault();
            var obj = new
            {
                OrderID = original.OrderID,
                OrderDate = original.OrderDate == null ? "" : String.Format("{0:yyyy-MM-dd}", original.OrderDate),
                Customer = original.Customers == null ? "" : original.Customers.CompanyName,
                Employee = original.Employees == null ? "" : original.Employees.FirstName,
                Shipper = original.Shippers == null ? "" : original.Shippers.CompanyName,
                ShipAddress = original.ShipAddress == null ? "" : original.ShipAddress,
                ShipCity = original.ShipCity == null ? "" : original.ShipCity,
                ShipCountry = original.ShipCountry == null ? "" : original.ShipCountry
            };

            return Json(obj);
        }
Ejemplo n.º 2
0
        public void SQLWithWhere()
        {
            var o = new Models.Orders();

            Verify(
                Select(o.CustomerID).Where(o.OrderID.IsEqualTo(10248))
                ,
                "SELECT CUSTOMERID from orders where orderid=10248"
                );
        }
Ejemplo n.º 3
0
        public void SQLWithDate()
        {
            var o = new Models.Orders();

            Verify(
                Select(o.CustomerID).Where(o.OrderDate.IsEqualTo(1996, 07, 04))
                ,
                @"SELECT CustomerID FROM Orders WHERE OrderDate='1996-07-04' "
                );
        }
Ejemplo n.º 4
0
        public void SQLWithUnionAll()
        {
            var o = new Models.Orders();
            var c = new Models.Customers();

            Verify(
                Select(c.City, c.Country).Where(c.Country.IsEqualTo("Germany")).UnionAll(
                    Select(o.ShipCity, o.ShipCountry).Where(o.ShipCountry.IsEqualTo("Germany"))).OrderBy(c.City)
                ,
                @"SELECT City, Country FROM Customers WHERE Country='Germany'
                  UNION  ALL
                  SELECT ShipCity, ShipCountry FROM orders WHERE ShipCountry='Germany'
                  ORDER BY City;"
                );
        }
Ejemplo n.º 5
0
        public void SQLWithInnerJoins()
        {
            var o = new Models.Orders();
            var c = new Models.Customers();

            Verify(
                Select(o.OrderID, c.CompanyName, o.OrderDate).
                From(o).
                InnerJoin(c, o.CustomerID.IsEqualTo(c.CustomerID))
                ,
                @"SELECT  Orders.OrderID, Customers.CompanyName, Orders.OrderDate
                  FROM Orders
                  INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID "
                );
        }
Ejemplo n.º 6
0
        public void SQLWithLamda_1()
        {
            var u = new ENV.UserMethods();
            var o = new Models.Orders();
            var c = new Models.Customers();

            Verify(
                Select(o.OrderID, c.CompanyName, o.OrderDate).
                From(o).
                InnerJoin(c, new SqlPart(o.CustomerID, "=", new SqlFunction("Left", c.CustomerID, 5)))
                ,
                @"SELECT  Orders.OrderID, Customers.CompanyName, Orders.OrderDate
                  FROM Orders
                  INNER JOIN Customers ON Orders.CustomerID = left(Customers.CustomerID,5) "
                );
        }
Ejemplo n.º 7
0
        public void SQLWithUnion()
        {
            var o = new Models.Orders();
            var c = new Models.Customers();

            Verify(
                Select(c.City, c.Country).Where(c.Country.IsEqualTo("Germany")).Union(

                    Select(o.ShipCity, o.ShipCountry).Where(o.ShipCountry.IsEqualTo("Germany"))).OrderBy(1, SortDirection.Descending)

                ,
                @"SELECT City, Country FROM Customers WHERE Country='Germany'
                  UNION 
                  SELECT ShipCity, ShipCountry FROM orders WHERE ShipCountry='Germany'
                  ORDER BY City desc;"
                );
        }
Ejemplo n.º 8
0
        public void SQLWithFullJoin()
        {
            var o = new Models.Orders();
            var c = new Models.Customers();

            Verify(
                Select(c.CompanyName, o.OrderID).
                From(c).
                FullOuterJoin(o, c.CustomerID.IsEqualTo(o.CustomerID)).
                OrderBy(c.CompanyName)
                ,
                @"SELECT Customers.CompanyName, Orders.OrderID
                  FROM Customers
                  FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
                  ORDER BY Customers.CompanyName "
                );
        }
Ejemplo n.º 9
0
        public void SQLWithRightJoin()
        {
            var o = new Models.Orders();
            var c = new Models.Customers();

            Verify(
                Select(c.CompanyName, o.OrderID).
                From(c).
                RightOuterJoin(o, o.CustomerID.IsEqualTo(c.CustomerID)).
                OrderBy(c.CompanyName)
                ,
                @"SELECT Customers.CompanyName, Orders.OrderID
                  FROM Customers
                  RIGHT JOIN Orders ON Customers.CustomerID=Orders.CustomerID
                  ORDER BY Customers.CompanyName "
                );
        }
Ejemplo n.º 10
0
        public JsonResult Edit(Orders updatedOrder)
        {
            var original = db.Orders.Find(updatedOrder.OrderID);

            if (original != null)
            {
                original.CustomerID = updatedOrder.CustomerID;
                original.EmployeeID = updatedOrder.EmployeeID;
                original.OrderDate = updatedOrder.OrderDate;
                original.RequiredDate = updatedOrder.RequiredDate;
                original.ShippedDate = updatedOrder.ShippedDate;
                original.ShipVia = updatedOrder.ShipVia;
                original.Freight = updatedOrder.Freight;
                original.ShipName = updatedOrder.ShipName;
                original.ShipAddress = updatedOrder.ShipAddress;
                original.ShipCity = updatedOrder.ShipCity;
                original.ShipRegion = updatedOrder.ShipRegion;
                original.ShipPostalCode = updatedOrder.ShipPostalCode;
                original.ShipCountry = updatedOrder.ShipCountry;
                db.SaveChanges();
            }

            var obj = new
            {
                OrderID = original.OrderID,
                OrderDate = String.Format("{0:yyyy-MM-dd}",original.OrderDate),
                Customer = original.Customers.CompanyName,
                Employee = original.Employees.FirstName,
                Shipper = original.Shippers.CompanyName,
                ShipAddress = original.ShipAddress,
                ShipCity = original.ShipCity,
                ShipCountry = original.ShipCountry
            };

            return Json(obj);
        }