This article gives a walk-through of selecting table rows with checkboxes using the JQuery plugin Tabulator.js with SQL Server as backend database and server as ASP.Net.
Below is the screenshot of the final output that we want to achieve at the end of this tutorial.
Selecting Tables Rows with Checkboxes
In the above result, we are displaying the data from the database; i.e SQL Server 2014 and fetching the data using jquery ajax call with WebMethod in the ASP.Net code behind CustomerDetails.aspx.cs.
The link for tabulator.js for more details is at http://tabulator.info/ and for the row selection documentation is at http://tabulator.info/docs/4.6/select.
Prerequisites for this tutorial
  1. Visual Studio (any version)
  2. SQL Server (any database version)
  3. Tabulator.js and Tabulator.css (download from the official site provided above)
Database structure and data
Create the database SQL server script with name as Practice.
  1. USE master;
  3. GO
  4. IF DB_ID (N'Practice') IS NOT NULL
  5. DROP DATABASE Practice;
  7. GO
  8. CREATE DATABASE Practice;
Now create the table called tblCustomers2
  1. USE [Practice]
  2. GO
  4. SET ANSI_NULLS ON
  5. GO
  7. SET QUOTED_IDENTIFIER ON
  8. GO
  10. CREATE TABLE [dbo].[tblCustomer2](
  11. [CustomerID] [float] NULL,
  12. [CustomerName] [nvarchar](255) NULL,
  13. [ContactName] [nvarchar](255) NULL,
  14. [Address] [nvarchar](255) NULL,
  15. [City] [nvarchar](255) NULL,
  16. [PostalCode] [float] NULL,
  17. [Country] [nvarchar](255) NULL
  18. ) ON [PRIMARY]
  20. GO
Steps to follow:
Step 1
Create a new project in Visual Studio as given in the below screenshot.
Selecting Tables Rows with Checkboxes
Step 2
Create Web Forms with No Authentication.
Selecting Tables Rows with Checkboxes
Step 3
Below is the structure of the project.
Selecting Tables Rows with Checkboxes
Step 4
Add the Tabulator.js and Tabulator.css into Scripts and Content/CSS folders.
Selecting Tables Rows with Checkboxes
Selecting Tables Rows with Checkboxes
Step 5
Now add the Tabulator.Js and Tabulator.css into Site.Master and Bundle.config as shown in the below screen.
Selecting Tables Rows with Checkboxes
Selecting Tables Rows with Checkboxes
Step 6
Now we have to change the page header and place the application in the header.
  1. <nav class="navbar navbar-expand-lg navbar-dark bg-primary">
  2. <a class="navbar-brand" href="#">Tabulator with C#</a>
  3. <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarColor01" aria-controls="navbarColor01" aria-expanded="false" aria-label="Toggle navigation">
  4. <span class="navbar-toggler-icon"></span>
  5. </button>
  7. <div class="collapse navbar-collapse" id="navbarColor01">
  8. <ul class="navbar-nav mr-auto">
  9. <li class="nav-item active">
  10. <a class="nav-link" href="/">Home <span class="sr-only">(current)</span></a>
  11. </li>
  12. <li class="nav-item">
  13. <a class="nav-link" href="CustomerDetails.aspx">Customer <span class="sr-only">(current)</span></a>
  14. </li>
  15. </ul>
  16. </div>
  17. </nav>
Step 7
Now create a class library as Database and in that create a class as CustomerDB.cs which will handle our database operation using normal ADO.Net SqlDataAdapter to read that from the database SQL query.
Selecting Tables Rows with Checkboxes
  1. public class CustomerDB
  2. {
  3. public readonly SqlConnection Conn = null;
  5. public CustomerDB()
  6. {
  7. this.Conn = new SqlConnection(ConfigurationManager.AppSettings["SQlConnectionString"].ToString());
  8. }
  10. /// <summary>
  11. /// This method will get the list of Customer from database table CustomerRawInfo.
  12. /// </summary>
  13. /// <returns>List of customers in datatable</returns>
  14. public DataTable Customers
  15. {
  16. get
  17. {
  18. string Query = string.Empty;
  19. DataTable dataTable = new DataTable();
  20. SqlDataAdapter sqlDataAdapter = null;
  22. try
  23. {
  24. Query = "SELECT CUSTOMERID,CUSTOMERNAME,CONTACTNAME,ADDRESS,CITY,POSTALCODE,COUNTRY FROM PRACTICE.DBO.TBLCUSTOMER2 WITH(NOLOCK)";
  25. SqlCommand sqlCommand = new SqlCommand(Query, Conn);
  26. Conn.Open();
  28. // create data adapter
  29. sqlDataAdapter = new SqlDataAdapter(sqlCommand);
  31. // this will query your database and return the result to your datatable
  32. sqlDataAdapter.Fill(dataTable);
  33. }
  34. catch (Exception ex)
  35. {
  36. dataTable = null;
  37. }
  38. finally
  39. {
  40. Conn.Close();
  41. sqlDataAdapter.Dispose();
  42. }
  44. return dataTable;
  45. }
  46. }
  47. }
In the connection string provide the details of your database inside the web.config file,
Selecting Tables Rows with Checkboxes

After this build the class library Database and then add the reference to TabulatorWithAspdotnet.
Selecting Tables Rows with Checkboxes
Step 8
Create a web form name CustomerDetails.aspx and in the code bind CustomerDetails.aspx.cs and fetch the data from SQL Database using the class library Database Repository class. To fill the page; i.e CustomerDetails.aspx with SQL database data, we are displaying it using the HTML Table for which table header will be filled with defined columns and rows will be filled from code behind (from CustomerDetails.aspx.cs) with HTML table body structure.

CustomerDetails.aspx.cs(code behind)
  1. public partial class CustomerDetails : System.Web.UI.Page
  2. {
  3. protected void Page_Load(object sender, EventArgs e)
  4. {
  5. }
  7. [WebMethod]
  8. public static object LoadCustomers()
  9. {
  10. // Return response object
  11. object Resp = new { status = 0, data = string.Empty };
  12. try
  13. {
  14. CustomerDB customerDB = new CustomerDB();
  15. DataTable CustomerTable = customerDB.Customers;
  17. if (CustomerTable != null && CustomerTable.Rows.Count > 0)
  18. {
  19. Resp = new
  20. {
  21. status = 1,
  22. data = Newtonsoft.Json.JsonConvert.SerializeObject(CustomerTable)
  23. };
  24. return Resp;
  25. }
  26. }
  27. catch (Exception ex)
  28. {
  29. return null;
  30. }
  31. return null;
  32. }
  33. }
CustomerDetails.aspx
  1. <div class="row">
  2. <div class="col-12 text-center">
  3. <br />
  4. <h4>Selecting Tables Rows with Checkboxes</h4>
  5. <br />
  6. </div>
  7. </div>
  8. <div class="row">
  9. <div class="col-8">
  10. <span class="text-success font-weight-bold mb-10">Selected row(s) : <span id="select-stats" class="pull-left"></span></span>
  11. <div id="CustomerGrid"></div>
  12. </div>
  13. <div class="col-4">
  14. <div class="ContainerGrid">
  15. <ul class="list-group">
  16. </ul>
  17. </div>
  18. </div>
  19. </div>
Now add the script to bind the json data from the server side response using the ajax call. Once the page loads then the grid will process and checkbox will be appended to each row. Now when checkbox is selected then we need to display that selected row data into the list.
  1. <div class="row">
  2. <div class="col-12 text-center mtb-10">
  3. <h3 class="text-underline">Product List(Downloading data into xlsx, json, csv, pdf, html)</h3>
  4. </div>
  5. </div>
  6. <div class="row">
  7. <div class="col-12">
  8. <div class="btn-group btn-group-md mb2-10 float-right">
  9. <button type="button" id="download-csv" class="btn btn-primary">Download CSV</button>
  10. <button type="button" id="download-json" class="btn btn-success">Download JSON</button>
  11. <button type="button" id="download-xlsx" class="btn btn-info">Download XLSX</button>
  12. <button type="button" id="download-pdf" class="btn btn-warning">Download PDF</button>
  13. <button type="button" id="download-html" class="btn btn-dark">Download HTML</button>
  14. </div>
  15. </div>
  16. </div>
  17. <div class="row">
  18. <div class="col-12">
  19. <div id="GridExample"></div>
  20. </div>
  21. </div>
  22. <script type="text/javascript" src="https://oss.sheetjs.com/sheetjs/xlsx.full.min.js"></script>
  23. <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/1.3.5/jspdf.min.js"></script>
  24. <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf-autotable/3.0.5/jspdf.plugin.autotable.js"></script>
  25. <script type="text/javascript">
  26. var table = "";
  27. $(document).ready(function () {
  29. var ProductData = LoadData("ProductListDownload.aspx/LoadProductList");
  31. //trigger download of data.csv file
  32. document.getElementById("download-csv").addEventListener("click", function () {
  33. table.download("csv", "data.csv");
  34. });
  36. //trigger download of data.json file
  37. document.getElementById("download-json").addEventListener("click", function () {
  38. table.download("json", "data.json");
  39. });
  41. //trigger download of data.xlsx file
  42. document.getElementById("download-xlsx").addEventListener("click", function () {
  43. table.download("xlsx", "data.xlsx", { sheetName: "My Data" });
  44. });
  46. //trigger download of data.pdf file
  47. document.getElementById("download-pdf").addEventListener("click", function () {
  48. table.download("pdf", "data.pdf", {
  49. orientation: "portrait", //set page orientation to portrait
  50. title: "Example Report", //add title to report
  51. });
  52. });
  54. //trigger download of data.html file
  55. document.getElementById("download-html").addEventListener("click", function () {
  56. table.download("html", "data.html", { style: true });
  57. });
  58. })
  60. // Load the Product grid Data from the server
  61. function LoadData(url) {
  62. try {
  63. var ajaxObj = $.ajax({
  64. type: "POST",
  65. url: url,
  66. data: null,
  67. dataType: "JSON",
  68. contentType: "application/json; charset=utf-8",
  69. async: false,
  70. cache: false,
  71. success: function (res) {
  72. // This will check if is any null or undefined data is there or not and also check the status of the request.
  73. if (res && res.d.status == 1 && res.d.data) {
  75. // Parsing the string data into Array of object
  76. var ProdtGridData = JSON.parse(res.d.data);
  77. ProcessTableStructure(ProdtGridData);
  78. }
  79. },
  80. error: function (err, type, httpStatus) {
  81. alert("Error! Please try later.");
  82. }
  83. });
  84. } catch (e) {
  85. console.log("Page :ProductListDownload.aspx | Function:LoadData | Error:" + e);
  86. }
  87. }
  89. // Process the customer grid data into multi-select row table
  90. function ProcessTableStructure(gridData) {
  91. try {
  92. table = new Tabulator("#GridExample", {
  93. height: "50vh",
  94. layout: "fitDataStretch",
  95. pagination: "local",
  96. paginationSize: 10,
  97. paginationSizeSelector: [10, 20, 30, 50, 100],
  98. data: gridData,
  99. columns: [
  100. { title: "Product Id", field: "PRODUCTID", width: 110 },
  101. { title: "Name", field: "NAME", width: 160 },
  102. { title: "Category", field: "CATEGORY", width: 150 },
  103. { title: "Main Category", field: "MAINCATEGORY", width: 150 },
  104. { title: "Supplier Name", field: "SUPPLIERNAME", width: 150 },
  105. { title: "Description", field: "DESCRIPTION", width: 450 },
  106. { title: "Quantity", field: "QUANTITY", width: 100 },
  107. {
  108. title: "Price", field: "PRICE", width: 80, formatter: "money", formatterParams: {
  109. decimal: ".",
  110. thousand: ",",
  111. symbol: "₹",
  112. symbolBefore: "p",
  113. precision: false,
  114. }
  115. },
  116. ]
  117. });
  118. } catch (e) {
  119. console.log("Page :CustomerDetails.aspx | Function:ProcessTableStructure | Error:" + e);
  120. }
  121. }
  122. </script>
Now run the application to get the below result.
Result
Selecting Tables Rows with Checkboxes