/// <summary>
/// Get Sql query with paging parametes and sortExpression and return IList of T type given
/// </summary>
/// <typeparam name="T">Input Type</typeparam>
/// <param name="sql">Sql Expression</param>
/// <param name="startRowIndex">Current Page</param>
/// <param name="maximumRows">Num Rows Displayed on Page</param>
/// <param name="sortExpression">Sort Column Name and Direction(Asc/Desc)</param>
/// <param name="filterExpression">Filter expression send from search UserControl</param>
/// <param name="columns">Searchable fields list (Grid Colums sort expression_</param>
/// <param name="noPaging">If true no paging done</param>
/// <returns>returns IList of input type</returns>
public IList <T> GetList <T>(string sql, int startRowIndex, int maximumRows, string sortExpression, string filterExpression, string columns, bool noPaging = false)
{
if (string.IsNullOrEmpty(sortExpression))
{
throw new Exception("sortExpression must be something");
}
T ViewModel = Reflect.GetInstance <T>();
DBHelper.Attributes.ViewModelPropertyAttribute attr = Reflect.GetViewModelProperty <T>(ViewModel, sortExpression);
if (attr.IsDesc)
{
sortExpression = attr.SortExpression + " DESC ";
}
else
{
sortExpression = attr.SortExpression;
}
bool IsReplaced = false;
if (!noPaging)
{
if (sql.Contains("FROM "))
{
IsReplaced = true;
sql = sql.Replace("FROM ", ",ROW_NUMBER() OVER(ORDER BY {sortExpression}) as RowNum FROM ");
}
if (sql.Contains("from "))
{
IsReplaced = true;
sql = sql.Replace("from ", ",ROW_NUMBER() OVER(ORDER BY {sortExpression}) as RowNum FROM ");
}
if (!IsReplaced)
{
throw new Exception("Your sql expression should contains 'FROM ' or 'from ' word (without quoates)");
}
}
filterExpression = getFilter(filterExpression, columns, ViewModel);
string baked_sql =
@"SELECT * FROM
({sqlExpression} AND {filterExpression}
) as DerivedName ";
if (!(sql.ToLower().Contains("where\n") || sql.ToLower().Contains("where ")))
{
baked_sql =
@"SELECT * FROM
({sqlExpression} WHERE {filterExpression}
) as DerivedName ";
}
if (!noPaging)
{
baked_sql += "\n WHERE RowNum BETWEEN {startRowIndex}+1 AND ({startRowIndex} + {maximumRows})";
}
//WHERE RowNum BETWEEN {startRowIndex} AND ({startRowIndex} + {maximumRows}) - 1 ";
baked_sql = Base.Extension.StringExtension.StringExtension.NamedStringFormat(baked_sql,
new
{
sqlExpression = sql,
startRowIndex = startRowIndex,
maximumRows = maximumRows
,
filterExpression = filterExpression,
sortExpression = sortExpression
});
DBHelper.SQLServer.DirectDB db = new DBHelper.SQLServer.DirectDB(_connectionString);
DataTable dt = db.ExecuteQuery(CommandType.Text, baked_sql);
db.Close();
return(Reflect.DataTable2ObjectList <T>(dt));
}
