Click here to Skip to main content
14,975,683 members
Articles / Web Development / CSS
Technical Blog
Posted 18 Dec 2018

Stats

4.9K views
4 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
2.00/5 (1 vote)
21 Jan 2019CPOL1 min read
How to implement w2ui grid with 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.
JavaScript
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:

JavaScript
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

JavaScript
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

JavaScript
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

JavaScript
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:

JavaScript
@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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

bapak71
Software Developer
Malaysia Malaysia
No Biography provided

Comments and Discussions

 
QuestionSecurity? Pin
HaBiX25-Jan-19 20:25
MemberHaBiX25-Jan-19 20:25 
Seems like a good way to inject some custom sql into your queries..

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.