Part 2 - Display database table metadata in asp.net web application

Suggested Videos:
Part 1 - How to retrieve data from different databases in asp.net



Dot net written test:
1. Create an asp.net web page with a dropdownlist and a gridview control.
2. When the page loads the dropdownlist should be populated with all the table names that are in a specific sql server database
3. Upon selecting a table from the dropdownlist, the respective table metadata i.e all the column names and their datatypes must be displayed in the gridview control.



In the Sample database we have 2 tables - Departments and Employees


DropDownList on the WebForm should display both the table names


When a table is selected from the DropDownList, it's metadata should be displayed in the GridView control


Step 1: Use the following SQL script to create database tables.

Create table Departments

(

     ID int primary key identity,

     Name nvarchar(50),

     Location nvarchar(50)

)

Create table Employees

(

     ID int primary key identity,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50),

     Salary int,

     DepartmentId int foreign key references Departments(Id)

)

Step 2: Stored procedures to retrieve the list of table names and their metadata.

Create Procedure spGetAllTables

as

Begin

     Select TABLE_NAME

     from INFORMATION_SCHEMA.TABLES

     where TABLE_TYPE='BASE TABLE'

End

Create Procedure spGetTableMetaData

@TableName nvarchar(50)

as

Begin

     Select COLUMN_NAME, DATA_TYPE

     from INFORMATION_SCHEMA.COLUMNS

     where TABLE_NAME = @TableName

End

Step 3: Create an asp.net web application. Drag and drop a DropDownList control.
a) Set AutoPostBack="true"
b) Double click on the DropDownList1 control to generate the click event handler.

At this point the HTML for the DropDownList should look as shown below.

<asp:DropDownList ID="DropDownList1" AutoPostBack="true" runat="server"     onselectedindexchanged="DropDownList1_SelectedIndexChanged">

</asp:DropDownList>

Step 4: Drag and drop a GridView control and AutoFormat it to select "Colourful" scheme. Add 2 bound columns
a) For the first bound column set
    DataField="Column_Name"
    HeaderText="Column Name"
b) For the second bound column set
    DataField="Data_Type"
    HeaderText="Data Type"
c) Set AutoGenerateColumns="False"

At this point the HTML for the GridView should look as shown below.

<asp:GridView ID="GridView1" runat="server" CellPadding="4"

                ForeColor="#333333" GridLines="None"

                AutoGenerateColumns="False"

                EnableViewState="False">

    <AlternatingRowStyle BackColor="White" />

    <Columns>

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

        <asp:BoundField DataField="Data_Type" HeaderText="Data Type" />

    </Columns>

    <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />

    <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />

    <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />

    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />

    <SortedAscendingCellStyle BackColor="#FDF5AC" />

    <SortedAscendingHeaderStyle BackColor="#4D0000" />

    <SortedDescendingCellStyle BackColor="#FCF6C0" />

    <SortedDescendingHeaderStyle BackColor="#820000" />

</asp:GridView>

Step 5: Copy and paste the following code in the code-behind file.

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Web.UI.WebControls;

namespace DemoTables

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

            if (!IsPostBack)

            {

                DropDownList1.DataTextField = "TABLE_NAME";

                DropDownList1.DataValueField = "TABLE_NAME";

                DropDownList1.DataSource = GetAllTables();

                DropDownList1.DataBind();

                DropDownList1.Items.Insert(0, new ListItem("Select Table", "-1"));

            }

        }

        protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

        {

            if (DropDownList1.SelectedValue != "-1")

            {

                GridView1.DataSource = GetTableMetadata(DropDownList1.SelectedValue);

                GridView1.DataBind();

            }

        }

        private DataTable GetAllTables()

        {

            string CS = ConfigurationManager.ConnectionStrings["SampleDBCS"].ConnectionString;

            SqlConnection con = new SqlConnection(CS);

            SqlDataAdapter da = new SqlDataAdapter("spGetAllTables", con);

            DataTable dataTable = new DataTable();

            da.Fill(dataTable);

            return dataTable;

        }

        private DataTable GetTableMetadata(string tableName)

        {

            string CS = ConfigurationManager.ConnectionStrings["SampleDBCS"].ConnectionString;

            SqlConnection con = new SqlConnection(CS);

            SqlDataAdapter da = new SqlDataAdapter("spGetTableMetadata", con);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            da.SelectCommand.Parameters.Add(new SqlParameter("@TableName", tableName));

            DataTable dataTable = new DataTable();

            da.Fill(dataTable);

            return dataTable;

        }

    }

}