Post
Ask Question

Selecting Table Rows with Checkboxes

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;    
  2.      
  3. GO    
  4.    IF DB_ID (N'Practice') IS NOT NULL    
  5.       DROP DATABASE Practice;    
  6.      
  7. GO    
  8.    CREATE DATABASE Practice;   
Now create the table called tblCustomers2 
  1. USE [Practice]  
  2. GO  
  3.   
  4. SET ANSI_NULLS ON  
  5. GO  
  6.   
  7. SET QUOTED_IDENTIFIER ON  
  8. GO  
  9.   
  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]  
  19.   
  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>  
  6.   
  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;  
  4.   
  5.        public CustomerDB()  
  6.        {  
  7.            this.Conn = new SqlConnection(ConfigurationManager.AppSettings["SQlConnectionString"].ToString());  
  8.        }  
  9.   
  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;  
  21.   
  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();  
  27.   
  28.                    // create data adapter  
  29.                    sqlDataAdapter = new SqlDataAdapter(sqlCommand);  
  30.   
  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.                }  
  43.   
  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.         }  
  6.   
  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;  
  16.   
  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 () {  
  28.   
  29.         var ProductData = LoadData("ProductListDownload.aspx/LoadProductList");  
  30.   
  31.         //trigger download of data.csv file  
  32.         document.getElementById("download-csv").addEventListener("click", function () {  
  33.             table.download("csv", "data.csv");  
  34.         });  
  35.   
  36.         //trigger download of data.json file  
  37.         document.getElementById("download-json").addEventListener("click", function () {  
  38.             table.download("json", "data.json");  
  39.         });  
  40.   
  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.         });  
  45.   
  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.         });  
  53.   
  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.     })  
  59.   
  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) {  
  74.   
  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.     }  
  88.   
  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


Similar Articles