SQL查询

ClownFish提供 CPQuery 的类型可用于直接执行SQL语句，具体场景可分为：

• 固定的参数化SQL： 可参考下面的示例
• 根据运行中的数据，可动态拼接参数化SQL，可参考

CPQuery 示例

CPQuery: Concat Parameterized Query，

设计意图可参考：CPQuery, 解决拼接SQL的新方法

使用示例：

// INSERT
using( DbContext dbContext = DbContext.Create(connName) ) {
    string sql = "insert into Customers(CustomerName, ContactName, Address, PostalCode, Tel) 
                  values(@CustomerName, @ContactName, @Address, @PostalCode, @Tel)";

    var args = new Customer{ CustomerName = "aa",  ContactName = "bb", Address = "cc", 
                            PostalCode = "430076",  Tel = "123454678"  };
    return await dbContext.CPQuery.Create(sql, args).ExecuteNonQueryAsync();
}

// QUERY
using( DbContext dbContext = DbContext.Create(connName) ) {
    string sql = "select * from Customers where CustomerID = @CustomerID";
    var args = new { CustomerID = 1 };
    return await dbContext.CPQuery.Create(sql, args).ToSingleAsync<Customer>();
}


// UPDATE
using( DbContext dbContext = DbContext.Create(connName) ) {
    string sql = "update Customers set Address = @Address where CustomerID = @CustomerID";
    var args = new { Address = "xxxxxxxx",  CustomerID = 1 };
    return await dbContext.CPQuery.Create(sql, args).ExecuteNonQueryAsync();
}

// DELETE
using( DbContext dbContext = DbContext.Create(connName) ) {
    string sql = "delete from Customers where CustomerID = @CustomerID";
    var args = new { CustomerID = 1 };
    return await dbContext.CPQuery.Create(sql, args).ExecuteNonQueryAsync();
}

本文只演示了2种执行方法：ExecuteNonQueryAsync、ToSingleAsync，
更多执行方法可参考：执行命令