Browse DevX
Sign up for e-mail newsletters from DevX


What's In Your Database? Find Out with the Schema API in ADO.NET 2.0 : Page 2

ADO.NET 2.0 includes an extremely useful set of providers that you can use dozens of ways to quickly and accurately query your SQL Server or JET database. This article shows you how to be the pied piper of your metadata using the Schema API.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Reading a List of Metadata Collections from SQL Server
Obtaining a list of the available metadata collections is easy and gives you a good feel for the types of information that you can obtain from your database. The following code, which is included in the Page_Load event handler of the ASP.NET example page named get-collections-sql.aspx (get the code download for this article), first collects the connection string from web.config and then creates a connection to the database. Then it calls the GetSchema method to obtain a standard ADO.NET DataTable containing the list of available metadata collections. Notice that to get the metadata collection list you can use the GetSchema method with the String parameter value "MetaDataCollections" or with no parameter:

Sub Page_Load() ' read connection string from web.config Dim connect As String = ConfigurationSettings.ConnectionStrings( _ "nwind-sql").ConnectionString ' create a connection to the database Using conn As New SqlConnection(connect) Try conn.Open() ' get DataTable with all available metadata collections Dim dt As DataTable = conn.GetSchema("MetaDataCollections") ' or could just use: Dim dt As DataTable = conn.GetSchema() ' bind DataTable to GridView control to display rows grid1.DataSource = dt grid1.DataBind() Catch ex As Exception output.Text = "* ERROR: " & ex.Message Finally conn.Close() End Try End Using End Sub

Author's Note: In version 2.0 of the .NET Framework, VB.NET supports the "Using" construct that was available only to C# in earlier versions. This is the preferred way of working with classes that implement the IDispose interface and ensures that resources are disposed of promptly and correctly. The code shown here uses this approach with the SqlConnection class.

To display the contents of the DataTable, the code binds it to a GridView control located in the HTML section of the page. The code below shows the GridView control declaration and Figure 1 shows the results:

<asp:GridView id="grid1" runat="server" BorderWidth="1px" ForeColor="Black" BackColor="White" CellPadding="4" BorderStyle="None" BorderColor="#dedfde" GridLines="Vertical" EnableViewstate="False"> <HeaderStyle ForeColor="White" Font-Bold="True" BackColor="#6b696b" /> <RowStyle BackColor="#f7f7de" /> <AlternatingRowStyle BackColor="White" /> <SelectedRowStyle ForeColor="White" Font-Bold="True" BackColor="#CE5D5A" /> </asp:GridView>

Figure 1. Selection, Collections: You can read a list of the available metadata collections from SQL Server.
Figure 2. Selection, Collections: You can read a list of the available metadata collections using the OleDb classes.

Querying Other Types of Databases
It's important to recognize that not all providers will return the same set of metadata collections. For example, using the System.Data.OleDb provider and the OleDbConnection class, the GetSchema method returns a much more restricted list of collections (see Figure 2).

Meanwhile, using the System.Data.Odbc.OdbcConnection class gives a different set of collections again (see Figure 3). All these examples use the same Northwind sample database, running on SQL Server 2005 ("Yukon") Beta 2 on Windows Server 2003. So you must be careful when writing code that uses the Schema API to ensure that you access only collections that are available through the specific data provider you are using.

Figure 3. Selection, Collections: You can read a list of the available metadata collections using the Odbc classes.
Figure 4. Selection, Collections: You can read lists of metadata collections from a JET database using the Odbc and OleDb classes.

Querying a Microsoft Access Database
The set of example pages you can download for this article also contains two that query a Microsoft Access (JET) database for lists of metadata collections. The results from these (see Figure 4) demonstrate again how the information available differs depending not only on the type of database but the provider you use to access it.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date