Getting Started
The metadata capabilities in Microsoft ADO.NET are exposed through a generic API using the DbConnection object. The DbConnection object's GetSchema method has overloads that allow you to pass the name of the schema information, called the metadata collection, that you are interested in. You can also pass filter information. Table 11-1 shows the overloads.
| Overload | Description |
|---|---|
| GetSchema() | Gets a DataTable with a row for each metadata collection that is available with this provider. This option is the same as calling GetSchema("MetaDataCollections"). |
| GetSchema(string) | Passes a metadata collection name and gets a DataTable containing a row for each item found in that metadata collection in the database. |
| GetSchema(string, string array) | Passes a metadata collection name and an array of string values that specify how to filter the results, and gets a DataTable containing a row for each filtered item found in the metadata collection in the database. |
You need an open connection to execute the GetSchema method. You can start by calling the GetSchema method with no parameters; it returns a list of the available metadata collections.
Our sample project is a Windows Forms application that contains both a Microsoft Access version and a SQL Server version of the Northwind database. It also has a copy of the Northwind database attached (mounted) to SQL Server Express that is used with the OLEDB and ODBC providers. The App.config file contains a clear statement first, which clears all connection strings that may have been defined in the machine.config file. Next, the following connection strings were added into the App.config file to provide connectivity to each database as needed.
App.config File
<?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <clear /> <add name="NorthwindSqlClient" connectionString= "Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\northwnd.mdf; Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> <add name="NorthwindSqlOleDb" connectionString= "Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=Northwind; Data Source=.\SQLEXPRESS" providerName="System.Data.OleDb" /> <add name="NorthwindSqlOdbc" connectionString= "DRIVER=SQL Server; SERVER=.\SQLEXPRESS; DATABASE=Northwind;Trusted_Connection=Yes" providerName="System.Data.Odbc" /> <add name="NorthwindAccess" connectionString= "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|\Northwind.mdb; Persist Security Info=True" providerName="System.Data.OleDb" /> </connectionStrings> </configuration>
Set a reference to System.Configuration.dll and add the using (C#) and imports (Visual Basic) statements for the System.Configuration namespace.
The connection strings are selectable by reading the ConnectionStringSettings collection and populating a combo box with the name of each connection. This application will be looking in SQL Express for a permanently mounted copy of the Northwind database. Rather than manually mounting the Northwind database, you can add code to check for the existence of the Northwind database and, if it is not found, mount the database. This is done in the form's Load event handler, as shown in the following code sample.
Visual Basic
Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load For Each cnSetting As ConnectionStringSettings In _ ConfigurationManager.ConnectionStrings comboBox1.Items.Add(cnSetting.Name) Next 'mount northwind if it isn't mounted Using cn As New SqlConnection() Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = "SELECT COUNT(*) FROM sysdatabases" _ + "WHERE name='Northwind'" cn.ConnectionString = "Server=.\SQLEXPRESS;" _ + "Database=master;" _ + "integrated security=true" cn.Open() Dim count As Integer = CType(cmd.ExecuteScalar(), Integer) If (count = 1) Then Return End If cn.Close() End Using cn.ConnectionString = "Server=.\SQLEXPRESS;" _ + "AttachDbFilename=|DataDirectory|\MountedNorthwnd.mdf;" _ + "Database=Northwind;" _ + "integrated security=true;" _ + "user instance=false" cn.Open() cn.Close() MessageBox.Show("Northwind Mounted") End Using End Sub 