jQuery datatables get data from database table

Suggested Videos
Part 103 - jQuery autocomplete with images and text
Part 104 - Cascading dropdownlist using jquery and asp.net
Part 105 - jQuery datatables plugin



In this video we will discuss how to use database data with jQuery datatables plugin. There are many ways to get data into DataTables. If the data is small you can retrieve all the data at once and the processing (i.e paging, searching, ordering) is  done on the client-side. 



On the other hand if the database data is large, server-side processing can be enabled. With server-side processing enabled, all paging, searching, ordering actions that DataTables performs are handed off to the server.

In this video we will discuss retrieving all the data at once. The datatables plugin performs all the processing (i.e paging, searching, ordering etc) on the client-side.

We want to retrieve data from the following database table tblEmployee


The database data should then be displayed using jQuery datatables plugin 


Step 1 : Create database table tblEmployee and the stored procedure

Create table tblEmployees

(

    ID int primary key identity,

    FirstName nvarchar(50),

    LastName nvarchar(50),

    Gender nvarchar(20),

    JobTitle nvarchar(20),

    WebSite nvarchar(100),

    Salary int,

    HireDate datetime

)

Go

Insert into tblEmployees values('Mark', 'Hastings','Male','Developer',

'http://pragimtech.com', 50000, '1/31/1978')

Insert into tblEmployees values('Maria', 'Nicholas','Female','Developer',

'http://csharp-video-tutorials.blogspot.com', 50000, '12/22/1976')

Insert into tblEmployees values('Robert', 'Stephenson','Male','Sr. Developer',

NULL, 45000, '3/25/1980')

Insert into tblEmployees values('Mary', 'Quant','Female','Sr. Developer',

NULL, 65000, '5/27/1979')

Insert into tblEmployees values('John', 'Stenson','Male','Sr. Developer',

'http://csharp-sample-programs.blogspot.com', 55000, '2/7/1980')

Insert into tblEmployees values('Gilbert', 'Sullivan','Male','Developer',

NULL, 56000, '1/31/1978')

Insert into tblEmployees values('Rob', 'Gerald','Male','Sr. Developer',

'http://www.venkatcsharpinterview.blogspot.com', 45000, '9/29/1975')

Insert into tblEmployees values('Ron', 'Simpson','Male','Developer',

'http://www.venkataspinterview.blogspot.com', 75000, '7/24/1972')

Insert into tblEmployees values('Sara', 'Solomon','Female','Sr. Developer',

NULL, 45000, '1/31/1978')

Insert into tblEmployees values('Rad', 'Wicht','Male','Sr. Developer',

'http://wcfinterviewquestions.blogspot.com', 55000, '8/20/1980')

Insert into tblEmployees values('Julian', 'John','Male','Developer',

'http://venkatsqlinterview.blogspot.com', 65000, '5/24/1981')

Insert into tblEmployees values('James', 'Bynes','Male','Sr. Developer',

'http://mvcquestions.blogspot.com', 55000, '4/14/1979')

Insert into tblEmployees values('Mary', 'Ward','Female','Developer',

'http://hrinterviewquestions.blogspot.com', 60000, '12/30/1979')

Insert into tblEmployees values('Michael', 'Niron','Male','Sr. Developer',

NULL, 45000, '8/28/1975')

Create procedure spGetEmployees

as

Begin

 Select * from tblEmployees

End

Go

Step 2 : Create new asp.net web application project. Name it Demo. 

Step 3 : Include a connection string in the web.config file to your database.

<add name="DBCS"

      connectionString="server=.;database=SampleDB;integrated security=SSPI"/>

Step 4 : Add a class file to the project. Name it Employee.cs. Copy and paste the following code. 

using System;

namespace Demo

{

    public class Employee

    {

        public int Id { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string Gender { get; set; }

        public string JobTitle { get; set; }

        public string WebSite { get; set; }

        public int Salary { get; set; }

        public DateTime HireDate { get; set; }

    }

}

Step 5 : Add a WebService (ASMX) to the project. Name it EmployeeService.asmx. Copy and paste the following code. 

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Web.Script.Serialization;

using System.Web.Services;

namespace Demo

{

    [WebService(Namespace = "http://tempuri.org/")]

    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

    [System.ComponentModel.ToolboxItem(false)]

    [System.Web.Script.Services.ScriptService]

    public class EmployeeService : System.Web.Services.WebService

    {

        [WebMethod]

        public void GetEmployees()

        {

            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

            List<Employee> employees = new List<Employee>();

            using (SqlConnection con = new SqlConnection(cs))

            {

                SqlCommand cmd = new SqlCommand("spGetEmployees", con);

                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

                    Employee employee = new Employee();

                    employee.Id = Convert.ToInt32(rdr["Id"]);

                    employee.FirstName = rdr["FirstName"].ToString();

                    employee.LastName = rdr["LastName"].ToString();

                    employee.Gender = rdr["Gender"].ToString();

                    employee.JobTitle = rdr["JobTitle"].ToString();

                    employee.WebSite = rdr["WebSite"].ToString();

                    employee.Salary = Convert.ToInt32(rdr["Salary"]);

                    employee.HireDate = Convert.ToDateTime(rdr["HireDate"]);

                    employees.Add(employee);

                }

            }

            JavaScriptSerializer js = new JavaScriptSerializer();

            Context.Response.Write(js.Serialize(employees));

        }

    }

}

Step 6 : Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code.  

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <script src="jquery-1.11.2.js"></script>

    <link rel="stylesheet" type="text/css"

        href="//cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />

    <script src="//cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js">

    </script>

    <script type="text/javascript">

        $(document).ready(function () {

            $.ajax({

                url: 'EmployeeService.asmx/GetEmployees',

                method: 'post',

                dataType: 'json',

                success: function (data) {

                    $('#datatable').dataTable({

                        paging: true,

                        sort: true,

                        searching: true,

                        scrollY: 200,

                        data: data,

                        columns: [

                            { 'data': 'Id' },

                            { 'data': 'FirstName' },

                            { 'data': 'LastName' },

                            { 'data': 'Gender' },

                            { 'data': 'JobTitle' },

                            {

                                'data': 'WebSite',

                                'sortable': false,

                                'searchable': false,

                                'render': function (webSite) {

                                    if (!webSite) {

                                        return 'N/A';

                                    }

                                    else {

                                        return '<a href=' + webSite + '>'

                                            + webSite.substr(0, 10) + '...' + '</a>';

                                    }

                                }

                            },

                            {

                                'data': 'Salary',

                                'render': function (salary) {

                                    return "$" + salary;

                                }

                            },

                            {

                                'data': 'HireDate',

                                'render': function (jsonDate) {

                                    var date = new Date(parseInt(jsonDate.substr(6)));

                                    var month = date.getMonth() + 1;

                                    return month + "/" + date.getDate() + "/" + date.getFullYear();

                                }

                            }

                        ]

                    });

                }

            });

        });

    </script>

</head>

<body style="font-family: Arial">

    <form id="form1" runat="server">

        <div style="width: 900px; border: 1px solid black; padding: 3px">

            <table id="datatable">

                <thead>

                    <tr>

                        <th>Id</th>

                        <th>First Name</th>

                        <th>Last Name</th>

                        <th>Gender</th>

                        <th>Job Title</th>

                        <th>Web Site</th>

                        <th>Salary</th>

                        <th>Hire Date</th>

                    </tr>

                </thead>

                <tfoot>

                    <tr>

                        <th>Id</th>

                        <th>First Name</th>

                        <th>Last Name</th>

                        <th>Gender</th>

                        <th>Job Title</th>

                        <th>Web Site</th>

                        <th>Salary</th>

                        <th>Hire Date</th>

                    </tr>

                </tfoot>

            </table>

        </div>

    </form>

</body>

</html>