When we need a database table to display on our Webpage in MVC, basically we prefer model binding methodology. If our budget is high, we go for some third party tools like Kendo, Dev Express, Telerik etc., which costs us a lot and as a common developer or as a small start up company, it is very difficult to purchase such types of tools and ultimately we go for traditional techniques, which don't give us greater productivity.

Here, I am going to introduce to you a tool, which is completely free of cost, has faster fetching capacity, rich and modern functional features, good security, much less configuration and a smoothly behaving UI. I have been using it for my projects for many days and believe me, it's awesome.

It is DataTable

As I mentioned above, it needs much less configuration. I will complete this session with very few steps. I hope, you can understand it and start implementing it in your project from here on out.

Let's take a scenario, where we have a product information table and we want to show on our product list page.

Products
ProdID ProdName Manufacturer Price Stock

Step 1 - Create a Product Model Class, as given below-

  1. public class Products {
  2. publicint ProdID {
  3. get;
  4. set;
  5. }
  6. publicstring ProdName {
  7. get;
  8. set;
  9. }
  10. publicstring Manufacturer {
  11. get;
  12. set;
  13. }
  14. publicdecimal Price {
  15. get;
  16. set;
  17. }
  18. publicint Stock {
  19. get;
  20. set;
  21. }
  22. }
Step 2 - Code to read from the database. You may write this method inside the controller or your separate DB handler class.
  1. public DataTable GetProducts() {
  2. string query = "SELECT ProdID,ProdName,Manufacturer,Price,Stock FROM Products ORDER BY ProdID DESC";
  3. SqlConnection connection = newSqlConnection();
  4. connection.ConnectionString = "your connection string";
  5. SqlCommand command = newSqlCommand(query, connection);
  6. SqlDataAdapter adapter = newSqlDataAdapter();
  7. adapter.SelectCommand = command;
  8. DataTable table = newDataTable();
  9. adapter.Fill(table);
  10. return table;
  11. }
Step 3 - Call GetProducts(), retrieve from the table and create a list object to contain product info. The method should return Json. This method must be written in controller.
  1. public JsonResult GetProductList() {
  2. List < Products > listProducts = newList < Products > ();
  3. DataTable table = GetProducts();
  4. foreach(DataRow row in table.Rows) {
  5. Products objProducts = newProducts();
  6. objProducts.ProdID = Convert.ToInt32(row["ProdID"]);
  7. objProducts.ProdName = row["ProdName"].ToString();
  8. objProducts.Manufacturer = row["Manufacturer"].ToString();
  9. objProducts.Price = Convert.ToDecimal(row["Price"]);
  10. objProducts.Stock = Convert.ToInt32(row["Stock"]);
  12. listProducts.Add(objProducts);
  13. }
  15. var jsonResult = Json(listProducts, JsonRequestBehavior.AllowGet);
  16. jsonResult.MaxJsonLength = int.MaxValue;
  17. return jsonResult;
  18. }
Step 4

a. Go to your View, where you want to display the Grid. Create a table with the following format-
  1. <table id="GridView" class="display" width="100%">
  2. <thead>
  3. <tr>
  4. <th>Product ID</th>
  5. <th>Product Name</th>
  6. <th>Manufacturer</th>
  7. <th>Price</th>
  8. <th>Stock</th>
  9. <th>Actions</th>
  10. </tr>
  11. </thead>
  12. <tbody></tbody>
  13. </table>
b. Place jQuery and DataTable, given below, plugin inside Head Tag-
  1. < link href = "https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css"
  2. rel = "stylesheet" / >
  3. < script href = "https://code.jquery.com/jquery-1.12.3.js" > < /script> < script src = "https://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js" >
  4. < /script>
c. Place the code, given below, inside the script-
  1. <script type="text/javascript">
  2. $(document).ready(function() {
  3. $('#GridView').dataTable({
  4. "bPaginate": true,
  5. "autoWidth": false,
  6. "sPaginationType": "full_numbers",
  7. "order": [
  8. [0, "asc"],
  9. [1, 'asc']
  10. ],
  11. "bFilter": true,
  12. "iDisplayLength": 20,
  13. "lengthMenu": [
  14. [10, 20, 50, 100, -1],
  15. [10, 20, 50, 100, "All"]
  16. ],
  17. "ajax": {
  18. "type": "POST",
  19. "dataType": 'json',
  20. "url": "@Url.Action("
  21. GetProductList ", "
  22. Home ")"
  23. },
  24. "columns": [{
  25. "data": "ProdID"
  26. }, {
  27. "data": "ProdName"
  28. }, {
  29. "data": "Manufacturer"
  30. }, {
  31. "data": "Price"
  32. }, {
  33. "data": "Stock"
  34. }, {
  35. "data": "ProdID"
  36. }],
  37. "aoColumnDefs": [{
  38. "aTargets": [5],
  39. "fnCreatedCell": function(nTd, sData, oData, iRow, iCol) {
  40. $(nTd).empty();
  41. $(nTd).append(" <
  42. button title = \"Edit Record\" onclick=\ "
  43. EditField('" + oData.ProdID + "', '" + oData.ProdName + "')\
  44. "></button>" + " <
  45. button title = \"Delete Record\" onclick=\ "
  46. DeleteField(''
  47. " + oData.ProdID + "
  48. ','
  49. " + oData.ProdName + "
  50. ')\"></button>"); } }, ] }); }); function EditField(prodID,prodName) { //your code to edit the selected record } function DeleteField(prodID, prodName) { //your code to delete the selected record }
  51. </script>
You will get a grid with product list, along with each row. You will get an Edit and a Delete button, which calls EditField() and DeleteField() JS method respectively.

Write your own JS code to edit and delete the selected record. This is to inform you that the “ProdID” is being sent from the button to the function. Call your back-end function to edit and delete the record, based on “ProdID”.

How can you call a back-end function from jQuery ? Read my previous article, whose link is given below-
There are many attractive features, which you will get inDataTable. In this article, I explained the basic features of DataTable.

You can export the displayed record into PDF, Excel, CSV. Can you imagine? It is very simple! Add one line of code. That's it.

To know how to export, please visit the below mentioned blog or else wait for my next article and meanwhile practice this by developing demo projects.
Thank you for reading this blog.