static void InsertOrder(
string shipName, string shipAddress,
string shipCity, string shipRegionm,
string shipPostalCode, string shipCountry,
string customerID = null, int? employeeID = null,
DateTime? orderDate = null, DateTime? requiredDate = null,
DateTime? shippedDate = null, int? shipVia = null,
decimal? freight = null)
{
using (NorthwindEntities context = new NorthwindEntities())
{
Order newOrder = new Order
{
ShipAddress = shipAddress,
ShipCity = shipCity,
ShipCountry = shipCountry,
ShipName = shipName,
ShippedDate = shippedDate,
ShipPostalCode = shipPostalCode,
ShipRegion = shipRegionm,
ShipVia = shipVia,
EmployeeID = employeeID,
OrderDate = orderDate,
RequiredDate = requiredDate,
Freight = freight,
CustomerID = customerID
};
context.Orders.Add(newOrder);
context.SaveChanges();
Console.WriteLine("Row is inserted.");
}
}
static void Main(string[] args)
{
using (NorthwindEntities context = new NorthwindEntities())
{
InsertMultiple();
}
}
public static void RemoveCustomerByID(NorthwindEntities db, string customerID)
{
var customerForRemove = db.Customers.Where(c => c.CustomerID == customerID).First();
db.Customers.Remove(customerForRemove);
db.SaveChanges();
}
private static IQueryable<Order> GetAllOrdersByYearAndCountry(NorthwindEntities db, int shipingYear, string shipingCountry)
{
var allOrdersFrom1997 =
from order in db.Orders
where order.OrderDate.Value.Year == shipingYear &&
order.ShipCountry == shipingCountry
select order;
return allOrdersFrom1997;
}
public static void ModifyCustomerByID(NorthwindEntities db, string customerID, string newCompanyName, string newContactName)
{
var customerForUpdate = db.Customers.Where(c => c.CustomerID == customerID).First();
customerForUpdate.CompanyName = newCompanyName;
customerForUpdate.ContactName = newContactName;
db.SaveChanges();
}
static void Main(string[] args)
{
Employee extended = new Employee();
NorthwindEntities context = new NorthwindEntities();
extended = context.Employees.Find(1);
foreach (var item in extended.Territories)
{
Console.WriteLine("Teritory description - {0}", item.TerritoryDescription);
}
}
public static void CreateNewCustomer(NorthwindEntities db, string customerID, string companyName, string contactName, string country)
{
var newCustomer = new Customer()
{
CustomerID = customerID,
CompanyName = companyName,
ContactName = contactName,
Country = country
};
db.Customers.Add(newCustomer);
db.SaveChanges();
}
static void Main(string[] args)
{
var db = new NorthwindEntities();
string orderRegion = null;
int startOrderYear = 1996;
int endOrderYear = 1996;
using (db)
{
ShowAllSalesByGivenRegionAndDate(db, orderRegion, startOrderYear, endOrderYear);
}
}
public static void Main()
{
var supplierName = "Exotic Liquids";
var startDate = new DateTime(1996, 1, 1);
var endDate = new DateTime(1997, 12, 31);
// First input the stored procedure and after thar uncomment the code lines.
using (var northwindEntities = new NorthwindEntities())
{
//var totalIncomes = northwindEntities.usp_TotalIncomesOfSupplier(supplierName, startDate, endDate).Single();
//Console.WriteLine("Total incomes: {0:C}", totalIncomes);
}
}
private static void ShowAllSalesByGivenRegionAndDate(NorthwindEntities db, string orderRegion, int startOrderYear, int endOrderYear)
{
var allOrders =
from order in db.Orders
where order.ShipRegion == orderRegion &&
order.ShippedDate.Value.Year >= startOrderYear &&
order.ShippedDate.Value.Year <= endOrderYear
select order;
foreach (var order in allOrders)
{
Console.WriteLine("OrderID " + order.OrderID +
"; ShipRegion: " + order.ShipRegion +
"; ShipDate: " + order.ShippedDate);
}
}
static void Main(string[] args)
{
var db = new NorthwindEntities();
int shipingYear = 1997;
string shipingCountry = "Canada";
using (db)
{
var allOrdersFrom1997 = GetAllOrdersByYearAndCountry(db, shipingYear, shipingCountry);
foreach (var order in allOrdersFrom1997)
{
Console.WriteLine("CustomerID: " + order.CustomerID +
"; OrderID: " + order.OrderID +
"; Year: " + order.OrderDate.Value.Year);
}
}
}
static void Main(string[] args)
{
using (NorthwindEntities northwindEntities1 = new NorthwindEntities())
{
using (NorthwindEntities northwindEntities2 = new NorthwindEntities())
{
Customer customerByFirstDataContext = northwindEntities1.Customers.Find("CHOPS");
customerByFirstDataContext.Region = "SW";
Customer customerBySecondDataContext = northwindEntities2.Customers.Find("CHOPS");
customerBySecondDataContext.Region = "SSWW";
northwindEntities1.SaveChanges();
northwindEntities2.SaveChanges();
}
}
Console.WriteLine("Changes successfully made!");
}
static void Main(string[] args)
{
IObjectContextAdapter context = new NorthwindEntities();
string cloneNorthwind = context.ObjectContext.CreateDatabaseScript();
string createNorthwindCloneDB = "CREATE DATABASE NorthwindTwin ON PRIMARY " +
"(NAME = NorthwindTwin, " +
"FILENAME = 'D:\\NorthwindTwin.mdf', " +
"SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = NorthwindTwinLog, " +
"FILENAME = 'D:\\NorthwindTwin.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)";
SqlConnection dbConForCreatingDB = new SqlConnection(
"Server=BOBBY-PC; " +
"Database=NORTHWND; " +
"Integrated Security=true");
dbConForCreatingDB.Open();
using (dbConForCreatingDB)
{
SqlCommand createDB = new SqlCommand(createNorthwindCloneDB, dbConForCreatingDB);
createDB.ExecuteNonQuery();
}
SqlConnection dbConForCloning = new SqlConnection(
"Server=LOCALHOST; " +
"Database=NorthwindTwin; " +
"Integrated Security=true");
dbConForCloning.Open();
using (dbConForCloning)
{
SqlCommand cloneDB = new SqlCommand(cloneNorthwind, dbConForCloning);
cloneDB.ExecuteNonQuery();
}
}
static void Main(string[] args)
{
var db = new NorthwindEntities();
using (db)
{
string customerID = "AAAAA";
string companyName = "ARS";
string contactName = "Kiril Ivanov";
string country = "Bulgaria";
DAO.CreateNewCustomer(db, customerID, companyName, contactName, country);
string newCompanyName = "BBR";
string newContactName = "Marin Ivanov";
DAO.ModifyCustomerByID(db, customerID, newCompanyName, newContactName);
DAO.RemoveCustomerByID(db, customerID);
}
}
private static void FindByOrderUsingSQL(int year, string country)
{
NorthwindEntities db = new NorthwindEntities();
string nativeSQLQuery =
"SELECT c.CustomerID " +
"FROM Customers c " +
"JOIN Orders o " +
"ON c.CustomerID = o.CustomerID " +
"WHERE YEAR(o.OrderDate) = {0} " +
"AND o.ShipCountry = {1} " +
"group by c.CustomerID";
object[] parameters = { year, country };
var orders = db.Database.SqlQuery<string>(nativeSQLQuery, parameters);
foreach (var order in orders)
{
Console.WriteLine(order);
}
}
