public void Do(ProviderType type)
{
string fileName = "Query";
int count = 1;
SqlLam <Area> sql = new SqlLam <Area>("u", type);
sql.Type = ProviderType.Oracle;
GlobalConfig.UseDb(ProviderType.Oracle);
Log.WriteLog(count, fileName, "生成最简单的SQL", "SqlLam<Area> sql = new SqlLam<Area>();", sql);
count++;
sql = new SqlLam <Area>("u");
Log.WriteLog(count, fileName, "带别名的简单SQL", "SqlLam<Area> sql = new SqlLam<Area>(\"u\");", sql);
count++;
sql.As("u");
Log.WriteLog(count, fileName, "带别名的简单SQL2,和上面效果一样", "SqlLam<Area> sql = new SqlLam<Area>();\r\nsql.As(\"u\");", sql);
count++;
sql = new SqlLam <Area>();
sql.Top(100);
Log.WriteLog(count, fileName, "SQL TOP * ", "sql.Top(100);", sql);
count++;
sql = new SqlLam <Area>();
sql.Top(100, true);
Log.WriteLog(count, fileName, "SQL TOP * 带 percent ", "sql.Top(100, true);", sql);
count++;
sql = new SqlLam <Area>();
sql.Select(m => m.AreaCode);
Log.WriteLog(count, fileName, "SQL Select 只查一列 ", "sql.Select(m => m.F_CreatorUserId);", sql);
count++;
sql = new SqlLam <Area>();
sql.Select(m => new { m.AreaCode, m.AreaId, m.AreaName });
Log.WriteLog(count, fileName, "SQL Select 查多列 ", "sql.Select(m => m.F_CreatorUserId);", sql);
count++;
sql = new SqlLam <Area>();
sql.Top(100).Select(m => new { m.AreaCode, m.AreaId, m.AreaName });
Log.WriteLog(count, fileName, "SQL TOP 其他列 先写 TOP 再写 Select", "sql.Top(100).Select(m => new { m.F_CreatorUserId, m.F_DeleteMark, m.F_EnCode });", sql);
count++;
sql = new SqlLam <Area>();
sql.Select(m => new { m.AreaCode, m.AreaId, m.AreaName }).Top(100);
Log.WriteLog(count, fileName, "SQL TOP 其他列 先写 Select 再写 TOP, 其结果一样", "sql.Top(100).Select(m => new { m.F_CreatorUserId, m.F_DeleteMark, m.F_EnCode });", sql);
count++;
sql.Clear();
sql.As("a");
sql.Where(m => !string.IsNullOrEmpty(m.AreaCode));
Log.WriteLog(count, fileName, "SQL 实现 IsNullOrEmpty 方法", "sql.Where(m => string.IsNullOrEmpty(m.AreaCode));", sql);
}
public IEnumerable <T> GetList(Expression <Func <T, bool> > where = null)
{
if (where != null)
{
sql.Where(where);
}
return(client.GetList <T>(sql.GetSql(), sql.GetParameters()));
}
public static IEnumerable <T> Query <T>(this IDbConnection db, Expression <Func <T, bool> > wherExpression = null)
{
var sqllam = new SqlLam <T>(db.GetAdapter());
if (wherExpression != null)
{
sqllam = sqllam.Where(wherExpression);
}
//var sqlString = sqllam.SqlString;
//var param = sqllam.Parameters;
//string parameterString = GetParameterString(sqllam.Parameters);
return(db.Query <T>(sqllam.SqlString, sqllam.Parameters));
}
public static PagedResult <T> PagedQuery <T>(this IDbConnection db, int pageSize, int pageNumber, Expression <Func <T, bool> > whereExpression = null, Expression <Func <T, object> > groupByexpression = null, params Expression <Func <T, object> >[] orderbyExpressions)
where T : class
{
var sqllam = new SqlLam <T>(db.GetAdapter());
var countSqlam = new SqlLam <T>(db.GetAdapter());
if (whereExpression != null)
{
sqllam = sqllam.Where(whereExpression);
countSqlam = countSqlam.Where(whereExpression);
}
if (orderbyExpressions != null && orderbyExpressions.Length > 0)
{
sqllam = sqllam.OrderBy(orderbyExpressions);
}
if (groupByexpression != null)
{
sqllam = sqllam.GroupBy(groupByexpression);
}
countSqlam = countSqlam.Count();
var countRet = db.Query <int>(countSqlam.SqlString, countSqlam.Parameters).FirstOrDefault();
//var sqlString = sqllam.SqlString;
//var param = sqllam.Parameters;
//string parameterString = GetParameterString(sqllam.Parameters);
var sqlstring = sqllam.QueryPage(pageSize, pageNumber);
var retlist = db.Query <T>(sqlstring, sqllam.Parameters);
return(new PagedResult <T>(retlist, countRet, pageSize, pageNumber));
}