Get data from Sql Server database and bind Gridview using Jquery, JSON and Ajax - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

Get data from Sql Server database and bind Gridview using Jquery, JSON and Ajax

In this article I am going to explain how to show the data in Gridview client side using Jquery, Json and Ajax


Implementation:
I have created a Table Tb_Country and insert some dummy data into it.

Id
int
CountryName
varchar(100)

HTML Markup of Webform:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
<script>
    $(document).ready(function () {
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "Default.aspx/GetCountry",
            data: "{}",
            dataType: "json",
            success: function (data) {
                for (var i = 0; i < data.d.length; i++) {
                    $("#grdcountry").append("<tr><td>" + data.d[i].Id + "</td><td>" + data.d[i].CountryName + "</td></tr>");
                }
            },
            error: function (result) {
                alert("Error");
            }
        });
    });
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div>       
        <asp:GridView ID="grdcountry" runat="server" AutoGenerateColumns="False"
            CellPadding="4" ForeColor="#333333" GridLines="None">
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        <Columns>
         <asp:BoundField DataField="Id" HeaderText="ID"> </asp:BoundField>
        <asp:BoundField DataField="CountryName" HeaderText="Country Name"></asp:BoundField>
        </Columns>
            <EditRowStyle BackColor="#999999" />
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#E9E7E2" />
            <SortedAscendingHeaderStyle BackColor="#506C8C" />
            <SortedDescendingCellStyle BackColor="#FFFDF8" />
            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Import the namespace:

C# code:
using System.Web.Services;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;


VB.Net code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Services

Write the below given code in code behind:
C# code:
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindHeaderColumnToGridview();
        }
    }
  
    private void BindHeaderColumnToGridview()
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[] { new DataColumn("id"), new DataColumn("CountryName") });  
        dt.Rows.Add();
        grdcountry.DataSource = dt;
        grdcountry.DataBind();
        grdcountry.Rows[0].Visible = false;
    }

[WebMethod]
    public static country[] GetCountry()
    {
        List<country> list = new List<country>();
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
        using (SqlCommand cmd = new SqlCommand("select * from Tb_Country", con))
        {
        con.Open();
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        adp.Fill(dt);
        foreach (DataRow dtrow in dt.Rows)
        {
            country ctry = new country();
            ctry.Id = Convert.ToInt32(dtrow["Id"].ToString());
            ctry.CountryName = dtrow["CountryName"].ToString();
            list.Add(ctry);
        }
    }   
        return list.ToArray();
    }

public class country
    {
        public int Id { get; set; }
        public string CountryName { get; set; }
    }

VB.Net code:
Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindHeaderColumnToGridview()
        End If
    End Sub

    Private Sub BindHeaderColumnToGridview()
        Dim dt As New DataTable()
        dt.Columns.AddRange(New DataColumn() {New DataColumn("id"), New DataColumn("CountryName")})
        dt.Rows.Add()
        grdcountry.DataSource = dt
        grdcountry.DataBind()
        grdcountry.Rows(0).Visible = False
    End Sub

<WebMethod()> _
    Public Shared Function GetCountry() As country()
        Dim list As New List(Of country)()
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
        Using cmd As New SqlCommand("select Id,CountryName from Tb_Country", con)
            con.Open()
            Dim adp As New SqlDataAdapter(cmd)
            Dim dt As New DataTable()
            adp.Fill(dt)
            For Each dtrow As DataRow In dt.Rows
                Dim ctry As New country()
                ctry.Id = Convert.ToInt32(dtrow("Id").ToString())
                ctry.CountryName = dtrow("CountryName").ToString()
                list.Add(ctry)
            Next
        End Using
        Return list.ToArray()
    End Function

Public Class country
        Public Property Id() As Integer
            Get
                Return m_Id
            End Get
            Set(value As Integer)
                m_Id = Value
            End Set
        End Property
        Private m_Id As Integer
        Public Property CountryName() As String
            Get
                Return m_CountryName
            End Get
            Set(value As String)
                m_CountryName = Value
            End Set
        End Property
        Private m_CountryName As String
    End Class 

What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon