private static void SimpleOrderQueries()
{
using (var db = new SaleContext())
{
db.Database.Log = Console.WriteLine;
var orders = db.Orders.ToList();
foreach (var order in orders)
{
Console.WriteLine("List all orders - order Id:{0}, order cost:{1}", order.Id, order.Cost);
}
orders = db.Orders.Where(x => x.Cost > 250).ToList();
foreach (var order in orders)
{
Console.WriteLine("List cost > 250 orders - Id:{0}, order cost:{1}", order.Id, order.Cost);
}
int maxCost = 250;
orders = db.Orders.Where(x => x.Cost > maxCost).ToList();
foreach (var order in orders)
{
Console.WriteLine("List cost > 250 orders - Id:{0}, order cost:{1}", order.Id, order.Cost);
}
}
}
public static List <Employee> GetList()
{
using (SaleContext db = new SaleContext())
{
return(db.Employee.ToList());
}
}
/// <summary>
/// 按照附件主键id进行删除
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static bool DeleteById(int id, Action removeAction)
{
using (SaleContext db = new SaleContext())
{
// 开始事务
var tran = db.Database.BeginTransaction();
try
{
var entity = db.Attachment.Find(id);
db.Attachment.Remove(entity);
db.SaveChanges();
// 调用委托方法，执行删除后的清理工作
removeAction.Invoke();
// 确认无误，进行事务提交
tran.Commit();
return(true);
}
catch (Exception ex)
{
// 发生异常进行回滚
tran.Rollback();
Log4Helper.ErrorLog.Error(ex);
return(false);
}
}
}
public static void Main()
{
using (var db = new SaleContext())
{
db.Database.EnsureCreated();
}
}
///<inheritdoc/>
public bool TryLogIn(Guid promoCode)
{
using (var db = new SaleContext())
{
return(db.Users.Any(x => x.PromoCode == promoCode));
}
}
/// <summary>
/// 更新用户信息，但不包含头像，头像单独进行更新
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static bool UpdateInfo(User entity)
{
using (SaleContext db = new SaleContext())
{
var dbEntity = db.Users.Find(entity.ID);
if (null == dbEntity)
{
Log4Helper.InfoLog.Warn("未找到该user id：" + entity.ID);
return(false);
}
else
{
dbEntity.Name = entity.Name;
dbEntity.Address = entity.Address;
dbEntity.BirthDate = entity.BirthDate;
dbEntity.Gender = entity.Gender;
// 用户角色和头像需要单独进行设置，TODO
//dbEntity.Roles = entity.Roles;
//dbEntity.HeadImg = entity.HeadImg;
db.SaveChanges();
return(true);
}
}
}
public static List <Product> GetList()
{
using (SaleContext db = new SaleContext())
{
return(db.Product.ToList());
}
}
public static void Main(string[] args)
{
using (SaleContext saleContext = new SaleContext())
{
saleContext.Database.Migrate();
}
}
public static List <Department> GetList()
{
using (SaleContext db = new SaleContext())
{
return(db.Department.ToList());
}
}
public static Record Parse(string line, SaleContext context)
{
var data = line.Split(';');
if (data.Length < 4)
{
return(null);
}
DateTime date;
decimal sum;
bool noErrors = DateTime.TryParse(data[0], out date);
noErrors = (noErrors & decimal.TryParse(data[3], out sum));
string clientName = data[1];
string productName = data[2];
if (!noErrors)
{
return(null);
}
else
{
Record record = new Record();
Client client;
Product product;
if (!context.ClientExists(c => c.Name == clientName))
{
client = new Client()
{
Name = clientName
};
context.Clients.Add(client);
}
else
{
client = context.GetClient(c => c.Name == clientName);
}
if (!context.ProductExists(p => p.Name == productName))
{
product = new Product()
{
Name = productName
};
context.Products.Add(product);
}
else
{
product = context.GetProduct(p => p.Name == productName);
}
record.Date = date;
record.Sum = sum;
record.Client = client;
record.Product = product;
return(record);
}
}
/// <summary>
/// 根据主键id获取实体对象
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Attachment Get(int id)
{
using (SaleContext db = new SaleContext())
{
var res = db.Attachment.Find(id);
return(res);
}
}
/// <summary>
/// 根据关联资源的主键id获取所有附件信息
/// </summary>
/// <param name="relatedID"></param>
/// <returns></returns>
public static List <Attachment> GetListByRelatedId(int relatedID)
{
using (SaleContext db = new SaleContext())
{
List <Attachment> list = db.Attachment.Where(t => t.RelatedID == relatedID).ToList();
return(list);
}
}
private static void AddToDb(System.IO.FileSystemEventArgs e, SaleContext context)
{
FileParser.FileParser parser = new FileParser.FileParser(e.FullPath, context);
File file = parser.Parse();
context.Files.Add(file);
context.SaveChanges();
}
/// <summary>
/// 根据主键获取用户实体对象
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static User GetUserById(int id)
{
using (SaleContext db = new SaleContext())
{
User res = db.Users.Find(id);
return(res);
}
}
/// <summary>
/// 根据产品编号返回产品对象
/// 如果不存在该产品则返回null
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public static Product GetProductById(int id)
{
using (SaleContext db = new SaleContext())
{
var res = db.Product.Find(id);
return(res);
}
}
public static Customer GetCustomerById(int id)
{
using (SaleContext db = new SaleContext())
{
var entity = db.Customer.Find(id);
return(entity);
}
}
public static Employee GetEmployeeById(int id)
{
using (SaleContext db = new SaleContext())
{
var entity = db.Employee.Find(id);
return(entity);
}
}
/// <summary>
/// 添加员工操作
/// </summary>
/// <param name="emp"></param>
/// <returns></returns>
public static bool Add(Employee emp)
{
using (SaleContext db = new SaleContext())
{
db.Employee.Add(emp);
db.SaveChanges();
return(true);
}
}
public static bool AddProduct(Product entity)
{
using (SaleContext db = new SaleContext())
{
db.Product.Add(entity);
db.SaveChanges();
return(true);
}
}
public static bool AddUser(User user)
{
using (SaleContext db = new SaleContext())
{
db.Users.Add(user);
db.SaveChanges();
return(true);
}
}
public static bool Add(Customer entity)
{
using (SaleContext db = new SaleContext())
{
db.Customer.Add(entity);
db.SaveChanges();
return(true);
}
}
///<inheritdoc/>
public OrderDto GetOrder(Guid promoCode)
{
using (var db = new SaleContext())
{
var orderId = db.Users.FirstOrDefault(x => x.PromoCode == promoCode)?.Id;
var dbOrder = db.Orders.FirstOrDefault(x => x.Id == orderId);
return(Mapping.Mapping.OrderToOrderDtoMapping(dbOrder));
}
}
/// <summary>
/// 添加附件资源
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static bool Add(Attachment entity)
{
using (SaleContext db = new SaleContext())
{
db.Attachment.Add(entity);
db.SaveChanges();
return(true);
}
}
public static bool BatchDelete(string[] ids)
{
using (SaleContext db = new SaleContext())
{
var list = db.Product.Where(t => ids.Contains(t.ProductID.ToString()));
db.Product.RemoveRange(list);
db.SaveChanges();
return(true);
}
}
public static bool Delete(int id)
{
using (SaleContext db = new SaleContext())
{
var entity = db.Customer.Where(t => t.CustomerId.Equals(id)).First();
db.Customer.Remove(entity);
db.SaveChanges();
return(true);
}
}
public static bool Delete(int id)
{
using (SaleContext db = new SaleContext())
{
var entity = db.Employee.Find(id);
db.Employee.Remove(entity);
db.SaveChanges();
return(true);
}
}
public static User Login(string uid, string pwd)
{
using (SaleContext db = new SaleContext())
{
var res = db.Users
.Where(t => t.UserId == uid && t.Password == pwd)
.FirstOrDefault();
return(res);
}
}
/// <summary>
/// 重新数据库，执行【InitTables.sql】脚本
/// </summary>
/// <returns></returns>
public static bool RebuildData()
{
string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "db_Script", "InitTables.sql");
string sql = File.ReadAllText(path);
using (SaleContext db = new SaleContext())
{
db.Database.ExecuteSqlCommand(sql);
return(true);
}
}
/// <summary>
/// 更新用户头像
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public static bool UpdateImage(User entity)
{
using (SaleContext db = new SaleContext())
{
var dbEntity = db.Users.Find(entity.ID);
dbEntity.HeadImg = entity.HeadImg;
db.SaveChanges();
return(true);
}
}
///<inheritdoc/>
public IEnumerable <BookDto> GetBooks()
{
using (var db = new SaleContext())
{
var dbBooks = db.Books.AsNoTracking().ToList();
var books = new List <BookDto>();
foreach (var dbBook in dbBooks)
{
books.Add(Mapping.Mapping.BookToBookDtoMapping(dbBook));
}
return(books);
}
}
