Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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
Display database table metadata in asp.net web application

DropDownList on the WebForm should display both the table names
DropDownList displaying all table names from the database

When a table is selected from the DropDownList, it's metadata should be displayed in the GridView control
Display table metadata in 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;
        }
    }
}

dot net written test questions answers

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.