Create and Read Operations using ASP.NET WCF Services

Description: In this article, we will create a WCF Service that will Insert data into Database and retreive the data from Database. Further we will Consume this service in ASP.NET Web Application and also learn the techniques to generate logs from the client application.

Agenda: In this example, we are having a student table that contains the details of a student and the type of course that he opted for during admission. We will insert student details and retreive them through our client application created in ASP.NET which consumes our WCF service.

Pre-requisite: Knowlege of the Concept of Inheritance, Classes, Interfaces. For a basic knowledge of WCF Services, I recommend to read my previous article which will makes it easy for you to understand this example.

Step 1: Creating Database tables and Stored procedures

Create a database in SQL Server name ‘DEMODB’ and execute the below SQL Commands to generate tables, test data and stored procedures that we will use in WCF service.

CREATE TABLE [dbo].[STUDENTS] ([Student_Id] INT IDENTITY(1, 1) NOT NULL, [Name] NVARCHAR(MAX) NOT NULL, [Gender] NVARCHAR(10) NOT NULL, [Contact_No] NVARCHAR(12) NOT NULL, [Fees] INT NOT NULL, [DateOfJoin] DATETIME NULL, [CourseType] INT NULL, [AnnualFee] INT NULL, [MonthlyFee] INT NULL, PRIMARY KEY CLUSTERED ([Student_Id] ASC));
 
SET IDENTITY_INSERT [dbo].[STUDENTS] ON
 
INSERT INTO [dbo].[STUDENTS] ([Student_Id], [Name], [Gender], [Contact_No], [Fees], [DateOfJoin], [CourseType], [AnnualFee], [MonthlyFee])
VALUES (1, N'John', N'Male', N'2310542310', 250000, N'2016-08-01 09:21:16', 1, NULL, 20000)
 
INSERT INTO [dbo].[STUDENTS] ([Student_Id], [Name], [Gender], [Contact_No], [Fees], [DateOfJoin], [CourseType], [AnnualFee], [MonthlyFee])
VALUES (2, N'James', N'Male', N'2310542310', 250000, N'2016-08-01 09:24:25', 1, NULL, 20000)
 
INSERT INTO [dbo].[STUDENTS] ([Student_Id], [Name], [Gender], [Contact_No], [Fees], [DateOfJoin], [CourseType], [AnnualFee], [MonthlyFee])
VALUES (3, N'Sana', N'Female', N'3302002102', 300000, N'2016-07-22 09:24:25', 2, 300000, NULL)
 
INSERT INTO [dbo].[STUDENTS] ([Student_Id], [Name], [Gender], [Contact_No], [Fees], [DateOfJoin], [CourseType], [AnnualFee], [MonthlyFee])
VALUES (4, N'Albert', N'Male', N'5542103365', 350000, N'2016-07-12 09:24:25', 1, NULL, 30000)
 
INSERT INTO [dbo].[STUDENTS] ([Student_Id], [Name], [Gender], [Contact_No], [Fees], [DateOfJoin], [CourseType], [AnnualFee], [MonthlyFee])
VALUES (5, N'Steve', N'Male', N'8865987455', 450000, N'2016-07-02 09:24:25', 2, 450000, NULL)
 
INSERT INTO [dbo].[STUDENTS] ([Student_Id], [Name], [Gender], [Contact_No], [Fees], [DateOfJoin], [CourseType], [AnnualFee], [MonthlyFee])
VALUES (6, N'Stark', N'Male', N'9988656623', 200000, N'2016-08-11 09:24:25', 1, NULL, 20000)
 
INSERT INTO [dbo].[STUDENTS] ([Student_Id], [Name], [Gender], [Contact_No], [Fees], [DateOfJoin], [CourseType], [AnnualFee], [MonthlyFee])
VALUES (7, N'James', N'Male', N'9899063210', 600000, N'2016-09-01 00:00:00', 2, 600000, NULL)
 
SET IDENTITY_INSERT [dbo].[STUDENTS] OFF
 
CREATE PROCEDURE uspGetStudentDetails @StudentId INT
AS
BEGIN
	SELECT *
	FROM dbo.Students
	WHERE Student_id = @StudentId
END
 
CREATE PROCEDURE [dbo].[uspInsertStudentDetails] (@Name NVARCHAR(max), @Gender NVARCHAR(10), @Contact_No NVARCHAR(12), @Fees INT, @DateOfJoin DATETIME, @CourseType INT, @AnnualFee INT = NULL, @MonthlyFee INT = NULL)
AS
BEGIN
	INSERT INTO [dbo].[STUDENTS]
	VALUES (@Name, @Gender, @Contact_No, @Fees, @DateOfJoin, @CourseType, @AnnualFee, @MonthlyFee)
END

Explanation:

  • We have created a table having Student details and inserted some sample data in it.
  • uspGetStudentDetails will be used in service for fetching the details of students on the basis of his StudentId.
  • uspInsertStudentDetails will be used to insert student details and student id is the Identity column, so, you dont have to pass student id while saving details.

Step 2: Create a new Project in Visual Studio of type Class Library.

We will create our WCF service in this project. Below are the steps to create the new Class Library Project in Visual Studio.

Step 3: Remove any class files that are created by default in this project and Add a new class file named ‘student.cs’ as shown below.

Step 4: Add the following code in this class file.

using System;
using System.Runtime.Serialization;
 
namespace studentService
{
    [KnownType(typeof(ShortTerm)), KnownType(typeof(LongTerm)), DataContract]
    public class Student
    {
        [DataMember(Order = 1)]
        public int StudentId { getset; }
 
        [DataMember(Order = 2)]
        public string Name { getset; }
 
        [DataMember(Order = 3)]
        public string Gender { getset; }
 
        [DataMember(Order = 4)]
        public string ContactNo { getset; }
 
        [DataMember(Order = 5)]
        public int Fees { getset; }
 
        [DataMember(Order = 6)]
        public DateTime DateOfJoin { getset; }
 
        [DataMember(Order = 7)]
        public CourseType Type { getset; }
    }
 
    [DataContract]
    public enum CourseType
    {
        [EnumMember] ShortTerm = 1,
 
        [EnumMember] LongTerm = 2
    }
}

Explanation:

  • We have created a class named Student having 7 DataMembers Ordered in a Serialized format. These DataMembers corresponds to our Database Table Columns.
  • The Student Class is decorated with a KnownType Attribute which will tell the class that it is using the concept of Inheritance.
  • An Enum is created which has two values that corresponds to the type of courses that the student opted for. These enum values are also decorated with EnumMember attributes which tells the service to Serialize them as well.

Step 5: Add two more class files named ‘LongTerm.cs’ and ‘ShortTerm.cs’ and add the below code to them.

namespace studentService
{
    public class LongTerm : Student
    {
        public int AnnualFee { getset; }
    }
}
namespace studentService
{
    public class ShortTerm : Student
    {
        public int MonthlyFee { getset; }
    }
}

Explanation: The above two classes are inherting the base class Student which we created in Step 4. If the student opted for LongTerm CourseType, then AnnualFee will be applicable for him otherwise for ShortTem CourseType, MonthlyFee will be applicable.

Step 6: Now Add a WCF Service Library to this project as shown below. After adding this, 2 files will be added to this project.One is the service file and another is the interface.

After adding the above, your project will look like below.

Step 7: Add the following code to the Interface file.

using System.ServiceModel;
 
namespace studentService
{
    [ServiceContract]
    public interface IStudentService
    {
        [OperationContract]
        Student GetStudentDetails(int studentId);
 
        [OperationContract]
        void InsertStudentDetails(Student student);
    }
}

Explanation: There are two operation contracts. One will fetch the data on the basis of StudentId and Other will insert the data in the Database.

Step 8: Now we will implement these two interface Methods in the Service file. Replace the below code.

namespace studentService
{
    using System;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
 
    public class StudentService : IStudentService
    {
        private readonly string _cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
 
        public Student GetStudentDetails(int studentId)
        {
            try
            {
                var student = new Student();
                using (var con = new SqlConnection(_cs))
                {
                    var cmd = new SqlCommand("uspGetStudentDetails", con) {CommandType = CommandType.StoredProcedure};
                    cmd.Parameters.AddWithValue("@StudentId", studentId);
                    con.Open();
                    var dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        if ((CourseType) dr["CourseType"] == CourseType.LongTerm)
                        {
                            student = new LongTerm
                            {
                                StudentId = Convert.ToInt32(dr["STUDENT_ID"]),
                                Name = dr[1].ToString(),
                                Gender = dr[2].ToString(),
                                ContactNo = dr[3].ToString(),
                                Fees = Convert.ToInt32(dr[4]),
                                DateOfJoin = Convert.ToDateTime(dr[5]),
                                Type = CourseType.LongTerm,
                                AnnualFee = Convert.ToInt32(dr["AnnualFee"])
                            };
                        }
                        else
                        {
                            student = new ShortTerm()
                            {
                                StudentId = Convert.ToInt32(dr["STUDENT_ID"]),
                                Name = dr[1].ToString(),
                                Gender = dr[2].ToString(),
                                ContactNo = dr[3].ToString(),
                                Fees = Convert.ToInt32(dr[4]),
                                DateOfJoin = Convert.ToDateTime(dr[5]),
                                Type = CourseType.ShortTerm,
                                MonthlyFee = Convert.ToInt32(dr["MonthlyFee"])
                            };
                        }
                    }
 
                    return student;
                }
            }
            catch (Exception exception)
            {
                throw exception.InnerException;
            }
        }
 
        public void InsertStudentDetails(Student student)
        {
            try
            {
                using (var con = new SqlConnection(_cs))
                {
                    var cmd = new SqlCommand("uspInsertStudentDetails", con)
                    {
                        CommandType = CommandType.StoredProcedure
                    };
                    cmd.Parameters.AddWithValue("@Name", student.Name);
                    cmd.Parameters.AddWithValue("@Gender", student.Gender);
                    cmd.Parameters.AddWithValue("@Contact_No", student.ContactNo);
                    cmd.Parameters.AddWithValue("@Fees", student.Fees);
                    cmd.Parameters.AddWithValue("@DateOfJoin", student.DateOfJoin);
                    cmd.Parameters.AddWithValue("@CourseType", student.Type);
                    if (student.GetType() == typeof(LongTerm))
                    {
                        cmd.Parameters.AddWithValue("@AnnualFee", ((LongTerm) student).AnnualFee);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue("@MonthlyFee", ((ShortTerm) student).MonthlyFee);
                    }
 
                    con.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception exception)
            {
                throw exception.InnerException;
            }
        }
    }
}

Explanation:

  • At the first we are creating the Connection string to the database.
  • In GetStudentDetails Method, we implement the Stored Procedure and pass StudentId as the Parameter. If the course type in Database is of type LongTerm, then service will return AnnualFee field otherwise MonthlyFee.
  • In InsertStudentDetails method, we implement the SP and pass all the details as Parameters and also, if the coursetype is LongTerm, then AnnualFee will be sent to Database otherwise MonthlyFee.

Now, we are done with the Service implementation.

Now, lets host the service using a Console Application.

Step 9: Add a new project to the same solution of type Console Application as shown below.

Step 10: Add reference to the WCF Service project and System.ServiceModel assembly as shown below.

Step 11: Add a config file to add the endpoints for the client application and Database connectionStrings.

Step 12: Add the following Code to Program.cs file.

using System;
using System.ServiceModel;
 
namespace studentServiceHost
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (var host = new ServiceHost(typeof(studentService.StudentService)))
            {
                host.Open();
                Console.WriteLine($"Host Successfully started at : {DateTime.Now}");
                Console.ReadLine();
            }
        }
    }
}

The above code Starts the host in the Console application.

Step 13: Add the following code to App.config file.

<?xml version="1.0" encoding="utf-8"?>
 
<configuration>
  <connectionStrings>
    <add name="DBCS"
         connectionString="data source=.;Integrated Security=SSPI;database=DEMODB"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
 
  <system.serviceModel>
    <behaviors>
      <serviceBehaviors>
        <behavior name="">
          <serviceMetadata httpGetEnabled="true" httpsGetEnabled="true" />
        </behavior>
      </serviceBehaviors>
    </behaviors>
    <services>
      <service name="studentService.StudentService">
        <endpoint address="" binding="basicHttpBinding" contract="studentService.IStudentService">
          <identity>
            <dns value="localhost" />
          </identity>
        </endpoint>
        <endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
        <host>
          <baseAddresses>
            <add baseAddress="http://localhost:8733/studentService/StudentService/" />
          </baseAddresses>
        </host>
      </service>
    </services>
  </system.serviceModel>
</configuration>

The above configuration is already explained in detail in my previous article about WCF Services.

Step 14: Now build the project and go to the root directory of the project and move to bin folder as shown below and run the exe file.

Step 15: Now lets Create client application that will consume this WCF Service host. Follow the steps below.

Now add a Webform to it.

Step 16: Add a reference to the service host as shown below. Make sure that Host is running in Console application. This will generate proxy classes to the service.

Step 17: Add the bootstrap reference from Nuget package library to create our webform.

Step 18: Now replace the code of Webform1.aspx with the below code.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="studentApp.WebForm1" %>
 
<!DOCTYPE html>
 
<html lang="en">
<head runat="server">
    <title>Student App</title>
    <link href="Content/bootstrap.css" rel="stylesheet"/>
</head>
<body>
<form id="form1" runat="server">
    <div class="container">
        <div class="jumbotron">
            <div class="form-group" role="form">
                <div class="form-group">
                    <h1>Student Details</h1>
                </div>
                <div class="form-group">
                    <label>Id</label>
                    <asp:TextBox ID="txtId" runat="server" CssClass="form-control"></asp:TextBox>
                </div>
                <div class="form-group">
                    <label>Name</label>
                    <asp:TextBox ID="txtName" runat="server" CssClass="form-control"></asp:TextBox>
                </div>
                <div class="form-group">
                    <label>Gender</label>
                    <asp:TextBox ID="txtGender" runat="server" CssClass="form-control"></asp:TextBox>
                </div>
                <div class="form-group">
                    <label>Contact No</label>
                    <asp:TextBox ID="txtContact" runat="server" CssClass="form-control"></asp:TextBox>
                </div>
                <div class="form-group">
                    <label>Fees</label>
                    <asp:TextBox ID="txtFees" runat="server" CssClass="form-control"></asp:TextBox>
                </div>
                <div class="form-group">
                    <label>Date of Admission</label>
                    <asp:TextBox ID="txtDOJ" runat="server" CssClass="form-control"></asp:TextBox>
                </div>
                <div class="form-group">
                    <label>Course Type</label>
                    <asp:DropDownList ID="ddlCourseType" runat="server" AutoPostBack="True" CssClass="form-control" OnSelectedIndexChanged="ddlCourseType_SelectedIndexChanged">
                        <asp:ListItem Text="Select Course Type" Value="-1">
                        </asp:ListItem>
                        <asp:ListItem Text="Short Term" Value="1">
                        </asp:ListItem>
                        <asp:ListItem Text="Long Term" Value="2">
                        </asp:ListItem>
                    </asp:DropDownList>
                </div>
                <div class="form-group" visible="false" runat="server" id="divAnnualFee">
                    <label>Annual Fee</label>
                    <asp:TextBox ID="txtAnnualFee" runat="server" CssClass="form-control"></asp:TextBox>
                </div>
                <div class="form-group" visible="false" runat="server" id="divMonthlyFee">
                    <label>Monthly Fee</label>
                    <asp:TextBox ID="txtMonthlyFee" runat="server" CssClass="form-control"></asp:TextBox>
                </div>
                <div class="form-group">
                    <asp:Button ID="btnAdd" runat="server" Text="Save" CssClass="btn btn-primary" OnClick="btnAdd_Click"/>
                    <asp:Button ID="btnGet" runat="server" Text="Retreive" CssClass="btn btn-info" OnClick="btnGet_Click"/>
                </div>
                <div runat="server" id="divNotify" visible="False">
                </div>
            </div>
        </div>
    </div>
 
</form>
</body>
</html>

Step 19: Now replace the code of Webform1.aspx.cs file.

using System;
using System.Web.UI;
using studentApp.StudentService;
 
namespace studentApp
{
    public partial class WebForm1 : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
 
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            var client = new StudentServiceClient();
 
            switch ((CourseTypeConvert.ToInt32(ddlCourseType.SelectedValue))
            {
                case CourseType.LongTerm:
                {
                    var student = new LongTerm()
                    {
                        Name = txtName.Text,
                        Gender = txtGender.Text,
                        ContactNo = txtContact.Text,
                        Fees = Convert.ToInt32(txtFees.Text),
                        DateOfJoin = Convert.ToDateTime(txtDOJ.Text),
                        AnnualFee = Convert.ToInt32(txtAnnualFee.Text),
                        Type = CourseType.LongTerm
                    };
                    client.InsertStudentDetails(student);
                    divNotify.Visible = true;
                    divNotify.Attributes.Add("class""alert alert-success visible");
                    divNotify.InnerText = "Hooray! Data Saved Successfully";
                    txtName.Text = "";
                    txtGender.Text = "";
                    txtContact.Text = "";
                    txtFees.Text = "";
                    txtDOJ.Text = "";
                    txtMonthlyFee.Text = "";
                    txtAnnualFee.Text = "";
                }
 
                    break;
                case CourseType.ShortTerm:
                {
                    var student = new ShortTerm()
                    {
                        Name = txtName.Text,
                        Gender = txtGender.Text,
                        ContactNo = txtContact.Text,
                        Fees = Convert.ToInt32(txtFees.Text),
                        DateOfJoin = Convert.ToDateTime(txtDOJ.Text),
                        MonthlyFee = Convert.ToInt32(txtMonthlyFee.Text),
                        Type = CourseType.ShortTerm
                    };
                    client.InsertStudentDetails(student);
                    divNotify.Visible = true;
                    divNotify.Attributes.Add("class""alert alert-success visible");
                    divNotify.InnerText = "Hooray! Data Saved Successfully";
                    txtName.Text = "";
                    txtGender.Text = "";
                    txtContact.Text = "";
                    txtFees.Text = "";
                    txtDOJ.Text = "";
                    txtMonthlyFee.Text = "";
                    txtAnnualFee.Text = "";
                }
 
                    break;
                default:
                    divNotify.Visible = true;
                    divNotify.Attributes.Add("class""alert alert-danger visible");
                    divNotify.InnerText = "Please Select Course Type";
                    break;
            }
        }
 
        protected void btnGet_Click(object sender, EventArgs e)
        {
            var client = new StudentServiceClient();
            var student = client.GetStudentDetails(Convert.ToInt32(txtId.Text));
 
            if (student.Type == CourseType.LongTerm)
            {
                txtAnnualFee.Text = ((LongTerm) student).AnnualFee.ToString();
                divAnnualFee.Visible = true;
                divMonthlyFee.Visible = false;
            }
            else
            {
                txtMonthlyFee.Text = ((ShortTerm) student).MonthlyFee.ToString();
                divMonthlyFee.Visible = true;
                divAnnualFee.Visible = false;
            }
 
            ddlCourseType.SelectedValue = ((int) student.Type).ToString();
            txtName.Text = student.Name;
            txtGender.Text = student.Gender;
            txtContact.Text = student.ContactNo;
            txtFees.Text = student.Fees.ToString();
            txtDOJ.Text = student.DateOfJoin.ToShortDateString();
            divNotify.Visible = true;
            divNotify.Attributes.Add("class""alert alert-success visible");
            divNotify.InnerText = "Hooray! Data Retreived Successfully";
        }
 
        protected void ddlCourseType_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch (ddlCourseType.SelectedValue)
            {
                case "-1":
                    divAnnualFee.Visible = false;
                    divMonthlyFee.Visible = false;
                    break;
                case "1":
                    divMonthlyFee.Visible = true;
                    divAnnualFee.Visible = false;
                    break;
                default:
                    divMonthlyFee.Visible = false;
                    divAnnualFee.Visible = true;
                    break;
            }
        }
    }
}

Explanation:

  • In the above code, we are referencing studentService proxy class generated by Service reference.
  • Now, we have all the Members and attributes of the Service.
  • This code simply Invoke the Interface methods and displaying the data in the HTML controls.

Step 20: Now, we can also trace the SOAP messages being exchange by the service between the client and Database. Follow the below steps to enable tracing.

The above steps will add Logging files to the project directory and the path is clearly shown in point 3 and 4 above.

Step 21: Now Press Ctrl + F5 and make sure that the host is running. Below will be the output.

Step 22: Now Lets check the Trace logs.

Below is the Request sent to the database for saving data.

Below is the request send to the server for processing.

Below is the response back from the service. All in XML format.

 

Display data in ASP.NET using jQuery DataTables Plugin

Description – This tutorial gives a walkthrough of jQuery Datatables plugin to display the data stored in Database using ASP.Net web Services.

Below is the screenshot of the final output that we want to achieve at the end of this tutorial.

7

So, the above is a table that allows almost all functionalities that a user wants to view data, filter data, sort data etc. This is achieved by using jQuery datatables plugin.

Step 1: First we need to create some sample data in our SQL database. Below is the script that I have used to generate test data. You can use the below script or create your own data.

Create Database Test
 
Use Test
 
Create Table Students 
(
iD int primary key not null identity(1,1),
firstName nvarchar(MAX) not null,
lastName nvarchar(MAX) not null,
feesPaid int not null,
gender nvarchar(MAX) not null,
emailId nvarchar(MAX) not null,
telephoneNumber nvarchar(MAX) not null,
dateOfBirth date not null,
isActive bit not null,
creationDate datetime not null,
lastModifiedDate datetime not null
)
GO
 
INSERT INTO Students VALUES ('Ankit','Bansal',45000, 'Male', 'ankit@a.com','2564210000','20/May/1993',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Raj','Kumar',25000, 'Male', 'raj@r.com','25400123000','20/June/1993',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Badrish','Joshi',32000, 'Male', 'badrish@b.com','10213330120','25/May/1989',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Rajesh','Negi',56000, 'Male', 'rajesh@r.com','89546332021','18/July/1978',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Rajkishor','Singh',46000, 'Male', 'rajkishor@r.com','8546795555','11/August/1985',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Neha','Arora',98652, 'Female', 'neha@n.com','96542231000','02/May/1996',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Sanjay','Sharma',65421, 'Male', 'sanjay@s.com','99876653222','25/Jan/1998',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Glenn','Block',65210, 'Male', 'glenn@g.com','54210000021','27/Feb/1990',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Sara','Silver',78900, 'Female', 'sara@s.com','89778930099','10/Sep/1989',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Lucy','Bane',23444, 'Female', 'lucy@l.com','2564210000','20/May/1993',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Suresh','Goswami',65321, 'Male', 'suresh@s.com','9876576489','21/Nov/1981',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Dinesh','Gola',76544, 'Male', 'dinesh@d.com','9899785412','15/March/1984',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Shivansh','Manaktala',87555, 'Male', 'shivansh@s.com','6755439000','17/Oct/1994',1,GETDATE(),GETDATE())
GO
INSERT INTO Students VALUES ('Mahendra','Dhoni',65789, 'Male', 'mahendra@m.com','9856421003','15/March/1987',1,GETDATE(),GETDATE())
GO
 
Create Procedure getStudents
As 
Begin
   Select * from Students Where isActive=1
End

Step 2: Download jQuery Datatables plugin core files from its website. Go to http://datatables.net/download/index and follow the below steps.

Step 3: Create an Empty ASP.NET web application project and add the above downloaded files to it.

Step 4: Add a class file named students.cs with some auto-implemented properties. You can copy and paste the below code if you are using my SQL script.

using System;
 
namespace dataTablesPlugin
{
    class Students
    {
        public int iD { getset; }
        public string firstName { getset; }
        public string lastName { getset; }
        public int feesPaid { getset; }
        public string gender { getset; }
        public string emailId { getset; }
        public string telephoneNumber { getset; }
        public DateTime dateOfBirth { getset; }
    }
}

Explanation: We have created properties in the class file with the same name as that of our database table columns. This helps us to retrieve data from sql database.

Step 5: Create a connection string in the Web.config file.

<connectionStrings>
    <add connectionString="Data Source=.;Initial Catalog=Test;Integrated Security=True" name="dbcs" providerName="System.Data.SqlClient"/>
  </connectionStrings>

The above string is used to connect to Database using .net sql client.

Step 6: Add a ASP.NET web service named StudentService.asmx and add the following code to it.

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 dataTablesPlugin
{
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    [System.Web.Script.Services.ScriptService]
    public class StudentService : System.Web.Services.WebService
    {
        [WebMethod]
        public void GetStudents()
        {
            var cs = ConfigurationManager.ConnectionStrings["dbcs"].ConnectionString;
            var students = new List<Students>();
            using (var con = new SqlConnection(cs))
            {
                var cmd = new SqlCommand("getStudents", con) {CommandType = CommandType.StoredProcedure};
                con.Open();
                var dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var student = new Students
                    {
                        iD = Convert.ToInt32(dr[0].ToString()),
                        firstName = dr[1].ToString(),
                        lastName = dr[2].ToString(),
                        feesPaid = Convert.ToInt32(dr[3].ToString()),
                        gender = dr[4].ToString(),
                        emailId = dr[5].ToString(),
                        telephoneNumber = dr[6].ToString(),
                        dateOfBirth = Convert.ToDateTime(dr[7].ToString())
                    };
                    students.Add(student);
                }
            }
            var js = new JavaScriptSerializer();
            Context.Response.Write(js.Serialize(students));
        }
    }
}

The above code is a simple ADO.NET code that create a list of Students and retrieve data from database stored procedure and read each property value from it and stores it in our class properties. At the last of this code, JavaScriptSerializer class is used to convert the received data in JSON format so that our Datatables plugin can consume that data using AJAX call.

Now Lets quickly test our web service if it is returning the correct data or not. Press ctrl + F5.

WebServiceCheck

Step 7: Add a webform to the project.

Step 8: Add the following references in the head section of the HTML.

    <script src="DataTables/jQuery-2.2.0/jquery-2.2.0.js"></script>
    <script src="DataTables/DataTables-1.10.11/js/jquery.dataTables.js"></script>
    <link href="DataTables/DataTables-1.10.11/css/jquery.dataTables.css" rel="stylesheet" />
    <link href="DataTables/Bootstrap-3.3.6/css/bootstrap.css" rel="stylesheet" />

The above references are of core jQuery, datatables plugin jquery, datatables plugin css and bootstrap css.

Step 9: Add the following HTML code to the Body tag.

<body>
    <form id="form1" runat="server">
        <div style="padding10pxborder5px solid blackmargin-top50px" class="container-fluid">
            <div>
                <b class="label label-danger" style="padding8.5px">Click to Show or Hide Column:</b>
                <div class="btn-group btn-group-sm">
                    <a class="showHide btn btn-primary" data-columnindex="0">ID</a>
                    <a class="showHide btn btn-primary" data-columnindex="1">FirstName</a>
                    <a class="showHide btn btn-primary" data-columnindex="2">LastName</a>
                    <a class="showHide btn btn-primary" data-columnindex="3">FeesPaid</a>
                    <a class="showHide btn btn-primary" data-columnindex="4">Gender</a>
                    <a class="showHide btn btn-primary" data-columnindex="5">Email</a>
                    <a class="showHide btn btn-primary" data-columnindex="6">TelephoneNumber</a>
                    <a class="showHide btn btn-primary" data-columnindex="7">Date of Birth</a>
                </div>
            </div>
            <br />
            <table id="studentTable" class="table table-responsive table-hover">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>First Name</th>
                        <th>Last Name</th>
                        <th>Fees Paid</th>
                        <th>Gender</th>
                        <th>Email</th>
                        <th>Telephone Number</th>
                        <th>Date of Birth</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>ID</th>
                        <th>First Name</th>
                        <th>Last Name</th>
                        <th>Fees Paid</th>
                        <th>Gender</th>
                        <th>Email</th>
                        <th>Telephone Number</th>
                        <th>Date of Birth</th>
                    </tr>
                </tfoot>
            </table>
        </div>
    </form>
</body>

We have just declared a table with header and footer. The main content will be displayed using jQuery AJAX call using our web service.

Step 10: Add the custom CSS to the Head section.

    <style>
        .showHide {
            cursorpointer;
        }
    </style>

Step 11: Add the following jQuery Code to the Head section.

    <script type="text/javascript">
        $(document).ready(function () {
            $.ajax({
                type: "POST",
                dataType: "json",
                url: "studentService.asmx/GetStudents",
                success: function (data) {
                    var datatableVariable = $('#studentTable').DataTable({
                        data: data,
                        columns: [
                            { 'data''iD' },
                            { 'data''firstName' },
                            { 'data''lastName' },
                            {
                                'data''feesPaid''render'function (feesPaid) {
                                    return '$ ' + feesPaid;
                                }
                            },
                            { 'data''gender' },
                            { 'data''emailId' },
                            { 'data''telephoneNumber' },
                            {
                                'data''dateOfBirth''render'function (date) {
                                    var date = new Date(parseInt(date.substr(6)));
                                    var month = date.getMonth() + 1;
                                    return date.getDate() + "/" + month + "/" + date.getFullYear();
                                }
                            }]
                    });
                    $('#studentTable tfoot th').each(function () {
                        var placeHolderTitle = $('#studentTable thead th').eq($(this).index()).text();
                        $(this).html('<input type="text" class="form-control input input-sm" placeholder = "Search ' + placeHolderTitle + '" />');
                    });
                    datatableVariable.columns().every(function () {
                        var column = this;
                        $(this.footer()).find('input').on('keyup change'function () {
                            column.search(this.value).draw();
                        });
                    });
                    $('.showHide').on('click'function () {
                        var tableColumn = datatableVariable.column($(this).attr('data-columnindex'));
                        tableColumn.visible(!tableColumn.visible());
                    });
                }
            });
 
        });
    </script>

Explanation:

1. We use the jquery ajax function to call our web service by mentioning its URL in the URL option.

2. If the call to the service is success, then we call DataTable function of the datatables plugin API which converts our table into a multi-purpose table which can help to show data, filter data, sort data, paging of data, hiding / showing columns etc.

3. We specify the columns that we want to display after getting data from the AJAX call.

4. To allow searching on each column, we use the each function of jQuery and append an input element which displays a textbox at the bottom of each column.

5. To show/hide columns, we are using visible function of jQuery and data-columnindex attribute.

Step 12: Press Ctrl + F5 together and you will see the following output and this is what we want to achieve.

Please comment if I missed anything or give your reviews. If you like my tutorial, please visit my blog and read my other articles too and share. Thanks.