Part 3 : Implementing w2ui in ASP.Net – Remote Data Source

In my last post, I already share how I do it with local data source, here I like to share on how I implement w2ui grid with remote data source.

1. CONTROLLER

Using remote data source, w2ui grid need to load data from the controller when user choose 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 generate query string base 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"]; // field + type + operator + value
  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"]; //'is', 'between', 'begins with', 'contains', 'ends with'

  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 controller.
  • 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 upload a video to show how I really do it in my project. Try view this video if you having problem to understand the given example .

On next posting, I plan to share on how I do inline editing in w2ui grid.

See you then.

 

One Reply to “Part 3 : Implementing w2ui in ASP.Net – Remote Data Source”

Leave a Reply

Your email address will not be published. Required fields are marked *