JTBarton.com

Skip Navigation Links
Home
Barcode overview
SQL playground
Web Cameras
Personal information
Developer links
About Web Host
Contact Me
Sign In

How to get the schema's from a SQL database

The intention of the article is not to show how to use web control, just the ADO.net portion of how the lists were populated.  Although there may be some cross over where I will state the standard web control used on the web form.

Please note that this is designed for Microsoft SQL 2000 due to limited funds.  When I get the time and money I will provide examples in 2005 using only the meta data to acquire the data.

There are three sections to the display page:
  1. Databases
  2. Tables
  3. Columns
The details about the connection string can be found in my article Login to ASP.NET 2.0 using the new tools provided and "form authentication"

Databases List

This is achieve by using a default stored procedure supplied by default SQL 2000.
    sp_databases
But I'm sure you would like to see the full code example, so here it is. This is populating a WebControls.ListBox with an ID of lbDatabases.
    using ... // Default list of resources not shown
    using System.Data.SqlClient;
    using System.Web.Configuration;
    
    private void GetDatabaseList()
    {

      string connectionString = 
        WebConfigurationManager.ConnectionStrings["AspUserMangement"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd; SqlDataReader commDR; lbDatabases.Items.Clear(); try { cmd = con.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "sp_databases"; using (con) { con.Open(); // Add list of databases to listbox commDR = cmd.ExecuteReader(); while (commDR.Read()) { lbDatabases.Items.Add(commDR.GetString(0)); } } } catch { lbDatabases.Items.Add("--No Databases Found--"); } lbDatabases.DataBind(); }

Tables List

This is achieved by selecting/opening the database and then using a simple select statement.
    cmd.CommandText = "use " + lbDatabases.SelectedItem.Text;
    cmd.ExecuteNonQuery();
      
    select name from SYSOBJECTS where TYPE = 'U' and name<>'dtProperties' order by NAME
    cmd.ExecuteReader();
Again the full code listing for this is below.  This is populating a WebControls.ListBox with an ID of lbTables.  
    using ... // Default list of resources not shown
    using System.Data.SqlClient;
    using System.Web.Configuration;
    
    private void GetTableList()
    {
        string connectionString = WebConfigurationManager.ConnectionStrings["AspUserMangement"].ConnectionString;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd;
        SqlDataReader commDR;

        cmd = con.CreateCommand();
        cmd.CommandType = CommandType.Text;
        if (lbDatabases.SelectedItem != null)
        {
            cmd.CommandText = "use " + lbDatabases.SelectedItem.Text;

            lbTables.Items.Clear();
            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select name from SYSOBJECTS where TYPE = 'U' and name<>'dtProperties' order by NAME";

                commDR = cmd.ExecuteReader();
                while (commDR.Read())
                {
                    lbTables.Items.Add(commDR.GetString(0));
                }
            }
            catch
            {
                lbTables.Items.Add("--No Tables Found--");
            }
            lbTables.DataBind();
        }
        else
        {
            lbTables.Items.Clear();
            lbTables.Items.Add("--No Tables Found--");
            lbTables.DataBind();
        }
        // Now refresh the column list, although nothing is selected
        GetColumnList();
    }

Columns List

This is achieved by using a slightly more complex select statement, which passes the currently selected database and table into the query.
    "SELECT Column_name as name, Data_type as type,Character_maximum_length as length FROM " + 
      lbDatabases.SelectedItem.Text.Trim() +
      ".INFORMATION_SCHEMA.Columns where TABLE_NAME = '" +
      lbTables.SelectedItem.Text.Trim() + "'";
But I'm sure you would like to see the full code example, so here it is. This is populating a WebControls.GridView with an ID of gvColumns.
    using ... // Default list of resources not shown
    using System.Data.SqlClient;
    using System.Web.Configuration;
    
    private void GetColumnList()
    {
        gvColumns.DataSource = null;

        if (lbTables.SelectedItem != null && lbDatabases.SelectedItem != null)
        {
            string sqlStatement = 
              "SELECT Column_name as name, Data_type as type,Character_maximum_length as length FROM " +
              lbDatabases.SelectedItem.Text.Trim() + ".INFORMATION_SCHEMA.Columns where TABLE_NAME = '" +
              lbTables.SelectedItem.Text.Trim() + "'"; string connectionString =
              WebConfigurationManager.ConnectionStrings["AspUserMangement"].ConnectionString; SqlConnection con = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(sqlStatement, con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); try { using (con) { con.Open(); da.Fill(dt); gvColumns.DataSource = dt; } } catch { } } gvColumns.DataBind(); }

Comments

I would like to point out that this is the way I have achieved the required functionality, if there is a better way to do this, please contact me and provide details.  Any updates will be credited.