Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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

15 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
  5. Getting this error: The name 'Context' does not exist in the current context

    ReplyDelete
  6. Please I need help on getting it to work with web service online. Only works on local machine.

    ReplyDelete
  7. My datatable function is not coming into my webform

    ReplyDelete
  8. DataTables warning: table id={id} - Requested unknown parameter '{parameter}' for row {row-index}
    Please check the class what you define
    ex: In Javascript , I have below code
    { 'data': 'MyId' },

    and In class I have
    public int MyId { get; set; }

    this will resolve the problem

    ReplyDelete
  9. How to implement update and delete function in this table

    ReplyDelete
  10. return month + "/" + date.getDate() + "/" + date.getFullYear();
    this will retun MM/DD/YYYY

    ReplyDelete
  11. When i use service method without parameters all things are good. But when i use service with parameters the Ajax program not succèss and i have en error message that gives a list of my JSON result. Can you help me please.

    ReplyDelete
  12. All work on the local or on the server computer linked Sql on the Server after publishing. But not show data when I run on the work station after publishing. I tried whole day, but could not solve this problem. Please help...

    ReplyDelete
  13. why this method is not working in MVC. i am unable to call webservice method from my view. please help

    ReplyDelete
  14. WEB SERVICES IS WORKING BUT SAME CODE AS HAVE WRITTEN JQUERY AJAX CODE IS NOT WORKING TO GET THE DATA.

    ReplyDelete

It would be great if you can help share these free resources