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