public static JsonResult Index(DataTables.AspNet.Core.IDataTablesRequest request, Cliver.Bot.DbConnection dbc, string from_sql, Field[] fields, bool ignore_first_column_search = true) { try { from_sql = " " + from_sql; //turn around as it is unclear how to pass additional parameters if (!string.IsNullOrWhiteSpace(request.Columns.First().Search.Value)) { List<string> wheres = new List<string>(); foreach (string f2c in Regex.Split(request.Columns.First().Search.Value, @"\|", RegexOptions.Singleline)) { Match m = Regex.Match(f2c, @"(?'DbField'.*?)\=(?'SearchValue'.*)", RegexOptions.Singleline); if (m.Success) { string search_value = m.Groups["SearchValue"].Value; decimal sv; if (!decimal.TryParse(search_value, out sv)) search_value = "'" + search_value + "'"; wheres.Add(m.Groups["DbField"].Value + "=" + search_value); } m = Regex.Match(f2c, @"(?'DbField'.*?)\s*LIKE\s*(?'SearchValue'.*)", RegexOptions.Singleline); if (m.Success) { string search_value = m.Groups["SearchValue"].Value; decimal sv; if (!decimal.TryParse(search_value, out sv)) search_value = "'" + search_value + "'"; wheres.Add(m.Groups["DbField"].Value + " LIKE " + search_value); } } if (wheres.Count > 0) { string select_sql = " WHERE " + string.Join(" AND ", wheres); from_sql += select_sql; } } int total_count = (int)dbc.Get("SELECT COUNT(ISNULL(" + fields[0].Entity + ", 0))" + from_sql).GetSingleValue(); int filtered_count = total_count; string where_sql = null; if (!string.IsNullOrEmpty(request.Search.Value)) { string search = Regex.Replace(request.Search.Value.ToLower(), @"\'|\%|\\|_", @"\$0", RegexOptions.Compiled | RegexOptions.Singleline); List<string> conditions = new List<string>(); foreach (Field f in fields.Where(r => r.Searchable)) conditions.Add("" + f.Entity + " LIKE '%" + search + "%'"); if (conditions.Count > 0) { if (from_sql.Contains(" WHERE ")) where_sql += " AND "; else where_sql += " WHERE "; where_sql += "(" + string.Join(" OR ", conditions) + @" ESCAPE '\')"; } } if (!string.IsNullOrWhiteSpace(where_sql)) filtered_count = (int)dbc.Get("SELECT COUNT(ISNULL(" + fields[0].Entity + ", 0))" + from_sql + where_sql).GetSingleValue(); Dictionary<string, int> of2nothing = new Dictionary<string,int>(); List<string> ofs = new List<string>(); foreach (var column in request.Columns) { if (ignore_first_column_search) { ignore_first_column_search = false; continue; } if (column.Sort == null) continue; string f = column.Field; int n; if (int.TryParse(f, out n)) f = fields[n].Entity; if (column.Sort.Direction == DataTables.AspNet.Core.SortDirection.Ascending) ofs.Add(f); else ofs.Add(f + " DESC"); of2nothing[f] = 0; } foreach (Field field in fields) { if (field.Order == Field.OrderMode.NONE) continue; if(of2nothing.ContainsKey(field.Name)) continue; if (field.Order == Field.OrderMode.ASC) ofs.Add(field.Entity); else ofs.Add(field.Entity + " DESC"); of2nothing[field.Name] = 0; } string order_sql = " ORDER BY "; if (ofs.Count > 0) order_sql += string.Join(", ", ofs); else order_sql += fields[0].Entity; string fields_sql = string.Join(",", (from r in fields select r.Expression != null ? r.Expression + " AS " + r.Name : r.Name)); string sql = "SELECT " + fields_sql + from_sql + where_sql + order_sql + " OFFSET " + request.Start + " ROWS FETCH NEXT " + request.Length + " ROWS ONLY"; List<object[]> array = new List<object[]>(); using (DbDataReader r = (DbDataReader)dbc.Get(sql).GetReader()) { string search = null; if (!string.IsNullOrEmpty(request.Search.Value)) search = Regex.Escape(request.Search.Value); while (r.Read()) { object[] vs = new object[fields.Length]; r.GetValues(vs); if (search != null) { for (int i = 0; i < fields.Length; i++) if (fields[i].Searchable) if(!(vs[i] is System.DBNull)) vs[i] = Regex.Replace((string)vs[i], search, @"<span class='match'>$0</span>", RegexOptions.Compiled | RegexOptions.IgnoreCase); } array.Add(vs); } } DataTables.AspNet.Mvc5.DataTablesResponse response = DataTables.AspNet.Mvc5.DataTablesResponse.Create(request, total_count, filtered_count, array); return new DataTables.AspNet.Mvc5.DataTablesJsonResult(response, JsonRequestBehavior.AllowGet); } catch (Exception e) { DataTables.AspNet.Mvc5.DataTablesResponse response = DataTables.AspNet.Mvc5.DataTablesResponse.Create(request, e.Message); return new DataTables.AspNet.Mvc5.DataTablesJsonResult(response, JsonRequestBehavior.AllowGet); } }