实体CURD

本文将演示基于数据实体的CURD操作。

单个实体 CURD / Entity方法版本

public async Task<long> Insert(Customer customer)
{
    using( DbContext dbContext = DbConnManager.CreateAppDb("mysqltest") ) {
        return dbContext.Entity.Insert(customer, InsertOption.GetNewId);
    }
}

public async Task<int> Delete(int id)
{
    using( DbContext dbContext = DbConnManager.CreateAppDb("mysqltest") ) {
        return await dbContext.Entity.DeleteAsync<Customer>(id);
    }
}

public async Task<int> Update(int id, Customer customer)
{
    using( DbContext dbContext = DbConnManager.CreateAppDb("mysqltest") ) {
        customer.CustomerID = id;
        return dbContext.Entity.Update(customer);
    }
}

public async Task<Customer> GetById(int id)
{
    using( DbContext dbContext = DbConnManager.CreateAppDb("mysqltest") ) {
        return await dbContext.Entity.GetByKeyAsync<Customer>(id);
    }
}

注意NULL值

• 上面的 dbContext.Insert(...) / dbContext.Update(...) 在执行时会忽略实体中的 NULL值字段
• 如果需要将空值写入数据库，请使用下面的的【实体代理版本】

单个实体 CURD / 实体代理版本

using( DbContext db = DbConnManager.CreateAppDb("mysqltest") ) {
    // 插入一条记录，只给2个字段赋值
    ModelX obj = db.Entity.CreateProxy<ModelX>();
    obj.IntField = 1978;
    obj.StringField = "abc";
    obj.Insert();

    // 检验刚才插入的数据行
    ModelX m1 = (from x in db.Entity.Query<ModelX>() where x.IntField == 1978 select x).FirstOrDefault();
    Assert.IsNotNull(m1);
    Assert.AreEqual("abc", m1.StringField);

    // m1 进入编辑状态
    m1 = db.Entity.CreateProxy(m1);
    m1.StringField = "12345";
    int effect = m1.Update();        // 提交更新，WHERE过滤条件由主键字段决定
    Assert.AreEqual(1, effect);

    // 检验刚才更新的数据行
    ModelX m2 = (from x in db.Entity.Query<ModelX>() where x.IntField == 1978 select x).FirstOrDefault();
    Assert.IsNotNull(m2);
    Assert.AreEqual("12345", m2.StringField);

    // 删除数据行
    ModelX obj2 = db.Entity.CreateProxy<ModelX>();
    obj2.IntField = 1978;
    effect = obj2.Delete();
    Assert.AreEqual(1, effect);

    // 检验删除结果
    ModelX m3 = (from x in db.Entity.Query<ModelX>() where x.IntField == 1978 select x).FirstOrDefault();
    Assert.IsNull(m3);
}

多行更新和删除

using( DbContext db = DbConnManager.CreateAppDb("mysqltest") ) {

    dbContext.Entity.Update<Product>(
        /* set */    x => { x.Unit = "x"; x.Quantity = 9; }, 
        /* where */  x => x.UnitPrice < 12 && x.CategoryID == 0);

    dbContext.Entity.Delete<Product>(
        /* where */  x => x.UnitPrice < 12 && x.CategoryID == 0);
}

实体批量插入/加载

public async Task<int> BatchInsert()
{
    int newOrderId = 0;

    // 从前端传递回来的主从表对象
    Order order = GetOrderObject(/****/);

    using( DbContext dbContext = DbConnManager.CreateAppDb("mysqltest") ) {

        // 建议在事务中执行批量插入操作
        dbContext.BeginTransaction();

        // 先插入主表记录
        newOrderId = dbContext.Entity.Insert(order, InsertOption.GetNewId);

        // 将 新ID 赋值给各子表实体
        order.Details.ForEach(x=>x.OrderID = newOrderId);

        // 批量插入 子表记录
        await dbContext.Batch.InsertAsync(order.Details);

        dbContext.Commit();
    }

    return newOrderId;
}


public async Task<Order> GetOrderById(int id)
{
    // 查询单个Order实体对象
    Order order = await (from x in dbContext.Entity.Query<Order>()
                            where x.OrderID == id
                            select x).FirstOrDefaultAsync();

    if( order != null )
        // 查询 OrderDetail 列表
        order.Details = await (from x in dbContext.Entity.Query<OrderDetail>()
                                where x.OrderID == id
                                select x).ToListAsync();

    return order;
}