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); }
public void SQLWithWhere() { var o = new Models.Orders(); Verify( Select(o.CustomerID).Where(o.OrderID.IsEqualTo(10248)) , "SELECT CUSTOMERID from orders where orderid=10248" ); }
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' " ); }
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;" ); }
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 " ); }
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) " ); }
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;" ); }
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 " ); }
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 " ); }
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); }