public async Task <SearchResult <List <dynamic> > > GetQueryResult(DapperClient dapperClient, ConditionBase conditionBase)
{
var result = new SearchResult <List <dynamic> >();
try
{
var sqlCount = @"WITH CTE AS ( [[CTE_SQL]] )
SELECT COUNT(*) FROM CTE";
var sql = @"WITH CTE AS ( [[CTE_SQL]] )
SELECT *
FROM (
SELECT ROW_NUMBER() OVER ([[ORDERBY]]) AS RowNumber,* FROM CTE
) as A
WHERE RowNumber > [[PAGESIZE]]*([[PAGEINDEX]]-1) and RowNumber <= [[PAGESIZE]]*([[PAGEINDEX]]) [[ORDERBY]]";
sqlCount = sqlCount.Replace("[[CTE_SQL]]", conditionBase.Sql);
sql = sql.Replace("[[CTE_SQL]]", conditionBase.Sql);
sql = sql.Replace("[[ORDERBY]]", "order by " + conditionBase.OrderBy + " " + conditionBase.OrderSequence);
sql = sql.Replace("[[PAGEINDEX]]", conditionBase.PageIndex.ToString());
sql = sql.Replace("[[PAGESIZE]]", conditionBase.PageSize.ToString());
var respositoryResult = await dapperClient.Query <dynamic>(sql).ToListAsync();
var respositorycount = dapperClient.ExecuteScalar <int>(sqlCount);
result.Status = ResultConfig.Ok;
result.Info = ResultConfig.SuccessfulMessage;
result.Rows = respositoryResult;
result.Total = respositorycount;
}
catch (Exception ex)
{
result.Status = ResultConfig.Fail;
result.Info = ex.Message;
}
return(result);
}
public object Get()
{
// var testQuery = _OracleDB.Query<dynamic>(@"SELECT * FROM BASE_DEPT where ROWNUM<=5");
var result = _SqlDB.Query <dynamic>(@"select * from UserInfo");
return(Ok());
}
public async Task <JsonResult> GetPlatform(string id)
{
var result = new SearchResult <List <dynamic> >();
try
{
string sql = $"SELECT code,descript FROM basecode WHERE cat=@cat ORDER BY seq";
var respositoryResult = _SqlDB.Query <dynamic>(sql, new { cat = id }).ToList();
result.Rows = respositoryResult;
result.Status = ResultConfig.Ok;
result.Info = ResultConfig.SuccessfulMessage;
}
catch (Exception ex)
{
result.Status = ResultConfig.Fail;
result.Info = ex.Message;
}
return(Json(result));
}
public Object Get()
{
var result = mysql.Query <dynamic>("select * from user");
return(Ok(result));
}
