Av rating:
Total votes: 39
Total comments: 7

 send to a friend
 printer friendly version

This article describes how to obtain different types of information from your SQL Server 2000 database using ADO.NET 2.0, part of Visual Studio.NET 2005 and Microsoft .NET 2.0.

ADO.NET 2.0 is the premier data access method for the Microsoft .NET platform, providing a uniform way of programmatically accessing metadata and schema information. Though the meta information might depend on the type of database, the way the information is obtained is similar across databases.

You should have a good understanding of ADO.NET and knowledge of databases before using ADO.NET 2.0 to access metadata and schema information.

Varying levels of information about your database and database server can be obtained programmatically, including top-level information such as the restricted words for your server and low-level information such as the datatype of a stored procedure parameter.

Various database products have different ways of storing this information. In SQL Server 2000, for example, the sys* tables and Master database are used to store metadata and schema information, and the tables must be queried to retrieve the required information.

Let’s look at how we can obtain meta and schema information from a SQL Server 2000 database.

The ADO.NET connection under various data providers offers a GetSchema method that returns a datatable with a collection of meta information. Each of these collections can be identified by a name (CollectionName).

If we want to retrieve a database from our database server, for example, we can open an ADO.NET connection to the server and use the collection name "Databases" as a parameter for the GetSchema method. This returns a datatable with column headings of database name, database id and created date, and each is filled with information about the databases on the server.

System.Data.SqlClient.SqlConnection con =
                new System.Data.SqlClient.SqlConnection
  ("Data Source=.;initial catalog=Master;Integrated security=true");
con.Open();
System.Data.DataTable tbl=con.GetSchema("Databases");

The GetSchema method also enables you to use a string array to restrict the rows of information returned. Each metadata collection has a number of restrictions and identifiers. The restrictions are used to limit the data returned based on columns, and identifiers specify the columns required to uniquely identify a particular instance of metadata.

The Databases collection, for example, has one restriction (database name) and one identifier (database name). This specifies that the Databases collection can be restricted by the database name, and only the database name is required to uniquely identify information about one database.

System.Data.SqlClient.SqlConnection con =
                new System.Data.SqlClient.SqlConnection("Data 
  Source=.;Integrated security=true");
con.Open();
System.Data.DataTable tbl =
                con.GetSchema("Databases", new string[] {"Northwind" });

The code above fills the datatable with metadata about the Northwind database. Some metadata collections expose many identifiers and restrictions. An easy way to find out the names of the collections that can be queried and the number of restrictions and identifiers available is to call the no-argument GetSchema method or use "MetadataCollections" as the collection name to the GetSchema method. This returns a datatable filled with the metadata collection names and the number of restrictions and identifiers available for each one of them.

System.Data.SqlClient.SqlConnection con =
                new System.Data.SqlClient.SqlConnection("Data 
  Source=.;Integrated security=true");
con.Open();
System.Data.DataTable tbl = con.GetSchema("MetadataCollections");

One problem you might face when retrieving metadata using ADO.NET is remembering all the available metadata collection names. The ADO.NET data provider comes with a few static classes you can use to specify the collection names. System.Data.CoFmmon.DBMetaDataCollectionNames, for example, contains fields for the collection names common to any database, and System.Data.SqlClient.SqlClientMetaDataCollectionNames contains fields representing the collection names that are SQL-Server specific.

The code snippet we saw earlier that retrieves the databases in SQL Server can be re-written as follows:

System.Data.SqlClient.SqlConnection con =
                new System.Data.SqlClient.SqlConnection("Data 
  Source=.;initial catalog=Master;Integrated security=true");
con.Open();
System.Data.DataTable tbl =
  con.GetSchema(System.Data.SqlClient.
                  SqlClientMetaDataCollectionNames.Databases);

Let’s run through one more slightly complex example to reinforce these concepts. Assume we are creating a console application that lists all the databases in the server and the user-created tables in each database. We can accomplish that with the following code:

System.Data.SqlClient.SqlConnectionStringBuilder builder =
                new System.Data.SqlClient.SqlConnectionStringBuilder
                           ("Data Source=.;Integrated security=true");
System.Data.SqlClient.SqlConnection con =
                new System.Data.SqlClient.SqlConnection(builder.ConnectionString);
con.Open();
System.Data.DataTable tblDatabases =
                con.GetSchema(System.Data.SqlClient.
                           SqlClientMetaDataCollectionNames.Databases);
con.Close();
System.Data.DataTable tblTables;
foreach (System.Data.DataRow rowDatabase in tblDatabases.Rows)
{
builder.InitialCatalog = rowDatabase["database_name"].ToString();
con.ConnectionString = builder.ConnectionString;
Console.WriteLine(rowDatabase["database_name"].ToString());
con.Open();
tblTables = con.GetSchema(System.Data.SqlClient.
                SqlClientMetaDataCollectionNames.Tables, 
                          new string[] { null, null, null, "BASE TABLE" });
con.Close();
foreach (System.Data.DataRow rowTable in tblTables.Rows)
{
Console.WriteLine("\t" + rowTable["table_name"].ToString());
}
}

This code connects to the the SQL Server and gets a list of the databases as a datatable (tblDatabases). Then the rows in the datatable are repeated to retrieve the names (database_name) of the databases, and the connection string is modified to connect to the respective database.

For each database, the collection of tables is retrieved as a datatable (tblTables), and uses the restriction "BASE_TABLE" for the table_type column to specify that we want only the user-created tables to be returned. The initial null elements are required to specify that we are not making restrictions on the table_catalog, table_schema or table_name columns. Then we go through the collection of tables for each database and print out the names of the tables.

The ADO.NET datareaders expose a GetSchemaTable method similar to the ADO.NET connections. This GetSchemaTable method returns metadata information on the columns retrieved from the ADO.NET datareader. For example:

System.Data.SqlClient.SqlConnection con = 
                new System.Data.SqlClient.SqlConnection
                     ("Data Source=.;initial catalog=Northwind;
                                     Integrated security=true");
con.Open();
System.Data.SqlClient.SqlCommand cmd =
                new System.Data.SqlClient.SqlCommand
                     ("Select CustomerId,CompanyName,
                       ContactName from Customers", con);
System.Data.SqlClient.SqlDataReader rd = cmd.ExecuteReader();
System.Data.DataTable tbl = rd.GetSchemaTable();

In the code above, the datatable is filled with column metadata on the CustomerId, CompanyName and ContactName from the Customer table in the Northwind database.

How does using ADO.NET 2.0 to retrieve metadata differ from using SQL Management Objects (SMO) or SQL Distributed Management Objects (SQL-DMO)? The schema and metadata retrieval methods in the ADO.NET layer are used to retrieve read-only information; they cannot be used to manipulate the server. SMO or SQL-DMO, on the other hand, lets you manipulate the database server at an administrative level.

One advantage of the schema retrieval methods in the ADO.NET layer is that they are similar for other data sources as well. We can easily retrieve the worksheets in an Excel workbook, for example, using the same schema retrieval methods available in the OleDb data providers in ADO.NET:

System.Data.OleDb.OleDbConnection con =
            new System.Data.OleDb.OleDbConnection(
                @"Provider=Microsoft.Jet.OLEDB.4.0;
                Data Source=C:\Sample.xls;
                Extended Properties=""Excel 8.0;
                HDR=Yes;IMEX=1""");
con.Open();
System.Data.DataTable tbl =
            con.GetSchema(System.Data.OleDb.
                OleDbMetaDataCollectionNames.Tables);
con.Close();
foreach (System.Data.DataRow row in tbl.Rows)
{
Console.WriteLine(row["table_name"]);
}

Retrieving metadata has been simplified in ADO.NET 2.0 using the schema retrieval methods available from the connection and datareader objects. This information can be used to simplify the creation of code generators or similar applications in which metadata is required.