public static PageList <T> SelectPageListPropertiesByKey <T>(this ISelectProperties selectProperties, int pageIndex, int pageSize, int id = 0,
string tableIndex = null, List <List <object> > listsIn = null, string sqlAppend = "", params string[] properties)
{
try
{
if (selectProperties == null)
{
return(default(PageList <T>));
}
string sqlCount = string.Empty;
using (IDbConnection dbConnection = selectProperties.GetDBConnection(id))
{
dbConnection.Open();
var dbType = selectProperties.GetDBServerType(id);
string sql = string.Empty;
if (dbType == DBServerType.MySql)
{
string sqlPageLimit = string.Format(" Limit {0},{1}", (pageIndex - 1) * pageSize, pageSize);
sql = selectProperties.GetInSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
sql += sqlPageLimit;
}
else if (dbType == DBServerType.Oracle)
{
string sqlPrepare = selectProperties.GetInSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
sql = string.Format(PageListSql.PageListOracle, sqlPrepare, (pageIndex * pageSize).ToString(), ((pageIndex - 1) * pageSize).ToString());
}
else if (dbType == DBServerType.SqlServer)
{
string sqlPrepare = selectProperties.GetInSqlServerPageListSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
int iOrderBy = sqlPrepare.LastIndexOf("Order By");
string orderBySql = string.Empty;
if (iOrderBy > 0)
{
orderBySql = sqlPrepare.Substring(iOrderBy);
sqlPrepare = sqlPrepare.Substring(0, iOrderBy);
}
sql = string.Format(PageListSql.PageListSqlServer, pageSize.ToString(), sqlPrepare, pageIndex.ToString()) + " " + orderBySql;
}
long total = GetTotalCount(selectProperties, dbConnection, id, tableIndex, listsIn, sqlAppend, properties);
var list = dbConnection.Query <T>(sql, selectProperties).ToList();
PageList <T> result = new PageList <T> {
ListItems = list, PageIndex = pageIndex, PageSize = pageSize, TotalCount = total, TotalPage = total / pageSize + (total % pageSize == 0 ? 0 : 1)
};
return(result);
}
}
catch (Exception ex)
{ throw ex; }
}
public static List <T> SelectPropertiesExists <T>(this ISelectProperties selectProperties, int id = MatchedID.SelectExists, string tableIndex = null, List <List <object> > listsIn = null, string sqlAppend = "", params string[] properties)
{
try
{
if (selectProperties == null)
{
return(default(List <T>));
}
using (IDbConnection dbConnection = selectProperties.GetDBConnection(id))
{
dbConnection.Open();
var sql = selectProperties.GetInSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
//Logger.Info(sql);
return(dbConnection.Query <T>(sql, selectProperties).ToList());
}
}
catch (Exception ex)
{ throw ex; }
}
public static List <T> SelectPropertiesByKey <T>(this ISelectProperties selectProperties, int id = 0, string tableIndex = null, List <List <object> > listsIn = null, string sqlAppend = "", params string[] properties)
{
try
{
if (selectProperties == null)
{
return(default(List <T>));
}
string connectionKey = selectProperties.GetConnectionKey();
string connectionString = AppSetting.GetConfig(connectionKey);
using (IDbConnection dbConnection = selectProperties.GetDBConnection(id))
{
dbConnection.Open();
var sql = selectProperties.GetInSelectSql(id, tableIndex, listsIn, sqlAppend, properties);
//Logger.Info(sql);
//if (listsIn == null)
return(dbConnection.Query <T>(sql, selectProperties).ToList());
//else
// return dbConnection.Query<T>(sql).ToList();
}
}
catch (Exception ex)
{ throw ex; }
}