Redgate Hub

  • Product articles
  • University
  • Events
  • Forums
  • Community
  • Simple Talk
  • Home
  • Cloud
  • DevOps
  • Sysadmin
  • Development
  • Databases
  • Opinion
  • Books
  • Blogs
  • Log in
  • Sign up
Amirthalingam Prasanna

02 August 2005

  • 83
  • 91114 views

  • 0
    • Printer friendly version
  • Home
  • Development
  • .NET
  • Schema and metadata retrieval using ADO.NET
Amirthalingam Prasanna

02 August 2005

91114 views

83
0

Schema and metadata retrieval using ADO.NET

How to obtain different types of information from your SQL Server 2000 database using ADO.NET 2.0, part of VS.NET 2005 and .NET 2.0.

Using ADO.NET 2.0 to get schema and metadata information

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.

Introduction

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.

Schema information and metadata

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.

Information from ADO.NET 2.0 connection

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.

C# Code:

1
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.

C# Code:

1
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.

C# Code:

1
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:

C# Code:

1
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:

C# Code:

1
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.

Information from ADO.NET 2.0 datareader

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:

C# Code:

1
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.

Why use ADO.NET 2.0 to retrieve metadata?

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:

C# Code:

1
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"]);}

Conclusion

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.

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Subscribe to our fortnightly newsletter

  • 83
  • 91114 views

    • Printer friendly version

Rate this article

Click to rate this post!
[Total: 2 Average: 4]

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Subscribe to our fortnightly newsletter

Amirthalingam Prasanna

Prasanna is a software engineer, technical author and trainer with many years of development and consulting experience in the software development industry. He is a Microsoft MVP in the Visual developer category, and a MCPD on enterprise application development. He has authored many articles and has worked on content creation for many Microsoft certification exams and courses. He is also a frequent speaker at Microsoft technology conferences and events. You can read his blog at www.prasanna.ws and e-mail him at feedback@prasanna.ws

View all articles by Amirthalingam Prasanna

Load comments

Related articles

Michal Kostrzewski
03 November 2017
Michal Kostrzewski
03 November 2017

Hacking Visual Studio

0
16
  • .NET
Visual Studio, like any Integrated Development Environment, can host extensions for more specialist languages or development tasks. This sort of work is reasonably straightforward most of the time but occasionally you need functionality that isn't available in the APIs. Michal takes two examples, printing code in an editing window, and gaining access to the Visual Studio Notifications, and explains how to hack Visual Studio to get to the functionality.… Read more
0
16
  • .NET
Michael Sorens
07 March 2016
Michael Sorens
07 March 2016

The Zen of Code Reviews: Review As If You Own the Code

0
27
  • .NET
A code review is a serious business; an essential part of development. Whoever signs off on a code review agrees, essentially, that they would be able to support it in the future, should the original author of the code be unavailable to do it. Review code with the energy you'd use if you owned the code. Michael Sorens runs through the principles of reviewing C# code.… Read more
0
27
  • .NET
Amirthalingam Prasanna
25 October 2005
Amirthalingam Prasanna
25 October 2005

ADO.NET 2.0 Factory Classes

0
140
  • .NET
This article explains how to use .NET 2.0's data provider factory classes to develop a pluggable data layer that is independent of database type and ADO.NET data provider. … Read more
0
140
  • .NET

Tags

.NET, .NET Framework, ADO.NET, Database, metadata, retrieve, schema, SQL, SQL Server

Simple Talk

  • FAQ
  • Sitemap
  • About Simple Talk
  • Contact Us