Introduction
In my last post, I already shared how I do it with local data source, here I would like to share how I implement w2ui
grid with remote data source.
1. Controller
Using remote data source, w2ui
grid needs to load data from the controller when user chooses to:
- sort
- search
- scroll up and down (when needed)
For this purpose, I have created related functions in the controller.
LoadRecords
- This is my main function to load records from database.
- I will show later how I call this function from
w2ui
grid.
public string LoadRecords()
{
string filter = RequestQueryString();
IEnumerable<Task> records = db.Task
.SqlQuery("SELECT * FROM Tasks " + filter);
var jsonData = JsonConvert.SerializeObject(records);
return jsonData;
}
RequestQueryString
This function generates query string
based on user request:
protected string RequestQueryString(string query = "", string defaultorder = "Id")
{
string filter = query;
var req = Request.Form["request"];
if (req == null) { return filter; }
JObject r = JObject.Parse(req);
int limit = (int)r["limit"];
int offset = (int)r["offset"];
JArray search = (JArray)r["search"];
if (search != null)
{
string SearchLogic = (string)r["searchLogic"];
foreach (JObject o in search.Children<JObject>())
{
if (filter == query)
filter += (query == "" ? "WHERE (" : " AND
(") + " (" + SearchFilter(o) + ") ";
else
filter += SearchLogic + " (" + SearchFilter(o) + ") ";
}
filter += ")";
}
JArray sort = (JArray)r["sort"];
filter += SortFilter(sort, defaultorder);
return filter + " OFFSET " + offset +
" ROWS FETCH NEXT " + limit + " ROWS ONLY";
}
SearchFilter
protected string SearchFilter(JObject o)
{
string field = (string)o["field"];
string opt = (string)o["operator"];
string val = (opt != "between" ? (string)o["value"] : "");
switch (opt)
{
case "is":
val = FormatDate(val);
return (field + " = '" + val + "'");
case "begins":
return (field + " LIKE '" + val + "%'");
case "contains":
return (field + " LIKE '%" + val + "%'");
case "ends":
return (field + " LIKE '%" + val + "'");
case "before":
case "less":
return (field + " < '" + FormatDate(val) + "'");
case "after":
case "more":
return (field + " > '" + FormatDate(val) + "'");
case "between":
string d1 = FormatDate((string)o["value"][0]);
string d2 = FormatDate((string)o["value"][1]);
return (field + " BETWEEN '" + d1 + "' AND '" + d2 + "'");
default: return "";
}
}
SortFilter
protected string SortFilter(JArray sort, string defaultorder)
{
if (sort == null)
return " ORDER BY " + defaultorder;
string ssql = "";
foreach (JObject o in sort.Children<JObject>())
{
string field = (string)o["field"];
string order = (string)o["direction"];
ssql += (ssql == "" ? " ORDER BY " : ", ");
ssql += field + (order == "asc" ? " ASC" : " DESC");
}
return ssql;
}
FormatDate
private string FormatDate(string input)
{
DateTime d;
if (DateTime.TryParseExact(input, "dd-MM-yyyy",
CultureInfo.InvariantCulture,
DateTimeStyles.None, out d))
{
return d.ToString("yyyy-MM-dd");
}
return input;
}
Notes
- I use these functions almost in each of my controllers.
- The only thing I have to change is the model and table name inside
LoadRecords
function.
2. View
Example for Index Page:
@Styles.Render("~/w2ui/css")
<div id="indexGrid" style="width: 100%; height: 400px; overflow: hidden;"></div>
@section Scripts {
@Scripts.Render("~/w2ui/js")
<script>
$(document).ready(function () {
$('#indexGrid').w2grid({
name: 'indexGrid',
url: {
get: '@Url.Action("LoadRecords")',
},
columns: [
{ field: 'lname', caption: 'Last Name', size: '30%', sortable: true },
{ field: 'fname', caption: 'First Name', size: '30%', sortable: true },
{ field: 'email', caption: 'Email', size: '40%', sortable: true },
{ field: 'sdate', caption: 'Start Date', size: '120px', sortable: true }
],
searches: [
{ field: 'fname', caption: 'First Name', type: 'text' },
{ field: 'email', caption: 'Email', type: 'text' },
],
});
});
</script>
}
- Define link to
w2ui
CSS and JavaScript:
@Styles.Render("~/w2ui/css")
@Scripts.Render("~/w2ui/js")
- Define
url
property to LoadRecords
:
url: {
get: '@Url.Action("LoadRecords")',
},
- Define
sortable: true
to enable sorting for selected field:
columns: [
{ field: 'lname', ... , sortable: true },
....
],
- Define the
search
fields:
searches: [
{ field: 'fname', caption: 'First Name', type: 'text' },
{ field: 'email', caption: 'Email', type: 'text' },
],
That’s it, we are good to go. I have uploaded a video to show how I really do it in my project. Try viewing this video if you are having problems understanding the given example.
In the next post, I plan to share how I do inline editing in w2ui
grid.
See you then!
Reference