Display records from multiple tables in ASP.Net with custom pager using Stored Procedure

Hi lingers,

Refer below modified code.

SQL

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cylinder101](
    [id] [int] NULL,
    [cylinderno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[inker](
    [id] [int] NULL,
    [inkno] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OD1](
    [id] [int] NULL,
    [quantity] [nvarchar](50) NULL,
    [model] [nvarchar](50) NULL,
    [posino] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[cylinder101] ([id], [cylinderno]) VALUES (51, N'CYLIN101')
GO
INSERT [dbo].[cylinder101] ([id], [cylinderno]) VALUES (52, N'CYLIN202')
GO
INSERT [dbo].[cylinder101] ([id], [cylinderno]) VALUES (53, N'CYLIN303')
GO
INSERT [dbo].[cylinder101] ([id], [cylinderno]) VALUES (54, N'CYLIN404')
GO
INSERT [dbo].[cylinder101] ([id], [cylinderno]) VALUES (55, N'CYLIN505')
GO
INSERT [dbo].[inker] ([id], [inkno]) VALUES (51, N'INK101')
GO
INSERT [dbo].[inker] ([id], [inkno]) VALUES (52, N'INK202')
GO
INSERT [dbo].[inker] ([id], [inkno]) VALUES (53, N'INK303')
GO
INSERT [dbo].[inker] ([id], [inkno]) VALUES (54, N'INK404')
GO
INSERT [dbo].[inker] ([id], [inkno]) VALUES (55, N'INK505')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (1, N'1', N'cylinder', N'51')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (2, N'1', N'cylinder', N'52')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (3, N'1', N'cylinder', N'53')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (4, N'1', N'cylinder', N'54')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (5, N'1', N'cylinder', N'55')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (6, N'2', N'ink', N'51')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (7, N'2', N'ink', N'52')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (8, N'1', N'ink', N'53')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (9, N'1', N'ink', N'54')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (10, N'4', N'ink', N'55')
GO
INSERT [dbo].[OD1] ([id], [quantity], [model], [posino]) VALUES (NULL, NULL, NULL, N'  ')

GO

-- EXEC koko 1,4,30
CREATE PROCEDURE [dbo].[koko]
      @PageIndex INT = 1
      ,@PageSize INT = 15
      ,@RecordCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT DISTINCT  DENSE_RANK() OVER (ORDER BY o.[id] ASC) AS RowNumber,
        o.id,
	CASE WHEN o.model = 'cylinder' THEN c.cylinderno
	WHEN o.model = 'ink' THEN i.inkno
	END 'inkno_cylinderno',
	o.model,o.quantity,o.posino
    INTO #Results
    FROM OD1 o
    INNER JOIN inker i ON i.id = o.posino
    INNER JOIN cylinder101 c ON c.id = o.posino
 
    SELECT @RecordCount = COUNT(*)
    FROM #Results
              
    SELECT * FROM #Results
    WHERE RowNumber between (@PageIndex-1)*@PageSize + 1
    AND (((@PageIndex-1)*@PageSize + 1) + @PageSize)-1 OR @PageIndex = -1
  
    DROP TABLE #Results
END
GO

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
<script type="text/javascript" src=" https://code.jquery.com/jquery-3.5.1.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.7/css/jquery.dataTables.min.css" />
<script type="text/javascript" src="https://cdn.datatables.net/1.10.7/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css" />
<script type="text/javascript" src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="ASPSnippets_Pager.min.js" type="text/javascript"></script>
<script type="text/javascript">
    $(function () {
        GetCustomers(1);
    });
    $(document).on("click", '.Pager .page', function () {
        GetCustomers(parseInt($(this).attr('page')));
    });

    $(document).on('click', '.view', function () {
        $('[id*=hfId]').val($(this).closest('tr').find('td').eq(0).html());
    });
    var i = 0;
    function GetCustomers(pageIndex) {
        $.ajax({
            type: "POST",
            url: "Default.aspx/GetCustomers",
            data: '{pageIndex: ' + pageIndex + '}',
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (response) {
                $('[id$=gvDetails]').prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first"))).DataTable().destroy();
                var xmlDoc = $.parseXML(response.d);
                var xml = $(xmlDoc);
                var customers = xml.find("OD1");
                var row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
                $("[id$=gvDetails] tbody tr").not($("[id$=gvDetails] tbody tr:first-child")).remove();
                $.each(customers, function () {
                    $("td", row).eq(0).html($(this).find("id").text());
                    $("td", row).eq(1).html($(this).find("inkno_cylinderno").text());
                    $("td", row).eq(2).html($(this).find("model").text());
                    $("td", row).eq(3).html($(this).find("quantity").text());
                    $("td", row).eq(4).html($(this).find("posino").text());

                    $("[id$=gvDetails]").append(row);
                    row = $("[id$=gvDetails] tbody tr:last-child").eq(0).clone(true);
                });
                $("[id$=gvDetails] tbody tr:first-child").remove();
                if (i != 0) {
                    $('[id$=gvDetails]').DataTable({
                        "paging": false,
                        "info": false
                    });
                } else {
                    $('[id$=gvDetails]')
                        .prepend($("<thead></thead>").append($('[id$=gvDetails]').find("tr:first")))
                        .DataTable({
                            "paging": false,
                            "info": false
                        });
                }
                i++;
                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())
                });
            },
            failure: function (response) {
                alert(response.responseText);
            },
            error: function (response) {
                alert(response.responseText);
            }
        });
    }
</script>
<asp:HiddenField ID="hfId" runat="server" />
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="False"
    BackColor="White" BorderColor="#3366CC" BorderStyle="None"
    Font-Names="Century Gothic" Font-Size="x-Small"
    Width="100%" Style="margin-bottom: 0px" CssClass="grid">
    <Columns>
        <asp:BoundField DataField="id" HeaderText="S/N" />
        <asp:BoundField DataField="inkno_cylinderno" HeaderText="finkno/Cylinder no" />
        <asp:BoundField DataField="model" HeaderText="fmodel" />
        <asp:BoundField DataField="quantity" HeaderText="fquantity" />
        <asp:BoundField DataField="posino" HeaderText="fposi" />
    </Columns>
    <FooterStyle BackColor="White" ForeColor="#000066" />
    <HeaderStyle BackColor="#7EA9D3" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
    <RowStyle ForeColor="#000000" />
    <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="#000000" />
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#007DBB" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>

Code

private static int PageSize = 2;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDummyRow();
    }
}

private void BindDummyRow()
{
    DataTable dummy = new DataTable();
    dummy.Columns.Add("id");
    dummy.Columns.Add("inkno_cylinderno");
    dummy.Columns.Add("model");
    dummy.Columns.Add("quantity");
    dummy.Columns.Add("posino");
    dummy.Rows.Add();
    gvDetails.DataSource = dummy;
    gvDetails.DataBind();
}

[WebMethod]
public static string GetCustomers(int pageIndex)
{
    string query = "[koko]";
    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, "OD1");
                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;
            }
        }
    }
}