Please disable your adblock and script blockers to view this page
 

Jquery and json to bind gridview from sql server database in asp.net C#,VB

Introduction: In this article I am going to share how to populate gridview clientside from sql server database using jquery,  json in asp.net in both C# and VB.Net through  ajax call.
bind gridview from sql server database using jquery,json,ajax


Implementation: Let’s create a page to demonstrate the concept. 
  • First of all create a table and add dummy data into it using the following sql script: 

CREATE TABLE tbBookDetails
(
                BookId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
                BookName         VARCHAR(100),
                Author                 VARCHAR(100),
                Publisher            VARCHAR(100),
                BookpPrice       DECIMAL(10,2)
)

INSERT INTO tbBookDetails VALUES
('Asp.Net','Ajay','Rozy Publication',1200),
('C#.Net','Bhavuk','Jai Publication',1000),
('VB.Net','Nancy','Rozy Publication',970),
('MVC','Sahil','Amar Publication',1480),
('JAVA','Supreet','Sam Publication',850),
('PHP','Parvesh','Maya Publication',800)

  • Now create a stored procedure to fetch all the records from table to bind in repeater data control

CREATE PROCEDURE spBookDetails
AS
BEGIN
                SET NOCOUNT ON;
                SELECT * FROM tbBookDetails
END

HTML Source
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            GetBookDetails();
        });

function GetBookDetails() {
            $.ajax({
                type: "POST",
                contentType: "application/json; charset=utf-8",
                url: "Default.aspx/GetBookDetails",
                data: "{}",
                dataType: "json",
                success: OnSuccess,
                failure: function (response) {
                    alert("Error: " + response.d);
                },
                error: function (response) {
                    alert("Error: "+response.d);
                }
            });
        }

        function OnSuccess(response) {
            var xmlDoc = $.parseXML(response.d);
            var xml = $(xmlDoc);
            var booksData = xml.find("BooksData");
            var row = $("[id*=grdBookDetails] tr:last-child").clone(true);
            $("[id*=grdBookDetails] tr").not($("[id*=grdBookDetails] tr:first-child")).remove();
            $.each(booksData, function () {
                var bookData = $(this);
                $("td", row).eq(0).html(bookData.find("BookId").text());
                $("td", row).eq(1).html(bookData.find("BookName").text());
                $("td", row).eq(2).html(bookData.find("Author").text());
                $("td", row).eq(3).html(bookData.find("Publisher").text());
                $("td", row).eq(4).html(bookData.find("BookPrice").text());
                $("[id*=grdBookDetails]").append(row);
                row = $("[id*=grdBookDetails] tr:last-child").clone(true);
            });
        };
    </script>
    <style type="text/css">
        table, th, td {
            border: 1px solid #808080;
            padding: 5px;
            text-align: left;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <asp:GridView ID="grdBookDetails" runat="server" HeaderStyle-BackColor="#1384d5" HeaderStyle-ForeColor="#ffffff" HeaderStyle-Font-Bold="true">
        </asp:GridView>
    </form>
</body>
</html>

  • Now In the <configuration> tag of web.config file add your connectionstring as:
<connectionStrings>
    <add name="conStr" connectionString="Data Source=LALIT-PC;Initial Catalog=MyDataBase;Integrated Security=True"/>
  </connectionStrings>
Note: Replace Data Source and Catalogue i.e. Database name as per your application

Asp.Net C# code to bind gridview from sql server database table using jquery, json, ajax
  • In code behind file(.aspx.cs)write the code as:

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindHeaderRowInGridview();
        }
    }
  
    // Bind dummy header row to gridview
    private void BindHeaderRowInGridview()
    {
        DataTable dt = new DataTable();  
        //Add columns to datatable
        dt.Columns.Add("BookId");
        dt.Columns.Add("Book Name");
        dt.Columns.Add("Author");
        dt.Columns.Add("Publisher");
        dt.Columns.Add("Book Price");
        //Add row to datatable
        dt.Rows.Add();
        //Bind row having 5 columns to gridview as a Gridview's header row.
        grdBookDetails.DataSource = dt;     
        grdBookDetails.DataBind();     
    }

    [WebMethod]
    public static String GetBookDetails()
    {
        DataSet ds=new DataSet();
        //Create datatable object and name it.
        DataTable dt = new DataTable("BooksData");
        //Create Connection object and get connection string defined in web.config file
        SqlConnection sqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString); 
        using (sqlCon)
        {
            using (SqlCommand cmd = new SqlCommand("spBookDetails", sqlCon))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                sqlCon.Open();
                using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
                {
                    adp.Fill(dt);  
                    //add filled datatable to dataset so that we can convert it to xml
                    ds.Tables.Add(dt);                   
                }
            }
        }    
        //return dataset data in xml format using DataSet's GetXml method
        return ds.GetXml();      
    } 

Asp.Net VB code to bind gridview from sql server database table using jquery, json, ajax
  • In code behind file(.aspx.vb)write the code as:
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindHeaderRowInGridview()
        End If
    End Sub

    ' Bind dummy header row to gridview
    Private Sub BindHeaderRowInGridview()
        Dim dt As New DataTable()
        'Add columns to datatable
        dt.Columns.Add("BookId")
        dt.Columns.Add("Book Name")
        dt.Columns.Add("Author")
        dt.Columns.Add("Publisher")
        dt.Columns.Add("Book Price")
        'Add row to datatable
        dt.Rows.Add()
        'Bind row having 5 columns to gridview as a Gridview's header row.
        grdBookDetails.DataSource = dt
        grdBookDetails.DataBind()
    End Sub

    <WebMethod> _
    Public Shared Function GetBookDetails() As [String]
        Dim ds As New DataSet()
        'Create datatable object and name it.
        Dim dt As New DataTable("BooksData")
        'Create Connection object and get connection string defined in web.config file
        Dim sqlCon As New SqlConnection(ConfigurationManager.ConnectionStrings("conStr").ConnectionString)
        Using sqlCon
            Using cmd As New SqlCommand("spBookDetails", sqlCon)
                cmd.CommandType = CommandType.StoredProcedure
                sqlCon.Open()
                Using adp As New SqlDataAdapter(cmd)
                    adp.Fill(dt)
                    'add filled datatable to dataset so that we can convert it to xml
                    ds.Tables.Add(dt)
                End Using
            End Using
        End Using
        'return dataset data in xml format using DataSet's GetXml method
        Return ds.GetXml()
    End Function

Now over to you:
"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 
Previous
Next Post »

1 comments:

Click here for comments
December 14, 2015 ×

Great Article

C# Training

Congrats bro prathap kumar you got PERTAMAX...! hehehehe...
Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon