Select multiple rows from DataTable using LINQ in C#

The LINQ select query will be used to fetch multiple records from DataTable in C#

HTML Markup

The following HTML Markup consists of a DropDownList and a GridView. The DropDownList is assigned OnSelectedIndexChanged property and the AutoPostBack property is set to True.

Country:

<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Country_Changed">

    <asp:ListItem Text="" Value=""></asp:ListItem>

    <asp:ListItem Text="United States" Value="United States"></asp:ListItem>

    <asp:ListItem Text="India" Value="India"></asp:ListItem>

    <asp:ListItem Text="France" Value="France"></asp:ListItem>

    <asp:ListItem Text="Russia" Value="Russia"></asp:ListItem>

</asp:DropDownList>

<hr />

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

    <Columns>

        <asp:BoundField DataField="CustomerId" HeaderText="Customer Id" />

        <asp:BoundField DataField="Name" HeaderText="Name" />

        <asp:BoundField DataField="Country" HeaderText="Country" />

    </Columns>

</asp:GridView>

 

 

Namespaces

You will need to import the following namespaces.

C#

using System.Data;

using System.Linq;

using System.Configuration;

using System.Data.SqlClient;

 

VB.Net

Imports System.Data

Imports System.Linq

Imports System.Configuration

Imports System.Data.SqlClient

 

 

Select multiple rows from DataTable using LINQ in C# and VB.Net

A function named GetData is used to fetch the records from the Customers table and return them in the form of a DataTable.

The GetData function is called at two places, first inside the Page_Load event of the page and second inside the Country_Changed event handler which is triggered on the DropDownList SelectedIndexChanged event.

Inside the Country_Changed event handler, first the selected value of the Country is fetched from the DropDownList.

If the selected value is not empty, then DataTable records are filtered using the WHERE clause of LINQ and the results are used to populate the GridView control.

If the selected value is empty, then simply the DataTable is used to populate the GridView control.

C#

protected void Page_Load(object sender, EventArgs e)

{

    if (!this.IsPostBack)

    {

        DataTable dt = this.GetData();

        gvCustomers.DataSource = dt;

        gvCustomers.DataBind();

    }

}

 

protected void Country_Changed(object sender, EventArgs e)

{

    string country = ddlCountry.SelectedItem.Value;

    DataTable dt = this.GetData();

    if (!string.IsNullOrEmpty(country))

    {

        var customers = from customer in dt.AsEnumerable()

                        where customer.Field<string>("Country") == country

                        select new

                        {

                            CustomerId = customer.Field<int>("CustomerId"),

                            Name = customer.Field<string>("Name"),

                            Country = customer.Field<string>("Country")

                        };

        gvCustomers.DataSource = customers;

        gvCustomers.DataBind();

    }

    else

    {

        gvCustomers.DataSource = dt;

        gvCustomers.DataBind();

    }

}

 

private DataTable GetData()

{

    DataTable dt = new DataTable();

    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    string sql = "SELECT * FROM Customers";

    using (SqlConnection conn = new SqlConnection(constr))

    {

        using (SqlCommand cmd = new SqlCommand(sql))

        {

            cmd.Connection = conn;

            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))

            {

                sda.Fill(dt);

            }

        }

    }

 

    return dt;

}