Get data from database based on List (Array) value using C# and VB.Net in ASP.Net

Hi marouane,

There is no need to use for loop. You can use the In condition in select query.

Check the below sample.

Database

HTML

<asp:GridView runat="server" ID="gvCustomers" AutoGenerateColumns="true"></asp:GridView>

Namespaces

C#

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

VB.Net

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

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        string[] lists = { "ALFKI", "ANATR", "ANTON", "AROUT", "BERGS" };
        string query = string.Empty;
        if (lists.Length > 0)
        {
            query = "SELECT CustomerID,ContactName,City,Country FROM Customers WHERE CustomerID IN ('" + string.Join("','", lists) + "')";
        }
        else
        {
            query = "SELECT CustomerID,ContactName,City,Country FROM Customers";
        }
        string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.Connection = con;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    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 IsPostBack Then
        Dim lists As String() = {"ALFKI", "ANATR", "ANTON", "AROUT", "BERGS"}
        Dim query As String = String.Empty
        If lists.Length > 0 Then
            query = "SELECT CustomerID,ContactName,City,Country FROM Customers WHERE CustomerID IN ('" & String.Join("','", lists) & "')"
        Else
            query = "SELECT CustomerID,ContactName,City,Country FROM Customers"
        End If
        Dim constr As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Using cmd As SqlCommand = New SqlCommand(query)
                cmd.Connection = con
                Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Dim dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    gvCustomers.DataSource = dt
                    gvCustomers.DataBind()
                End Using
            End Using
        End Using
    End If
End Sub

Screenshot