This is another in an occasional series of short articles about working with SQL Server data in a .Net application, to explain how to work with SQL Server data, what is possible, and how to work with the issues that can come up. For the next article am exploring the options for working with the metadata of a SQL Server database using the ADO.Net Framework.

The ADO.Net Framework is a collection of objects that provides a consistent way to access data using any .Net language, regardless of the original format of the data. For example ADO.Net objects can work with text files, XML, and SQL Server and Oracle databases as data sources.

Options for Reading Schema Information in ADO.Net

There are two ADO objects that have methods for reading the metadata of a data source, the Connection object and the DataReader object. The SqlConnection object and the SqlDataReader object are the versions that are designed specifically to connect to a SQL Server database.

SqlConnection object is used to configure a connection to a SQL Server database. The previous article in this series, SQL Server From .Net: Making A Connection, shows how to create a connection and explores some of the options available.

You can use the GetSchema() method of the SqlCOnnection object to read the schema information from the database that the SqlConnection is connected to.

The GetSchemaTable() Method, https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getschematable(v=vs.110).aspx, of the ADO SqlDataReader object provides metadata information about the data that will be returned by the reader. A data reader is created by the ExecuteReader() method of a SqlCommand object. A SqlCommand object executes T-SQL commands or stored procedures with parameters.

Another option for reading the metadata is to query the System Views, Metadata Functions and Catalog Stored Procedures that are available for any SQL Server database. There is an excellent Stairway series on these collections at the SSC website, Stairway to Exploring Database Metadata.

There is a tool called SqlMetal.exe https://docs.microsoft.com/en-us/dotnet/framework/tools/sqlmetal-exe-code-generation-tool that reads the schema and generates a .Net object model based on an existing SQL Server database. 

The code generator is run at design-time and the solution must be re-compiled to work with a different schema or if the existing schema is changed, but the resulting structures allow database objects to be referenced by name in code and by using LINQ.

This article explores some of the options available for reading the schema using the SqlDataReader and SqlConnection objects. 

Sample Application

I created a simple console application to try out the options for reading the metadata of a SQL Server database. The .zip file with the code for the test is included below.

I added a class called SchemaTest with one method, TestSchemaData(). The namespace SchemaToDataSet comes from the name I gave to the project. Here is the code for the basic class:

using System;
using System.Data;
using System.Data.SqlClient;
namespace SchemaToDataSet
{
    class SchemaTest
    {
        public void TestSchemaData()
        {
            // Insert test code here
        }
    }
}

To run the test code I added code to the Main() method to create an instance of my class and run the test.

static void Main(string[] args)
        {
            SchemaTest st = new SchemaTest();
            st.TestSchemaData();
        }

The sample database is the same one I used in another article in the series, SQL Server From .Net: Building a DataSet with Code. The script for the tables and relations in the sample database is also included below.

Using the SqlConnection to Retrieve Metadata

The GetSchema() method of the SqlConnection object returns metadata information about the SQL Server database that the connection is connected to. There are 3 different overloads of the GetSchema() method that allow you to specify different collections of schema information, and to allow filtering of the metadata. If you call the GetSchema() method with no parameters, it returns the list of collections available as an ASP.Net DataTable object.

This code segment shows how to create and open a SqlConnection object, and use the GetSchema() method to get the list of metadata collections. 

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=(local);Initial Catalog=testDBExamples;Integrated Security=True";
conn.Open();
DataTable info = conn.GetSchema();

The first line creates a SqlConnection, called conn;. The next line sets the connection string to point to my sample database, called testDBExamples, on my local SQL Server instance, using Windows (integrated) security. For more details on the connection string and what options are available, see the article in this series, SQL Server From .Net: Making A Connection.

In the next line we open the connection. Then in the following line the GetSchema() method of the SqlConnection object is called. This method returns a DataTable object that contains a list of the collections of schema information available. To list the fields in the DataTable you can use a foreach loop to go through the Columns collection of the DataTable. This code sample will list the column names and the data type of each column:

foreach (DataColumn dc in info.Columns)
 {
     Console.WriteLine("Column Name: {0}  Type: {1}", dc.ColumnName, dc.DataType);
 }

This is the list of columns printed by the above code:

Column Name: CollectionName  Type: System.String
Column Name: NumberOfRestrictions  Type: System.Int32
Column Name: NumberOfIdentifierParts  Type: System.Int32

The first column is the name of the collection. The second column, NumberOfRestrictions, will be explained below. The NumberOfIdentifierParts column specifies the number of identifiers that can be strung together to uniquely identify the object. For example for identifiers in the “Tables” collection there are 3 parts, database, schema and table name. In the “Columns” collection the identifier has 4 parts, the three that specify the table and the fourth is the column name.

To access the values in the columns of the collection you can use the columns names as an index into each of the rows in the Rows collection of the DataTable. This code displays the values for the first row (row 0 in C# indexing) in the collectiion.

Console.WriteLine("CollectionName: {0}  Restrictions: {1} ID Parts: {2}", 
  info.Rows[0]["CollectionName"], info.Rows[0]["NumberOfRestrictions"], info.Rows[0]["NumberOfIdentifierParts"])

This is what is printed by the above code:

CollectionName: MetaDataCollections  Restrictions: 0 ID Parts: 0

You can also use the integer indexes of the columns to access the values. This will print the same result:

Console.WriteLine("CollectionName: {0}  Restrictions: {1} ID Parts: {2}", 
  info.Rows[0][0], info.Rows[0][1], info.Rows[0][2])

The next code example shows how to use a foreach loop to access each row of the DataTable and list the values in the columns:

foreach (DataRow dr in info.Rows)
{
    Console.WriteLine("CollectionName: {0}  Restrictions: {1} ID Parts: {2}",
      dr["CollectionName"], dr["NumberOfRestrictions"], dr["NumberOfIdentifierParts"]); }
} 

This is the list of collections from the above code:

Collection Name: MetaDataCollections  Restrictions: 0 ID Parts: 0
Collection Name: DataSourceInformation  Restrictions: 0 ID Parts: 0
Collection Name: DataTypes  Restrictions: 0 ID Parts: 0
Collection Name: Restrictions  Restrictions: 0 ID Parts: 0
Collection Name: ReservedWords  Restrictions: 0 ID Parts: 0
Collection Name: Users  Restrictions: 1 ID Parts: 1
Collection Name: Databases  Restrictions: 1 ID Parts: 1
Collection Name: Tables  Restrictions: 4 ID Parts: 3
Collection Name: Columns  Restrictions: 4 ID Parts: 4
Collection Name: AllColumns  Restrictions: 4 ID Parts: 4
Collection Name: ColumnSetColumns  Restrictions: 3 ID Parts: 3
Collection Name: StructuredTypeMembers  Restrictions: 4 ID Parts: 4
Collection Name: Views  Restrictions: 3 ID Parts: 3
Collection Name: ViewColumns  Restrictions: 4 ID Parts: 4
Collection Name: ProcedureParameters  Restrictions: 4 ID Parts: 1
Collection Name: Procedures  Restrictions: 4 ID Parts: 3
Collection Name: ForeignKeys  Restrictions: 4 ID Parts: 3
Collection Name: IndexColumns  Restrictions: 5 ID Parts: 4
Collection Name: Indexes  Restrictions: 4 ID Parts: 3
Collection Name: UserDefinedTypes  Restrictions: 2 ID Parts: 1

To access the different collections of metadata, you supply the name of the collection to the GetSchema() method like this:

     DataTable info =  conn.GetSchema("MetaDataCollections")

This retrieves the first collcetion in the list, the “MetaDataCollections” collection, which is actually the same collection shown above where we invoked the GetSchema() method with no parameters:

     DataTable info = conn.GetSchema();

Some of the collections apply to all data sources and some apply only to SQLServer databases. The collections of schemas that are defined for all data sources are described in this article and the collections specific to SQL Server are described in this article.

Applying Restrictions to Collections

For many of the schema collections there is a set of restrictions that can be applied to filter the data returned. The number of restrictions in the schema list above is the number of restrictions that can be applied for that collection.

The list of restrictions for each collection can be accessed by retrieving another collection called “Restrictions” as in this example:

DataTable restrictions = conn.GetSchema("Restrictions");

These are the columns of “Restrictions” collection:

CollectionName
RestrictionName
ParameterName
RestrictionDefault
RestrictionNumber 

The list of restrictions is quite long. Unfortunately the Restrictions collection does not itself have any restrictions, so you must get the entire collection. However, once you have the table of restrictions you can narrow down the list to a specific collection by supplying a selection condition to the Select() method of the DataTable, using the column names as listed above:

DataRow[] tableRestrictions = restrictions.Select("CollectionName='Tables'");

This sample returns the set of rows of the restrictions table where the CollectionName field is equal to “Tables.” This returns the four rows of the restrictions that apply to the “Tables” collection.  This table shows the list of restrictions for the “Tables” collection:

{Author: Hi. Will these tables work? I pasted them from Word via Open Office, not sure if one can format in this editor? Let me know if an image works better.}

To apply the restrictions to the query of the “Tables” collection, we use another overload of the GetSchema() method. Besides the collection name you also supply a string array that specifies the restrictions. The number of elements in the string array is equal to the number of restrictions for the collection, which is the number in the NumberOfRestrictions column of the “MetaDataCollections” collection shown above.

This code example demonstrates how apply a restriction to return only schema rows that apply to a specific table name from the “Tables” collection.

string[] filters = new string[4];
filters[2] = "Catalog";  
DataTable filterTables = conn.GetSchema("Tables", filters);

This example first creates a string array with 4 elements (the number of restrictions for the “Tables” collection). The next line sets the 3rd parameter (C# uses 0-based indexes so the third element has index 2) to specify the table name to be returned.  Then the GetSchema() method is called with the name of the “Tables” collectiion and the array with the filters. Only one row is returned, the row representing the Catalog table in the sample database. This table shows the resulting row:

Using the SqlDataReader to Retrieve Metadata

Another method for accessing metadata of a SQL Server database is to use the GetSchemaTable() method of the SqlDataReader object. This method returns a DataTable object that contains the metadata for the columns in the data that would be returned by current T-SQL command assigned to the  SqlCommand object that was used to create the data reader.

Since the GetSchemaTable() method of the SqlDataReader returns the schema of whatever the current command specifies, I decided to test it with a simple query using the @@VERSION. function. This sample creates a reader and retrieves the schema table:

SqlCommand cmd = new SqlCommand("Select @@VERSION", conn);
IDataReader rdr = cmd.ExecuteReader();
DataTable schema = rdr.GetSchemaTable();

The first line creates the SqlCommand object with the sample query. The next line uses the ExecuteReader() method of the command to create a SqlDataReader object. Then the GetSchemaTable() method of the reader returns a DataTable with the metadata for the data in the query result. Each row of the DataTable contains the metadata for one column of the query result.

For the @@VERSION. function the GetSchemaTable() function returned one row. I used this foreach loop to list the columns in the resulting DataTable and the values

foreach (DataColumn dc in schema.Columns)
{
    Console.WriteLine("{0} = {1}", dc.ColumnName, schema.Rows[0][dc.ColumnName]);
}

Here is the resulting output:

ColumnName = 
ColumnOrdinal = 0
ColumnSize = 300
NumericPrecision = 255
NumericScale = 255
IsUnique = False
IsKey = 
BaseServerName = 
BaseCatalogName = 
BaseColumnName = 
BaseSchemaName = 
BaseTableName = 
DataType = System.String
AllowDBNull = True
ProviderType = 12
IsAliased = 
IsExpression = 
IsIdentity = False
IsAutoIncrement = False
IsRowVersion = False
IsHidden = 
IsLong = False
IsReadOnly = True
ProviderSpecificDataType = System.Data.SqlTypes.SqlString
DataTypeName = nvarchar
XmlSchemaCollectionDatabase = 
XmlSchemaCollectionOwningSchema = 
XmlSchemaCollectionName = 
UdtAssemblyQualifiedName = 
NonVersionedProviderType = 12
IsColumnSet = False 

The columns in the schema definition are described in the BOL article about the GetSchemaTable() method.

As another example, I implemented this query in my test database, joining all the tables together.

Select o.order_id, cust.first_name, cust.last_name, order_date, item_name, 
    quantity, unit_price, unit_price * quantity As total_price 
From Orders o
    Join Customers cust On o.customer_id = cust.customer_id
    Join OrderDetail d On o.order_id = d.order_id
    Join [Catalog] cat on d.item_id = cat.item_id  */

This example creates a SqlCommand object and a SqlDataReader and then retrieves the column metadata:

SqlCommand cmd = new SqlCommand("Select o.order_id, cust.first_name, cust.last_name, order_date, item_name, quantity, "
    + " unit_price, unit_price * quantity As total_price From Orders o Join Customers cust On o.customer_id = cust.customer_id "
    + " Join OrderDetail d On o.order_id = d.order_id Join [Catalog] cat on d.item_id = cat.item_id", conn);
SqlDataReader rdr = cmd.ExecuteReader();
DataTable joinSchema = rdr.GetSchemaTable();

This is the resulting list of columns returned by my sample query:

Conclusion

This has been an exploration of the different ways the schema data from an SQL Server database can be accessed from a .Net program using the GetSchema() method of the SqlConnection object and the GetSchemaTable() method of the SqlDataReader object.

In the next installment of the series I will put this together with the techniques I explored in another article in this series, SQL Server From .Net: Building a DataSet with Code to show how you can use the schema information to build a DataSet object in code that implements the tables and relationships of any SQL Server database you can connect to.