Filter records with comma separated row number using C# and VB.Net in ASP.Net

Hi muhammad12,

Please refer below sample.

Here i have used Row_Number() and Split function. For more details please refer Split a String into Rows in SQL Server.

SQL

CREATE PROCEDURE [Customers_GetCustomersByRowNumber]
   @RowNumber VARCHAR(100) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    ;With CTE(RowNumber, CustomerID, ContactName,Country )
	AS
	(
		SELECT ROW_NUMBER() OVER
		(
			ORDER BY [CustomerID] ASC
		)AS RowNumber
			,[CustomerID]
			,[ContactName]
			,[Country]
		FROM Customers
		)

		SELECT * FROM CTE 
		WHERE RowNumber IN(
			SELECT CAST(Item AS INTEGER)
			FROM SplitString(@RowNumber,  ',')
		)
		OR @RowNumber IS NULL
END

HTML

<asp:TextBox ID="txtNumbers" runat="server" />
<asp:Button ID="btnGetCustomer" runat="server" Text="Submit" OnClick="OnSubmit" />
<hr />
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="CustomerID" HeaderText="Customer ID" />
        <asp:BoundField DataField="ContactName" HeaderText="Contact Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>

NameSpace

C#

using System.Data.SqlClient;
using System.Data;
using System.Configuration; 

VB.Net

Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.BindGrid(string.Empty);
    }
}
 
protected void OnSubmit(object sender, EventArgs e)
{
    this.BindGrid(txtNumbers.Text);
}
 
private void BindGrid(string number)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        con.Open();
        using (SqlCommand cmd = new SqlCommand("[Customers_GetCustomersByRowNumber]", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            if (!string.IsNullOrEmpty(number))
            {
                cmd.Parameters.AddWithValue("@RowNumber", number);
            }
            gvCustomers.DataSource = cmd.ExecuteReader();
            gvCustomers.DataBind();
        }
        con.Close();
    }
}

VB.Net

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

    Protected Sub OnSubmit(ByVal sender As Object, ByVal e As EventArgs)
        Me.BindGrid(txtNumbers.Text)
    End Sub

    Private Sub BindGrid(ByVal number As String)
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            con.Open()
            Using cmd As SqlCommand = New SqlCommand("[Customers_GetCustomersByRowNumber]", con)
                cmd.CommandType = CommandType.StoredProcedure
                If Not String.IsNullOrEmpty(number) Then
                    cmd.Parameters.AddWithValue("@RowNumber", number)
                End If
                gvCustomers.DataSource = cmd.ExecuteReader()
                gvCustomers.DataBind()
            End Using
            con.Close()
        End Using
    End Sub

Screenshot