//
// GET: /CellAlignment/
public ActionResult CellAlignment()
{
var DataSource = new NorthwindDataContext().OrdersViews.ToList();
ViewBag.datasource = DataSource;
return(View());
}
//
// GET: /ColumnTemplate/
public ActionResult ColumnTemplate()
{
var DataSource = new NorthwindDataContext().EmployeeViews.Take(8).ToList();
ViewBag.datasource = DataSource;
return(View());
}
public bool MarcaExcluir(Guid IdMarca)
{
bool sucesso = false;
using (NorthwindDataContext db = new NorthwindDataContext())
{
//excluir o item primeiro
//var itemAcessorio=()
var marca = (from c in db.Marcas
where c.IdMarca.Equals(IdMarca)
select c).FirstOrDefault();
//adicionando o objeto para excluir no DataContext
db.Marcas.DeleteOnSubmit(marca);
///envia alterações pro bd
try
{
db.SubmitChanges();
sucesso = true;
}
catch (Exception e)
{
sucesso = false;
throw e;
}
}
return(sucesso);
}
public ActionResult SpreadsheetFeatures()
{
var dataSource = new NorthwindDataContext().Orders.Take(50).ToList();
ViewBag.DataSource = dataSource;
return(View());
}
private IEnumerable GetServerData(GridCommand command)
{
DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Order>(o => o.Customer);
var dataContext = new NorthwindDataContext
{
LoadOptions = loadOptions
};
IQueryable<Order> data = dataContext.Orders;
//Apply filtering
data = data.ApplyFiltering(command.FilterDescriptors);
ViewData["Total"] = data.Count();
//Apply sorting
data = data.ApplySorting(command.GroupDescriptors, command.SortDescriptors);
//Apply paging
data = data.ApplyPaging(command.Page, command.PageSize);
//Apply grouping
if (command.GroupDescriptors.Any())
{
return data.ApplyGrouping(command.GroupDescriptors);
}
return data.ToList();
}
public bool AcessorioAtualizar(Acessorio tipo)
{
bool sucesso = false;
using (NorthwindDataContext db = new NorthwindDataContext())
{
var acessorio = (from c in db.Acessorios
where c.IdAcessorio.Equals(tipo.acessorio)
select c).Single();
acessorio.Nome = tipo.nome;
acessorio.Parcelas = tipo.parcelas;
acessorio.Descricao = tipo.descricao;
acessorio.Preco = tipo.preco;
try
{
db.SubmitChanges();
sucesso = true;
}
catch (Exception e)
{
sucesso = false;
throw e;
}
}
return(sucesso);
}
//
// GET: /ShowHideColumn/
public ActionResult ShoworHideColumn()
{
var data = new NorthwindDataContext().Tasks.Take(30).ToList();
ViewBag.datasource = data;
return(View());
}
//
// GET: /StackedHeader/
public ActionResult StackedHeader()
{
var DataSource = new NorthwindDataContext().OrdersViews.ToList();
ViewBag.datasource = DataSource;
return(View());
}
//
// GET: /MultiSortUISupport/
public ActionResult MultiSortingUISupport()
{
var DataSource = new NorthwindDataContext().OrdersViews.ToList();
ViewBag.datasource = DataSource;
return(View());
}
//
// GET: /FilteringAndSearching/
public ActionResult FilteringAndSearching()
{
var data = new NorthwindDataContext().Tasks.Take(30).ToList();
ViewBag.datasource = data;
return(View());
}
//
// GET: /CustomCommand/
public ActionResult CustomCommand()
{
var DataSource = new NorthwindDataContext().EmployeeViews.ToList();
ViewBag.datasource = DataSource;
return(View());
}
//
// GET: /StackedHeader/
public ActionResult StackedHeader()
{
var data = new NorthwindDataContext().Tasks.Take(30).ToList();
ViewBag.datasource = data;
return(View());
}
private void btnEliminar_Click(object sender, EventArgs e)
{
if (dgvTerritory.Rows.Count != 0)
{
if (MessageBox.Show("Desea Eliminar?", "Eliminación", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
using (var db = new NorthwindDataContext())
{
string id = dgvTerritory.CurrentRow.Cells[0].Value.ToString();
var query = db.Territories.Where(x => x.RegionID.Equals(id)).ToList();
foreach (Territories oTerritory in query)
{
oTerritory.habilitado = false;
}
try
{
db.SubmitChanges();
MessageBox.Show("Se elimino con exito");
MostrarData();
}
catch (Exception ex)
{
MessageBox.Show("error: " + ex.Message);
}
}
}
}
}
private static int GetCount()
{
using (NorthwindDataContext dataContext = new NorthwindDataContext())
{
return dataContext.Orders.Count();
}
}
bool ExisteNombre(string nombre)
{
using (var db = new NorthwindDataContext())
{
return(db.Territories.Any(x => x.TerritoryDescription.Equals(nombre)));
}
}
public Order ExecuteQueryForOrder(XElement xml)
{
Debugger.Launch();
NorthwindDataContext db = new NorthwindDataContext();
IQueryable queryAfter = db.DeserializeQuery(xml);
return (Order)queryAfter.Provider.Execute(queryAfter.Expression);
}
bool ExisteID(string id)
{
using (var db = new NorthwindDataContext())
{
return(db.Territories.Any(x => x.TerritoryID.Equals(id)));
}
}
public ActionResult ResponsiveGrid()
{
var DataSource = new NorthwindDataContext().OrdersViews.Take(200).ToList();
ViewBag.datasource = DataSource;
return(PartialView());
}
public AjaxStoreResult SaveCustomersWithConfirmation()
{
AjaxStoreResult ajaxStoreResult = new AjaxStoreResult(StoreResponseFormat.Save);
try
{
NorthwindDataContext db = this.DBContext;
StoreDataHandler dataHandler = new StoreDataHandler(HttpContext.Request["data"]);
ChangeRecords <Customer> data = dataHandler.ObjectData <Customer>();
ConfirmationList confirmationList = dataHandler.BuildConfirmationList("CustomerID");
foreach (Customer customer in data.Deleted)
{
db.Customers.Attach(customer);
db.Customers.DeleteOnSubmit(customer);
}
foreach (Customer customer in data.Updated)
{
db.Customers.Attach(customer);
db.Refresh(RefreshMode.KeepCurrentValues, customer);
}
foreach (Customer customer in data.Created)
{
db.Customers.InsertOnSubmit(customer);
}
db.SubmitChanges();
//ideally we should confirm after each operation
//but LINQ can make batch submit of changes
foreach (Customer customer in data.Deleted)
{
confirmationList[customer.CustomerID].ConfirmRecord();
}
foreach (Customer customer in data.Updated)
{
confirmationList[customer.CustomerID].ConfirmRecord();
}
foreach (Customer customer in data.Created)
{
confirmationList[customer.CustomerID].ConfirmRecord();
}
ajaxStoreResult.SaveResponse.ConfirmationList = confirmationList;
}
catch (Exception e)
{
ajaxStoreResult.SaveResponse.Success = false;
ajaxStoreResult.SaveResponse.Message = e.Message;
}
return(ajaxStoreResult);
}
bool ExiteID(int id)
{
using (var db = new NorthwindDataContext())
{
bool existe = db.Region.Any(x => x.RegionID.Equals(id));
return(existe);
}
}
public Order ExecuteQueryForOrder(XElement xml)
{
Debugger.Launch();
NorthwindDataContext db = new NorthwindDataContext();
IQueryable queryAfter = db.DeserializeQuery(xml);
return((Order)queryAfter.Provider.Execute(queryAfter.Expression));
}
public void TestInitialize()
{
var connectionString = @"Provider=VFPOLEDB.1;Data Source=Northwind.dbc;Exclusive=false";
northwind = new NorthwindDataContext(connectionString);
northwind.Provider.Log = new TestContextWriter(TestContext);
northwind.Provider.AutoRightTrimStrings = true;
}
bool ExisteCompanyName(string companyName)
{
using (var db = new NorthwindDataContext())
{
bool existe = db.Suppliers.Any(x => x.CompanyName.Equals(companyName));
return(existe);
}
}
public ActionResult Accessibility(string itemName)
{
ViewData["itemName"] = itemName;
var list = new NorthwindDataContext().Categories.Take(2);
return(View(list));
}
TenMostExpensiveProducts()
{
NorthwindDataContext dc = new
NorthwindDataContext();
return(dc.Ten_Most_Expensive_Products().ToList <
Ten_Most_Expensive_ProductsResult>());
}
public virtual void TestInitialize()
{
string connectionString = @"Provider=VFPOLEDB.1;Data Source=" + Path.GetFullPath("Northwind.dbc") + ";Exclusive=false";
this.northwind = new LinqToVfp.Northwind.Tests.NorthwindEntityProvider.NorthwindDataContext(connectionString);
//this.northwind.Provider.Log = new TestContextWriter(this.TestContext);
this.northwind.Provider.AutoRightTrimStrings = true;
}
/// <summary>
/// Exemplo: a cláusuala Where
/// Retorna um funcionario onde o codigo coincinde com o valor
/// passado na parâmetro empID
/// </summary>
/// <param name="empId"></param>
/// <returns>O único valor que coincide ou o valor padrão</returns>
public static Employee GetEmployeeById(int empId)
{
NorthwindDataContext dc = new NorthwindDataContext();
return((from e in dc.GetTable <Employee>()
where (e.EmployeeID == empId)
select e).SingleOrDefault <Employee>());
}
public void ExportToExcel(string GridModel)
{
ExcelExport exp = new ExcelExport();
var DataSource = new NorthwindDataContext().OrdersViews.Take(100).ToList();
GridProperties obj = ConvertGridObject(GridModel);
exp.Export(obj, DataSource, "Export.xlsx", ExcelVersion.Excel2010, false, false, "flat-saffron");
}
public List<string> GetCountries()
{
using (var context = new NorthwindDataContext())
{
return (from c in context.Customers
select c.Country).Distinct().ToList();
}
}
public void ExportToPdf(string GridModel)
{
PdfExport exp = new PdfExport();
var DataSource = new NorthwindDataContext().OrdersViews.Take(100).ToList();
GridProperties obj = ConvertGridObject(GridModel);
exp.Export(obj, DataSource, "Export.pdf", false, false, "flat-saffron");
}
private void ReturnData(HttpContext context)
{
NorthwindDataContext db = new NorthwindDataContext();
StoreResponseData sr = new StoreResponseData();
sr.Data = JSON.Serialize(db.Suppliers.ToList());
sr.Return();
}
private static IEnumerable <Order> GetOrdersForCustomer(string customerId)
{
NorthwindDataContext northwind = new NorthwindDataContext();
return(from order in northwind.Orders
where order.CustomerID == customerId
select order);
}
void MostrarData()
{
using (NorthwindDataContext db = new NorthwindDataContext())
{
dgvTerritory.DataSource = from t in db.Territories
select new { t.TerritoryID, t.TerritoryDescription, t.RegionID };
}
}
private static Employee FetchEmployee(int employeeID)
{
++Application.FetchEmployeeDetailCalls;
var data = new NorthwindDataContext();
return(data.Employees.Single(x => x.EmployeeID == employeeID));
}
private static IEnumerable<Order> GetOrdersForCustomer(string customerId)
{
NorthwindDataContext northwind = new NorthwindDataContext();
return from order in northwind.Orders
where order.CustomerID == customerId
select order;
}
public ActionResult Delete(int id)
{
using (var db = new NorthwindDataContext())
{
db.Products.DeleteOnSubmit(db.Products.First(p => p.ProductID == id));
db.SubmitChanges();
}
return GetView();
}
public List<Customer> GetCustomersByCountry(string country)
{
using (var context = new NorthwindDataContext())
{
return (from c in context.Customers
where c.Country == country
select c).ToList();
}
}
public void ShouldUseLinqToSqlToQueryData()
{
var db = new NorthwindDataContext();
var customers = db.Customers.Take(2);
foreach (var customer in customers)
{
Console.WriteLine("{0} {1} {2}",
customer.CustomerID, customer.CompanyName, customer.ContactName);
}
}
public ActionResult Index(string letter)
{
var db = new NorthwindDataContext();
return View(new AlphabeticProductsViewModel
{
Products = db.Products,
Letters = Enumerable.Range(65, 25).Select(i => ((char)i).ToString()),
SelectedLetter = letter
});
}
private static IEnumerable<Order> GetOrders()
{
NorthwindDataContext northwind = new NorthwindDataContext();
DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Order>(o => o.Customer);
northwind.LoadOptions = loadOptions;
return northwind.Orders;
}
public Customer GetCustomer(string custID)
{
Thread.Sleep(2000);
using (var context = new NorthwindDataContext())
{
return (from c in context.Customers
where c.CustomerID == custID
select c).SingleOrDefault();
}
}
public ActionResult Update(int id)
{
using (var db = new NorthwindDataContext())
{
if(TryUpdateModel(db.Products.First(p => p.ProductID == id)))
{
db.SubmitChanges();
}
}
return GetView();
}
public ActionResult HeaderFooterTemplates(int[] checkedRecords)
{
checkedRecords = checkedRecords ?? new int[] { };
var db = new NorthwindDataContext();
return View(new AggregatedProductModel
{
Products = db.Products,
TotalPrice = db.Products.Sum(p => p.UnitPrice).Value,
SelectedProducts = db.Products.Where(p => checkedRecords.Contains(p.ProductID))
});
}
private JsonResult _GetProducts(int? CategoryID)
{
NorthwindDataContext nw = new NorthwindDataContext();
IQueryable<Product> products = nw.Products.AsQueryable<Product>();
if (CategoryID.HasValue)
{
products = products.Where(p => p.CategoryID == CategoryID.Value);
}
return Json(new SelectList(products, "ProductID", "ProductName"), JsonRequestBehavior.AllowGet);
}
public ActionResult Insert()
{
using (var db = new NorthwindDataContext())
{
var product = new Product();
if (TryUpdateModel(product))
{
db.Products.InsertOnSubmit(product);
db.SubmitChanges();
}
}
return GetView();
}
private JsonResult _GetOrders(int? ProductID)
{
NorthwindDataContext nw = new NorthwindDataContext();
IList<Order> orders = new List<Order>();
if (ProductID.HasValue)
{
orders = nw.Order_Details.Where(od => od.ProductID == ProductID).Select(od => od.Order).ToList();
}
return Json(new SelectList(orders, "OrderID", "ShipName"), JsonRequestBehavior.AllowGet);
}
public ActionResult _GetCustomers(string text)
{
using (var db = new NorthwindDataContext())
{
IQueryable<Customer> result = db.Customers;
if (text.HasValue())
{
result = db.Customers.Where(c => c.ContactName.StartsWith(text));
}
return new JsonResult
{
Data = new SelectList(result.ToList(), "CustomerID", "ContactName")
};
}
}
public ActionResult _AutoCompleteAjaxLoading(string text)
{
Thread.Sleep(1000);
using (var nw = new NorthwindDataContext())
{
var products = nw.Products.AsQueryable();
if (text.HasValue())
{
products = products.Where((p) => p.ProductName.StartsWith(text));
}
return new JsonResult { Data = products.Select(p => p.ProductName).ToList() };
}
}
public JsonResult Update(int id)
{
var db = new NorthwindDataContext();
var product = db.Products.SingleOrDefault(p => p.ProductID == id);
if(TryUpdateModel(product))
{
db.SubmitChanges();
}
return Json(new GridModel(db.Products.Select(p => new ProductViewModel
{
ProductID = p.ProductID,
ProductName = p.ProductName,
CategoryID = p.CategoryID,
CategoryName = p.Category.CategoryName
})));
}
public ProductSearch InitProductSearch(ProductSearch model)
{
if (model == null) {
model = new ProductSearch();
model.SelectedCat = -1;
}
using (var db = new NorthwindDataContext()) {
model.Options = db.Categories.ToList();
if (model.SelectedCat.HasValue) {
model.Results = db.Products.Where(x => x.CategoryID == model.SelectedCat.Value).ToList();
}
}
return model;
}
private static IEnumerable<OrderDto> GetOrderDto()
{
NorthwindDataContext northwind = new NorthwindDataContext();
DataLoadOptions loadOptions = new DataLoadOptions();
loadOptions.LoadWith<Order>(o => o.Customer);
northwind.LoadOptions = loadOptions;
return northwind.Orders.Select(order => new OrderDto
{
ContactName = order.Customer.ContactName,
OrderDate = order.OrderDate,
OrderID = order.OrderID,
ShipAddress = order.ShipAddress
});
}
public static NorthwindDataContext GetInstance()
{
//StreamWriter sw = new StreamWriter("log.txt", true);
if (sw == null)
sw = new StreamWriter(DateTime.Now.ToLongDateString() + "_log.txt", true);
if (instance == null)
instance = new NorthwindDataContext();
sw.AutoFlush = true;//将其缓冲区刷新到基础流(立即保存,不放在IO缓冲区)。
instance.Log = sw;
sw.WriteLine("发生时间为:"+DateTime.Now.ToString());
sw.WriteLine("日志内容为:");
return instance;
}
public ActionResult _AjaxLoading(TreeViewItemModel node)
{
NorthwindDataContext northwind = new NorthwindDataContext();
int? parentId = !string.IsNullOrEmpty(node.Value) ? (int?) Convert.ToInt32(node.Value) : null;
IEnumerable nodes = from item in northwind.Employees
where item.ReportsTo == parentId || (parentId == null && item.ReportsTo == null)
select new TreeViewItemModel
{
Text = item.FirstName + " " + item.LastName,
Value = item.EmployeeID.ToString(),
LoadOnDemand = item.Employees.Count > 0,
Enabled = true
};
return new JsonResult { Data = nodes };
}
private static IQueryable<Customer> GetCustomers()
{
NorthwindDataContext northwind = new NorthwindDataContext();
return northwind.Customers;
}
private IEnumerable<dynamic> GetData()
{
var db = new NorthwindDataContext();
return db.Products;
}
/// <summary>
///Requires: Sql Server or SQL Server Express Edition : http://www.microsoft.com/en-us/download/details.aspx?id=22973
///Download Northwind:http://www.microsoft.com/en-us/download/details.aspx?id=23654
///Install Northwind: http://msdn.microsoft.com/en-us/library/vstudio/ff851969.aspx
///License:http://msdn.microsoft.com/en-US/vstudio/bb894665.aspx
///Original Source:http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
NorthwindDataContext northwind = new NorthwindDataContext();
var result = northwind.Products
.Where("CategoryID = 3 AND UnitPrice > 3")
.OrderBy("SupplierID");
Console.WriteLine("==================One=========================");
Console.WriteLine("Count {0}",result.Count());
Console.WriteLine("==============================================");
result.ToList().ForEach(r => Console.WriteLine("Supplier: {0}", r.SupplierID));
IQueryable<Product> queryableData = northwind.Products.AsQueryable<Product>();
var externals = new Dictionary<string, object>();
externals.Add("Products", queryableData);
Console.WriteLine("===================Two========================");
string query = "Products.Where(Product => (Product.CategoryID = 3 And Product.UnitPrice > 3)).OrderBy(Product=>(Product.SupplierID)).Select(Product=>(Product))";
var expression = System.Linq.Dynamic.DynamicExpression.Parse(typeof(IQueryable<Product>), query, new[] { externals });
result = queryableData.Provider.CreateQuery<Product>(expression);
Console.WriteLine("Count {0}", result.Count());
Console.WriteLine("=============================================");
result.Select(r => r).ToList().ForEach(r => Console.WriteLine("Supplier: {0}", r.SupplierID));
Console.WriteLine("==================Thee========================");
query = "Products.Where(Product => (Product.CategoryID = 3 And Product.UnitPrice > 10)).OrderBy(Product=>(Product.SupplierID)).Take(10)";
expression = System.Linq.Dynamic.DynamicExpression.Parse(typeof(IQueryable<Product>), query, new[] { externals });
result = queryableData.Provider.CreateQuery<Product>(expression);
Console.WriteLine("Count {0}", result.Count());
Console.WriteLine("=============================================");
result.ToList().ForEach(r => Console.WriteLine("Supplier: {0}", r.SupplierID));
Console.WriteLine("==================Four========================");
query = "Products.Where(Product => (Product.CategoryID = 3 And Product.UnitPrice > 10)).OrderBy(Product=>(Product.SupplierID)).Take(3).Union(Products.Where(Product => (Product.CategoryID = 4 And Product.UnitPrice > 3)).OrderBy(Product=>(Product.SupplierID)).Take(2))";
expression = System.Linq.Dynamic.DynamicExpression.Parse(typeof(IQueryable<Product>), query, new[] { externals });
result = queryableData.Provider.CreateQuery<Product>(expression);
Console.WriteLine("Count {0}", result.Count());
Console.WriteLine("=============================================");
result.ToList().ForEach(r => Console.WriteLine("Supplier: {0} , Category: {1}", r.SupplierID, r.CategoryID));
Console.WriteLine("==================Five========================");
query = "Products.Where(Product => (Product.CategoryID = 3 And Product.UnitPrice > 10)).OrderBy(Product=>(Product.SupplierID)).Union(Products.Where(Product => (Product.CategoryID = 4 And Product.UnitPrice > 3)).OrderBy(Product=>(Product.SupplierID)))";
expression = System.Linq.Dynamic.DynamicExpression.Parse(typeof(IQueryable<Product>), query, new[] { externals });
result = queryableData.Provider.CreateQuery<Product>(expression);
Console.WriteLine("Count {0}", result.Count());
Console.WriteLine("=============================================");
result.ToList().ForEach(r => Console.WriteLine("Supplier: {0} , Category: {1}", r.SupplierID, r.CategoryID));
Console.WriteLine("===================Six========================");
query = "Products.Where(Product => Product.CategoryID = 3 And Product.UnitPrice > 3).OrderByDescending(Product=>Product.SupplierID)";
expression = System.Linq.Dynamic.DynamicExpression.Parse(typeof(IQueryable<Product>), query, new[] { externals });
result = queryableData.Provider.CreateQuery<Product>(expression);
Console.WriteLine("Count {0}", result.Count());
Console.WriteLine("=============================================");
result.Select(r => r).ToList().ForEach(r => Console.WriteLine("Supplier: {0}", r.SupplierID));
Console.WriteLine("=============================================");
Console.ReadLine();
}
public Customer ExecuteQueryForCustomer(XElement xml)
{
NorthwindDataContext db = new NorthwindDataContext();
IQueryable queryAfter = db.DeserializeQuery(xml);
return (Customer)queryAfter.Provider.Execute(queryAfter.Expression);
}
public object ExecuteQueryForObject(XElement xml)
{
NorthwindDataContext db = new NorthwindDataContext();
IQueryable queryAfter = db.DeserializeQuery(xml);
return queryAfter.Provider.Execute(queryAfter.Expression);
}
public object[] ExecuteQueryForObjects(XElement xml)
{
NorthwindDataContext db = new NorthwindDataContext();
IQueryable queryAfter = db.DeserializeQuery(xml);
return queryAfter.Cast<object>().ToArray();
}
private void PopulateEmployees()
{
ViewData["employees"] = new NorthwindDataContext().Employees
.Select(e => new { Id = e.EmployeeID, Name = e.FirstName + " " + e.LastName })
.OrderBy(e => e.Name);
}
