Populate all table and column names from database using C# and VB.Net in ASP.Net

Hi nauna,

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

HTML

<asp:CheckBoxList ID="chkTables" runat="server" AutoPostBack="true"
    OnSelectedIndexChanged="OnSelectedIndexChanged" RepeatDirection="Horizontal">
</asp:CheckBoxList>
<hr />
<asp:GridView runat="server" ID="gvColumns" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="COLUMN_NAME" HeaderText="Columns" />
        <asp:TemplateField>
            <ItemTemplate>
                <asp:Button Text="Show" runat="server" OnClick="OnShow"
                    CommandArgument='<%# Eval("COLUMN_NAME") %>' />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<hr />
<asp:GridView runat="server" ID="gvData"></asp:GridView>
<script type="text/javascript">
    function MutExChkList(chk) {
        var chkList = chk.parentNode.parentNode.parentNode;
        var chks = chkList.getElementsByTagName("input");
        for (var i = 0; i < chks.length; i++) {
            if (chks[i] != chk && chk.checked) {
                chks[i].checked = false;
            }
        }
    }
</script>

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 (!this.IsPostBack)
    {
        this.chkTables.DataSource = GetData("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'");
        this.chkTables.DataValueField = "TABLE_NAME";
        this.chkTables.DataTextField = "TABLE_NAME";
        this.chkTables.DataBind();
    }
    for (int i = 0; i < this.chkTables.Items.Count; i++)
    {
        this.chkTables.Items[i].Attributes.Add("onclick", "MutExChkList(this)");
    }
}

protected void OnSelectedIndexChanged(object sender, EventArgs e)
{
    this.gvColumns.DataSource = null;
    this.gvColumns.DataBind();
    this.gvData.DataSource = null;
    this.gvData.DataBind();

    string tableName = chkTables.SelectedValue;
    DataTable dt = GetData(string.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'{0}'", tableName));
    this.gvColumns.DataSource = dt;
    this.gvColumns.DataBind();
}

protected void OnShow(object sender, EventArgs e)
{
    string columnName = (sender as Button).CommandArgument;
    string tableName = chkTables.SelectedValue;
    DataTable dt = GetData(string.Format("SELECT {0} FROM {1}", columnName, tableName));
    this.gvData.DataSource = dt;
    this.gvData.DataBind();
}

private DataTable GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                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
    If Not Me.IsPostBack Then
        Me.chkTables.DataSource = GetData("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'")
        Me.chkTables.DataValueField = "TABLE_NAME"
        Me.chkTables.DataTextField = "TABLE_NAME"
        Me.chkTables.DataBind()
    End If

    For i As Integer = 0 To Me.chkTables.Items.Count - 1
        Me.chkTables.Items(i).Attributes.Add("onclick", "MutExChkList(this)")
    Next
End Sub

Protected Sub OnSelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
    Me.gvColumns.DataSource = Nothing
    Me.gvColumns.DataBind()
    Me.gvData.DataSource = Nothing
    Me.gvData.DataBind()
    Dim tableName As String = chkTables.SelectedValue
    Dim dt As DataTable = GetData(String.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'{0}'", tableName))
    Me.gvColumns.DataSource = dt
    Me.gvColumns.DataBind()
End Sub

Protected Sub OnShow(ByVal sender As Object, ByVal e As EventArgs)
    Dim columnName As String = (TryCast(sender, Button)).CommandArgument
    Dim tableName As String = chkTables.SelectedValue
    Dim dt As DataTable = GetData(String.Format("SELECT {0} FROM {1}", columnName, tableName))
    Me.gvData.DataSource = dt
    Me.gvData.DataBind()
End Sub

Private Function GetData(ByVal query As String) As DataTable
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(conString)
        Using cmd As SqlCommand = New SqlCommand(query)
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Using
End Function

Screenshot