private static void SalesInREgionBetweenPeriod(string region, DateTime startDate, DateTime endDate)
{
using (NorthwindEntities connection = new NorthwindEntities())
{
string dateFormat = "{0:yyyy.MM.dd}";
string sqlQuery =
"SELECT o.EmployeeID, c.CompanyName, o.RequiredDate, o.ShipRegion " +
"FROM [Customers] c " +
"JOIN [Orders] o ON c.CustomerID = o.CustomerID " +
"WHERE o.RequiredDate BETWEEN '{1}' AND '{2}' " +
"AND o.ShipRegion LIKE '{0}'";
string theNewQuery =
string.Format(sqlQuery, region, string.Format(dateFormat, startDate), string.Format(dateFormat, endDate));
//// s parametri ne stava nekvi gurmeji ima...
IEnumerable<CustomerOrder> sales = connection.Database.SqlQuery<CustomerOrder>(theNewQuery);
foreach (var sale in sales)
{
Console.WriteLine(
"{0,-35}{1,-25}{2,-20}",
sale.CompanyName,
sale.RequiredDate.Value.ToShortDateString(),
sale.ShipRegion);
}
}
}
public static void Main()
{
using (NorthwindEntities connection = new NorthwindEntities())
{
///// EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE TEEEEEEEEE imat Dostaaaaaaaaaaaaaapppppp !!!!!!!!!!!!!!!!!!!!!!!!!!!!
/// Kakuv e smisala ot nov class !!!!!!!@?#?!@?#?!?!@?>#>!#>!@#!@#$?!#>!@#>!>$#>?!Q?>!>!
}
}
internal static void RemoveCustomer(string idOfTheCustomer)
{
using (dataConnection = new NorthwindEntities())
{
Customer customerToRemove = dataConnection.Customers.Where(cust => cust.CustomerID == idOfTheCustomer).FirstOrDefault();
dataConnection.Customers.Remove(customerToRemove);
int rowsChanged = dataConnection.SaveChanges();
string message = rowsChanged != 0 ? "Success" : "NothingChanged";
Console.WriteLine(message);
}
}
/// <summary>
/// Task 1. Using the Visual Studio Entity Framework designer create a DbContext for the Northwind database
/// </summary>
public static void Main()
{
using (NorthwindEntities dataBaseConnection = new NorthwindEntities())
{
//// Do queries
}
//// Test insert
DataAccessObject.InsertEmployee("Eaten apple", "Gosho pochivka", "Owner", "Amzon 89 avenu");
DataAccessObject.UpdateCustomer();
DataAccessObject.RemoveCustomer("EATEA");
}
public static void Main()
{
NorthwindEntities connection = new NorthwindEntities();
NorthwindEntities anotherConnection = new NorthwindEntities();
Customer anton = connection.Customers.Where(cust => cust.CustomerID == "ANTON").FirstOrDefault();
Customer antonio = anotherConnection.Customers.Where(cust => cust.CustomerID == "ANTON").FirstOrDefault();
anton.Region = "BG";
antonio.Region = "GR";
connection.SaveChanges();
anotherConnection.SaveChanges();
connection.Dispose();
anotherConnection.Dispose();
}
internal static void InsertEmployee(
string companyName,
string contactName,
string contactTitle = Empty,
string address = Empty,
string city = Empty,
string region = Empty,
string postalCode = Empty,
string country = Empty,
string phone = Empty,
string fax = Empty)
{
Customer newCustomer = new Customer()
{
CompanyName = companyName,
ContactName = contactName,
ContactTitle = (contactTitle != Empty ? contactTitle : null),
Address = (address != Empty ? address : null),
City = (city != Empty ? city : null),
Region = (region != Empty ? region : null),
PostalCode = (postalCode != Empty ? postalCode : null),
Country = (country != Empty ? country : null),
Phone = (phone != Empty ? phone : null),
Fax = (fax != Empty ? fax : null)
};
using (dataConnection = new NorthwindEntities())
{
IList<string> idsOfCustomers = dataConnection.Customers.Select(cust => cust.CustomerID).ToList();
string idOftheNewCustomer = GenrateID(companyName, idsOfCustomers);
newCustomer.CustomerID = idOftheNewCustomer;
try
{
dataConnection.Customers.Add(newCustomer);
dataConnection.SaveChanges();
}
catch (Exception ex)
{
var a = (ex as System.Data.Entity.Validation.DbEntityValidationException).EntityValidationErrors.ToList()[0].ValidationErrors.ToList()[0];
Console.WriteLine(a);
ShowMessage(ex);
}
}
}
internal static void UpdateCustomer()
{
using (dataConnection = new NorthwindEntities())
{
Customer customerToUpdate = dataConnection.Customers.Where(cust => cust.CustomerID == "EATEN").FirstOrDefault();
//// Test update
customerToUpdate.Fax = "090-123";
customerToUpdate.Phone = "2883812";
customerToUpdate.PostalCode = "0231";
int rowsChnaged = dataConnection.SaveChanges();
if (rowsChnaged == 0)
{
Console.WriteLine("Nothing changed");
}
else
{
Console.WriteLine("Success");
}
}
}
public static void Main()
{
const string FormatInformation = "║{0,-35}║{1,-20}║{2,-15}║";
using (NorthwindEntities connection = new NorthwindEntities())
{
string nativeSql =
"SELECT c.CompanyName, o.RequiredDate, o.ShipCountry " +
"FROM [Customers] c " +
"JOIN [Orders] o ON c.CustomerID = o.CustomerID " +
"WHERE o.RequiredDate BETWEEN '1997.01.01' AND '1997.12.31' " +
"AND o.ShipCountry LIKE 'Canada' " +
"ORDER BY o.RequiredDate";
var result = connection.Database.SqlQuery<CustomerJoinedOrder>(nativeSql);
Console.WriteLine(FormatInformation, "CompanyName", "Date", "Country");
foreach (var order in result)
{
Console.WriteLine(FormatInformation, order.CompanyName, order.RequiredDate.Value.ToShortDateString(), order.ShipCountry);
}
}
}
public static void Main()
{
using (NorthwindEntities connectionToDataBase = new NorthwindEntities())
{
var meshWithGrapes =
from customer in connectionToDataBase.Customers
join order in connectionToDataBase.Orders
on customer.CustomerID equals order.CustomerID
where order.ShipCountry == "Canada" &&
order.OrderDate >= new DateTime(1997, 1, 1) &&
order.OrderDate <= new DateTime(1997, 12, 31)
orderby order.RequiredDate
select new
{
Company = customer.CompanyName,
OrderDate = order.RequiredDate,
OrderCountry = order.ShipCountry
};
int maxLengthCompany = 0;
int maxLengthDate = 0;
int maxLengthCountry = 0;
meshWithGrapes
.ToList()
.ForEach(
x =>
{
maxLengthCompany = Math.Max(maxLengthCompany, x.Company.Length);
maxLengthDate = Math.Max(maxLengthDate, x.OrderDate.Value.ToShortDateString().Length);
maxLengthCountry = Math.Max(maxLengthCountry, x.OrderCountry.Length);
});
maxLengthCompany += 5;
maxLengthDate += 5;
maxLengthCountry += 5;
//// I heard that you love high quality code, so I just write some pretty code below
Console.WriteLine(
"┌{0}┬{1}┬{2}┐",
new string('─', maxLengthCompany),
new string('─', maxLengthDate),
new string('─', maxLengthCountry));
Console.WriteLine("│{0,-" + maxLengthCompany + "}│{1,-" + maxLengthDate + "}│{2,-" + maxLengthCountry + "}│", "Name", "Date", "Country");
Console.WriteLine(
"├{0}┼{1}┼{2}┤",
new string('─', maxLengthCompany),
new string('─', maxLengthDate),
new string('─', maxLengthCountry));
foreach (var customerOrder in meshWithGrapes)
{
Console.WriteLine(
"│{0,-" + maxLengthCompany + "}│{1,-" + maxLengthDate + "}│{2,-" + maxLengthCountry + "}│",
customerOrder.Company,
customerOrder.OrderDate.Value.ToShortDateString(),
customerOrder.OrderCountry);
Console.WriteLine(
"├{0}┼{1}┼{2}┤",
new string('─', maxLengthCompany),
new string('─', maxLengthDate),
new string('─', maxLengthCountry));
}
Console.WriteLine(
"└{0}┴{1}┴{2}┘",
new string('─', maxLengthCompany),
new string('─', maxLengthDate),
new string('─', maxLengthCountry));
}
}
