public async Task<Customer[]> GetAllCustomersAsync()
{
using (var ctx = new NorthwindEntities())
{
return await ctx.Customers.ToArrayAsync();
}
}
static void Main()
{
IObjectContextAdapter context = new NorthwindEntities();
string northwindScript = context.ObjectContext.CreateDatabaseScript();
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; ";
SqlConnection dbConnection = new SqlConnection("Server=NIKOLAI\\SQLEXPRESS; " +
"Database=master; " +
"Integrated Security=true");
dbConnection.Open();
using (dbConnection)
{
SqlCommand cmd = new SqlCommand(createNorthwindCloneDB, dbConnection);
cmd.ExecuteNonQuery();
string changeToNorthwind = "Use NorthwindTwin";
SqlCommand changeDBCmd = new SqlCommand(changeToNorthwind, dbConnection);
changeDBCmd.ExecuteNonQuery();
SqlCommand cloneDB = new SqlCommand(northwindScript, dbConnection);
cloneDB.ExecuteNonQuery();
}
}
public static void FindSpecificSales(string region = null, string startDate = null, string endDate = null)
{
using (NorthwindEntities dbContext = new NorthwindEntities())
{
DateTime startDateParsed = DateTime.Parse(startDate);
DateTime 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
{
OrderDate = sale.OrderDate,
ShippedDate = sale.ShippedDate,
City = sale.ShipCity
};
int 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);
}
}
}
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.");
}
}
public async Task<Customer> GetCustomerAsync(int id)
{
using (var ctx = new NorthwindEntities())
{
return await ctx.Customers.FindAsync(id);
}
}
protected void Page_Load(object sender, EventArgs e)
{
NorthwindEntities objectContext = new NorthwindEntities();
var result = objectContext.SelectCategory(65985);
GridView1.DataSource = result;
GridView1.DataBind();
}
public static void InsertCustomer(string customerId, string companyName, string contactName, string contactTitle,
string adress, string city, string region, string postalCode, string country, string phone, string fax)
{
if (customerId.Length == 0 || customerId.Length>5)
{
throw new ArgumentException("The lenght of CustomerId must be between 0 and 5 characters!");
}
using (NorthwindEntities nwDb = new NorthwindEntities())
{
Customer cusomer = new Customer
{
CustomerID = customerId,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Address = adress,
City = city,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax
};
nwDb.Customers.Add(cusomer);
nwDb.SaveChanges();
}
}
static void Main()
{
//NorthwindEntities dbContext = new NorthwindEntities();
using (var dbContext = new NorthwindEntities())
{
}
}
public object[] ExecuteQueryForObjects(XElement xml)
{
NorthwindEntities db = new NorthwindEntities();
IQueryable queryAfter = db.DeserializeQuery(xml);
return queryAfter.Cast<object>().ToArray();
throw new NotImplementedException();
}
static void PlaceNewOrder(string customerId,int employeeId,Order_Detail[] details)
{
using (NorthwindEntities nwDb = new NorthwindEntities())
{
using (TransactionScope scope = new TransactionScope())
{
Order order = new Order
{
CustomerID = customerId,
EmployeeID = employeeId,
ShipVia = 1,
ShipName = "UnknownShip",
ShipAddress = "UnknownAddress",
ShipCity = "Unknown",
ShipRegion = "Unknown",
ShipPostalCode = "121311",
ShipCountry = "Unknown",
Order_Details = details
};
nwDb.Orders.Add(order);
nwDb.SaveChanges();
scope.Complete();
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
var db = new NorthwindEntities();
var id = int.Parse(Request.QueryString["id"]);
this.EmployeeDetailView.DataSource = db.Employees.Where(em => em.EmployeeID == id).ToList();
Page.DataBind();
}
public static void AddCustomer(NorthwindEntities dbNorthwnd, string name)
{
var customer = new Customer()
{
CustomerID = "AAAA" + name,
CompanyName = "YYYY" + name,
ContactName = "Pesho Peshev",
ContactTitle = "Shef",
Address = "aaaaaaaaaa",
City = "Sofia",
PostalCode = "1330",
Country = "Bulgaria",
Phone = "0000000",
Fax = "0000000"
};
dbNorthwnd.Customers.Add(customer);
try
{
dbNorthwnd.SaveChanges();
}
catch (Exception ex)
{
}
}
public static void DeleteCustomer(NorthwindEntities dbNorthwnd)
{
var customer = dbNorthwnd.Customers.Where(z => z.CustomerID == "AAAA1").First();
dbNorthwnd.Customers.Remove(customer);
var affectedRows = dbNorthwnd.SaveChanges();
Console.WriteLine("({0} row(s) affected)", affectedRows);
}
static void Main(string[] args)
{
EFTracingProviderConfiguration.RegisterProvider();
EFCachingProviderConfiguration.RegisterProvider();
//ICache cache = new InMemoryCache();
ICache cache = MemcachedCache.CreateMemcachedCache();
CachingPolicy cachingPolicy = CachingPolicy.CacheAll;
// log SQL from all connections to the console
EFTracingProviderConfiguration.LogToConsole = true;
for (int i = 0; i < 3; i++)
{
Console.WriteLine();
Console.WriteLine("*** Pass #{0}...", i);
Console.WriteLine();
using (var nDb = new NorthwindEntities())
{
nDb.Cache = cache;
nDb.CachingPolicy = cachingPolicy;
var emp = nDb.Customers.First(x => x.CustomerID == "ALFKI");
Console.WriteLine(nDb.Customers.First(x => x.CustomerID == "ALFKI").ContactName);
Console.WriteLine(nDb.Customers.First(x => x.CustomerID == "ALFKI").ContactName);
Console.WriteLine(nDb.Customers.First().ContactName);
Console.WriteLine(nDb.Customers.First().ContactName);
Console.WriteLine(nDb.Customers.AsNoTracking().First(x => x.CustomerID == "ALFKI").Orders.Count());
}
}
}
static void Main(string[] args)
{
Customer newCustmer = new Customer();
newCustmer.CustomerID = "KULO";
newCustmer.CompanyName = "Mala";
newCustmer.ContactName = "Misoto Kulano";
newCustmer.ContactTitle = "Owner";
newCustmer.Address = "Amela str 23";
newCustmer.City = "Pelon";
newCustmer.PostalCode = "1231";
newCustmer.Country = "France";
newCustmer.Phone = "3443-4323-432";
newCustmer.Fax = "3245-243";
using (var otherDataBase = new NorthwindEntities())
{
using (var dataBase = new NorthwindEntities())
{
otherDataBase.Customers.Add(newCustmer);
otherDataBase.SaveChanges();
//Customer customer = dataBase.Customers.First(x => x.CustomerID == "KULO");
dataBase.Customers.Attach(newCustmer);
dataBase.Customers.Remove(newCustmer);
dataBase.SaveChanges();
}
}
}
public static void InsertCustomer(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 (NorthwindEntities northwindDBContext = new NorthwindEntities())
{
Customer 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
};
northwindDBContext.Customers.Add(customer);
northwindDBContext.SaveChanges();
Console.WriteLine("Row is inserted.");
}
}
// First way
static void Main()
{
using (NorthwindEntities firstDB = new NorthwindEntities())
{
using (NorthwindEntities 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!");
//SecondWayForChangeRecords();
}
}
}
public static void Main()
{
using (var dbNorthwnd = new NorthwindEntities())
{
// Task 2. Create a DAO class with static methods which provide functionality for inserting, modifying and deleting customers.
// START JUST ONE TIME - otherwise throw exception, because it creates customers with the same id number.
// ADO.AddCustomer(dbNorthwnd, "1");
// ADO.AddCustomer(dbNorthwnd, "2");
// ADO.ModifyCustomer(dbNorthwnd);
// ADO.DeleteCustomer(dbNorthwnd);
// Task 3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
FindCustomersWithOrdersFrom1997ToCanada(dbNorthwnd);
Console.WriteLine();
// Task 4. Implement previous by using native SQL query and executing it through the DbContext.
Console.WriteLine("Task 4");
FindCustomersWithOrdersFrom1997ToCanadaWithNativeSQL(dbNorthwnd);
Console.WriteLine();
// Task 5. Write a method that finds all the sales by specified region and period (start / end dates).
Console.WriteLine("Task 5");
FindSalesByRegionAndPeriod(dbNorthwnd, "Canada", new DateTime(1996, 10, 10), new DateTime(1997, 10, 10));
}
}
protected void Page_Load(object sender, EventArgs e)
{
NorthwindEntities context = new NorthwindEntities();
#region forma 1
var datos = from entidadCustomers in context.Customers
where entidadCustomers.Orders.Count() > 10
select new
{
customerId = entidadCustomers.CustomerID,
companyName = entidadCustomers.CompanyName,
orders = entidadCustomers.Orders.Count()
};
GridView1.DataSource = datos;
GridView1.DataBind();
#endregion forma 1
#region forma 2
var datos2 = context.Customers.Where(x => x.Orders.Count > 10);
GridView2.DataSource = datos2;
GridView2.DataBind();
#endregion forma 2
}
static void DeleteProduct(int productId)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
Product product = GetProductById(northwindEntities, productId);
northwindEntities.Products.Remove(product);
northwindEntities.SaveChanges();
}
static void ModifyProductName(int productId, string newName)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
Product product = GetProductById(northwindEntities, productId);
product.ProductName = newName;
northwindEntities.SaveChanges();
}
protected void Page_Load(object sender, EventArgs e)
{
var db = new NorthwindEntities();
var employees = db.Employees.Select(empl => new { Id = empl.EmployeeID, Name = empl.FirstName + " " + empl.LastName }).ToList();
this.EmployeesGridView.DataSource = employees;
this.EmployeesGridView.DataKeyNames = new string[] { "Id" };
Page.DataBind();
}
public static void ExecuteDelete(NorthwindEntities dbConnection)
{
var customersToDelete = dbConnection.Customers
.Where(c => c.PostalCode == "666")
.ToList();
DAO.Delete(dbConnection, customersToDelete);
}
private static void PrintRegionDescription(int regionId)
{
using (var context = new NorthwindEntities())
{
Console.WriteLine("Current description = {0}",
context.Regions.First(r => r.RegionID == regionId).RegionDescription);
}
}
static void Main(string[] args)
{
NorthwindEntities dbContext = new NorthwindEntities();
string sqlGenerator = ((IObjectContextAdapter)dbContext).ObjectContext.CreateDatabaseScript();
dbContext.Database.ExecuteSqlCommand("CREATE DATABASE NorthwindTwin");
dbContext.Database.ExecuteSqlCommand("USE NorthwindTwin " + sqlGenerator.ToString());
}
static Product GetProduct(int id)
{
using (NorthwindEntities northwindEntities = new NorthwindEntities())
{
Product product = northwindEntities.Products.First(p => p.ProductID == id);
return product;
}
}
static int SelectCustomersCount()
{
NorthwindEntities northwindEntities = new NorthwindEntities();
string nativeSqlQuery = "SELECT count(*) FROM dbo.Customers";
var queryResult = northwindEntities.Database.SqlQuery<int>(nativeSqlQuery);
int customersCount = queryResult.FirstOrDefault();
return customersCount;
}
static void Main()
{
NorthwindEntities northwindEntities = new NorthwindEntities();
JoinCustomerWithSupplier(northwindEntities);
JoinCustomerWithSupplierExtentedMethods(northwindEntities);
GroupCustomersByCountry(northwindEntities);
GroupCustomersByCountryExtentedMethods(northwindEntities);
}
private static Region GetRegionById(NorthwindEntities context, int regionId)
{
return context.Regions.SqlQuery(
string.Format(@"SELECT TOP 1 RegionID,
RegionDescription
FROM Region WITH (UPDLOCK)
WHERE RegionID = {0}", regionId)).Single();
}
protected void Page_Load(object sender, EventArgs e)
{
//NorthwindEntities objectContext = new NorthwindEntities();
////inner join
//var result = from cat in objectContext.Categories
// join prod in objectContext.Products
// on cat.CategoryID equals prod.CategoryID
// group cat by cat.CategoryID into groupedCategories
// select new { CategoryID = groupedCategories.Key };
//GridView1.DataSource = result;
//GridView1.DataBind();
////outer join
//var result1 = from cat in objectContext.Categories
// join prod in objectContext.Products
// on cat.CategoryID equals prod.CategoryID
// into groupedCategories
// select new { CategoryID = cat.CategoryID };
//GridView2.DataSource = result1;
//GridView2.DataBind();
//var result2 = objectContext.Categories.Where((item) => (item.CategoryID == 287389)).Select((item) => (item)).First();
//result2.CategoryName = "mumbai";
//objectContext.Categories.AddObject( new Category() { CategoryID = 876221, CategoryName = "India" });
//objectContext.SaveChanges();
//var result3 = objectContext.Categories.First<Category>((cat) => (cat.CategoryID == 65985));
//result3.CategoryName = "london";
//objectContext.SaveChanges();
//var result4 = objectContext.Categories.First<Category>((cat) => (cat.CategoryID == 876221));
//objectContext.DeleteObject(result4);
//objectContext.SaveChanges();
//var result5 = objectContext.Categories.Where((cat) => (cat.CategoryID == 287389)).First<Category>();
//Product prod1 = new Product() { CategoryID = result5.CategoryID, ProductID = 72561, ProductName = "film", UnitPrice = 56, UnitsInStock = 132, Category = result5 };
//objectContext.Products.AddObject(prod1);
//objectContext.SaveChanges();
NorthwindEntities end1 = new NorthwindEntities();
var result6 = from item in end1.Products
select item;
GridView1.DataSource = result6;
GridView1.DataBind();
var result7 = from cat in end1.Categories
join prod in end1.Products
on cat.CategoryID equals prod.CategoryID
select new { CategoryName = cat.CategoryName, ProductName = prod.ProductName };
GridView1.DataSource = result7;
GridView1.DataBind();
var result8 = end1.Categories.Where<Category>((cat) => (cat.CategoryID == 287389)).First<Category>();
result8.CategoryName = "paris";
end1.SaveChanges();
}
public One_OneController(NorthwindEntities context)
{
_context = context;
}
public void Setup()
{
var context = new NorthwindEntities();
_controller = new CustomersController(context);
}
public CategoriaDAO(NorthwindEntities cnn) : base(cnn)
{
}
public CategoryController()
{
_northwindService = new NorthwindEntities();
}
public CustomersRepository()
{
_DB = new NorthwindEntities();
}
public CategoriesController(NorthwindEntities context)
{
_context = context;
}
internal static void FindAllSalesByRegionForGivenPeriod(string region, DateTime startDate, DateTime EndDate, NorthwindEntities dbContext)
{
var orders = dbContext.Orders
.Where(o => o.ShipRegion != null &&
o.ShipRegion == region &&
o.OrderDate >= startDate &&
o.OrderDate <= EndDate)
.OrderBy(o => o.ShippedDate)
.ToList();
Console.WriteLine($"Orders between, Start: {startDate.ToShortDateString()}, End: {EndDate.ToShortDateString()}, Region: {region}");
foreach (var order in orders)
{
Console.WriteLine($"Ship date {order.OrderDate} Region: {order.ShipRegion} Company: {order.ShipName}");
}
}
public static List <Customer> GetCustomers()
{
NorthwindEntities dataContext = new NorthwindEntities();
return(dataContext.Customers.ToList());
}
public HomeController()
{
_dbContext = new NorthwindEntities();
}
public ProductController()
{
DB = new NorthwindEntities();
}
public KategoriRepository(NorthwindEntities _db)
{
db = _db;
}
internal static void CustomersWithOrdersFromYearToCountryWithSQL(int year, string country, NorthwindEntities dbContext)
{
var queryTemplate = @"SELECT *
FROM Customers c
JOIN Orders o
ON o.CustomerID = c.CustomerID
WHERE o.ShipCountry = '{1}' AND YEAR(o.OrderDate) = {0}
ORDER BY c.CompanyName";
var query = string.Format(queryTemplate, year, country);
var customers = dbContext.Customers.SqlQuery(query).Distinct().ToList();
Console.WriteLine($"Customers with NativeSQL, OrderYear: {year}, Country: {country}");
foreach (var customer in customers)
{
Console.WriteLine($"Company: {customer.CompanyName}, Contact Name: {customer.ContactName}, Country: {customer.Country} ");
}
}
static void Main(string[] args)
{
string resp;
do
{
Console.Clear();
Console.WriteLine("Escoja una opcion:");
Console.WriteLine("1-Mantenimiento de Productos");
Console.WriteLine("2-Mantenimiento de Territorios");
Console.WriteLine("3-Mantenimiento de Categorias");
Console.WriteLine("4-Creacion de Factura");
Console.WriteLine("5-Cargar informacion de cliente");
Console.WriteLine("6-Exportar Factura");
Console.WriteLine("7-Salir");
resp = Console.ReadLine();
switch (resp)
{
case "1":
Console.Clear();
Product product = new Product();
ProductData pd = new ProductData();
do
{
Console.WriteLine("Escoja una opcion para el mantenimiento de producto:");
Console.WriteLine("1-Enlistar");
Console.WriteLine("2-Agregar");
Console.WriteLine("3-Actualizar");
Console.WriteLine("4-Eliminar");
Console.WriteLine("5-Salir");
resp = Console.ReadLine();
switch (resp)
{
case "1":
Console.Write("ID".PadRight(3));
Console.Write("Nombre".PadRight(40));
Console.Write("Categoria".PadRight(16));
Console.Write("Cantidad por Unidad".PadRight(21));
Console.Write("Precio por Unidad".PadRight(18));
Console.WriteLine("¿Descontinuado?".PadRight(15));
NorthwindEntities np = new NorthwindEntities();
pd.ObtenerProductos(product);
foreach (Product p in pd.LProducto)
{
var category = np.Categories.Where(a => a.CategoryID == p.CategoryID).Select(x => x).FirstOrDefault();
Console.Write(Convert.ToString(p.ProductID).PadRight(3));
Console.Write(p.ProductName.PadRight(40));
Console.Write(category.CategoryName.PadRight(16));
Console.Write(Convert.ToString(p.QuantityPerUnit).PadRight(21));
Console.Write(Convert.ToString(p.UnitPrice).PadRight(18));
Console.WriteLine(Convert.ToString(p.Discontinued).PadRight(15));
}
Console.ReadLine();
break;
case "2":
pd.Insertar(product);
break;
case "3":
pd.Actualizar(product);
break;
case "4":
pd.Eliminar(product);
break;
case "5":
break;
default:
Console.WriteLine("La opcion no es valida");
Console.ReadLine();
break;
}
} while (resp != "5");
break;
case "2":
Console.Clear();
Territory territory = new Territory();
TerritoryData td = new TerritoryData();
do
{
Console.WriteLine("Escoja una opcion para el mantenimiento de territorio:");
Console.WriteLine("1-Enlistar");
Console.WriteLine("2-Agregar");
Console.WriteLine("3-Actualizar");
Console.WriteLine("4-Eliminar");
Console.WriteLine("5-Salir");
resp = Console.ReadLine();
switch (resp)
{
case "1":
Console.Write("ID".PadRight(6));
Console.Write("Descripcion".PadRight(50));
Console.WriteLine("Region".PadRight(11));
td.ObtenerTerritorios(territory);
NorthwindEntities nr = new NorthwindEntities();
foreach (Territory t in td.LTerritorio)
{
var region = nr.Regions.Where(a => a.RegionID == t.RegionID).Select(x => x).FirstOrDefault();
Console.Write(Convert.ToString(t.TerritoryID).PadRight(6));
Console.Write(t.TerritoryDescription.PadRight(50));
Console.WriteLine(region.RegionDescription.PadRight(11));
}
Console.ReadLine();
break;
case "2":
td.Insertar(territory);
break;
case "3":
td.Actualizar(territory);
break;
case "4":
td.Eliminar(territory);
break;
case "5":
break;
default:
Console.WriteLine("La opcion no es valida");
break;
}
} while (resp != "5");
break;
case "3":
Console.Clear();
Category categoria = new Category();
CategoriaData cd = new CategoriaData();
do
{
Console.WriteLine("Escoja una opcion para el mantenimiento de categorias:");
Console.WriteLine("1-Enlistar");
Console.WriteLine("2-Agregar");
Console.WriteLine("3-Actualizar");
Console.WriteLine("4-Eliminar");
Console.WriteLine("5-Salir");
resp = Console.ReadLine();
switch (resp)
{
case "1":
Console.Write("ID".PadRight(3));
Console.Write("Nombre".PadRight(16));
Console.WriteLine("Descripcion".PadRight(60));
cd.ObtenerCategorias(categoria);
foreach (Category c in cd.LCategoria)
{
Console.Write(Convert.ToString(c.CategoryID).PadRight(3));
Console.Write(c.CategoryName.PadRight(16));
Console.WriteLine(c.Description.PadRight(60));
}
Console.ReadLine();
break;
case "2":
cd.Insertar(categoria);
break;
case "3":
cd.Actualizar(categoria);
break;
case "4":
cd.Eliminar(categoria);
break;
case "5":
break;
default:
Console.WriteLine("La opcion no es valida");
break;
}
} while (resp != "5");
break;
case "4":
Order order = new Order();
FactData fd = new FactData();
fd.Insertar(order, resp);
break;
case "5":
List <Customer> LCustomer = new List <Customer>();
NorthwindEntities n = new NorthwindEntities();
LCustomer = n.Customers.ToList();
string ruta = Properties.Settings.Default.RutaArchivoIC;
StreamReader reader = new StreamReader(ruta);
string contenido = null;
string[] contenidoTemp = null;
contenido = reader.ReadLine();
try
{
if (contenido != null)
{
bool comp = false;
contenidoTemp = contenido.Split(',');
foreach (Customer c in LCustomer)
{
if (contenidoTemp[0].PadRight(5) == c.CustomerID)
{
try
{
var resultado = n.Customers.Where(a => a.CustomerID == c.CustomerID).Select(x => x).FirstOrDefault();
resultado.CompanyName = contenidoTemp[1];
comp = true;
n.SaveChanges();
Console.WriteLine("El cliente fue actualizado");
Console.ReadLine();
}
catch
{
Console.WriteLine("No se pudo actualizar el cliente");
Console.ReadLine();
}
break;
}
}
if (comp == false)
{
try
{
n.Customers.Add(new Customer {
CustomerID = contenidoTemp[0], CompanyName = contenidoTemp[1]
});
n.SaveChanges();
Console.WriteLine("El cliente fue agregado");
Console.ReadLine();
}
catch
{
Console.WriteLine("No se pudo agregar el cliente");
Console.ReadLine();
}
}
}
}
catch (Exception)
{
Console.WriteLine("No se pudo ejecutar la accion");
Console.ReadLine();
}
reader.Close();
break;
case "6":
int orderID;
StreamWriter fct = new StreamWriter(Properties.Settings.Default.RutaArchivoF);
List <Order_Detail> LDetalle = new List <Order_Detail>();
decimal Total = 0;
using (NorthwindEntities nf = new NorthwindEntities())
{
Console.WriteLine("Ingrese el codigo de la factura que desee exportar:");
orderID = Convert.ToInt32(Console.ReadLine());
var fact = nf.Orders.Where(a => a.OrderID == orderID).Select(x => x).FirstOrDefault();
var cliente = nf.Customers.Where(a => a.CustomerID == fact.CustomerID).Select(x => x).FirstOrDefault();
decimal Cargo = 0;
fct.WriteLine("Factura No." + fact.OrderID);
fct.WriteLine("Fecha de factura: " + fact.OrderDate);
fct.WriteLine("Nombre Cliente:" + cliente.CompanyName);
LDetalle = nf.Order_Details.ToList();
fct.Write("Producto".PadRight(41));
fct.Write("Precio".PadRight(10));
fct.Write("Cantidad".PadRight(10));
fct.Write("Descuento".PadRight(10));
fct.WriteLine("Cargo".PadRight(10));
foreach (Order_Detail od in LDetalle)
{
if (od.OrderID == fact.OrderID)
{
var producto = nf.Products.Where(a => a.ProductID == od.ProductID).Select(x => x).FirstOrDefault();
fct.Write(producto.ProductName.PadRight(41));
fct.Write(Convert.ToString(od.UnitPrice).PadRight(10));
fct.Write(Convert.ToString(od.Quantity).PadRight(10));
fct.Write(Convert.ToString(od.Discount).PadRight(10));
Cargo = (od.UnitPrice * od.Quantity) - ((od.UnitPrice * od.Quantity) * Convert.ToDecimal(od.Discount));
fct.WriteLine(Convert.ToString(Cargo).PadRight(10));
Total = Total + Cargo;
}
}
fct.WriteLine("Total: " + Total);
}
fct.Close();
Console.WriteLine("La factura ha sido exportada");
Console.ReadLine();
break;
case "7":
break;
default:
Console.WriteLine("La opcion no es valida");
Console.ReadLine();
break;
}
} while (resp != "7");
Console.WriteLine("Presione cualquier tecla para salir");
Console.ReadKey();
}
public EmployeeRepository(NorthwindEntities context) : base(context)
{
}
public ProductRepository(NorthwindEntities northwindEntityDataModel)
{
_northwindEntityDataModel = northwindEntityDataModel;
}
private IEnumerable <Customer> GetData()
{
NorthwindEntities context = new NorthwindEntities();
return(from c in context.Customers.Take(10) select c);
}
public static Customer GetCustomerById(NorthwindEntities northwindEntities, string customerID)
{
return(northwindEntities.Customers.FirstOrDefault(c => c.CustomerID == customerID));
}
public CategoryController()
{
DB = new NorthwindEntities();
}
public AnaForm()
{
InitializeComponent();
db = new NorthwindEntities();
}
public IQueryable <Category> List()
{
NorthwindEntities ent = new NorthwindEntities();
return(ent.CategorySet);
}
public void Search(string tableName, string query)
{
using (var context = new NorthwindEntities())
{
switch (tableName)
{
case "Categories":
{
var compiler = SearchQueryCompilerBuilder.Instance.CreateDefaultCompilerForEntity <Category>();
DataSource = context.Categories.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Category>();
break;
}
case "CustomerDemographics":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <CustomerDemographic>();
DataSource = context.CustomerDemographics.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <CustomerDemographic>();
break;
}
case "Customers":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <Customer>();
DataSource = context.Customers.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Customer>();
break;
}
case "Employees":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <Employee>();
DataSource = context.Employees.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Employee>();
break;
}
case "Order Details":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <Order_Detail>();
DataSource = context.Order_Details.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Order_Detail>();
break;
}
case "Orders":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <Order>();
DataSource = context.Orders.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Order>();
break;
}
case "Products":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <Product>();
DataSource = context.Products.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Product>();
break;
}
case "Regions":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <Region>();
DataSource = context.Regions.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Region>();
break;
}
case "Shippers":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <Shipper>();
DataSource = context.Shippers.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Shipper>();
break;
}
case "Suppliers":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <Supplier>();
DataSource = context.Suppliers.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Supplier>();
break;
}
case "Territories":
{
var compiler = SearchQueryCompilerFactory.CreateDefaultCompilerForEntity <Territory>();
DataSource = context.Territories.Where(compiler.Compile(query)).ToList();
CompilerSetting = SearchQueryCompilerFactory.GetDefaultCompilerSettingForEntity <Territory>();
break;
}
}
}
}
private void Form1_Load(object sender, EventArgs e)
{
NorthwindEntities db = new NorthwindEntities();
#region Sorgu 1
//var query = (
// from c in db.Categories
// join p in db.Products on c.CategoryID equals p.CategoryID
// join od in db.Order_Details on p.ProductID equals od.ProductID
// join o in db.Orders on od.OrderID equals o.OrderID
// where c.CategoryName == "Condiments"
// select new
// {
// p.UnitsInStock,
// o.OrderDate
// }).Distinct().ToList();
//dgvTest.DataSource = query;
#endregion
#region Sorgu 2
//var query = (
// from p in db.Products
// join od in db.Order_Details on p.ProductID equals od.ProductID
// join o in db.Orders on od.OrderID equals o.OrderID
// join c in db.Customers on o.CustomerID equals c.CustomerID
// where p.ProductName == "Chai" || p.ProductName == "Tofu"
// && o.OrderDate < new DateTime(1997, 07, 01)
// select new
// {
// p.ProductName,
// p.UnitsInStock,
// c.City
// }).ToList();
//dgvTest.DataSource = query;
#endregion
#region Sorgu 3
//var query = (
// from od in db.Order_Details
// join o in db.Orders on od.OrderID equals o.OrderID
// join c in db.Customers on o.CustomerID equals c.CustomerID
// join emp in db.Employees on o.EmployeeID equals emp.EmployeeID
// from et in emp.Territories
// join t in db.Territories on et.TerritoryID equals t.TerritoryID
// join r in db.Regions on t.RegionID equals r.RegionID
// select new
// {
// c.ContactName,
// c.Phone
// }).Distinct();
//dgvTest.DataSource = query.ToList();
#endregion
#region Sorgu 4
//var query = (
// from p in db.Products
// join s in db.Suppliers on p.SupplierID equals s.SupplierID
// join od in db.Order_Details on p.ProductID equals od.ProductID
// join o in db.Orders on od.OrderID equals o.OrderID
// join sh in db.Shippers on o.ShipVia equals sh.ShipperID
// where sh.CompanyName == "United Package"
// select new
// {
// p.ProductID,
// p.ProductName,
// p.Order_Details,
// p.QuantityPerUnit,
// p.ReorderLevel,
// p.Supplier,
// p.SupplierID,
// p.UnitsInStock,
// p.UnitsOnOrder,
// sh.CompanyName
// }).ToList();
//dgvTest.DataSource = query;
#endregion
#region Sorgu 5
//var query = (
// from e1 in db.Employees
// join e2 in db.Employees on e1.EmployeeID equals e2.ReportsTo
// join o in db.Orders on e1.EmployeeID equals o.EmployeeID
// join sh in db.Shippers on o.ShipVia equals sh.ShipperID
// join c in db.Customers on o.CustomerID equals c.CustomerID
// where o.ShipCountry == "Venezuela"
// select new
// {
// CalisanAdi = e1.FirstName,
// Amiri = e2.ReportsTo,
// Kargocu = sh.CompanyName,
// Musteri = c.CompanyName,
// KargocuTelefon = sh.Phone
// }).ToList();
//dgvTest.DataSource = query;
#endregion
#region Sorgu 6
//var query = (
// from p in db.Products
// join od in db.Order_Details on p.ProductID equals od.ProductID
// join o in db.Orders on od.OrderID equals o.OrderID
// join c in db.Customers on o.CustomerID equals c.CustomerID
// join emp in db.Employees on o.EmployeeID equals emp.EmployeeID
// where emp.FirstName == "Nancy" && c.CustomerID == "ALFKI"
// select new
// {
// Stok = p.UnitsInStock
// }).ToList();
//dgvTest.DataSource = query;
#endregion
}
private void frmSamples_Load(object sender, EventArgs e)
{
Context = new NorthwindEntities();
}
internal static void FindAllWithOrdersByShippedYearAndShipCountry(int shippedYear, string shipCountry, NorthwindEntities dbContext)
{
string shipCountryLowered = shipCountry.ToLower();
var customers = dbContext.Customers
.Where(c => c.Orders.Any(o => o.ShipCountry.ToLower() == shipCountryLowered && o.ShippedDate.Value.Year == shippedYear))
.Select(c => new { c.CompanyName, c.ContactName, c.Country })
.OrderBy(c => c.CompanyName);
Console.WriteLine($"Customers with Linq, OrderYear: {shippedYear}, Country: {shipCountry}");
foreach (var customer in customers)
{
Console.WriteLine($"Company: {customer.CompanyName}, Contact Name: {customer.ContactName}, Country: {customer.Country} ");
}
}
public static NorthwindEntities GetContext()
{
var dbContext = new NorthwindEntities();
return(dbContext);
}
public void TestInsertNewCustomer()
{
using (NorthwindEntities dbContext = new NorthwindEntities())
{
DataAccess.Initialize(dbContext);
DataAccess.InsertNewCustomer(
"TELRK",
"Telerik Corp.",
"Svetozar Georgiev",
"Mr.",
"33 Alexander Malinov Blvd.",
"Sofia",
"Sofia",
"1729",
"Bulgaria",
"+359 2 809 98 62",
"+359 2 809 98 62");
Customer customer = DataAccess.GetCustomerByID("TELRK");
Assert.IsTrue(customer != null);
Assert.AreEqual("Telerik Corp.", customer.CompanyName);
Assert.AreEqual("Svetozar Georgiev", customer.ContactName);
Assert.AreEqual("Mr.", customer.ContactTitle);
Assert.AreEqual("33 Alexander Malinov Blvd.", customer.Address);
Assert.AreEqual("Sofia", customer.City);
Assert.AreEqual("Sofia", customer.Region);
Assert.AreEqual("1729", customer.PostalCode);
Assert.AreEqual("Bulgaria", customer.Country);
Assert.AreEqual("+359 2 809 98 62", customer.Phone);
Assert.AreEqual("+359 2 809 98 62", customer.Fax);
DataAccess.UpdateCustomerByID(
"TELRK",
"Telerik Corp.",
"Vassil Terziev",
"Mr.",
"33 Alexander Malinov Blvd.",
"Sofia",
"Sofia",
"1729",
"Bulgaria",
"+359 2 809 98 62",
"+359 2 809 98 62");
customer = DataAccess.GetCustomerByID("TELRK");
Assert.IsTrue(customer != null);
Assert.AreEqual("Telerik Corp.", customer.CompanyName);
Assert.AreEqual("Vassil Terziev", customer.ContactName);
Assert.AreEqual("Mr.", customer.ContactTitle);
Assert.AreEqual("33 Alexander Malinov Blvd.", customer.Address);
Assert.AreEqual("Sofia", customer.City);
Assert.AreEqual("Sofia", customer.Region);
Assert.AreEqual("1729", customer.PostalCode);
Assert.AreEqual("Bulgaria", customer.Country);
Assert.AreEqual("+359 2 809 98 62", customer.Phone);
Assert.AreEqual("+359 2 809 98 62", customer.Fax);
DataAccess.RemoveCustomerByID("TELRK");
customer = DataAccess.GetCustomerByID("TELRK");
Assert.IsTrue(customer == null);
}
}
public IActionResult Index(string searchPhrase, string sortOrder)
{
ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "firstname" : "";
ViewBag.NameSortParm = sortOrder == "firstname" ? "firstname_desc" : "firstname";
ViewBag.LastNameSortParm = sortOrder == "lastname" ? "lastname_desc" : "lastname";
ViewBag.TitleSortParm = sortOrder == "title" ? "title_desc" : "title";
ViewBag.CitySortParm = sortOrder == "city" ? "city_desc" : "city";
ViewBag.CountrySortParm = sortOrder == "country" ? "country_desc" : "country";
ViewBag.AddressSortParm = sortOrder == "address" ? "address_desc" : "address";
ViewBag.AddressSortParm = sortOrder == "address" ? "address_desc" : "address";
ViewBag.BirthSortParm = sortOrder == "birth" ? "birth_desc" : "birth";
ViewBag.HireSortParm = sortOrder == "hire" ? "hire_desc" : "hire";
NorthwindEntities context = new NorthwindEntities(new Uri("https://services.odata.org/V4/Northwind/Northwind.svc/"));
IQueryable <Employee> employees = context.Employees;
switch (sortOrder)
{
case "firstname":
employees = employees.OrderBy(x => x.FirstName);
break;
case "firstname_desc":
employees = employees.OrderByDescending(x => x.FirstName);
break;
case "lastname":
employees = employees.OrderBy(x => x.LastName);
break;
case "lastname_desc":
employees = employees.OrderByDescending(x => x.LastName);
break;
case "title":
employees = employees.OrderBy(x => x.Title);
break;
case "title_desc":
employees = employees.OrderByDescending(x => x.Title);
break;
case "city":
employees = employees.OrderBy(x => x.City);
break;
case "city_desc":
employees = employees.OrderByDescending(x => x.City);
break;
case "country":
employees = employees.OrderBy(x => x.Country);
break;
case "country_desc":
employees = employees.OrderByDescending(x => x.Country);
break;
case "address":
employees = employees.OrderBy(x => x.Address);
break;
case "address_desc":
employees = employees.OrderByDescending(x => x.Address);
break;
case "birth":
employees = employees.OrderBy(x => x.BirthDate);
break;
case "birth_desc":
employees = employees.OrderByDescending(x => x.BirthDate);
break;
case "hire":
employees = employees.OrderBy(x => x.HireDate);
break;
case "hire_desc":
employees = employees.OrderByDescending(x => x.HireDate);
break;
default:
employees = employees.OrderBy(x => x.LastName);
break;
}
if (!string.IsNullOrEmpty(searchPhrase))
{
employees = employees.Where(x => x.FirstName.Contains(searchPhrase) || x.LastName.Contains(searchPhrase));
}
var vm = new EmployeeViewModel()
{
Employees = employees.ToArray()
};
return(View(vm));
}
private static void PrintWhatSecondUserSeesAfterChanges(NorthwindEntities secondConnection)
{
Console.WriteLine("After all changes:");
Console.WriteLine("User2 see: {0}\n", secondConnection.Employees.First().FirstName);
}
public CargoController()
{
_db = DBTool.DBInstance;
}
public SupplierRepository(NorthwindEntities northwindEntityDataModel)
{
_northwindEntityDataModel = northwindEntityDataModel;
}
