Monday, February 22, 2016

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 

No comments:

Post a Comment