Select record from DataTable and Database using Linq in ASP.Net

Hey Omega,

If you want to write query like your way so you need use database with entity framework.

Please refer below sample.

HTML

Country :
<asp:TextBox runat="server" ID="txtId" /><br />
<asp:Button Text="Search" runat="server" OnClick="Search" />
<asp:GridView runat="server" ID="gvCustomers" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="CustomerId" DataField="CustomerId" />
        <asp:BoundField HeaderText="Name" DataField="Name" />
        <asp:BoundField HeaderText="Country" DataField="Country" />
    </Columns>
</asp:GridView>
<br />
<asp:GridView runat="server" ID="GridView1" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField HeaderText="CustomerId" DataField="CustomerId" />
        <asp:BoundField HeaderText="Name" DataField="Name" />
        <asp:BoundField HeaderText="Country" DataField="Country" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Linq;
using TestModel;

VB.Net

Imports System.Data.SqlClient
Imports System.Data
Imports TestModel

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = GetData();
        gvCustomers.DataSource = dt;
        gvCustomers.DataBind();
    }
}

private static DataTable GetData()
{
    using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString))
    {
        using (SqlCommand cmd = new SqlCommand("Select * from Customers", con))
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
            {
                using (DataTable dt = new DataTable())
                {
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }
    }
}

protected void Search(object sender, EventArgs e)
{
    //Database.
    var id = int.Parse(txtId.Text);
    using (TestEntities test = new TestEntities())
    {
        var customers = from c in test.Customers
                        where c.CustomerId >= id
                        select new { c.CustomerId, c.Name, c.Country };

        gvCustomers.DataSource = customers;
        gvCustomers.DataBind();
    }

    // Datatable 
    DataTable dt = GetData();
    DataTable result = (from customer in dt.AsEnumerable()
                        where customer.Field<int>("CustomerId") >= Convert.ToInt32(txtId.Text)
                        select customer).CopyToDataTable();

    GridView1.DataSource = result;
    GridView1.DataBind();
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = GetData()
        gvCustomers.DataSource = dt
        gvCustomers.DataBind()
    End If
End Sub

Private Shared Function GetData() As DataTable
    Using con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
        Using cmd As SqlCommand = New SqlCommand("Select * from Customers", con)
            Using adapter As SqlDataAdapter = New SqlDataAdapter(cmd)
                Using dt As DataTable = New DataTable()
                    adapter.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs)
    Dim id = Integer.Parse(txtId.Text)

    Using test As TestEntities = New TestEntities()
        Dim customers = From c In test.Customers Where c.CustomerId >= id Select New With {c.CustomerId, c.Name, c.Country
        }
        gvCustomers.DataSource = customers
        gvCustomers.DataBind()
    End Using

    Dim dt As DataTable = GetData()
    Dim result As DataTable = (From customer In dt.AsEnumerable() Where customer.Field(Of Integer)("CustomerId") >= Convert.ToInt32(txtId.Text) Select customer).CopyToDataTable()
    GridView1.DataSource = result
    GridView1.DataBind()
End Sub

Screenshot