protected override void UpdatingItem(PurchaseOrder newVal, PurchaseOrder original, System.Data.Linq.DataContext dc)
{
dc.GetTable <PurchaseOrder>().Attach(newVal, original);
foreach (PurchaseItem item in newVal.Items)
{
PurchaseItem old = original.Items.SingleOrDefault(it => it.ID == item.ID);
if (old != null)
{
dc.GetTable <PurchaseItem>().Attach(item, old);
}
else
{
item.PurchaseID = newVal.ID;
dc.GetTable <PurchaseItem>().InsertOnSubmit(item);
}
}
foreach (PurchaseItem item in original.Items)
{
if (newVal.Items.SingleOrDefault(it => it.ID == item.ID) == null)
{
dc.GetTable <PurchaseItem>().Attach(item);
dc.GetTable <PurchaseItem>().DeleteOnSubmit(item);
}
}
}
private void RefreshBrandAdvices(Brand brand)
{
var brandAdvices = (EntitySet <BrandAdvice>)brand.BrandAdvices;
var tempContext = new DataContext(Context.Connection);
if (brandAdvices.HasLoadedOrAssignedValues)
{
List <BrandAdvice> allBrandAdvices =
tempContext.GetTable <AdviceBase>().OfType <BrandAdvice>().Where(x => x.BrandsId == brand.Id).ToList();
//Context.GetTableForType(typeof (BrandAdvice)).OfType<BrandAdvice>().Where(
// a => a.BrandsId == brand.Id).ToList();))
foreach (BrandAdvice brandAdvice in allBrandAdvices.Except(brand.BrandAdvices))
{
brand.BrandAdvices.Add(brandAdvice);
}
foreach (BrandAdvice brandAdvice in brand.BrandAdvices.Except(allBrandAdvices))
{
brand.BrandAdvices.Remove(brandAdvice);
}
//Refresh EntityRefs
foreach (BrandAdvice brandAdvice in brand.BrandAdvices)
{
Context.Refresh(RefreshMode.KeepChanges, brandAdvice);
Context.Refresh(RefreshMode.KeepChanges, brandAdvice.Mentor);
Context.Refresh(RefreshMode.KeepChanges, brandAdvice.Semaphore);
}
}
}
protected override List <Product> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <Product>(p => p.Category);
dc.LoadOptions = opt;
IQueryable <Product> ret = dc.GetTable <Product>();
if (search is ProductSearchCondition)
{
ProductSearchCondition con = search as ProductSearchCondition;
if (con.ProductIDS != null && con.ProductIDS.Count > 0)
{
ret = ret.Where(item => con.ProductIDS.Contains(item.ID));
}
if (!string.IsNullOrEmpty(con.Name))
{
ret = ret.Where(item => item.Name.Contains(con.Name));
}
if (!string.IsNullOrEmpty(con.BarCode))
{
ret = ret.Where(item => item.BarCode.Contains(con.BarCode));
}
if (!string.IsNullOrEmpty(con.CategoryID))
{
ret = ret.Where(item => item.CategoryID == con.CategoryID);
}
if (!string.IsNullOrEmpty(con.Specification))
{
ret = ret.Where(item => item.Specification.Contains(con.Specification));
}
}
return(ret.ToList());
}
protected override List <InventoryCheckRecord> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
IQueryable <InventoryCheckRecord> ret = dc.GetTable <InventoryCheckRecord>();
List <InventoryCheckRecord> items = ret.ToList();
return(items);
}
protected override List <StackOutSheet> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <StackOutSheet>(item => item.Items);
dc.LoadOptions = opt;
IQueryable <StackOutSheet> ret = dc.GetTable <StackOutSheet>();
if (search is SheetSearchCondition)
{
SheetSearchCondition con = search as SheetSearchCondition;
if (con.SheetDate != null)
{
ret = ret.Where(item => item.SheetDate >= con.SheetDate.Begin && item.SheetDate <= con.SheetDate.End);
}
if (con.LastActiveDate != null)
{
ret = ret.Where(item => item.LastActiveDate >= con.LastActiveDate.Begin && item.LastActiveDate <= con.LastActiveDate.End);
}
if (con.SheetNo != null && con.SheetNo.Count > 0)
{
ret = ret.Where(item => con.SheetNo.Contains(item.ID));
}
if (con.States != null && con.States.Count > 0)
{
ret = ret.Where(item => con.States.Contains(item.State));
}
}
if (search is StackOutSheetSearchCondition)
{
StackOutSheetSearchCondition con = search as StackOutSheetSearchCondition;
if (!string.IsNullOrEmpty(con.CustomerID))
{
ret = ret.Where(item => item.CustomerID == con.CustomerID);
}
if (!string.IsNullOrEmpty(con.WareHouseID))
{
ret = ret.Where(item => item.WareHouseID == con.WareHouseID);
}
if (con.SheetTypes != null && con.SheetTypes.Count > 0)
{
ret = ret.Where(item => con.SheetTypes.Contains(item.ClassID));
}
if (con.WithTax != null)
{
if (con.WithTax.Value)
{
ret = ret.Where(item => item.WithTax == true);
}
else
{
ret = ret.Where(item => item.WithTax == false);
}
}
}
List <StackOutSheet> sheets = ret.ToList();
return(sheets);
}
protected override Product GetingItemByID(string id, System.Data.Linq.DataContext dc)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <Product>(p => p.Category);
dc.LoadOptions = opt;
return(dc.GetTable <Product>().SingleOrDefault(item => item.ID == id));
}
protected override OrderItemRecord GetingItemByID(Guid id, System.Data.Linq.DataContext dc)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <OrderItemRecord>(item => item.Customer);
opt.LoadWith <OrderItemRecord>(item => item.Product);
opt.LoadWith <Product>(item => item.Category);
dc.LoadOptions = opt;
return(dc.GetTable <OrderItemRecord>().SingleOrDefault(item => item.ID == id));
}
protected override StackInSheet GetingItemByID(string id, System.Data.Linq.DataContext dc)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <StackInSheet>(s => s.Items);
dc.LoadOptions = opt;
StackInSheet sheet = dc.GetTable <StackInSheet>().SingleOrDefault(item => item.ID == id);
return(sheet);
}
protected override PurchaseOrder GetingItemByID(string id, System.Data.Linq.DataContext dc)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <PurchaseOrder>(item => item.Items);
dc.LoadOptions = opt;
PurchaseOrder sheet = dc.GetTable <PurchaseOrder>().SingleOrDefault(item => item.ID == id);
return(sheet);
}
protected override List <CustomerPayment> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
IQueryable <CustomerPayment> ret = dc.GetTable <CustomerPayment>();
if (search is SheetSearchCondition)
{
SheetSearchCondition con = search as SheetSearchCondition;
if (con.SheetDate != null)
{
ret = ret.Where(item => item.SheetDate >= con.SheetDate.Begin && item.SheetDate <= con.SheetDate.End);
}
if (con.LastActiveDate != null)
{
ret = ret.Where(item => item.LastActiveDate >= con.LastActiveDate.Begin && item.LastActiveDate <= con.LastActiveDate.End);
}
if (con.SheetNo != null && con.SheetNo.Count > 0)
{
ret = ret.Where(item => con.SheetNo.Contains(item.ID));
}
if (con.States != null && con.States.Count > 0)
{
ret = ret.Where(item => con.States.Contains(item.State));
}
}
if (search is CustomerPaymentSearchCondition)
{
CustomerPaymentSearchCondition con = search as CustomerPaymentSearchCondition;
if (!string.IsNullOrEmpty(con.CustomerID))
{
ret = ret.Where(item => item.CustomerID == con.CustomerID);
}
if (con.PaymentTypes != null && con.PaymentTypes.Count > 0)
{
ret = ret.Where(item => con.PaymentTypes.Contains(item.ClassID));
}
if (con.HasRemain != null)
{
if (con.HasRemain.Value)
{
ret = ret.Where(item => item.Assigned < item.Amount);
}
else
{
ret = ret.Where(item => item.Assigned >= item.Amount);
}
}
}
List <CustomerPayment> sheets = ret.ToList();
return(sheets);
}
protected override StudentScore GetingItemByID(long id, System.Data.Linq.DataContext dc)
{
return(dc.GetTable <StudentScore>().SingleOrDefault(item => item.ID == id));
}
protected override List <OrderItemRecord> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <OrderItemRecord>(item => item.Customer);
opt.LoadWith <OrderItemRecord>(item => item.Product);
dc.LoadOptions = opt;
IQueryable <OrderItemRecord> ret = dc.GetTable <OrderItemRecord>();
if (search is SheetSearchCondition)
{
SheetSearchCondition con = search as SheetSearchCondition;
if (con.LastActiveDate != null)
{
ret = ret.Where(item => item.LastActiveDate >= con.LastActiveDate.Begin && item.LastActiveDate <= con.LastActiveDate.End);
}
if (con.SheetNo != null && con.SheetNo.Count > 0)
{
ret = ret.Where(item => con.SheetNo.Contains(item.SheetNo));
}
if (con.States != null && con.States.Count > 0)
{
ret = ret.Where(item => con.States.Contains(item.State));
}
}
if (search is OrderSearchCondition)
{
OrderSearchCondition con = search as OrderSearchCondition;
if (!string.IsNullOrEmpty(con.CustomerID))
{
ret = ret.Where(item => item.CustomerID == con.CustomerID);
}
if (!string.IsNullOrEmpty(con.Sales))
{
ret = ret.Where(item => item.SalesPerson == con.Sales);
}
if (con.WithTax != null)
{
if (con.WithTax.Value)
{
ret = ret.Where(item => item.WithTax == true);
}
else
{
ret = ret.Where(item => item.WithTax == false);
}
}
}
if (search is OrderItemRecordSearchCondition)
{
OrderItemRecordSearchCondition con = search as OrderItemRecordSearchCondition;
if (!string.IsNullOrEmpty(con.ProductID))
{
ret = ret.Where(item => item.ProductID == con.ProductID);
}
if (!string.IsNullOrEmpty(con.CategoryID))
{
ret = ret.Where(item => item.Product.CategoryID == con.CategoryID);
}
}
List <OrderItemRecord> items = ret.ToList();
if (search is OrderItemRecordSearchCondition) //从数据库获取回来数据后再筛选
{
OrderItemRecordSearchCondition con = search as OrderItemRecordSearchCondition;
if (con.HasToDelivery != null)
{
if (con.HasToDelivery.Value)
{
items = items.Where(item => item.NotShipped > 0).ToList();
}
else
{
items = items.Where(item => item.NotShipped == 0).ToList();
}
}
if (con.HasToPurchase != null)
{
if (con.HasToPurchase.Value)
{
items = items.Where(item => item.NotPurchased > 0).ToList();
}
else
{
items = items.Where(item => item.NotPurchased == 0).ToList();
}
}
}
return(items);
}
protected override List <StackOutRecord> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <StackOutRecord>(item => item.Customer);
opt.LoadWith <StackOutRecord>(item => item.WareHouse);
opt.LoadWith <StackOutRecord>(item => item.Product);
opt.LoadWith <Product>(item => item.Category);
dc.LoadOptions = opt;
IQueryable <StackOutRecord> ret = dc.GetTable <StackOutRecord>();
if (search is SheetSearchCondition)
{
SheetSearchCondition con = search as SheetSearchCondition;
if (con.LastActiveDate != null)
{
ret = ret.Where(item => item.LastActiveDate >= con.LastActiveDate.Begin && item.LastActiveDate <= con.LastActiveDate.End);
}
if (con.SheetNo != null && con.SheetNo.Count > 0)
{
ret = ret.Where(item => con.SheetNo.Contains(item.SheetNo));
}
if (con.States != null && con.States.Count > 0)
{
ret = ret.Where(item => con.States.Contains(item.State));
}
}
if (search is StackOutSheetSearchCondition)
{
StackOutSheetSearchCondition con = search as StackOutSheetSearchCondition;
if (!string.IsNullOrEmpty(con.CustomerID))
{
ret = ret.Where(item => item.CustomerID == con.CustomerID);
}
if (!string.IsNullOrEmpty(con.WareHouseID))
{
ret = ret.Where(item => item.WareHouseID == con.WareHouseID);
}
if (con.SheetTypes != null && con.SheetTypes.Count > 0)
{
ret = ret.Where(item => con.SheetTypes.Contains(item.ClassID));
}
}
if (search is StackOutRecordSearchCondition)
{
StackOutRecordSearchCondition con = search as StackOutRecordSearchCondition;
if (!string.IsNullOrEmpty(con.ProductID))
{
ret = ret.Where(item => item.ProductID == con.ProductID);
}
if (!string.IsNullOrEmpty(con.CategoryID))
{
ret = ret.Where(item => item.Product.CategoryID == con.CategoryID);
}
if (!string.IsNullOrEmpty(con.OrderID))
{
ret = ret.Where(item => item.OrderID == con.OrderID);
}
if (con.OrderItem != null)
{
ret = ret.Where(item => item.OrderItem == con.OrderItem);
}
}
List <StackOutRecord> items = ret.ToList();
return(items);
}
private void Button_Click(object sender, RoutedEventArgs e)
{
type = (comboBox.SelectedItem as ListBoxItem).Content.ToString();
table = (listBox.SelectedItem as ListBoxItem).Content.ToString();
switch (type)
{
case "ADO.Net":
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=db_labs;Integrated Security=true"))
{
SqlCommand command = null;
switch (table)
{
case "client":
command = new SqlCommand("SELECT * from labs.client", connection);
break;
case "device":
command = new SqlCommand("SELECT * from labs.device", connection);
break;
case "order":
command = new SqlCommand("SELECT * from labs.ordr", connection);
break;
}
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
_collection.Clear();
while (reader.Read())
{
switch (table)
{
case "client":
_collection.Add(new { first_name = reader[1], last_name = reader[2], registration_date = reader[3], phone = reader[4] });
break;
case "device":
_collection.Add(new { name = reader[1], description = reader[2], category = reader[3], location = reader[4] });
break;
case "order":
_collection.Add(new { client = reader[1], price = reader[2], employee = reader[3], delivery = reader[4] });
break;
}
}
reader.Close();
dataGrid.ItemsSource = null;
dataGrid.ItemsSource = _collection;
}
catch (Exception ex)
{
}
}
break;
case "LINQ to SQL":
DataContext context = new System.Data.Linq.DataContext("Data Source=(local);Initial Catalog=db_labs;Integrated Security=true");
_collection.Clear();
switch (table)
{
case "client":
var clients = from c in context.GetTable <client>() select c;
foreach (client c in clients)
{
_collection.Add(new { first_name = c.first_name, last_name = c.last_name, registration_date = c.registration_date, phone = c.phone });
}
break;
case "device":
var devices = from c in context.GetTable <device>() select c;
foreach (var d in devices)
{
_collection.Add(new { name = d.name, description = d.description, category = d.category, location = d.location });
}
break;
case "order":
var orders = from c in context.GetTable <ordr>() select c;
foreach (var o in orders)
{
_collection.Add(new { client = o.client, price = o.price, employee = o.employee, delivery = o.delivery });
}
break;
}
dataGrid.ItemsSource = null;
dataGrid.ItemsSource = _collection;
break;
case "ADO.Net EntityFramework":
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=db_labs;Integrated Security=true"))
{
SqlCommand command = null;
switch (table)
{
case "client":
command = new SqlCommand("SELECT * from labs.client", connection);
break;
case "device":
command = new SqlCommand("SELECT * from labs.device", connection);
break;
case "order":
command = new SqlCommand("SELECT * from labs.ordr", connection);
break;
}
try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
_collection.Clear();
while (reader.Read())
{
switch (table)
{
case "client":
_collection.Add(new { first_name = reader[1], last_name = reader[2], registration_date = reader[3], phone = reader[4] });
break;
case "device":
_collection.Add(new { name = reader[1], description = reader[2], category = reader[3], location = reader[4] });
break;
case "order":
_collection.Add(new { client = reader[1], price = reader[2], employee = reader[3], delivery = reader[4] });
break;
}
}
reader.Close();
dataGrid.ItemsSource = null;
dataGrid.ItemsSource = _collection;
}
catch (Exception ex)
{
}
}
break;
}
}
protected override InventoryCheckRecord GetingItemByID(Guid id, System.Data.Linq.DataContext dc)
{
return(dc.GetTable <InventoryCheckRecord>().SingleOrDefault(item => item.ID == id));
}
protected override List <PurchaseItemRecord> GetingItems(System.Data.Linq.DataContext dc, SearchCondition search)
{
DataLoadOptions opt = new DataLoadOptions();
opt.LoadWith <PurchaseItemRecord>(item => item.Supplier);
opt.LoadWith <PurchaseItemRecord>(item => item.Product);
dc.LoadOptions = opt;
IQueryable <PurchaseItemRecord> ret = dc.GetTable <PurchaseItemRecord>();
if (search is SheetSearchCondition)
{
SheetSearchCondition con = search as SheetSearchCondition;
if (con.LastActiveDate != null)
{
ret = ret.Where(item => item.LastActiveDate >= con.LastActiveDate.Begin && item.LastActiveDate <= con.LastActiveDate.End);
}
if (con.SheetNo != null && con.SheetNo.Count > 0)
{
ret = ret.Where(item => con.SheetNo.Contains(item.SheetNo));
}
if (con.States != null && con.States.Count > 0)
{
ret = ret.Where(item => con.States.Contains(item.State));
}
}
if (search is PurchaseOrderSearchCondition)
{
PurchaseOrderSearchCondition con = search as PurchaseOrderSearchCondition;
if (!string.IsNullOrEmpty(con.SupplierID))
{
ret = ret.Where(item => item.SupplierID == con.SupplierID);
}
if (!string.IsNullOrEmpty(con.Buyer))
{
ret = ret.Where(item => item.Buyer == con.Buyer);
}
}
if (search is PurchaseItemRecordSearchCondition)
{
PurchaseItemRecordSearchCondition con = search as PurchaseItemRecordSearchCondition;
if (!string.IsNullOrEmpty(con.ProductID))
{
ret = ret.Where(item => item.ProductID == con.ProductID);
}
if (!string.IsNullOrEmpty(con.OrderID))
{
ret = ret.Where(item => item.OrderID == con.OrderID);
}
}
List <PurchaseItemRecord> items = ret.ToList();
if (search is PurchaseItemRecordSearchCondition)
{
PurchaseItemRecordSearchCondition con = search as PurchaseItemRecordSearchCondition;
if (con.HasOnway != null)
{
if (con.HasOnway.Value)
{
items = items.Where(item => item.OnWay > 0).ToList();
}
else
{
items = items.Where(item => item.OnWay == 0).ToList();
}
}
}
return(items);
}
public ITable <TEntity> GetTable <TEntity>() where TEntity : class
{
return(_linqContext.GetTable <TEntity>());
}