how to bind datatables.net grid from database in asp.net using json jquery ajax in asp.net
  • yyy
  • 4 Comments sorted by
  • HTML Markup

    <asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" RowStyle-BackColor="#A1DCF2"
        HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White">
        <Columns>
            <asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />
            <asp:BoundField ItemStyle-Width="150px" DataField="ContactName" HeaderText="Contact Name" />
            <asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />
        </Columns>
    </asp:GridView>
    <br />
    <div class="Pager"></div>

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

    Populating GridView with Dummny data

    private static int PageSize = 10;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindDummyRow();
        }
    }
     
    private void BindDummyRow()
    {
        DataTable dummy = new DataTable();
        dummy.Columns.Add("CustomerID");
        dummy.Columns.Add("ContactName");
        dummy.Columns.Add("City");
        dummy.Rows.Add();
        gvCustomers.DataSource = dummy;
        gvCustomers.DataBind();
    }



  • Pagination SQL Stored Procedure
    CREATE PROCEDURE [dbo].[GetCustomers_Pager]
          @PageIndex INT = 1
          ,@PageSize INT = 10
          ,@RecordCount INT OUTPUT
    AS
    BEGIN
          SET NOCOUNT ON;
          SELECT ROW_NUMBER() OVER
          (
                ORDER BY [CustomerID] ASC
          )AS RowNumber
          ,[CustomerID]
          ,[CompanyName]
          ,[ContactName]
          ,[City]
          INTO #Results
          FROM [Customers]
         
          SELECT @RecordCount = COUNT(*)
          FROM #Results
               
          SELECT * FROM #Results
          WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
         
          DROP TABLE #Results
    END

    WebMethod to handle jquery Ajax Call
    [WebMethod]
    public static string GetCustomers(int pageIndex)
    {
        string query = "[GetCustomers_Pager]";
        SqlCommand cmd = new SqlCommand(query);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@PageIndex", pageIndex);
        cmd.Parameters.AddWithValue("@PageSize", PageSize);
        cmd.Parameters.Add("@RecordCount"SqlDbType.Int, 4).Direction = ParameterDirection.Output;
        return GetData(cmd, pageIndex).GetXml();
    }
     
    private static DataSet GetData(SqlCommand cmd, int pageIndex)
    {
        string strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(strConnString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    sda.Fill(ds, "Customers");
                    DataTable dt = new DataTable("Pager");
                    dt.Columns.Add("PageIndex");
                    dt.Columns.Add("PageSize");
                    dt.Columns.Add("RecordCount");
                    dt.Rows.Add();
                    dt.Rows[0]["PageIndex"] = pageIndex;
                    dt.Rows[0]["PageSize"] = PageSize;
                    dt.Rows[0]["RecordCount"] = cmd.Parameters["@RecordCount"].Value;
                    ds.Tables.Add(dt);
                    return ds;
                }
            }
        }
    }



  • Client side Implimentation:
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(function () {
            GetCustomers(1);
        });
        $(".Pager .page").live("click"function () {
            GetCustomers(parseInt($(this).attr('page')));
        });
        function GetCustomers(pageIndex) {
            $.ajax({
                type: "POST",
                url: "Default.aspx/GetCustomers",
                data: '{pageIndex: ' + pageIndex + '}',
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: OnSuccess,
                failure: function (response) {
                    alert(response.d);
                },
                error: function (response) {
                    alert(response.d);
                }
            });
        }
     
        function OnSuccess(response) {
            var xmlDoc = $.parseXML(response.d);
            var xml = $(xmlDoc);
            var customers = xml.find("Customers");
            var row = $("[id*=gvCustomers] tr:last-child").clone(true);
            $("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();
            $.each(customers, function () {
                var customer = $(this);
                $("td", row).eq(0).html($(this).find("CustomerID").text());
                $("td", row).eq(1).html($(this).find("ContactName").text());
                $("td", row).eq(2).html($(this).find("City").text());
                $("[id*=gvCustomers]").append(row);
                row = $("[id*=gvCustomers] tr:last-child").clone(true);
            });
            var pager = xml.find("Pager");
            $(".Pager").ASPSnippets_Pager({
                ActiveCssClass: "current",
                PagerCssClass: "pager",
                PageIndex: parseInt(pager.find("PageIndex").text()),
                PageSize: parseInt(pager.find("PageSize").text()),
                RecordCount: parseInt(pager.find("RecordCount").text())
            });
        };
    </script>

    CSS
    <style type="text/css">
        body
        {
            font-familyArial;
            font-size10pt;
        }
        .Pager span
        {
            text-aligncenter;
            color#999;
            displayinline-block;
            width20px;
            background-color#A1DCF2;
            margin-right3px;
            line-height150%;
            border1px solid #3AC0F2;
        }
        .Pager a
        {
            text-aligncenter;
            displayinline-block;
            width20px;
            background-color#3AC0F2;
            color#fff;
            border1px solid #3AC0F2;
            margin-right3px;
            line-height150%;
            text-decorationnone;
        }
    </style>

Howdy, Stranger!

It looks like you're new here. If you want to get involved, or you want to Ask a new Question, Please Login or Create a new Account by Clicking below

Login with Facebook

Popular Posts of the Week

    Optimum Creative