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:
- Databases
- Tables
- 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.
|