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

Last Reply 13 days ago By dharmendr

Posted 13 days ago

I have following SQL tabls

1. I want as webpage where all my sql table name is binding in checkboxlist.

2. Then user can select any table name from checkbox list.

3. So based on on selection it shows a gridview and show each table column.

4. Then once user select which column want to see

5. Click on show button process the select statement and fetch data from sql tables

CREATE TABLE [dbo].[users](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Username] [nvarchar](50) NULL,
	[password] [nvarchar](50) NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[User_Details](
	[UserDetailId] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NULL,
	[city] [nvarchar](50) NULL,
	[age] [nvarchar](50) NULL,
 CONSTRAINT [PK_User_Details] PRIMARY KEY CLUSTERED 
(
	[UserDetailId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

 

Posted 13 days ago

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