CodeSnip: Access Database Schema with ASP.NET 2.0
page 1 of 1
Published: 06 Oct 2004
Unedited - Community Contributed
Abstract
Retrieving Access database schema with ASP.NET 2.0 is actually very simple. First you create an OleDbConnection to a database. Next, you fill a DataTable with schema information using GetSchema method. Finally, you bind the DataTable to a GridView.
by Andrew Mooney
Feedback
Average Rating: 
Views (Total / Last 10 Days): 13300/ 17

[ Run Sample ]
Retrieving Access database schema with ASP.NET 2.0 is actually very simple. First you create an OleDbConnection to a database. Next, you fill a DataTable with schema information using GetSchema method. Finally, you bind the DataTable to a GridView.

When you use the GetSchema method of the OleDbConnection with no overloads it returns the same information that you get when requesting MetaDataCollections.

// These two line get you the same information.
OleDbConnection1.GetSchema();
OleDbConnection1.GetSchema("MetaDataCollections");

Using either of the above methods gives you a list of what schema information you can get from the database. This is a complete list from an Access 2003 database:

  • MetaDataCollections
  • DataSourceInformation
  • DataTypes
  • Restrictions
  • ReservedWords
  • Columns
  • Indexes
  • Procedures
  • Tables
  • Views

You can also use restrictions with the GetSchema method to get more specific information. For example, you might want a list of columns for just one table. Or, you may want a list of tables that does not include hidden system tables. Restrictions are a string array in this format: {TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}.

// Get all of the column information from Table1.
string[] restrictions1 = new string[] { null,null, "Table1",null };
System.Data.DataTable DataTable1 = OleDbConnection1.GetSchema("Columns", restrictions1);


// Get all of the table information except system tables.
string[] restrictions1 = new string[] { null, null, null, "TABLE" };
System.Data.DataTable DataTable1 = OleDbConnection1.GetSchema("Tables", restrictions1);

The AutoNumber data type shows up in the schema as a long integer. This means that there is no way to determine if a field auto increments. I have found a work around for this. I type "AutoNumber" in the description for AutoNumber fields and this can be seen in the sample. Since, description is the last field you may need to scroll to the right.

Sample web page for retrieving Access database schema:

<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" 
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
private void Page_Load(object sender, EventArgs e)
{
System.Data.OleDb.OleDbConnection OleDbConnection1 = 
new System.Data.OleDb.OleDbConnection(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
Server.MapPath("data\\database.mdb"));
OleDbConnection1.Open();
System.Data.DataTable DataTable1 = OleDbConnection1.GetSchema();
OleDbConnection1.Close();
GridView1.DataSource = DataTable1;
GridView1.DataBind();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Database Schema</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" Runat="server">
</asp:GridView>   
</div>
</form>
</body>
</html>



User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 7:28:11 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search