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

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"]; // 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 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:
  JavaScript

  @Styles.Render("~/w2ui/css")
  @Scripts.Render("~/w2ui/js")

• Define url property to LoadRecords:
  JavaScript

  url: { 
      get: '@Url.Action("LoadRecords")', 
  },

• Define sortable: true to enable sorting for selected field:
  JavaScript

  columns: [ 
    { field: 'lname', ... , sortable: true }, 
    ....
  ],

• Define the search fields:
  JavaScript

  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

• w2ui homepage
• Part 1 - Implement w2ui in asp.net mvc - basic setting
• Part 2 - w2ui.grid with local data source
• Part 4 - How I show photo in w2ui grid