public ActionResult Register([Bind(Include = "Email,Password,CompanyName,ContactName,ContactTitle,Address,City,Region,PostalCode,Country,Phone,Fax")] CustomerRegister customerRegister)
{
// Add new customer to database
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
if (ModelState.IsValid)
{
// create customer
Customer customer = customerRegister.MapToCustomer();
// first, make sure that the CompanyName is unique
if (db.Customers.Any(c => c.CompanyName == customer.CompanyName))
{
// duplicate CompanyName
return(View());
}
// Generate guid for this customer
customer.UserGuid = System.Guid.NewGuid();
// Hash & Salt the customer Password using SHA-1 algorithm
customer.Password = UserAccount.HashSHA1(customer.Password + customer.UserGuid);
// Save customer to database
db.Customers.Add(customer);
db.SaveChanges();
return(RedirectToAction(actionName: "Index", controllerName: "Home"));
}
// validation error
return(View());
}
}
public List <Product> Category()
{
NORTHWNDEntities db = new NORTHWNDEntities();
List <Product> Products = db.Products.ToList();
return(Products);
}
public ActionResult Account()
{
// cookies
if (Request.Cookies["role"].Value != "customer")
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
//ViewBag.CustomerID = UserAccount.GetUserID();
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
// find customer using CustomerID (stored in authentication ticket)
Customer customer = db.Customers.Find(UserAccount.GetUserID());
// display original values in textboxes when customer is editing data
CustomerEdit EditCustomer = new CustomerEdit()
{
CompanyName = customer.CompanyName,
ContactName = customer.ContactName,
ContactTitle = customer.ContactTitle,
Address = customer.Address,
City = customer.City,
Region = customer.Region,
PostalCode = customer.PostalCode,
Country = customer.Country,
Phone = customer.Phone,
Fax = customer.Fax,
Email = customer.Email
};
return(View(EditCustomer));
}
}
public ActionResult Register(CustomerRegister customerRegister)
{
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
if (ModelState.IsValid)
{
Customer customer = Mapper.Map <Customer>(customerRegister);
//Check for duplicate Registration (Name already in DB)
if (db.Customers.Any(c => c.CompanyName == customer.CompanyName))
{
ModelState.AddModelError("CompanyName", "There is already a company named that");
return(View());
}
// Generate guid for this customer
customer.UserGuid = System.Guid.NewGuid();
// Generate hash (with guid + pass)
customer.Password = UserAccount.HashSHA1(customer.Password + customer.UserGuid);
//Add Customer and Save Changes
db.Customers.Add(customer);
db.SaveChanges();
return(RedirectToAction(actionName: "Index", controllerName: "Home"));
}
return(View());
}
}
public static List <string> FindOrderByRegion(string region, DateTime startDate, DateTime endDate)
{
NORTHWNDEntities db = new NORTHWNDEntities();
List <string> result = new List <string>();
using (db)
{
// Thx to vic.alexiev form here http://forums.academy.telerik.com/107782/databases-%D0%B4%D0%BE%D0%BC%D0%B0%D1%88%D0%BD%D0%BE-entity-framework-2-5-%D0%B7%D0%B0%D0%B4%D0%B0%D1%87%D0%B8
var salesResult =
from salesByYear in db.Sales_by_Year(startDate, endDate)
join orders in db.Orders.Where(o => o.ShipRegion == region)
on salesByYear.OrderID equals orders.OrderID
select salesByYear;
foreach (var sale in salesResult)
{
result.Add(String.Format("{0} | {1} | {2} | {3}", sale.OrderID, sale.ShippedDate, sale.Subtotal, sale.Year));
}
}
Console.WriteLine();
return(result);
}
public ActionResult Zad3_2()
{
using (NORTHWNDEntities dbModel = new NORTHWNDEntities())
{
List <Customers> klienci = new List <Customers>();
var q = (from c in dbModel.Customers select c).ToList();
foreach (var item in q)
{
klienci.Add(new Lab13.Models.Customers
{
CustomerID = item.CustomerID,
Address = item.Address,
City = item.City,
CompanyName = item.CompanyName,
ContactName = item.ContactName,
ContactTitle = item.ContactTitle,
Country = item.Country,
CustomerDemographics = item.CustomerDemographics,
Fax = item.Fax,
Orders = item.Orders,
Phone = item.Phone,
PostalCode = item.PostalCode,
Region = item.Region
});
}
return(View(klienci));
}
}
public ActionResult Account(CustomerEdit updatedCust)
{
if (Request.Cookies["role"].Value != "customer")
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
if (ModelState.IsValid)
{
Customer customer = db.Customers.Find(UserAccount.GetUserID());
if (customer.CompanyName.ToLower() != updatedCust.CompanyName.ToLower())
{
// Ensure that the CompanyName is unique
if (db.Customers.Any(c => c.CompanyName == updatedCust.CompanyName))
{
// duplicate CompanyName
ModelState.AddModelError("CompanyName", "There is already a company named that");
return(View(updatedCust));
}
}
Mapper.Map(updatedCust, customer);
db.SaveChanges();
return(RedirectToAction(actionName: "Index", controllerName: "Home"));
}
//Validation Problem
return(View(updatedCust));
}
}
public ActionResult Zad3()
{
using (NORTHWNDEntities dbModel = new NORTHWNDEntities())
{
return(View(new Customers()));
}
}
public ActionResult Signout()
{
String userId = Session["userId"] as String;
if (!String.IsNullOrEmpty(userId))
{
//StoredCredentialsDBContext db = new StoredCredentialsDBContext();
//StoredCredentials sc =
// db.StoredCredentialSet.FirstOrDefault(x => x.UserId == userId);
NORTHWNDEntities db = new NORTHWNDEntities();
StoredCredential sc = db.StoredCredentials.FirstOrDefault(x => x.UserId == userId);
if (sc != null)
{
// Revoke the token.
UriBuilder builder = new UriBuilder(OAUTH2_REVOKE_ENDPOINT);
builder.Query = "token=" + sc.RefreshToken;
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(builder.Uri);
request.GetResponse();
//db.StoredCredentialSet.Remove(sc);
db.StoredCredentials.DeleteObject(sc);
}
Session.Remove("userId");
}
return(Redirect(Url.Action("Index", "Main")));
}
// GET: EditProfile
public ActionResult Index()
{
NORTHWNDEntities nwe = new NORTHWNDEntities();
Employee e = nwe.Employees.Where(emp => emp.EmployeeID == employeeId).SingleOrDefault();
return View("ProfileView", e);
}
public JsonResult GetCustomers()
{
NORTHWNDEntities en = new NORTHWNDEntities();
var customer = (from c in en.Customers
select new {
CustomerID = c.CustomerID,
CompanyName = c.CompanyName,
ContactName = c.ContactName,
ContactTitle = c.ContactTitle,
Address = c.Address,
City = c.City,
Region = c.Region,
PostalCode = c.PostalCode,
Country = c.Country,
Phone = c.Phone,
Fax = c.Fax
}).ToList();
en.Dispose();
string json = JsonConvert.SerializeObject(customer);
return(Json(json, JsonRequestBehavior.AllowGet));
}
protected void Page_Load(object sender, EventArgs e)
{
NORTHWNDEntities context = new NORTHWNDEntities();
List <Employee> employeesList = context.Employees.OrderBy(x => x.FirstName).ToList <Employee>();
this.employees.DataSource = employeesList;
}
public void AddCustomer_CheckIsFieldsCorrectAdded()
{
using (var db = new NORTHWNDEntities())
{
string companyName = "Telerik";
string customerID = "ABC12";
Customer testCustomer = new Customer()
{
CompanyName = companyName,
CustomerID = customerID
};
CustomerOperations.AddCustomer(testCustomer);
var getAddedCustomer = (from c in db.Customers
where c.CompanyName == companyName
select c).ToList();
Assert.AreEqual(getAddedCustomer.FirstOrDefault().CompanyName, companyName, "Company name is incorect.");
Assert.AreEqual(getAddedCustomer.FirstOrDefault().CustomerID, customerID, "CustomerID is incorect");
// Delete customer added for test
CustomerOperations.DeleteCustomer(testCustomer);
}
}
public static void Main()
{
using (db = new NORTHWNDEntities())
{
var customers = db.Customers
.Join(db.Orders,
(c => c.CustomerID),
(o => o.CustomerID),
(c, o) => new
{
CustomerName = c.ContactName,
OrderYear = o.OrderDate.Value.Year,
o.ShipCountry
})
.ToList()
.FindAll(c => c.ShipCountry == "Canada" && c.OrderYear == 1997)
.ToList()
.OrderBy(c => c.CustomerName);
foreach (var c in customers)
{
Console.WriteLine(c.CustomerName + " : " + c.OrderYear + " : " + c.ShipCountry);
}
}
}
public List <Product> Supplier()
{
NORTHWNDEntities db = new NORTHWNDEntities();
List <Product> Products = db.Products.ToList();
return(Products);
}
public ActionResult Edit(Employee e, string btnSave)
{
if (btnSave == "Save")
{
if (!ModelState.IsValid)
{
return(RedirectToAction("Index"));
}
NORTHWNDEntities nwe = new NORTHWNDEntities();
nwe.Configuration.ProxyCreationEnabled = false;
Employee original = nwe.Employees.Where(emp => emp.EmployeeID == employeeId).SingleOrDefault();
foreach (PropertyInfo propertyInfo in ((Employee)original).GetType().GetProperties())
{
if (propertyInfo.GetValue(e) == null)
{
propertyInfo.SetValue(e, propertyInfo.GetValue(original));
}
}
nwe.Entry(original).CurrentValues.SetValues(e);
nwe.SaveChanges();
return(View("ProfileView", e));
}
else
{
return(View("Index"));
}
}
// GET: Product/Category
public ActionResult Category()
{
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
return(View(db.Categories.OrderBy(c => c.CategoryID).ToList()));
}
}
// GET: Product/Discount
public ActionResult Discounts()
{
using (var db = new NORTHWNDEntities())
{
return(View(db.Discounts.OrderByDescending(q => q.DiscountPercent).ToList()));
}
}
public JsonResult AddToCart(CartDTO cartDto)
{
if (!ModelState.IsValid)
{
Response.StatusCode = 400;
return(Json(new { }, JsonRequestBehavior.AllowGet));
}
//CREATE CART
Cart sc = new Cart();
sc.ProductID = cartDto.ProductID;
sc.CustomerID = cartDto.CustomerID;
sc.Quantity = cartDto.Quantity;
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
if (db.Carts.Where(c => c.ProductID == sc.ProductID && c.CustomerID == sc.CustomerID).Any())
{
//UPDATE
Cart cart = db.Carts.FirstOrDefault(c => c.ProductID == sc.ProductID && c.CustomerID == sc.CustomerID);
cart.Quantity += sc.Quantity;
}
else
{
db.Carts.Add(sc);
}
db.SaveChanges();
}
return(Json(sc, JsonRequestBehavior.AllowGet));
}
public void DeleteCustomer_TestIsCustomerDeleted()
{
using (var db = new NORTHWNDEntities())
{
string companyName = "Telerik";
Customer testCustomer = new Customer()
{
CompanyName = companyName,
CustomerID = "ABC12"
};
CustomerOperations.AddCustomer(testCustomer);
// Using ToList() to force execution
var numberOfCustomersAfterAdding = (from c in db.Customers
where c.CompanyName == companyName
select c).ToList();
// Delete customer added for test
CustomerOperations.DeleteCustomer(testCustomer);
var numberOfCustomersAfterDeleting = (from c in db.Customers
where c.CompanyName == companyName
select c).ToList();
Assert.AreEqual(numberOfCustomersAfterAdding.Count(),
numberOfCustomersAfterDeleting.Count() + 1,
"Check is customer deleted");
}
}
public int Insert(Shippers s)
{
NORTHWNDEntities db = new NORTHWNDEntities();
db.Shippers.Add(s);
return(db.SaveChanges());
}
public List <Shippers> Select()
{
NORTHWNDEntities db = new NORTHWNDEntities();
db.Configuration.ProxyCreationEnabled = false;
return(db.Shippers.ToList());
}
// Create
public IEnumerable <CategoryViewModel> AddCategory(IEnumerable <CategoryViewModel> categories)
{
var result = new List <Category>();
using (var context = new NORTHWNDEntities())
{
foreach (var categoryViewModel in categories)
{
Category category = new Category();
Mapper.Map(categoryViewModel, category);
result.Add(category);
context.Categories.Add(category);
}
context.SaveChanges();
return(result.Select(p => new CategoryViewModel
{
CategoryID = p.CategoryID,
CategoryName = p.CategoryName,
Description = p.Description
}).ToList());
}
}
public ActionResult Display(int id)
{
NORTHWNDEntities db = new NORTHWNDEntities();
var data = db.departs.Find(id);
return(View(data));
}
public ActionResult Edit()
{
NORTHWNDEntities nwe = new NORTHWNDEntities();
nwe.Configuration.ProxyCreationEnabled = false;
//Employee e = nwe.Employees.Where(emp => emp.EmployeeID == employeeId).SingleOrDefault();
//EmployeeEditViewModel evm = new EmployeeEditViewModel();
//CopyPropertyValues(e, evm);
//var evm = nwe.Employees.Where(emp => emp.EmployeeID == employeeId).Select(em => new EmployeeEditViewModel
//{
// EmployeeID = em.EmployeeID,
// Address = em.Address,
// City = em.City,
// Country = em.Country,
// Extension = em.Extension,
// HomePhone = em.HomePhone,
// Notes = em.Notes,
// PostalCode = em.PostalCode,
// Region = em.Region,
// Title = em.Title,
// TitleOfCourtesy = em.TitleOfCourtesy
//}).FirstOrDefault();
var tmp = PartialObject(modelViewType);
var evm = Activator.CreateInstance(Type.GetType(modelViewType.FullName));
CopyFieldValues(tmp, evm);
return View("ProfileEdit", evm);
}
// GET: Dept
public ActionResult Index()
{
NORTHWNDEntities db = new NORTHWNDEntities();
var departmentlist = db.departs.ToList();
return(View(departmentlist));
}
// GET api/values
public IEnumerable <Customer> Get(string City = "all")
{
using (EmployeeDataLib.NORTHWNDEntities EntityObj = new NORTHWNDEntities())
{
return(EntityObj.Customers.Where(C => City == "all" || C.City == City).Take(10).ToList());
}
}
public ActionResult Enrolled()
{
NORTHWNDEntities db = new NORTHWNDEntities();
ViewBag.DeptId = new SelectList(db.departs, "Id", "Name");
return(View());
}
public JsonResult FilterProducts(int?id, String SearchString, decimal PriceFilter)
{
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
var Products = db.Products.Where(p => p.Discontinued == false).ToList();
if (id != null)
{
Products = Products.Where(p => p.CategoryID == id).ToList();
}
if (!String.IsNullOrEmpty(SearchString))
{
Products = Products.Where(p => p.ProductName.Contains(SearchString)).ToList();
}
var ProductDTOs = (from p in Products.Where(p => p.UnitPrice >= 10)
orderby p.ProductName
select new
{
p.ProductID,
p.ProductName,
p.QuantityPerUnit,
p.UnitPrice,
p.UnitsInStock
}).ToList();
return(Json(ProductDTOs, JsonRequestBehavior.AllowGet));
}
}
/// <summary>
/// Returns list of all products
/// </summary>
/// <returns>List of products</returns>
public async Task <IEnumerable <Product> > GetAllProducts()
{
using (var dbContext = new NORTHWNDEntities())
{
try
{
var result = dbContext.Products;
/* select new Product
* {
* UnitPrice = p.UnitPrice.HasValue ? p.UnitPrice.Value : 0,
* Category = p.Category,
* ProductID = p.ProductID,
* ProductName = p.ProductName,
* CategoryID = p.CategoryID,
* Discontinued = p.Discontinued,
* QuantityPerUnit = p.QuantityPerUnit,
* Supplier = p.Supplier,
* SupplierID = p.SupplierID,
* UnitsInStock = p.UnitsInStock,
* Order_Details = p.Order_Details,
* UnitsOnOrder = p.UnitsOnOrder
*
* });*/
return(await result.ToListAsync());
}
catch (Exception exc)
{
//TBD use logfornet to log the exception details here
}
}
return(null);
}
public Customer GetCustomer(string custID)
{
using (NORTHWNDEntities context = new NORTHWNDEntities())
{
return(context.Customers.FirstOrDefault(c => c.CustomerID == custID));
}
}
public List <Customer> GetCustomersByCountry(string country)
{
using (NORTHWNDEntities context = new NORTHWNDEntities())
{
return(context.Customers.Where(c => c.Country == country).ToList());
}
}
public List <string> GetCountries()
{
using (NORTHWNDEntities context = new NORTHWNDEntities())
{
return(context.Customers.Select(c => c.Country).Distinct().ToList());
}
}
public ActionResult Edit()
{
NORTHWNDEntities nwe = new NORTHWNDEntities();
Employee e = nwe.Employees.Where(emp => emp.EmployeeID == employeeId).SingleOrDefault();
return(View("ProfileEdit", e));
}
public static void AddCustomer(Customer customer)
{
using (var db = new NORTHWNDEntities())
{
db.Customers.Add(customer);
db.SaveChanges();
}
Console.WriteLine("Customer added.");
}
public static void Main()
{
using (var db = new NORTHWNDEntities())
{
string companyName = "Telerik";
var telerikCustomers = from c in db.Customers
where c.CompanyName == companyName
select c;
foreach (var customer in telerikCustomers)
{
db.Customers.Remove(customer);
}
db.SaveChanges();
// Create and add customer in NORTHWND database
Customer testCustomer = new Customer()
{
CompanyName = companyName,
CustomerID = "ABC42"
};
Console.WriteLine("First customer is added");
db.Customers.Add(testCustomer);
Console.WriteLine("Changes saved");
db.SaveChanges();
// Create two other customers and try to add them in NORTHWND database
// If we try to add 'incorrectTestCustomer' SaveChanges will rollback operation (no customers added)
// If we comment adding 'incorrectTestCustomer' this will work correct (will commit changes)
Customer secondTestCustomer = new Customer()
{
CompanyName = companyName,
CustomerID = "ABC98"
};
Customer incorrectTestCustomer = new Customer()
{
CompanyName = companyName,
CustomerID = "ABC98"
};
Console.WriteLine("Second customer is added");
db.Customers.Add(secondTestCustomer);
Console.WriteLine("Adding incorrect Customer");
// Uncomment line below. This will trow exception and call rollback.
//db.Customers.Add(incorrectTestCustomer);
//Console.WriteLine("Trying to save changes. This will throw exception");
db.SaveChanges();
}
}
public static void ModifyCustomerCompanyName(Customer customer, string newName)
{
using (var db = new NORTHWNDEntities())
{
var customerFromDB = db.Customers.FirstOrDefault(x => x.CustomerID == customer.CustomerID);
customerFromDB.CompanyName = newName;
db.SaveChanges();
}
Console.WriteLine("Customer updated.");
}
public static void DeleteCustomer(Customer customer)
{
using (var db = new NORTHWNDEntities())
{
var customerFromDB = db.Customers.FirstOrDefault(x => x.CustomerID == customer.CustomerID);
db.Customers.Remove(customerFromDB);
db.SaveChanges();
}
Console.WriteLine("Customer deleted.");
}
public static void Main()
{
using (var db = new NORTHWNDEntities())
{
string supplierFullName = "Steven Buchanan";
DateTime startDate = new DateTime(1995, 1, 1);
DateTime endDate = new DateTime(1996, 8, 1);
// After creating stored procedure in Managment studio
// you should add this procedure and after that update model from DB.
var result = db.uspTotalIncomesForPeriod(supplierFullName, startDate, endDate);
Console.WriteLine(result.FirstOrDefault());
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.Params["EmployeeID"] == null)
{
Response.Redirect("Employees.aspx");
}
var id = int.Parse(Request.Params["EmployeeID"]);
var context = new NORTHWNDEntities();
var employee = context.Employees.Where(x => x.EmployeeID == id).ToList();
this.DetailsViewEmployeeDetails.DataSource = employee;
this.DetailsViewEmployeeDetails.DataBind();
}
public static void Main()
{
using (var db = new NORTHWNDEntities())
{
Customer testCustomer = new Customer()
{
CompanyName = "Telerik",
CustomerID = "ABC12"
};
CustomerOperations.AddCustomer(testCustomer);
//CustomerOperations.DeleteCustomer(testCustomer);
//CustomerOperations.ModifyCustomerCompanyName(testCustomer, "Telerik Academy");
}
}
public ActionResult Save(Employee e, string btnSave)
{
if (btnSave == "Save")
{
if (!ModelState.IsValid)
return RedirectToAction("Index");
NORTHWNDEntities nwe = new NORTHWNDEntities();
nwe.Employees.Add(e);
nwe.SaveChanges();
return View("ProfileView", e);
}
else
return View("Index");
}
public static void FindAllSales(string region, DateTime startDate, DateTime endDate)
{
using (var db = new NORTHWNDEntities())
{
var sales = from o in db.Orders
join od in db.Order_Details
on o.OrderID equals od.OrderID
where o.ShipRegion == region &&
(o.ShippedDate >= startDate && o.ShippedDate <= endDate)
select new { o.ShipName, od.Product.ProductName, od.Quantity };
foreach (var sale in sales)
{
Console.WriteLine("Ship name: {0} Product name: {1} Quantity: {2}",
sale.ShipName.PadRight(20), sale.ProductName.PadRight(35), sale.Quantity);
}
}
}
public ActionResult Edit(EmployeeEditViewModel e, string btnSave)
{
if (btnSave == "Save")
{
if (!ModelState.IsValid)
return RedirectToAction("Index");
NORTHWNDEntities nwe = new NORTHWNDEntities();
nwe.Configuration.ProxyCreationEnabled = false;
Employee original = nwe.Employees.Where(emp => emp.EmployeeID == employeeId).SingleOrDefault();
CopyPropertyValues(e, original);
nwe.SaveChanges();
return View("ProfileView", original);
}
else
return View("Index");
}
public static void Main()
{
using (var db = new NORTHWNDEntities())
{
Order firstOrder = new Order() { CustomerID = "VINET", EmployeeID = 5, ShipVia = 3 };
Order secondOrder = new Order() { CustomerID = "VINET", EmployeeID = 5, ShipVia = 3 };
Order thirdOrder = new Order() { CustomerID = "VINET", EmployeeID = 5, ShipVia = 3 };
//Order orderWithDuplicatedPrimaryKey = new Order() { CustomerID = "INVAL", EmployeeID = 5, ShipVia = 3 };
db.Orders.Add(firstOrder);
db.SaveChanges();
db.Orders.Add(secondOrder);
db.SaveChanges();
db.Orders.Add(thirdOrder);
db.SaveChanges();
//db.Orders.Add(orderWithDuplicatedPrimaryKey);
//db.SaveChanges();
}
}
public static void Main()
{
IObjectContextAdapter db = new NORTHWNDEntities();
string cloneNorthwind = db.ObjectContext.CreateDatabaseScript();
// You can change .mdf and .ldf directory
string createNorthwindCloneDB = "CREATE DATABASE NorthwindTwin ON PRIMARY " +
"(NAME = NorthwindTwin, " +
@"FILENAME = 'D:\Telerik Academy\Telerik\Databases\08.Entity-Framework\Homework-Entity-Framework\Homework-Entity-Framework\Task06.CreateNorthwindTwinDB\NorthwindTwin.mdf', " +
"SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = NorthwindTwinLog, " +
@"FILENAME = 'D:\Telerik Academy\Telerik\Databases\08.Entity-Framework\Homework-Entity-Framework\Homework-Entity-Framework\Task06.CreateNorthwindTwinDB\NorthwindTwin.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)";
SqlConnection dbConForCreatingDB = new SqlConnection(
"Server=LOCALHOST; " +
"Database=master; " +
"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();
}
}
public static void FindCustomersByYearAndCountry()
{
DateTime startDate = new DateTime(1997, 1, 1);
DateTime endDate = new DateTime(1997, 12, 31);
string country = "Canada";
using (var db = new NORTHWNDEntities())
{
var customers = from o in db.Orders
join c in db.Customers
on o.CustomerID equals c.CustomerID
where o.ShipCountry == country &&
(o.ShippedDate >= startDate && o.ShippedDate <= endDate)
select new { c.ContactName, o.ShippedDate, o.ShipCountry };
foreach (var customer in customers)
{
Console.WriteLine("Name: {0} Shipped date: {1} Country: {2}",
customer.ContactName.PadRight(20), customer.ShippedDate.ToString().PadRight(25), customer.ShipCountry);
}
}
}
public static void Main()
{
DateTime startDate = new DateTime(1997, 1, 1);
DateTime endDate = new DateTime(1997, 12, 31);
string country = "Canada";
var northwinthEntities = new NORTHWNDEntities();
string nativeSQLQuery = "use NORTHWND" +
" select c.ContactName, o.ShippedDate, o.ShipCountry" +
" from Orders o" +
" join Customers c" +
" on o.CustomerID = c.CustomerID" +
" where o.ShipCountry = {0} and (o.ShippedDate between {1} and {2})";
object[] parametars = { country, startDate, endDate };
var customers = northwinthEntities.Database.SqlQuery<CustomersInfo>(nativeSQLQuery, parametars);
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
public ActionResult Edit(Employee e, string btnSave)
{
if (btnSave == "Save")
{
if (!ModelState.IsValid)
return RedirectToAction("Index");
NORTHWNDEntities nwe = new NORTHWNDEntities();
nwe.Configuration.ProxyCreationEnabled = false;
Employee original = nwe.Employees.Where(emp => emp.EmployeeID == employeeId).SingleOrDefault();
foreach (PropertyInfo propertyInfo in ((Employee)original).GetType().GetProperties())
{
if (propertyInfo.GetValue(e) == null)
propertyInfo.SetValue(e, propertyInfo.GetValue(original));
}
nwe.Entry(original).CurrentValues.SetValues(e);
nwe.SaveChanges();
return View("ProfileView", e);
}
else
return View("Index");
}
public object PartialObject(Type typeOfClass)
{
NORTHWNDEntities nwe = new NORTHWNDEntities();
nwe.Configuration.ProxyCreationEnabled = false;
var tmp = nwe.Employees.Where(emp => emp.EmployeeID == employeeId)
.SelectPartially(typeOfClass.GetProperties().Select(p => p.Name).ToList<string>())
.FirstOrDefault();
return tmp;
}
public void ModifyCustomerCompanyName_TestIsCompanyNameModified()
{
using (var db = new NORTHWNDEntities())
{
string companyName = "Telerik";
string companyNewName = "Telerik Academy";
Customer testCustomer = new Customer()
{
CompanyName = companyName,
CustomerID = "ABC12"
};
CustomerOperations.AddCustomer(testCustomer);
CustomerOperations.ModifyCustomerCompanyName(testCustomer, companyNewName);
var getModifiedCustomer = (from c in db.Customers
where c.CompanyName == companyNewName
select c).ToList();
// Delete customer added for test
CustomerOperations.DeleteCustomer(testCustomer);
Assert.AreEqual(companyNewName, getModifiedCustomer.FirstOrDefault().CompanyName, "Company name is not modified");
}
}