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.

Table 11-1: The GetSchema 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.

image from book

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> 
image from book

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.

image from book

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 
image from book

image from book

C#

private void Form1_Load(object sender, EventArgs e) {    foreach (ConnectionStringSettings cnSetting in       ConfigurationManager.ConnectionStrings)    {       comboBox1.Items.Add(cnSetting.Name);    }    //mount northwind if it isn't mounted    using (SqlConnection cn = new SqlConnection())    {       using (SqlCommand cmd = cn.CreateCommand())       {          cmd.CommandText = "SELECT COUNT(*) FROM sysdatabases"             + "WHERE name='Northwind'";          cn.ConnectionString = @"Server=.\SQLEXPRESS;"             + "Database=master;"             + "integrated security=true";          cn.Open();          int count = (int)cmd.ExecuteScalar();          if (count == 1)          {             return;          }          cn.Close();          cn.ConnectionString = @"Server=.\SQLEXPRESS;"             + @"AttachDbFilename=|DataDirectory|\MountedNorthwnd.mdf;"             + "Database=Northwind;"             + "integrated security=true;"             + "user instance=false";          cn.Open();          cn.Close();          MessageBox.Show("Northwind Mounted");     }   } } 
image from book

The code to mount the Northwind database is proactively checking the sysdatabases table, which is located in the master database, to see whether the Northwind database exists. If the database does not exist, the connection is closed and a new connection string is assigned that will mount the file called MountedNorthwnd.mdf as a database called Northwind. This database is used for the OLEDB and ODBC connections that will be tested.

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

Similar book on Amazon
Beginning Linux Programming
Beginning Linux Programming
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net