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; }
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 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 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 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 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 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 Mock_SelectAllData_SelectVM_Shortcut() { xx = string.Empty; var res1 = await MyDAL_TestDB.SelectListAsync <Agent, AgentVM>(it => true); Assert.True(res1.Count == 28620); /***************************************************************************************************************************/ }
public async Task Mock_SelectAllData_SelectSingleColumn_Shortcut() { xx = string.Empty; var res1 = await MyDAL_TestDB.SelectListAsync <Agent, Guid>(it => true, it => it.Id); Assert.True(res1.Count == 28620); /***************************************************************************************************************************/ }
public async Task SelectVM_Shortcut() { xx = string.Empty; var res2 = await MyDAL_TestDB.SelectListAsync <AlipayPaymentRecord, AlipayPaymentRecordVM>(it => it.CreatedOn >= DateTime.Parse("2018-08-20")); Assert.True(res2.Count == 29); /****************************************************************************************/ }
public async Task NotEqual() { xx = string.Empty; // != --> <> var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.AgentLevel != AgentLevel.Customer); Assert.True(res1.Count == 556); xx = string.Empty; }
public async Task GreaterThan() { 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 == 28619); xx = string.Empty; }
public async Task LessThan() { xx = string.Empty; // < --> < var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.CreatedOn < DateTime.Parse("2019-02-10")); Assert.True(res1.Count == 28620); xx = string.Empty; }
public async Task SelectVMColumn_Shortcut() { xx = string.Empty; var res3 = await MyDAL_TestDB.SelectListAsync <AlipayPaymentRecord, AlipayPaymentRecordVM>(it => it.CreatedOn >= DateTime.Parse("2018-08-20"), it => new AlipayPaymentRecordVM { TotalAmount = it.TotalAmount, Description = it.Description }); Assert.True(res3.Count == 29); }
public async Task test() { /*************************************************************************************************************************/ xx = string.Empty; var res2 = await MyDAL_TestDB .Selecter <Agent>() .Distinct() .SelectListAsync(it => it.Name); Assert.True(res2.Count == 24444); /****************************************************************************************************************************************/ xx = string.Empty; var res6 = await MyDAL_TestDB .Selecter <Agent>() .Where(it => it.Name == "刘中华") .Distinct() .SelectOneAsync(); Assert.NotNull(res6); var res61 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name == "刘中华"); Assert.True(res61.Count == 2); /****************************************************************************************************************************************/ xx = string.Empty; var res7 = await MyDAL_TestDB .Selecter(out Agent agent1, out AgentInventoryRecord record1) .From(() => agent1) .InnerJoin(() => record1) .On(() => agent1.Id == record1.AgentId) .Where(() => agent1.AgentLevel == AgentLevel.DistiAgent) .Distinct() .SelectListAsync(() => agent1.Name); Assert.True(res7.Count == 543); /****************************************************************************************************************************************/ xx = string.Empty; }
public async Task SelectSingleColumn_Shortcut() { xx = string.Empty; var res7 = await MyDAL_TestDB.SelectListAsync <Agent, string>(it => it.Name.StartsWith("张"), it => it.Name); Assert.True(res7.Count == 1996); /****************************************************************************************/ xx = string.Empty; }
public async Task Like_Shortcut() { xx = string.Empty; // like var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.Contains("陈")); Assert.True(res1.Count == 1431); /***************************************************************************************************************************/ xx = string.Empty; }
public async Task NotLike_Shortcut() { xx = string.Empty; // not like var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !it.Name.Contains("刘")); Assert.True(res1.Count == 27159); /***************************************************************************************************************************/ xx = string.Empty; }
public async Task SelectVM_SQL() { xx = string.Empty; var totalSql = @" select count(*) from ( select agent9.`Name` as XXXX, agent9.`PathId` as YYYY from `agent` as agent9 inner join `agentinventoryrecord` as record9 on agent9.`Id`=record9.`AgentId` where agent9.`AgentLevel`=@AgentLevel ) temp; "; var dataSql = @" select agent9.`Name` as XXXX, agent9.`PathId` as YYYY from `agent` as agent9 inner join `agentinventoryrecord` as record9 on agent9.`Id`=record9.`AgentId` where agent9.`AgentLevel`=@AgentLevel order by agent9.`Id` desc limit 0,10; "; var paras = new List <XParam> { new XParam { ParamName = "AgentLevel", ParamValue = AgentLevel.DistiAgent, ParamType = ParamTypeEnum.Int_MySQL_SqlServer } }; var paging = new PagingResult <AgentVM>(); paging.PageIndex = 1; paging.PageSize = 10; paging.TotalCount = await MyDAL_TestDB.SelectOneAsync <int>(totalSql, paras); paging.Data = await MyDAL_TestDB.SelectListAsync <AgentVM>(dataSql, paras); Assert.True(paging.Data.Count == 10); xx = string.Empty; }
public async Task NotIn_Shortcut() { xx = string.Empty; // in var res2 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !new List <AgentLevel?> { AgentLevel.CityAgent, AgentLevel.DistiAgent }.Contains(it.AgentLevel)); Assert.True(res2.Count == 28064 || res2.Count == 28065); // 此处为test /*******************************************************************************************************************/ xx = string.Empty; }
public async Task Mock_SelectAllData_SelectVMColumn_Shortcut() { xx = string.Empty; var res1 = await MyDAL_TestDB.SelectListAsync <Agent, AgentVM>(it => true, it => new AgentVM { XXXX = it.Name, YYYY = it.PathId }); Assert.True(res1.Count == 28620); /***************************************************************************************************************************/ xx = string.Empty; }
public async Task SelectSingleColumn_SQL() { xx = string.Empty; var totalSql = @" select count(agent1.`Id`) from `agent` as agent1 inner join `agentinventoryrecord` as record1 on agent1.`Id`=record1.`AgentId` where agent1.`AgentLevel`=@AgentLevel; "; var dataSql = @" select agent1.`Id` from `agent` as agent1 inner join `agentinventoryrecord` as record1 on agent1.`Id`=record1.`AgentId` where agent1.`AgentLevel`=@AgentLevel order by agent1.`Id` desc limit 0,10; "; var paras = new List <XParam> { new XParam { ParamName = "AgentLevel", ParamValue = AgentLevel.DistiAgent, ParamType = ParamTypeEnum.Int_MySQL_SqlServer } }; var paging = new PagingResult <Guid>(); paging.PageIndex = 1; paging.PageSize = 10; paging.TotalCount = await MyDAL_TestDB.SelectOneAsync <int>(totalSql, paras); paging.Data = await MyDAL_TestDB.SelectListAsync <Guid>(dataSql, paras); Assert.True(paging.TotalPage == 58); xx = string.Empty; }
public async Task Mock_UpdateAll_SetField_ST() { xx = string.Empty; var res1 = await MyDAL_TestDB .Updater <AgentInventoryRecord>() .Set(it => it.LockedCount, 0) .UpdateAsync(); Assert.True(res1 == 574); var res11 = await MyDAL_TestDB.SelectListAsync <AgentInventoryRecord>(it => it.LockedCount != 0); Assert.True(res11.Count == 0); xx = string.Empty; }
public async Task Mock_UpdateAll_Shortcut() { xx = string.Empty; var model = new AgentInventoryRecord(); model.LockedCount = 0; var res1 = await MyDAL_TestDB.UpdateAsync <AgentInventoryRecord>(it => true, new { model.LockedCount }); Assert.True(res1 == 574); var res11 = await MyDAL_TestDB.SelectListAsync <AgentInventoryRecord>(it => it.LockedCount != 0); Assert.True(res11.Count == 0); xx = string.Empty; }
public async Task MySQL_通配符_转义() { var resx4 = await Pre02(); /************************************************************************************************************/ xx = string.Empty; // /%(百分号转义): "刘/%_" --> "刘/%_" var res7 = await MyDAL_TestDB .Selecter <Agent>() .Where(it => it.Name.Contains("刘/%_")) .And(it => it.Id == resx4.Id) .And(it => it.Name.Contains("%华")) .And(it => it.Name.Contains("%/%%")) .SelectListAsync(); Assert.True(res7.Count == 1); /************************************************************************************************************/ xx = string.Empty; // /_(下划线转义): "何/__" --> "何/__" var res8 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.Contains("何/__")); Assert.True(res8.Count == 1); /************************************************************************************************************/ xx = string.Empty; }
public async Task String_EndsWith() { /***************************************************************************************************************************/ xx = string.Empty; // like EndsWith var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.EndsWith("陈")); Assert.True(res1.Count == 2); /************************************************************************************************************/ xx = string.Empty; // like EndsWith var res12 = await MyDAL_TestDB .Selecter <Agent>() .Where(it => it.PathId.EndsWith("~00-d-3-1-")) .SelectPagingAsync(1, 10); Assert.True(res12.TotalCount == 0); /************************************************************************************************************/ xx = string.Empty; // like EndsWith var res13 = await MyDAL_TestDB .Selecter(out Agent agent13, out AgentInventoryRecord record13) .From(() => agent13) .InnerJoin(() => record13) .On(() => agent13.Id == record13.AgentId) .Where(() => agent13.Name.EndsWith("华")) .SelectListAsync <Agent>(); Assert.True(res13.Count == 22); /***************************************************************************************************************************/ xx = string.Empty; // not like EndsWith var res2 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !it.Name.EndsWith("刘")); Assert.True(res2.Count == 28620); /************************************************************************************************************/ xx = string.Empty; // not like EndsWith var res21 = await MyDAL_TestDB .Selecter <Agent>() .Where(it => !it.PathId.EndsWith("~00-d-3-1-")) .SelectPagingAsync(1, 10); Assert.True(res21.TotalCount == 28620); /************************************************************************************************************/ xx = string.Empty; // not like EndsWith var res22 = await MyDAL_TestDB .Selecter(out Agent agent22, out AgentInventoryRecord record22) .From(() => agent22) .InnerJoin(() => record22) .On(() => agent22.Id == record22.AgentId) .Where(() => !agent22.Name.EndsWith("华")) .SelectListAsync <Agent>(); Assert.True(res22.Count == 552); /************************************************************************************************************/ xx = string.Empty; }
public async Task WhereTestx() { xx = string.Empty; /************************************************************************************************************************/ // is not null var res2 = await MyDAL_TestDB .Selecter <Agent>() .Where(it => it.ActiveOrderId != null) .SelectListAsync(); Assert.True(res2.Count == 554); /************************************************************************************************************************/ xx = string.Empty; var m = await PreData3(); // try { var res3 = await MyDAL_TestDB .Selecter <Agent>() .Where(it => it.AgentLevel == WhereTest.AgentLevelNull) .SelectListAsync(); } catch (Exception ex) { var errStr = "【ERR-078】 -- [[[[Convert(value(MyDAL.Compare._09_Null).WhereTest.AgentLevelNull, Nullable`1)]] 中,传入的 SQL 筛选条件为 Null !!!]] ,请 EMail: --> [email protected] <--"; Assert.Equal(errStr, ex.Message, ignoreCase: true); } await ClearData3(m); /************************************************************************************************************************/ var xx4 = string.Empty; // is not null var res4 = await MyDAL_TestDB .Selecter <Agent>() .Where(it => it.ActivedOn != null && it.ActiveOrderId != null && it.CrmUserId == null) .SelectListAsync(); Assert.True(res4.Count == 554); /************************************************************************************************************************/ var xx5 = string.Empty; // is not null var res5 = await MyDAL_TestDB .Selecter(out Agent a5, out AgentInventoryRecord r5) .From(() => a5) .LeftJoin(() => r5) .On(() => a5.Id == r5.AgentId) .Where(() => a5.ActiveOrderId == null) .SelectListAsync <Agent>(); Assert.True(res5.Count == 28085); /************************************************************************************************************************/ var res7 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.ActiveOrderId == null); Assert.True(res7.Count == 28066); /************************************************************************************************************************/ xx = string.Empty; // is not null var res8 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.ActiveOrderId != null); Assert.True(res8.Count == 554); /************************************************************************************************************************/ xx = string.Empty; }