public void TestMysqlPage1()
{
var sql = "select id,a.name as fullname,a.city address from person2 a order by a.name";
var parser = new MysqlParser();
var result = parser.ParserPage(sql, 1, 10);
Assert.Equal("select * from (select id,a.name as fullname,a.city address from person2 a order by a.name) sbOuter limit @sbPageSkip,@sbPageSize", result.PageSql);
Assert.Equal("select count(1) from (select id,a.name as fullname,a.city address from person2 a order by a.name) sbCount", result.CountSql);
Assert.Equal(2, result.SqlParameters.Count);
Assert.Equal(10, result.SqlParameters.FirstOrDefault(it => it.ParameterName == "sbPageSize")?.Value);
Assert.Equal(0, result.SqlParameters.FirstOrDefault(it => it.ParameterName == "sbPageSkip")?.Value);
}
static void Main(string[] args)
{
var sql = @"SELECT DISTINCT `p0`.`Name` As `Key`, SUM(`p0`.`Age`) As `Count` FROM `Person` As `p0` GROUP BY `p0`.`Name` LIMIT @y0,@y1";
var result = new MysqlParser().Parse(sql);
foreach (var sqlToken in result)
{
Console.WriteLine(sqlToken.ToString());
}
Console.Read();
}
public Page <T> PageBaseExecute <T>(MethodInfo method, object[] args, IServiceProvider serviceProvider)
{
ServiceProvider = serviceProvider;
Init();
//处理select逻辑
var selectAttribute = method.GetCustomAttribute <SelectAttribute>();
if (selectAttribute != null)
{
var repositoryOption = serviceProvider.GetService <RepositoryOption>();
//获得动态参数
var dbArgs = GetParameters(method, args);
if (pageable == null)
{
throw new Exception("method argument must have pageable");
}
OpenDb();
var sql = selectAttribute.Sql;
if (!sql.Contains("order by", StringComparison.OrdinalIgnoreCase))
{
throw new NotSupportedException("sql must contain order by clause");
}
sql = ReplaceSqlBindWhereCondition(sql);
var result = new Page <T>()
{
};
SqlParser.SqlParser parser;
if (repositoryOption.IsOracle)
{
parser = new OracleParser();
}
else if (repositoryOption.IsSqlServer)
{
parser = new SqlServerParser();
}
else if (repositoryOption.IsMysql)
{
parser = new MysqlParser();
}
else
{
parser = new SqliteParser();
}
var parseResult = parser.ParserPage(sql, pageable.PageNumber, pageable.PageSize);
ChangeDynamicParameters(parseResult.SqlParameters, dbArgs);
var count = dbConnection.QueryFirst <int>(parseResult.CountSql, dbArgs, transaction: dbTransaction);
var resultList = dbConnection.Query <T>(parseResult.PageSql, dbArgs, transaction: dbTransaction).ToList();
result.TotalPages = count;
result.Data = resultList;
result.PageSize = pageable.PageSize;
result.PageNumber = pageable.PageNumber;
CloseDb();
return(result);
}
throw new Exception("can not process method name:" + method.Name);
}
