Display Data in Tabulator.js Table from ASP.NET

Introduction 

 
This article gives a walk-through of the jQuery Tabulator plugin used to display data stored in the database using ASP.NET Ado.net.
 
Below is a screenshot of the final output we want to achieve at the end of this tutorial.

 
In the above result, we are displaying the data from the database (i.e. from SQL Server 2014) and displaying the Tabulator.js table from the HTML table.
 
Below is a list for further details regarding the Tabulator.
 
http://tabulator.info/
 
Prerequisites
  1. Visual Studio (any version) 
  2. SQL Server (the database version can be any) 
  3. Tabulator.js and Tabulator.css (download from the official site provided in the above) 
Database Structure and Data
 
Create the database SQL server script with the name Practice. Then, create a table called CustomerRawInfo.
  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 CustomerRawInfo
  1. USE [Practice]  
  2. GO  
  3. SET ANSI_NULLS ON  
  4. GO  
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7. SET ANSI_PADDING ON  
  8. GO  
  9. CREATE TABLE [dbo].[CustomerRawInfo](  
  10.    [CustomerName]  [varchar](255)  NULL,  
  11.    [Email] [varchar](255)  NULL,  
  12.    [Company] [varchar](255)    NULL,  
  13.    [Street] [varchar](255) NULL,  
  14.    [City] [varchar](255)   NULL,  
  15.    [Region] [varchar](50)  NULL,  
  16.    [Country] [varchar](100)    NULL  
  17. ON [PRIMARY]  
  18. GO  
  19. SET ANSI_PADDING OFF  
  20. GO  
 
Step 1
 
Create a new project in Visual Studio, as shown in the below screenshot.
 
 
Step 2
 
Create Web Forms with No Authentication.

 
Step 3
 
Below the structure of the project.
 
 
 
Step 4
 
Add the Tabulator.js and Tabulator.css into the Scripts and Content/CSS folders.
 
 
 
 
Step 5
 
Now add the Tabulator.Js and Tabulator.css into Site.Master and Bundle.config, as shown in the below screen.
 
 

 
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.          </ul>  
  13.      </div>  
  14.  </nav>   
Step 7
 
Now, create a class library as Database. In that, create a class as Repository.cs that will handle our database operation using normal ADO.Net SqlDataAdapter to read from the database SQL query.
 
 
  1. public class Repository  
  2.     {  
  3.         public readonly SqlConnection Conn = null;  
  4.   
  5.         public Repository()  
  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 GetCustomers()  
  15.         {  
  16.             string Query                    = string.Empty;  
  17.             DataTable dataTable             = new DataTable();  
  18.             SqlDataAdapter sqlDataAdapter   = null;  
  19.   
  20.             try  
  21.             {  
  22.                 Query = "SELECT ROW_NUMBER() OVER (ORDER BY CUSTOMERNAME) ROW_NUM,CUSTOMERNAME,EMAIL,COMPANY,STREET,CITY,REGION,COUNTRY FROM CUSTOMERRAWINFO WITH(NOLOCK)";  
  23.                 SqlCommand sqlCommand = new SqlCommand(Query, Conn);  
  24.                 Conn.Open();  
  25.   
  26.                 // create data adapter  
  27.                 sqlDataAdapter = new SqlDataAdapter(sqlCommand);  
  28.   
  29.                 // this will query your database and return the result to your datatable  
  30.                 sqlDataAdapter.Fill(dataTable);  
  31.             }  
  32.             catch (Exception ex)  
  33.             {  
  34.                 dataTable = null;  
  35.             }  
  36.             finally  
  37.             {  
  38.                 Conn.Close();  
  39.                 sqlDataAdapter.Dispose();  
  40.             }  
  41.   
  42.             return dataTable;  
  43.         }  
  44.     }  
 In the connection string, provide the details of your database inside the web.config file.
 
 
 
After this, build the class library Database and then add a reference to TabulatorWithAspdotnet. 
 
 
Step 8
 
Create a web form named Default.aspx, and in the code bind Default.aspx.cs to fetch the data from SQL Database using the class library Database Repository class. To fill the page (i.e Default.aspx) with SQL database data, we are displaying it using the HTML Table for which the table header will be filled with defined columns and rows will be filled from code behind (from Default.aspx.cs) with the HTML table body structure prepared.
 
Default.aspx.cs (code behind)
  1. public partial class _Default : Page  
  2.    {  
  3.        private Repository repository = null;  
  4.   
  5.        protected void Page_Load(object sender, EventArgs e)  
  6.        {  
  7.            if (!IsPostBack)  
  8.            {  
  9.                this.repository = new Repository();  
  10.            }  
  11.        }  
  12.   
  13.        /// <summary>  
  14.        /// This method will return the prepare html table body  
  15.        /// </summary>  
  16.        /// <returns>string html table body</returns>  
  17.        public string GetTableRow()  
  18.        {  
  19.            string result = string.Empty;  
  20.            string htmlTr = string.Empty;  
  21.            string htmlTd = string.Empty;  
  22.   
  23.            DataTable dataTable = new DataTable();  
  24.   
  25.            try  
  26.            {  
  27.                // Get the data from the database repository  
  28.                dataTable = this.repository.GetCustomers();  
  29.                if (dataTable != null && dataTable.Rows.Count > 0)  
  30.                {  
  31.                    foreach (DataRow row in dataTable.Rows)  
  32.                    {  
  33.                        foreach (var item in row.ItemArray)  
  34.                        {  
  35.                            // append the data to each cell of a row  
  36.                            htmlTd += "<td>" + item + "</td>";  
  37.                        }  
  38.   
  39.                        // append the row data to table row  
  40.                        htmlTr += "<tr>" + htmlTd + "</tr>";  
  41.   
  42.                        // clear for the next row cell data so that previous data will not append  
  43.                        htmlTd = string.Empty;  
  44.                    }  
  45.                }  
  46.   
  47.                result = htmlTr;  
  48.            }  
  49.            catch (Exception)  
  50.            {  
  51.                result = string.Empty;  
  52.            }  
  53.            finally  
  54.            {  
  55.                dataTable = null;  
  56.            }  
  57.            return result;  
  58.        }  
  59.   
  60.    }  
 Default.aspx 
  1. <asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">  
  2.     <br />  
  3.     <h3>Tabulator with C# basic(Converting the HTML Table into Tabulator table</h3>  
  4.     <br />  
  5.     <div class="row">  
  6.         <div class="col-12 tabulator-example">  
  7.             <table class="table table-condensed table-striped table-hover table-sm tabulator_table">  
  8.                 <thead>  
  9.                     <tr class="table-primary">  
  10.                         <th scope="col">#</th>  
  11.                         <th scope="col">Customer Name</th>  
  12.                         <th scope="col">Email</th>  
  13.                         <th scope="col">Company</th>  
  14.                         <th scope="col">Street</th>  
  15.                         <th scope="col">City</th>  
  16.                         <th scope="col">Region</th>  
  17.                         <th scope="col">Country</th>  
  18.                     </tr>  
  19.                 </thead>  
  20.                 <tbody>  
  21.                     <%= GetTableRow() %>  
  22.                 </tbody>  
  23.             </table>  
  24.         </div>  
  25.     </div>  
  26. </asp:Content>  
 Below is result that we get before implementing the tabulator.js.
 
 
 
Step 9
 
Now we have to implement the tabulator.js table to the normal html table. For that, in the table class we are defining class as tabulator_table  and below to HTML Table add the script for tabulator. 
  1. <asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">  
  2.     <br />  
  3.     <h3>Tabulator with C# basic(Converting the HTML Table into Tabulator table</h3>  
  4.     <br />  
  5.     <div class="row">  
  6.         <div class="col-12 tabulator-example">  
  7.             <table class="table table-condensed table-striped table-hover table-sm tabulator_table">  
  8.                 <thead>  
  9.                     <tr class="table-primary">  
  10.                         <th scope="col">#</th>  
  11.                         <th scope="col">Customer Name</th>  
  12.                         <th scope="col">Email</th>  
  13.                         <th scope="col">Company</th>  
  14.                         <th scope="col">Street</th>  
  15.                         <th scope="col">City</th>  
  16.                         <th scope="col">Region</th>  
  17.                         <th scope="col">Country</th>  
  18.                     </tr>  
  19.                 </thead>  
  20.                 <tbody>  
  21.                     <%= GetTableRow() %>  
  22.                 </tbody>  
  23.             </table>  
  24.         </div>  
  25.     </div>  
  26.   
  27.     <script type="text/javascript">  
  28.         $(document).ready(function () {  
  29.             var table = new Tabulator(".tabulator_table", {  
  30.                 height: "70vh",  
  31.                 layout:"fitDataStretch",  
  32.                 pagination: "local",  
  33.                 paginationSize: 10,  
  34.                 paginationSizeSelector: [10, 20, 30, 50, 100]  
  35.             });  
  36.         });  
  37.     </script>  
  38. </asp:Content>  
Now run the application again and tabulator will convert the normal HTML table to a table with pagination, sorting and many other options based in the option provided inside the tabulator object.
 
Result
 
 
 
 
 

Summary

 
In this article, we discussed how to implement a tabulator.js table on an HTML Table with pagination, sorting, etc.