public void Test_ConnectionScope事务中使用StoreProcedure_XmlCommand_CPQuery()
{
using (ConnectionScope scope = ConnectionScope.Create()) {
scope.BeginTransaction();
var newCustomer = new {
// 下行代码创建一个输出参数
CustomerID = scope.Context.CreateOutParameter(DbType.Int32),
CustomerName = Guid.NewGuid().ToString(),
ContactName = Guid.NewGuid().ToString(),
Address = "111111 Address",
PostalCode = "111111",
Tel = "123456789"
};
// 插入一条记录
StoreProcedure.Create("InsertCustomer", newCustomer).ExecuteNonQuery();
// 获取输出参数的返回值
int newCustomerId = (int)newCustomer.CustomerID.Value;
var queryArgument = new { CustomerID = newCustomerId };
Customer customer1 = XmlCommand.Create("GetCustomerById", queryArgument).ToSingle <Customer>();
Customer customer2 = StoreProcedure.Create("GetCustomerById", queryArgument).ToSingle <Customer>();
string sql = CPQueryTest.GetSql("GetCustomerById");
Customer customer3 = CPQuery.Create(sql, queryArgument).ToSingle <Customer>();
Assert.AreEqual(customer1.CustomerID, customer2.CustomerID);
Assert.AreEqual(customer1.CustomerID, customer3.CustomerID);
Assert.AreEqual(customer1.CustomerName, customer2.CustomerName);
Assert.AreEqual(customer1.CustomerName, customer3.CustomerName);
Assert.AreEqual(customer1.Address, customer2.Address);
Assert.AreEqual(customer1.Address, customer3.Address);
scope.Commit();
}
}
private void 一般用法介绍()
{
string connectionName = "default";
IsolationLevel isolation = IsolationLevel.ReadCommitted;
string name = "command1";
string sql = "select * from table1 where .....";
object args = new { a = 1, b = 2 };
// 基本数据库操作
using (DbContext db = DbContext.Create(connectionName)) {
db.BeginTransaction(isolation); // 开启事务，并允许设置事务隔离级别（可选）
db.XmlCommand.Create(name, args).ExecuteNonQuery();
db.CPQuery.Create(sql, args).ExecuteNonQuery();
db.StoreProcedure.Create(name, args).ExecuteNonQuery();
db.Commit();
}
// 封装 DbContext ，允许跨方法调用
using (ConnectionScope scope = ConnectionScope.Create(connectionName)) {
scope.BeginTransaction(isolation); // 开启事务，并允许设置事务隔离级别（可选）
// 下面三个调用，调用了三个静态工厂方法，
// 因此可以放在不同的方法中，不必传递 scope 变量
XmlCommand.Create(name, args).ExecuteNonQuery();
CPQuery.Create(sql, args).ExecuteNonQuery();
StoreProcedure.Create(name, args).ExecuteNonQuery();
scope.Commit();
}
// DbContext/ConnectionScope 其它创建方法
DbContext c1 = DbContext.Create("connectionString", "providerName");
ConnectionScope c2 = ConnectionScope.Create("connectionString", "providerName");
// 甚至可以由显式转换来实现：
DbContext c3 = (DbContext)"connectionString";
// 嵌套使用（一段代码访问不同数据源）
using (ConnectionScope scope = ConnectionScope.Create("connectionName_1")) {
XmlCommand.Create(name, args).ExecuteNonQuery();
using (DbContext db = DbContext.Create("connectionName_2")) {
db.XmlCommand.Create(name, args).ExecuteNonQuery();
}
}
/*
* Execute 包含的操作
*
* abstract class BaseCommand {
* ExecuteNonQuery()
* ExecuteScalar<T>()
* ToScalarList<T>()
* ToSingle<T>()
* ToList<T>()
* ToDataTable()
* ToDataSet()
*
* ExecuteNonQueryAsync()
* ExecuteScalarAsync<T>()
* ToScalarListAsync<T>()
* ToSingleAsync<T>()
* ToListAsync<T>()
* ToDataTableAsync()
* ToDataSetAsync()
* }
*/
/*
* // 从DataTable中加载数据
* class TableExtensions{
* ToList<T>(DataTable);
* ToSingle<T>(DataRow);
* }
*/
// 嵌套使用，允许：XmlCommnad 包含 CPQuery， CPQuery 包含 CPQuery
using (DbContext db = DbContext.Create(connectionName)) {
CPQuery subQuery1 = db.CPQuery.Create(name, args);
CPQuery subQuery2 = db.CPQuery.Create(name, args);
db.XmlCommand.Create(
"select * from t1 where id=@id and {filter1} and {filter2}",
new {
id = 2,
filter1 = subQuery1,
filter2 = subQuery2
}
).ExecuteNonQuery();
db.XmlCommand.Create(
"select * from t1 where id=@id and {filter1} and {filter2}",
new {
id = 2,
filter1 = CPQuery.Create(name, args),
filter2 = CPQuery.Create(name, args)
}
).ExecuteNonQuery();
}
}
