public static void FindSpecificSales(string region = null, string startDate = null, string endDate = null)
{
using (var dbContext = new NorthwindEntities())
{
var startDateParsed = DateTime.Parse(startDate);
var endDateParsed = DateTime.Parse(endDate);
var selectedSales =
from sale in dbContext.Orders
where
(sale.ShipRegion == region) &&
(sale.OrderDate > startDateParsed && sale.OrderDate < endDateParsed)
orderby sale.ShipCity, sale.OrderDate, sale.ShippedDate
select new
{
sale.OrderDate,
sale.ShippedDate,
City = sale.ShipCity
};
var counter = 0;
Console.WriteLine("Region - {0}", region ?? "No region");
Console.WriteLine("Start date - {0}", startDateParsed);
Console.WriteLine("End date - {0}", endDateParsed);
Console.WriteLine(new string('-', 50));
Console.WriteLine();
foreach (var sale in selectedSales)
{
counter++;
Console.WriteLine("{0}. City: {1} || Order date: {2} || Shipped date: {3}", counter, sale.City,
sale.OrderDate, sale.ShippedDate);
}
}
}
private static void Main()
{
IObjectContextAdapter context = new NorthwindEntities();
var northwindScript = context.ObjectContext.CreateDatabaseScript();
const string createNorthwindCloneDB = "USE master; " +
"CREATE DATABASE NorthwindTwin; " +
"SELECT name, size, size*1.0/128 AS [Size in MBs] " +
"FROM sys.master_files " +
"WHERE name = NorthwindTwin; ";
var dbConnection = new SqlConnection("Server=NIKOLAI\\SQLEXPRESS; " +
"Database=master; " +
"Integrated Security=true");
dbConnection.Open();
using (dbConnection)
{
var cmd = new SqlCommand(createNorthwindCloneDB, dbConnection);
cmd.ExecuteNonQuery();
var changeToNorthwind = "Use NorthwindTwin";
var changeDbCmd = new SqlCommand(changeToNorthwind, dbConnection);
changeDbCmd.ExecuteNonQuery();
var cloneDb = new SqlCommand(northwindScript, dbConnection);
cloneDb.ExecuteNonQuery();
}
}
private static void Main()
{
using (var firstDb = new NorthwindEntities())
{
using (var secondDb = new NorthwindEntities())
{
var firstCustomer =
(from c in firstDb.Customers
where c.CustomerID == "PARIS"
select c).First();
var secondCustomer =
(from c in secondDb.Customers
where c.CustomerID == "PARIS"
select c).First();
firstCustomer.CompanyName = "First Change with LINQ";
secondCustomer.ContactName = "Second Change with LINQ";
firstDb.SaveChanges();
secondDb.SaveChanges();
Console.WriteLine("Changes are made successfully!");
}
}
}
public static void AddCustomer(string customerId, string companyName, string contactName = null,
string contactTitle = null, string address = null, string city = null, string region = null,
string postalCode = null, string country = null, string phone = null, string fax = null)
{
using (var dbContext = new NorthwindEntities())
{
var customer = new Customers
{
CustomerID = customerId,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Address = address,
City = city,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax
};
dbContext.Customers.Add(customer);
dbContext.SaveChanges();
Console.WriteLine("Row is inserted");
}
}
public static void CustomersWithOrdersIn1997FromCanada()
{
using (var dbContext = new NorthwindEntities())
{
var selectedCustomers =
from order in dbContext.Orders
join customer in dbContext.Customers on order.CustomerID equals customer.CustomerID
where order.OrderDate.Value.Year == 1997 && order.ShipCountry == "Canada"
orderby customer.CompanyName, customer.ContactName, order.OrderDate, order.ShipCountry
select new
{
customer.CompanyName,
CustomerName = customer.ContactName,
order.OrderDate,
order.ShipCountry
};
var counter = 0;
foreach (var customer in selectedCustomers)
{
counter++;
Console.WriteLine(
"{0}. Company name: {1} || Customer name: {2} || Date: {3} || ShipCountry: {4}", counter,
customer.CompanyName, customer.CustomerName, customer.OrderDate, customer.ShipCountry);
Console.WriteLine();
}
}
}
private static void Main()
{
var context = new NorthwindEntities();
var employee = context.Employees.First();
var counter = 0;
foreach (var territory in employee.Territories)
{
counter++;
Console.WriteLine("{0}. Territory description: {1}", counter, territory.TerritoryDescription);
}
}
public static void DeleteCustomer(string customerId)
{
using (var dbContext = new NorthwindEntities())
{
try
{
var customer = dbContext
.Customers
.First(c => c.CustomerID == customerId);
dbContext.Customers.Remove(customer);
dbContext.SaveChanges();
Console.WriteLine("Row is deleted");
}
catch (InvalidOperationException e)
{
throw new InvalidOperationException("There is no customer with such Id!", e);
}
}
}
public static void SqlQueryCustomersWithOrdersIn1997FromCanada()
{
using (var dbContext = new NorthwindEntities())
{
var sqlQuery = ("SELECT c.CompanyName " + "FROM Orders o JOIN Customers c " +
"ON (o.CustomerID = c.CustomerID) " +
"WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada' ");
var result = dbContext.Database.SqlQuery<string>(sqlQuery);
var counter = 0;
foreach (var customer in result)
{
counter++;
Console.WriteLine("{0}. Company name: {1}", counter, customer);
Console.WriteLine();
}
}
}
public static void UpdateCustomer(string customerId, string companyName, string contactName = null,
string contactTitle = null, string address = null, string city = null, string region = null,
string postalCode = null, string country = null, string phone = null, string fax = null)
{
using (var dbContext = new NorthwindEntities())
{
var customer = dbContext.Customers.First(x => x.CustomerID == customerId);
customer.CompanyName = companyName ?? customer.CompanyName;
customer.ContactName = contactName ?? customer.ContactName;
customer.ContactTitle = contactTitle ?? customer.ContactTitle;
customer.Address = address ?? customer.Address;
customer.City = city ?? customer.City;
customer.Region = region ?? customer.Region;
customer.PostalCode = postalCode ?? customer.PostalCode;
customer.Country = country ?? customer.Country;
customer.Phone = phone ?? customer.Phone;
customer.Fax = fax ?? customer.Fax;
dbContext.SaveChanges();
Console.WriteLine("Row is updated");
}
}
