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();
}
}
public object Query(string x_name, [FromRequest] NameValueCollection form)
{
XmlCommandItem command = GetXmlCommand(x_name);
Dictionary <string, object> args = null;
if (command.Parameters != null || command.Parameters.Count > 0)
{
args = GetCommandParameters(command, form);
}
XmlCommand xmlCommand = XmlCommand.Create(x_name, args);
DataTable table = xmlCommand.ToDataTable();
// 处理输出参数，回填到参数字典中返回给前端
BackFillOutputArgs(xmlCommand, args);
return(new { args = args, data = table });
}
public void Test_BaseCommand_CloneParameters()
{
var newCustomer = new {
CustomerName = s_newName,
ContactName = Guid.NewGuid().ToString(),
Address = "111111 Address",
PostalCode = "111111",
Tel = "123456789"
};
using (ConnectionScope scope = ConnectionScope.Create()) {
XmlCommand command = XmlCommand.Create("InsertCustomer", newCustomer);
DbParameter[] parameters1 = command.Command.Parameters.Cast <DbParameter>().ToArray();
DbParameter[] parameters2 = command.CloneParameters();
AssertAreEqual_DbParameterArray(parameters1, parameters2);
}
}
public void Test_XmlCommand_SubQuery()
{
using (ConnectionScope scope = ConnectionScope.Create()) {
CPQuery subQuery = CPQuery.Create() + "Tel like " + "021%".AsQueryParameter();
int[] customerIdArray = { 1, 2, 3, 4, 5 };
// 注意：下面二个参数名，它们只是SQL语句中的占位符，在替换时是区分大小写的。
var queryArgument = new {
table = "dbo.Customers",
CustomerID = customerIdArray,
filter = subQuery
};
XmlCommand query = XmlCommand.Create("FindCustomers", queryArgument);
string commandText = query.Command.CommandText;
Console.WriteLine(commandText);
// 断言占位符已被替换
Assert.AreEqual(
"select * from dbo.Customers where CustomerID in (1,2,3,4,5) and Tel like @p1",
commandText
);
// 断言参数已产生
Assert.AreEqual(1, query.Command.Parameters.Count);
List <Customer> list = query.ToList <Customer>();
// 这里不需要检查 list 的结果，因为结果不重要，只要能正确拼接成有效的SQL就行了。
Assert.IsNotNull(list);
Console.WriteLine("list.Count: " + list.Count);
foreach (Customer c in list)
{
Assert.IsTrue(c.CustomerID <= 5);
}
}
}
public object Execute(string x_name, [FromRequest] NameValueCollection form)
{
// name 这个参数名太常见，所以就改成 x_name
XmlCommandItem command = GetXmlCommand(x_name);
Dictionary <string, object> args = null;
if (command.Parameters != null || command.Parameters.Count > 0)
{
args = GetCommandParameters(command, form);
}
XmlCommand xmlCommand = XmlCommand.Create(x_name, args);
int result = xmlCommand.ExecuteNonQuery();
// 处理输出参数，回填到参数字典中返回给前端
BackFillOutputArgs(xmlCommand, args);
return(new { args = args, data = result });
}
public void Test_XmlCommand_StringArray()
{
string[] customerIdArray = { "1", "2", "3", "4", "5" };
// 注意：下面二个参数名，它们只是SQL语句中的占位符，在替换时是区分大小写的。
var queryArgument = new { table = "dbo.Customers", CustomerID = customerIdArray };
using (ConnectionScope scope = ConnectionScope.Create()) {
XmlCommand query = XmlCommand.Create("GetCustomerListById", queryArgument);
string commandText = query.Command.CommandText;
Console.WriteLine(commandText);
// 断言占位符已被替换
Assert.AreEqual(
"select * from dbo.Customers where CustomerID in (@x1,@x2,@x3,@x4,@x5)",
commandText
);
// 断言参数已产生
Assert.AreEqual(5, query.Command.Parameters.Count);
}
}
public object Paging(string x_name, int pageIndex, int pageSize, [FromRequest] NameValueCollection form)
{
XmlCommandItem command = GetXmlCommand(x_name);
Dictionary <string, object> args = null;
if (command.Parameters != null || command.Parameters.Count > 0)
{
args = GetCommandParameters(command, form);
}
PagingInfo info = new PagingInfo {
PageIndex = pageIndex, PageSize = pageSize
};
XmlCommand xmlCommand = XmlCommand.Create(x_name, args);
DataTable table = xmlCommand.ToPageTable(info);
// 处理输出参数，回填到参数字典中返回给前端
BackFillOutputArgs(xmlCommand, args);
return(new { args = args, paging = info, data = table });
}
public void Test_XmlCommand_IntArray()
{
int[] customerIdArray = { 1, 2, 3, 4, 5 };
// 注意：下面二个参数名，它们只是SQL语句中的占位符，在替换时是区分大小写的。
var queryArgument = new { table = "dbo.Customers", CustomerID = customerIdArray };
using (ConnectionScope scope = ConnectionScope.Create()) {
XmlCommand query = XmlCommand.Create("GetCustomerListById", queryArgument);
string commandText = query.Command.CommandText;
Console.WriteLine(commandText);
// 断言占位符已被替换
Assert.AreEqual(
// 注意：int[] 不会生成命令参数，将直接输出到SQL中
"select * from dbo.Customers where CustomerID in (1,2,3,4,5)",
commandText
);
// 断言参数已产生
Assert.AreEqual(0, query.Command.Parameters.Count);
}
}
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();
}
}