private DataTableParameter MapParameterModel(HttpContext httpContext)
{
var request = httpContext.Request.Form;
int draw = Convert.ToInt32(request["draw"]);
int start = Convert.ToInt32(request["start"]);
int length = Convert.ToInt32(request["length"]);
var search = new DataTableSearch
{
Value = request["search[value]"],
Regex = Convert.ToBoolean(request["search[regex]"])
};
var o = 0;
var order = new List <DataTableOrder>();
while (request["order[" + o + "][column]"].Count > 0)
{
order.Add(new DataTableOrder()
{
Column = Convert.ToInt32(request["order[0][column]"].ToList()[0]),
Dir = request["order[" + o + "][dir]"]
});
o++;
}
// Columns
var c = 0;
var columns = new List <DataTableColumn>();
while (request["columns[" + c + "][name]"].Count > 0)
{
columns.Add(new DataTableColumn
{
Data = request["columns[" + c + "][data]"][0],
Name = request["columns[" + c + "][name]"][0],
Orderable = Convert.ToBoolean(request["columns[" + c + "][orderable]"][0]),
Search = new DataTableSearch
{
Value = request["columns[" + c + "][search][value]"][0],
Regex = Convert.ToBoolean(request["columns[" + c + "][search][regex]"][0])
}
});
c++;
}
var mapData = new DataTableParameter
{
Draw = draw,
Start = start,
Length = length,
Search = search,
Order = order,
Columns = columns,
};
return(mapData);
}
public DataTableSearch GetValue(ModelBindingContext context)
{
var modelname = context.ModelName;
var model = new DataTableSearch();
model.Value = DataTableBinderProvider.GetValue(context, $"{ modelname }[value]");
if (bool.TryParse(DataTableBinderProvider.GetValue(context, $"{ modelname }[regex]"), out bool regex))
{
model.Regex = regex;
}
return(model);
}
public DataTable <T> GetDataTableResult <T>(string procedureName, DataTableSearch search, List <MySqlParameter> filters)
{
using (var context = new AppDb())
{
string connectionstring = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
MySqlConnection sql = new MySqlConnection(connectionstring);
MySqlCommand cmd = new MySqlCommand(procedureName, sql)
{
CommandType = System.Data.CommandType.StoredProcedure,
// CommandTimeout=0
};
cmd.Parameters.Clear();
cmd.Parameters.Add(CreateSqlParameter("@pStart", search.start, MySqlDbType.Int32));
cmd.Parameters.Add(CreateSqlParameter("@pSize", search.length, MySqlDbType.Int32));
foreach (var param in filters)
{
cmd.Parameters.Add(param);
}
sql.Open();
var reader = cmd.ExecuteReader();
//Getting records
var result = ((IObjectContextAdapter)context).ObjectContext.Translate <T>(reader).ToList();
reader.NextResult();
double?Count = 0;
if (reader.HasRows)
{
Count = ((IObjectContextAdapter)context).ObjectContext.Translate <double>(reader).FirstOrDefault();
}
sql.Close();
cmd.Dispose();
return(new DataTable <T>
{
draw = search.draw,
recordsTotal = result.Count(),
recordsFiltered = result.Count > 0 ? Count : result.Count,
data = result
});
}
}
public static IQueryable <TData> Search <TData>(this IQueryable <TData> query, DataTableSearch searchParameters, DataTableResolver resolver)
where TData : class
{
if (resolver == null)
{
throw new InvalidOperationException("A DataTable resolver is required to search.");
}
if (!string.IsNullOrWhiteSpace(searchParameters.Value))
{
return(query.DynamicWhere(resolver.SearchableColumns, searchParameters.Value));
}
return(query);
}
/// <summary>
/// Attempts to bind a model.
/// </summary>
/// <param name="bindingContext">The <see cref="T:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingContext" />.</param>
/// <returns>
/// <para>
/// A <see cref="T:System.Threading.Tasks.Task" /> which will complete when the model binding process completes.
/// </para>
/// <para>
/// If model binding was successful, the <see cref="P:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingContext.Result" /> should have
/// <see cref="P:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingResult.IsModelSet" /> set to <c>true</c>.
/// </para>
/// <para>
/// A model binder that completes successfully should set <see cref="P:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingContext.Result" /> to
/// a value returned from <see cref="M:Microsoft.AspNetCore.Mvc.ModelBinding.ModelBindingResult.Success(System.Object)" />.
/// </para>
/// </returns>
public Task BindModelAsync(ModelBindingContext bindingContext)
{
if (bindingContext == null)
{
throw new ArgumentNullException(nameof(bindingContext));
}
if (bindingContext.HttpContext.Request.Headers["x-requested-with"] == "XMLHttpRequest")
{
try
{
var request = bindingContext.HttpContext.Request.Form;
int draw = Convert.ToInt32(request["draw"]);
int start = Convert.ToInt32(request["start"]);
int length = Convert.ToInt32(request["length"]);
var search = new DataTableSearch
{
Value = request["search[value]"],
Regex = Convert.ToBoolean(request["search[regex]"])
};
var o = 0;
var order = new List <DataTableOrder>();
while (request["order[" + o + "][column]"].Count > 0)
{
order.Add(new DataTableOrder()
{
Column = Convert.ToInt32(request["order[0][column]"].ToList()[0]),
Dir = request["order[" + o + "][dir]"]
});
o++;
}
// Columns
var c = 0;
var columns = new List <DataTableColumn>();
while (request["columns[" + c + "][name]"].Count > 0)
{
columns.Add(new DataTableColumn
{
Data = request["columns[" + c + "][data]"][0],
Name = request["columns[" + c + "][name]"][0],
Orderable = Convert.ToBoolean(request["columns[" + c + "][orderable]"][0]),
Search = new DataTableSearch
{
Value = request["columns[" + c + "][search][value]"][0],
Regex = Convert.ToBoolean(request["columns[" + c + "][search][regex]"][0])
}
});
c++;
}
var result = new DataTableParameter
{
Draw = draw,
Start = start,
Length = length,
Search = search,
Order = order,
Columns = columns
};
bindingContext.Result = ModelBindingResult.Success(result);
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
return(Task.CompletedTask);
}
internal protected async Task <DataTableResult <T> > GetDataTableResult <T>(string procedureName, DataTableSearch search, List <SqlParameter> filters)
{
string connectionstring = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection sql = new SqlConnection(connectionstring);
SqlCommand cmd = new SqlCommand(procedureName, sql)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.Add(CreateSqlParameter("@Start", search.Start, SqlDbType.Int));
cmd.Parameters.Add(CreateSqlParameter("@Length", search.Length == -1 ? int.MaxValue : search.Length, SqlDbType.Int));
cmd.Parameters.AddRange(filters.ToArray());
sql.Open();
var reader = await cmd.ExecuteReaderAsync();
//Getting records
var result = ((IObjectContextAdapter)Context).ObjectContext.Translate <T>(reader).ToList();
await reader.NextResultAsync();
var count = ((IObjectContextAdapter)Context).ObjectContext.Translate <int>(reader).FirstOrDefault();
sql.Close();
return(new DataTableResult <T>
{
Draw = search.Draw,
RecordsFiltered = count,
Data = result
});
}
