Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-2-display-database-table-metadata_10.html 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. 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" 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 DatabaseTables { 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; } } }

• published: 10 May 2014
• views: 11515