static void Main()
{
// Task 6
var dbContext = new NWEntities();
dbContext.Database.CreateIfNotExists();
}
static void Main(string[] args)
{
using (var context = new NWEntities())
{
ViewQuery(context).Where(vw => vw.Product == "Foo").ToList();
}
}
static void Main()
{
for (int i = 0; i < 5; i++)
{
var firstConection = new NWEntities();
var secondConection = new NWEntities();
Console.WriteLine($"Now i = {i}");
var firstCustomer = firstConection.Customers
.Where(x => x.ContactName == "Maria Anders")
.FirstOrDefault();
var secondCustomer = secondConection.Customers
.Where(x => x.ContactName == "Maria Anders")
.FirstOrDefault();
Console.WriteLine("First connection name: " + firstCustomer.CompanyName);
Console.WriteLine("Second connection name: " + secondCustomer.CompanyName);
firstCustomer.CompanyName = "Telerik 1";
secondCustomer.CompanyName = "Telerik 2";
secondConection.SaveChanges();
firstConection.SaveChanges();
var result = new NWEntities()
.Customers.Where(x => x.ContactName == "Maria Anders")
.FirstOrDefault();
Console.WriteLine("Final company name {0}", result.CompanyName);
Console.WriteLine();
}
}
static void Main(string[] args)
{
// task - 7 - https://msdn.microsoft.com/en-us/data/jj592904.aspx
for (int i = 0; i < 5; i++)
{
var firstConection = new NWEntities();
var secondConection = new NWEntities();
Console.WriteLine($"Now i = {i}");
// Original company name "Alfreds Futterkiste"
var firstCustomer = firstConection.Customers
.Where(x => x.ContactName == "Maria Anders")
.FirstOrDefault();
var secondCustomer = secondConection.Customers
.Where(x => x.ContactName == "Maria Anders")
.FirstOrDefault();
Console.WriteLine("Name from first connection: " + firstCustomer.CompanyName);
Console.WriteLine("Name from second connection: " + secondCustomer.CompanyName);
firstCustomer.CompanyName = "Tlerik 1";
secondCustomer.CompanyName = "Telerik 2";
secondConection.SaveChanges();
firstConection.SaveChanges();
var result = new NWEntities()
.Customers.Where(x => x.ContactName == "Maria Anders")
.FirstOrDefault();
Console.WriteLine("Final company name {0}", result.CompanyName);
Console.WriteLine();
}
}
// POST: api/Productos
public void Post([FromBody] Product product)
{
try
{
using (var db = new NWEntities())
{
var myProduct = db.Products.Where(x => x.ProductID == product.ProductID).FirstOrDefault();
if (myProduct != null)
{
db.Entry(myProduct).State = EntityState.Modified;
db.Entry(myProduct).CurrentValues.SetValues(product);
}
else
{
db.Products.Add(product);
}
db.SaveChanges();
}
}
catch (Exception ex)
{
throw ex;
}
}
// Task 3---------------------------------
private static void FindCustomersByOrdersYearAndCountry(NWEntities northwind, int year, string country)
{
northwind.Orders
.Where(o => o.OrderDate.Value.Year == year && o.ShipCountry == country)
.Select(ord => ord.Customer)
.GroupBy(c => c.CompanyName)
.ToList().ForEach(cust => Console.WriteLine("- " + cust.Key));
}
public static void ModifyContactName(string customerId, string contactName)
{
var northwind = new NWEntities();
var customer = GetCustomerById(northwind, customerId);
customer.ContactName = contactName;
northwind.SaveChanges();
}
// GET: api/Productos
public IEnumerable <Product> Get()
{
using (var db = new NWEntities())
{
var data = db.Products.OrderBy(x => x.ProductName).ToList();
return(data);
}
}
public static void DeleteCustomer(string customerId)
{
var northwind = new NWEntities();
var customer = GetCustomerById(northwind, customerId);
northwind.Customers.Remove(customer);
northwind.SaveChanges();
}
private static IQueryable <vwProducts_by_Categories> ViewQuery(NWEntities context)
{
return
(from p in context.Products
join c in context.Categories on p.CategoryID equals c.CategoryID
select new vwProducts_by_Categories {
Product = p.ProductName, Category = c.CategoryName
});
}
// actually 6
static void Main()
{
// Just set diferent connection string in app.config and it makes code first from
// allready exsisting models (made with database first before that)
var dbContext = new NWEntities();
dbContext.Database.CreateIfNotExists();
}
private static void FindSalesByRegionAndTimePeriod(
NWEntities northwind,
string region,
DateTime startDate,
DateTime endDate)
{
northwind.Orders
.Where(o => o.ShipRegion == region &&
o.OrderDate >= startDate &&
o.OrderDate <= endDate)
.Select(o => new { o.OrderID, o.ShipCity })
.ToList()
.ForEach(o => Console.WriteLine(string.Format("Order #{0} to {1}", o.OrderID, o.ShipCity)));
}
// GET: api/Categorias
public IEnumerable <Category> Get()
{
try
{
using (var db = new NWEntities())
{
var data = db.Categories.ToList();
return(data);
}
}
catch (Exception ex)
{
throw ex;
}
}
// GET: api/Productos/5
public Product Get(int id)
{
try
{
using (var db = new NWEntities())
{
var data = db.Products.Where(x => x.ProductID == id).Include("Category").FirstOrDefault();
return(data);
}
}
catch (Exception ex)
{
throw ex;
throw;
}
}
// Task 4 - -------------------------------------------
private static void FindCustomersByOrdersYearAndCountryWithNativeSql(NWEntities northwind, int year, string country)
{
string query = "SELECT c.CompanyName FROM Customers AS c " +
"JOIN Orders AS o " +
"ON c.CustomerId = o.CustomerId " +
"WHERE Country = '{0}' " +
"AND YEAR(OrderDate) = {1}" +
"GROUP BY c.CompanyName ";
object[] parameters = { country, year };
var customers = northwind.Database.SqlQuery <string>(string.Format(query, parameters));
foreach (var customer in customers)
{
Console.WriteLine("- " + customer);
}
}
public static void Main()
{
var northwind = new NWEntities();
CustomerModifier.AddCustomer("TELER", "Telerik", "Doncho Minkov", "Trainer",
"Al. Malinov 33", "Sofia", "SC", "1712", "Bulgaria", "02987654", "02345678");
CustomerModifier.ModifyCompanyName("TELER", "Telerik, Progress");
CustomerModifier.DeleteCustomer("TELER");
northwind.SaveChanges();
Console.WriteLine("Customers who shipped to Canada in 1997: ");
FindCustomersByOrdersYearAndCountry(northwind, 1997, "Canada");
FindCustomersByOrdersYearAndCountryWithNativeSql(northwind, 1997, "Canada");
Console.WriteLine("Orders shipped to Sao Paulo region between 18 and 19 years ago.");
FindSalesByRegionAndTimePeriod(northwind, "SP", DateTime.Now.AddYears(-19), DateTime.Now.AddYears(-18));
}
public static string AddCustomer(string customerId, string companyName, string contactName, string contactTitle,
string address, string city, string region, string postalCode, string country, string phone, string fax)
{
var northwind = new NWEntities();
var customer = new Customer()
{
CustomerID = customerId,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Address = address,
City = city,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax,
};
northwind.Customers.Add(customer);
northwind.SaveChanges();
return(customer.CustomerID);
}
public static Customer GetCustomerById(NWEntities northwind, string customerId)
{
var customer = northwind.Customers.FirstOrDefault(c => c.CustomerID == customerId);
return(customer);
}
