Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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
jQuery datatables get data from database table

The database data should then be displayed using jQuery datatables plugin 
jquery datatables database

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>

jQuery tutorial for beginners

5 comments:

  1. i got following errors..

    DataTables warning: table id={id} - Requested unknown parameter '{parameter}' for row {row-index}

    ReplyDelete
  2. does not work,
    after google i change
    method : 'post' to
    type : 'post' and it work now

    ReplyDelete
  3. I didn't find it hard to tweak this for my needs, well easy

    ReplyDelete
  4. Thank you Sr for sharing this easy reading sample... After implementing this and retrieve from a WS data... i wonder if you can give us with this sample a way to refresh the data of datatables if something change into database... i was trying several method without results, ajax.reload, fnReloadAjax, etc, but nothing work or worst, show errors...

    Can you provide a simple way to call a refresh of the data?
    Thanks in advance

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.