Create Generic List of Parameters in ASP.Net using C# and VB.Net

Dear tanweeruddinb...,

Kindly reder below sample.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

Procedure

CREATE PROCEDURE [dbo].[Customers_CRUD]
      @Action VARCHAR(10)
      ,@CustomerId INT = NULL
      ,@Name VARCHAR(100) = NULL
      ,@Country VARCHAR(100) = NULL
AS
BEGIN
      SET NOCOUNT ON;
 
      --SELECT
    IF @Action = 'SELECT'
      BEGIN
            SELECT CustomerId, Name, Country
            FROM Customers
            WHERE (Name = @Name OR @Name IS NULL)
            AND (Country = @Country OR @Country IS NULL)
      END
END

HTML

<table>
    <tr>
        <td>Id:</td>
        <td><asp:TextBox runat="server" ID="txtId" /></td>
    </tr>
    <tr>
        <td>Name:</td>
        <td><asp:TextBox runat="server" ID="txtName" /></td>
    </tr>
    <tr>
        <td>Country:</td>
        <td><asp:TextBox runat="server" ID="txtCountry" /></td>
    </tr>
    <tr>
        <td colspan="2">
            <asp:Button Text="Search" ID="btnSearch" runat="server" OnClick="OnSearch" />
        </td>
    </tr>
</table>
<asp:GridView runat="server" ID="gvCustomers" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="Customer Id" DataField="CustomerId" />
        <asp:TemplateField HeaderText="Name">
            <ItemTemplate>
                <asp:Label Text='<%# Eval("Name") %>' runat="server" ID="lblName" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country">
            <ItemTemplate>
                <asp:Label Text='<%# Eval("Country") %>' runat="server" ID="lblCountry" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

Namespaces

C#

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

VB.Net

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

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        List<SqlParameter> parameters = GetParameters("SELECT");
        this.BindGrid(parameters);
    }
}

protected void OnSearch(object sender, EventArgs e)
{
    List<SqlParameter> parameters = GetParameters("SELECT");
    this.BindGrid(parameters);
}

private List<SqlParameter> GetParameters(string action)
{
    List<SqlParameter> parameters = new List<SqlParameter>();
    parameters.Add(new SqlParameter() { ParameterName = "@Action", Value = action });
    string id = txtId.Text;
    if (string.IsNullOrEmpty(id))
    {
        parameters.Add(new SqlParameter() { ParameterName = "@CustomerId", Value = DBNull.Value });
    }
    else
    {
        parameters.Add(new SqlParameter() { ParameterName = "@CustomerId", Value = id });
    }

    string name = txtName.Text;
    if (string.IsNullOrEmpty(name))
    {
        parameters.Add(new SqlParameter() { ParameterName = "@Name", Value = DBNull.Value });
    }
    else
    {
        parameters.Add(new SqlParameter() { ParameterName = "@Name", Value = name });
    }

    string country = txtCountry.Text;
    if (string.IsNullOrEmpty(country))
    {
        parameters.Add(new SqlParameter() { ParameterName = "@Country", Value = DBNull.Value });
    }
    else
    {
        parameters.Add(new SqlParameter() { ParameterName = "@Country", Value = country });
    }

    return parameters;
}

private void BindGrid(List<SqlParameter> parameters)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand("Customers_CRUD", con))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Parameters.AddRange(parameters.ToArray());
                cmd.CommandType = CommandType.StoredProcedure;
                sda.SelectCommand = cmd;
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    gvCustomers.DataSource = dt;
                    gvCustomers.DataBind();
                }
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim parameters As List(Of SqlParameter) = GetParameters("SELECT")
        Me.BindGrid(parameters)
    End If
End Sub

Protected Sub OnSearch(ByVal sender As Object, ByVal e As EventArgs)
    Dim parameters As List(Of SqlParameter) = GetParameters("SELECT")
    Me.BindGrid(parameters)
End Sub

Private Function GetParameters(ByVal action As String) As List(Of SqlParameter)
    Dim parameters As List(Of SqlParameter) = New List(Of SqlParameter)()
    parameters.Add(New SqlParameter() With {
    .ParameterName = "@Action",
    .Value = action
})
    Dim id As String = txtId.Text

    If String.IsNullOrEmpty(id) Then
        parameters.Add(New SqlParameter() With {
        .ParameterName = "@CustomerId",
        .Value = DBNull.Value
    })
    Else
        parameters.Add(New SqlParameter() With {
        .ParameterName = "@CustomerId",
        .Value = id
    })
    End If

    Dim name As String = txtName.Text
    If String.IsNullOrEmpty(name) Then
        parameters.Add(New SqlParameter() With {
        .ParameterName = "@Name",
        .Value = DBNull.Value
    })
    Else
        parameters.Add(New SqlParameter() With {
        .ParameterName = "@Name",
        .Value = name
    })
    End If

    Dim country As String = txtCountry.Text
    If String.IsNullOrEmpty(country) Then
        parameters.Add(New SqlParameter() With {
        .ParameterName = "@Country",
        .Value = DBNull.Value
    })
    Else
        parameters.Add(New SqlParameter() With {
        .ParameterName = "@Country",
        .Value = country
    })
    End If

    Return parameters
End Function

Private Sub BindGrid(ByVal parameters As List(Of SqlParameter))
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand("Customers_CRUD", con)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Parameters.AddRange(parameters.ToArray())
                cmd.CommandType = CommandType.StoredProcedure
                sda.SelectCommand = cmd
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    gvCustomers.DataSource = dt
                    gvCustomers.DataBind()
                End Using
            End Using
        End Using
    End Using
End Sub

 Screenshot