public static dynamic DataSource(AutoCompOptions options, string query, string orderBy)
{
var connection = new CommonConnection();
try
{
query = query.Replace(';', ' ');
string sqlQuery = GridQueryBuilder <T> .Query(options, query, orderBy, "");
DataTable dataTable = connection.GetDataTable(sqlQuery);
var dataList = (List <T>)ListConversion.ConvertTo <T>(dataTable);
return(dataList);
}
catch (Exception ex)
{
throw;
}
finally
{
connection.Close();
}
}
public static dynamic DataSource(MultiSelectOptions options, string query, string orderBy)
{
var objOptions = new AutoCompOptions();
if (options != null)
{
objOptions.take = options.take;
objOptions.page = options.page;
objOptions.pageSize = options.pageSize;
objOptions.skip = options.skip;
objOptions.filter = options.filter;
}
return(AutoComplete.DataSource(objOptions, query, orderBy));
}
public static string Query(AutoCompOptions options, string query, string orderBy, string gridCondition)
{
string condition = "";
condition = FilterCondition(options.filter);
if (!string.IsNullOrEmpty(condition))
{
condition = " WHERE " + condition;
}
if (!string.IsNullOrEmpty(gridCondition))
{
if (string.IsNullOrEmpty(condition))
{
condition = " WHERE " + gridCondition;
}
else
{
condition += " AND " + gridCondition;
}
}
string orderby = "";
if (orderby == "")
{
if (!String.IsNullOrEmpty(orderBy))
{
orderby = " ORDER BY " + orderBy;
}
else
{
throw new Exception("Must be set Orderby column Name");
}
}
var pageupperBound = options.skip + options.take;
var sql =
string.Format(
@"SELECT * FROM (SELECT ROW_NUMBER() OVER({4}) AS ROWINDEX, T.* FROM ({0}) T {2}) tbl WHERE ROWINDEX >{1} AND ROWINDEX <={3}",
query, options.skip, condition, pageupperBound, orderby);
return(sql);
}