public async Task NotNotEqual()
{
xx = string.Empty;
// !(!=) --> =
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !(it.AgentLevel != AgentLevel.Customer));
Assert.True(res1.Count == 28063 || res1.Count == 28064); // 此处为test
/***********************************************************************************************************************/
xx = string.Empty;
// == --> =
var res2 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.AgentLevel == AgentLevel.DistiAgent)
.OrderBy(it => it.CreatedOn)
.SelectListAsync <AgentVM>();
Assert.True(res2.Count == 555);
/***********************************************************************************************************************/
xx = string.Empty;
}
public async Task SelectVM_SQL()
{
xx = string.Empty;
var sql = @"
select agent12.`PathId` as nn,
record12.`Id` as yy,
agent12.`Id` as xx,
agent12.`Name` as zz,
record12.`LockedCount` as mm
from `agent` as agent12
inner join `agentinventoryrecord` as record12
on agent12.`Id`=record12.`AgentId`
where record12.`CreatedOn`>=@CreatedOn;
";
var param = new List <XParam>
{
new XParam {
ParamName = "@CreatedOn", ParamValue = Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30), ParamType = ParamTypeEnum.DateTime_MySQL_SqlServer
}
};
var res1 = await MyDAL_TestDB.SelectListAsync <AgentVM>(sql, param);
Assert.True(res1.Count == 574);
Assert.True("~00-d-3-2-1-c-2-1a-1" == res1.First().nn);
xx = string.Empty;
}
public async Task NotLessThanOrEqual()
{
xx = string.Empty;
// !(<=) --> >
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !(it.CreatedOn <= DateTime.Parse("2018-08-16 19:20:35.867228")));
Assert.True(res1.Count == 21778);
/***********************************************************************************************************************************************/
xx = string.Empty;
// > --> >
var res2 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.CreatedOn > DateTime.Parse("2018-08-16 19:20:35.867228"));
Assert.True(res2.Count == 21778);
/***********************************************************************************************************************************************/
xx = string.Empty;
}
private async Task <Agent> PreData01()
{
return(await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.Id == Guid.Parse("0ce552c0-2f5e-4c22-b26d-01654443b30e"))
.SelectOneAsync());
}
public async Task SelectSingleColumn_SQL()
{
xx = string.Empty;
var sql = @"
select agent1.`CreatedOn`
from `agent` as agent1
inner join `agentinventoryrecord` as record1
on agent1.`Id`=record1.`AgentId`
where agent1.`AgentLevel`=@AgentLevel;
";
var paras = new List <XParam>
{
new XParam {
ParamName = "AgentLevel", ParamValue = AgentLevel.DistiAgent, ParamType = ParamTypeEnum.Int_MySQL_SqlServer
}
};
var res1 = await MyDAL_TestDB.SelectListAsync <DateTime>(sql, paras);
Assert.True(res1.Count == 574);
xx = string.Empty;
}
public async Task NotEqual()
{
xx = string.Empty;
// !(==) --> <>
var res1 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => !(it.AgentLevel == AgentLevel.DistiAgent))
.SelectListAsync();
Assert.True(res1.Count == 28064 || res1.Count == 28065);
/********************************************************************************************************************************/
xx = string.Empty;
// != --> <>
var res2 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.AgentLevel != AgentLevel.DistiAgent)
.SelectListAsync();
Assert.True(res2.Count == 28064 || res2.Count == 28065);
/********************************************************************************************************************************/
xx = string.Empty;
}
private async Task xxx(Guid id)
{
xx = string.Empty;
// where method parameter
var res1 = await MyDAL_TestDB.SelectOneAsync <Agent>(it => it.Id == id);
Assert.NotNull(res1);
xx = string.Empty;
// where method parameter
var resR1 = await MyDAL_TestDB.SelectOneAsync <Agent>(it => id == it.Id);
Assert.NotNull(resR1);
Assert.True(res1.Id.Equals(Guid.Parse("000a9465-8665-40bf-90e3-0165442d9120")));
Assert.True(resR1.Id.Equals(Guid.Parse("000a9465-8665-40bf-90e3-0165442d9120")));
xx = string.Empty;
}
private async Task ClearData6()
{
await MyDAL_TestDB
.Deleter <Agent>()
.Where(it => it.Id == Guid.Parse("ea1ad309-56f7-4e3e-af12-0165c9121e9b"))
.DeleteAsync();
}
private async Task ClearData7()
{
await MyDAL_TestDB
.Deleter <Agent>()
.Where(it => it.Id == Guid.Parse("08d60369-4fc1-e8e0-44dc-435f31635e6d"))
.DeleteAsync();
}
public async Task Create_Shotcut()
{
var m1 = new BodyFitRecord
{
Id = Guid.Parse("1fbd8a41-c75b-45c0-9186-016544284e2e"),
CreatedOn = DateTime.Now,
UserId = Guid.NewGuid(),
BodyMeasureProperty = "{xxx:yyy,mmm:nnn}"
};
await PreCreate(m1);
xx = string.Empty;
// 新建
var res1 = await MyDAL_TestDB.InsertAsync(m1);
Assert.True(res1 == 1);
/********************************************************************************************************************************/
xx = string.Empty;
}
public async Task Create_Batch_Shortcut()
{
var json = File.ReadAllText(@"D:\GitHub\Me\MyDal\DalTestDB\ProfileData.json");
var list = JsonConvert.DeserializeObject <List <UserInfo> >(json);
foreach (var item in list)
{
item.Id = Guid.NewGuid();
//Thread.Sleep(1);
item.CreatedOn = DateTime.Now;
}
xx = string.Empty;
Assert.True(!list.Any(it => it.RootUser));
Assert.True(!list.Any(it => it.InvitedCount > 0));
Assert.True(!list.Any(it => !string.IsNullOrWhiteSpace(it.ArrangePathId)));
Assert.True(!list.Any(it => it.IsVIP));
Assert.True(!list.Any(it => it.IsActived));
var res4 = await MyDAL_TestDB.InsertBatchAsync(list);
Assert.True(res4 == list.Count);
xx = string.Empty;
}
public async Task SelectVM_SQL_NoneParam()
{
xx = string.Empty;
var sql = @"
select agent.`PathId` as nn,
record.`Id` as yy,
agent.`Id` as xx,
agent.`Name` as zz,
record.`LockedCount` as mm
from `agent` as agent
inner join `agentinventoryrecord` as record
on agent.`Id`=record.`AgentId`
where agent.`Id`='544b9053-322e-4857-89a0-0165443dcbef'
limit 0,1;
";
var res1 = await MyDAL_TestDB.SelectOneAsync <AgentVM>(sql);
Assert.NotNull(res1);
Assert.Equal("夏明君", res1.zz);
xx = string.Empty;
}
public async Task SelectVM_SQL()
{
xx = string.Empty;
var sql = @"
select agent.`PathId` as nn,
record.`Id` as yy,
agent.`Id` as xx,
agent.`Name` as zz,
record.`LockedCount` as mm
from `agent` as agent
inner join `agentinventoryrecord` as record
on agent.`Id`=record.`AgentId`
where agent.`Id`=@Id
limit 0,1;
";
var paras = new List <XParam>
{
new XParam {
ParamName = "Id", ParamValue = Guid.Parse("544b9053-322e-4857-89a0-0165443dcbef")
}
};
var res1 = await MyDAL_TestDB.SelectOneAsync <AgentVM>(sql, paras);
Assert.NotNull(res1);
Assert.Equal("夏明君", res1.zz);
xx = string.Empty;
}
public async Task SelectSingleColumn_SQL()
{
xx = string.Empty;
var sql = @"
select agent.`Name`
from `agent` as agent
inner join `agentinventoryrecord` as agentRecord
on agent.`Id`=agentRecord.`AgentId`
where agent.`AgentLevel`=@AgentLevel
limit 0,1;
";
var paras = new List <XParam>
{
new XParam {
ParamName = "AgentLevel", ParamValue = AgentLevel.DistiAgent
}
};
var res1 = await MyDAL_TestDB.SelectOneAsync <string>(sql, paras);
Assert.True(res1.Length > 1);
xx = string.Empty;
}
public async Task History()
{
/************************************************************************************************************/
await Pre01();
xx = string.Empty;
// 默认 "%"+"xx"+"%"
var res1 = await MyDAL_TestDB.SelectOneAsync <BodyFitRecord>(it => it.BodyMeasureProperty.Contains("xx"));
Assert.NotNull(res1);
/************************************************************************************************************/
xx = string.Empty;
// 默认 "%"+"~00-d-3-1-"+"%"
var res3 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.CreatedOn >= Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30))
.And(it => it.PathId.Contains("~00-d-3-1-"))
.SelectPagingAsync(1, 10);
Assert.True(res3.TotalCount == 5680);
/************************************************************************************************************/
}
public async Task MySQL_通配符()
{
xx = string.Empty;
// %(百分号): "陈%" --> "陈%"
var res5 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.Contains("陈%"));
Assert.True(res5.Count == 1421);
/***************************************************************************************************************************/
xx = string.Empty;
// _(下划线): "王_" --> "王_"
var res6 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.Contains("王_"));
Assert.True(res6.Count == 498);
/***************************************************************************************************************************/
xx = string.Empty;
}
public async Task NotLessThan()
{
xx = string.Empty;
// !(<) --> >=
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !(it.CreatedOn < DateTime.Parse("2019-02-10")));
Assert.True(res1.Count == 0);
/*********************************************************************************************************************************/
xx = string.Empty;
// >= --> >=
var res2 = await MyDAL_TestDB.SelectListAsync <AlipayPaymentRecord>(it => it.CreatedOn >= DateTime.Parse("2018-08-20"));
Assert.True(res2.Count == 29);
/****************************************************************************************/
xx = string.Empty;
}
public async Task Concat_Multi_ST()
{
xx = string.Empty;
// 上下文条件 变量
var userId = "08d6036b-0a7e-b07d-b9bd-af03841b3baa";
var firstName = "伏";
var where = MyDAL_TestDB.Selecter <Agent>().WhereSegment;
// 根据条件 判断 是否 拼接 UserId 字段 的 过滤条件
if (userId.IsNotBlank())
{
where = where.And(it => it.UserId == Guid.Parse(userId));
}
// 根据条件 判断 是否 拼接 Name 字段 的 过滤条件
if (firstName.IsNotBlank())
{
where = where.And(it => it.Name.StartsWith(firstName));
}
// 对 WhereSegment 设定的条件 进行 select 动作
var res1 = await where.SelectListAsync();
Assert.True(res1.Count == 1);
xx = string.Empty;
}
public async Task SelectVM_ST()
{
xx = string.Empty;
var res6 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.CreatedOn >= Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30))
.SelectPagingAsync <AgentVM>(1, 10);
var resR6 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30) <= it.CreatedOn)
.SelectPagingAsync <AgentVM>(1, 10);
Assert.True(res6.TotalCount == resR6.TotalCount);
Assert.True(res6.TotalCount == 28619);
/*************************************************************************************************************************/
xx = string.Empty;
}
public async Task NotGreaterThan()
{
xx = string.Empty;
// !(>) --> <=
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !(it.CreatedOn > Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30)));
Assert.True(res1.Count == 1);
/******************************************************************************************************************************************************/
xx = string.Empty;
// <= --> <=
var res2 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.CreatedOn <= Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30));
Assert.True(res2.Count == 1);
/******************************************************************************************************************************************************/
xx = string.Empty;
}
public async Task Update_SetObject_ST_02()
{
xx = string.Empty;
// 要更新的 model 字段 赋值
var model = new AgentInventoryRecord();
model.TotalSaleCount = 1000;
//
var res1 = await MyDAL_TestDB
.Updater <AgentInventoryRecord>() //更新表 AgentInventoryRecord
.Set(new
{
model.TotalSaleCount, // 更新 字段 TotalSaleCount
xxx = 2000 // 字段 xxx 在表中无对应 ，自动忽略
})
.Where(it => it.Id == Guid.Parse("032ce51f-1034-4fb2-9741-01655202ecbc"))
.UpdateAsync();
Assert.True(res1 == 1);
/***************************************************************************************************************************/
xx = string.Empty;
}
public async Task Update_SetField_NotAllowedNull_ST()
{
xx = string.Empty;
var agent = await MyDAL_TestDB.SelectOneAsync <Agent>(it => it.Id == Guid.Parse("040afaad-ae07-42fc-9dd0-0165443c847d"));
try
{
var res1 = await MyDAL_TestDB
.Updater <Agent>()
.Set(it => it.PathId, null)
.Where(it => it.Id == agent.Id)
.UpdateAsync();
}
catch (Exception ex)
{
var errStr = "【ERR-054】 -- [[NotAllowedNull -- 字段:[[PathId]]的值不能设为 Null !!!]] ，请 EMail: --> [email protected] <--";
Assert.Equal(errStr, ex.Message, true);
}
/*****************************************************************************************************************************************************************/
xx = string.Empty;
}
public async Task Update_SetField_AllowedNull_Shortcut()
{
xx = string.Empty;
var agent = await MyDAL_TestDB.SelectOneAsync <Agent>(it => it.Id == Guid.Parse("040afaad-ae07-42fc-9dd0-0165443c847d"));
agent.PathId = null;
var res1 = await MyDAL_TestDB.UpdateAsync <Agent>(it => it.Id == agent.Id, new
{
agent.PathId
});
Assert.True(res1 == 1);
var res11 = await MyDAL_TestDB.SelectOneAsync <Agent>(it => it.Id == agent.Id);
Assert.Null(res11.PathId);
/*****************************************************************************************************************************************************************/
xx = string.Empty;
}
public async Task Update_Shortcut_02()
{
xx = string.Empty;
var pk1 = Guid.Parse("8f2cbb64-8356-4482-88ee-016558c05b2d");
var model = new AlipayPaymentRecord();
model.Description = "new desc"; // 修改 AlipayPaymentRecord 字段 Description 的值为: "new desc"
model.PaymentUrl = "new url"; // 修改 AlipayPaymentRecord 字段 PaymentUrl 的值为: "new url"
// 修改一条数据: AlipayPaymentRecord
var res1 = await MyDAL_TestDB.UpdateAsync <AlipayPaymentRecord>(it => it.Id == pk1, // where 条件: it=>it.Id==pk1 , 可输入任意条件的表达式
new
{
model.Description, // 修改 AlipayPaymentRecord 字段 Description 的值
model.PaymentUrl // 修改 AlipayPaymentRecord 字段 PaymentUrl 的值
});
Assert.True(res1 == 1);
// 查询一条数据: AlipayPaymentRecord
var res11 = await MyDAL_TestDB.SelectOneAsync <AlipayPaymentRecord>(it => it.Id == pk1);
Assert.True(res11.Description == "new desc");
/****************************************************************************************/
xx = string.Empty;
}
public async Task History_04()
{
xx = string.Empty;
var agent = await MyDAL_TestDB.SelectOneAsync <Agent>(it => it.Id == Guid.Parse("040afaad-ae07-42fc-9dd0-0165443c847d"));
agent.PathId = "xxxxxxx";
agent.ActiveOrderId = null;
var res1 = await MyDAL_TestDB
.Updater <Agent>()
.Set(new
{
agent.PathId,
agent.ActiveOrderId
})
.Where(it => it.Id == agent.Id)
.UpdateAsync();
var res11 = await MyDAL_TestDB.SelectOneAsync <Agent>(it => it.Id == agent.Id);
Assert.Equal("xxxxxxx", res11.PathId, true);
Assert.Null(res11.ActiveOrderId);
/*****************************************************************************************************************************************************************/
xx = string.Empty;
}
private async Task PreTrim()
{
var pk1 = Guid.Parse("b3866d7c-2b51-46ae-85cb-0165c9121e8f");
var res1 = await MyDAL_TestDB.UpdateAsync <Product>(it => it.Id == pk1, new
{
Title = " 演示商品01 "
});
}
private async Task PreRTrim()
{
var res1 = await MyDAL_TestDB
.Updater <Product>()
.Set(it => it.Title, "演示商品01 ")
.Where(it => it.Id == Guid.Parse("b3866d7c-2b51-46ae-85cb-0165c9121e8f"))
.UpdateAsync();
}
private async Task ClearData3(Agent m)
{
await MyDAL_TestDB
.Updater <Agent>()
.Set(it => it.AgentLevel, m.AgentLevel)
.Where(it => it.Id == m.Id)
.UpdateAsync();
}
public async Task Count_Star_Shortcut()
{
xx = string.Empty;
var res1 = await MyDAL_TestDB.CountAsync <Agent>(it => it.Name.Length > 3);
Assert.True(res1 == 116);
xx = string.Empty;
}
public None test(None none)
{
var res5 = (MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.Name == "刘中华")
.Distinct()
.SelectOneAsync()).GetAwaiter().GetResult();
return(none);
}
