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.

USE master;

GO
   IF DB_ID (N'Practice') IS NOT NULL
      DROP DATABASE Practice;

GO
   CREATE DATABASE Practice;

Now, create the table called CustomerRawInfo

USE [Practice]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CustomerRawInfo](
   [CustomerName]  [varchar](255)  NULL,
   [Email] [varchar](255)  NULL,
   [Company] [varchar](255)    NULL,
   [Street] [varchar](255) NULL,
   [City] [varchar](255)   NULL,
   [Region] [varchar](50)  NULL,
   [Country] [varchar](100)    NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
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.

<nav class="navbar navbar-expand-lg navbar-dark bg-primary">
     <a class="navbar-brand" href="#">Tabulator with C#</a>
     <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarColor01" aria-controls="navbarColor01" aria-expanded="false" aria-label="Toggle navigation">
         <span class="navbar-toggler-icon"></span>
     </button>

     <div class="collapse navbar-collapse" id="navbarColor01">
         <ul class="navbar-nav mr-auto">
             <li class="nav-item active">
                 <a class="nav-link" href="#">Home <span class="sr-only">(current)</span></a>
             </li>
         </ul>
     </div>
 </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.

 

 

public class Repository
    {
        public readonly SqlConnection Conn = null;

        public Repository()
        {
            this.Conn = new SqlConnection(ConfigurationManager.AppSettings["SQlConnectionString"].ToString());
        }

        /// <summary>
        /// This method will get the list of Customer from database table CustomerRawInfo.
        /// </summary>
        /// <returns>List of customers in datatable</returns>
        public DataTable GetCustomers()
        {
            string Query                    = string.Empty;
            DataTable dataTable             = new DataTable();
            SqlDataAdapter sqlDataAdapter   = null;

            try
            {
                Query = "SELECT ROW_NUMBER() OVER (ORDER BY CUSTOMERNAME) ROW_NUM,CUSTOMERNAME,EMAIL,COMPANY,STREET,CITY,REGION,COUNTRY FROM CUSTOMERRAWINFO WITH(NOLOCK)";
                SqlCommand sqlCommand = new SqlCommand(Query, Conn);
                Conn.Open();

                // create data adapter
                sqlDataAdapter = new SqlDataAdapter(sqlCommand);

                // this will query your database and return the result to your datatable
                sqlDataAdapter.Fill(dataTable);
            }
            catch (Exception ex)
            {
                dataTable = null;
            }
            finally
            {
                Conn.Close();
                sqlDataAdapter.Dispose();
            }

            return dataTable;
        }
    }

 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)

public partial class _Default : Page
   {
       private Repository repository = null;

       protected void Page_Load(object sender, EventArgs e)
       {
           if (!IsPostBack)
           {
               this.repository = new Repository();
           }
       }

       /// <summary>
       /// This method will return the prepare html table body
       /// </summary>
       /// <returns>string html table body</returns>
       public string GetTableRow()
       {
           string result = string.Empty;
           string htmlTr = string.Empty;
           string htmlTd = string.Empty;

           DataTable dataTable = new DataTable();

           try
           {
               // Get the data from the database repository
               dataTable = this.repository.GetCustomers();
               if (dataTable != null && dataTable.Rows.Count > 0)
               {
                   foreach (DataRow row in dataTable.Rows)
                   {
                       foreach (var item in row.ItemArray)
                       {
                           // append the data to each cell of a row
                           htmlTd += "<td>" + item + "</td>";
                       }

                       // append the row data to table row
                       htmlTr += "<tr>" + htmlTd + "</tr>";

                       // clear for the next row cell data so that previous data will not append
                       htmlTd = string.Empty;
                   }
               }

               result = htmlTr;
           }
           catch (Exception)
           {
               result = string.Empty;
           }
           finally
           {
               dataTable = null;
           }
           return result;
       }

   }

 Default.aspx 

<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
    <br />
    <h3>Tabulator with C# basic(Converting the HTML Table into Tabulator table</h3>
    <br />
    <div class="row">
        <div class="col-12 tabulator-example">
            <table class="table table-condensed table-striped table-hover table-sm tabulator_table">
                <thead>
                    <tr class="table-primary">
                        <th scope="col">#</th>
                        <th scope="col">Customer Name</th>
                        <th scope="col">Email</th>
                        <th scope="col">Company</th>
                        <th scope="col">Street</th>
                        <th scope="col">City</th>
                        <th scope="col">Region</th>
                        <th scope="col">Country</th>
                    </tr>
                </thead>
                <tbody>
                    <%= GetTableRow() %>
                </tbody>
            </table>
        </div>
    </div>
</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. 

<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">
    <br />
    <h3>Tabulator with C# basic(Converting the HTML Table into Tabulator table</h3>
    <br />
    <div class="row">
        <div class="col-12 tabulator-example">
            <table class="table table-condensed table-striped table-hover table-sm tabulator_table">
                <thead>
                    <tr class="table-primary">
                        <th scope="col">#</th>
                        <th scope="col">Customer Name</th>
                        <th scope="col">Email</th>
                        <th scope="col">Company</th>
                        <th scope="col">Street</th>
                        <th scope="col">City</th>
                        <th scope="col">Region</th>
                        <th scope="col">Country</th>
                    </tr>
                </thead>
                <tbody>
                    <%= GetTableRow() %>
                </tbody>
            </table>
        </div>
    </div>

    <script type="text/javascript">
        $(document).ready(function () {
            var table = new Tabulator(".tabulator_table", {
                height: "70vh",
                layout:"fitDataStretch",
                pagination: "local",
                paginationSize: 10,
                paginationSizeSelector: [10, 20, 30, 50, 100]
            });
        });
    </script>
</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.