Create (assign) variable from DataTable column in ASP.Net using C# and VB.Net

Hi Tevin,

Check this example. Now please take its reference and correct your code.

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

Namespaces

C#

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

VB.Net

Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    DataTable dt = GetData();
    List<string> ids = dt.AsEnumerable().Select(x => x["CustomerId"].ToString()).ToList();
    List<string> names = dt.AsEnumerable().Select(x => x["Name"].ToString()).ToList();
    List<string> countries = dt.AsEnumerable().Select(x => x["Country"].ToString()).ToList();
}

protected DataTable GetData()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Customers", con))
        {
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);

                return dt;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    Dim dt As DataTable = GetData()
    Dim ids As List(Of String) = dt.AsEnumerable().Select(Function(x) x("CustomerId").ToString()).ToList()
    Dim names As List(Of String) = dt.AsEnumerable().Select(Function(x) x("Name").ToString()).ToList()
    Dim countries As List(Of String) = dt.AsEnumerable().Select(Function(x) x("Country").ToString()).ToList()
End Sub

Protected Function GetData() As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using sda As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers", con)
            Using dt As DataTable = New DataTable()
                sda.Fill(dt)

                Return dt
            End Using
        End Using
    End Using
End Function

Screenshot