Retrieving Metadata

In This Topic

This topic is applicable only for full .NET Framework.

Usually, you have to dig through SQL references to find out how to get metadata information for specific DBMS. Few servers support the same SQL commands. Sometimes, the syntax differs slightly; sometimes, a server does not support certain statements. Now, you can forget those problems because dotConnect for SQL Server retrieves your metadata.

With dotConnect for SQL Server, you can use a handy feature - the GetSchema method. It lets you read server schema information without writing queries and parsing the output. All information you want to obtain is brought to you by a single function in an easy-to-process format. You can get information on databases, tables, columns, indexes, users, stored procedures and functions, user-defined procedures, and reserved words. The method is introduced in System.Data.Common.DbConnection. .

To illustrate capabilities of the feature, we have prepared MetaData sample. Please refer to the sample to learn the functionality quickly.

This article consists of the following sections:

How To Use
GetSchema Method Reference
Samples

How To Use

The GetSchema method is available in three overloads, each serving its purpose. All overloads return the System.Data.DataTable object containing information about server elements.

If you call the GetSchema method without parameters or with a single parameter "MetaDataCollections" (which is the same), the table object returned by the method will contain two columns. The first field of every row is a keyword allowed to be passed to the method (as the collectionName argument). The second field is the number of restriction values for this keyword (passed through the restrictionValues argument).

GetSchema, with 1 argument, returns general information about the collection queried. For example, GetSchema("Users") returns a list of users on the server.

In this overload, the first parameter is the name of a collection, and the second parameter is the array of restrictions to be applied when querying information. These restrictions specify which subset of the collection will be returned. The restrictions can include, for example, the database name (in this case, only collection elements belonging to this database will be returned) or the mask for the name of the collection elements (only the elements satisfying this mask will be returned). The quantity and description of restrictions allowed for each metadata collection are represented in the table here. Their number can also be obtained from the return of the GetSchema() method. If the second parameter is null/Nothing, it is ignored.

GetSchema Method Reference

The following table provides detailed information on metadata collections that can be retrieved using the GetSchema method, as well as restrictions that can be applied to them. When calling the GetSchema method, you can pass all or a few arguments. In the latter case, some default values are assumed, if they were not specified explicitly. The default value of database restriction is table restriction, which is the base table (.NET2) or table (.NET1). Some collections are not supported in older server versions. If you try to get metadata for the unsupported collection, you will get an exception with the message "Collection not defined."

Collection Name	Number of restrictions	Remarks
AllColumns	4	Beginning with the .NET Framework version 3.5 SP1 and SQL Server 2008, the AllColumns schema collection has been added to support sparse columns. AllColumns is not supported in earlier versions of .NET Framework and SQL Server. AllColumns has the same restrictions and resulting DataTable schema as the Columns schema collection. The only difference is that AllColumns includes ColumnSetColumns not included in the Columns schema collection.
Arguments	2	Returns a list of arguments for stored procedures and functions on the server. Restricts it to the database name and procedure name.
Columns	4	Returns a list of columns, their type, and some extra information. Restricted by a catalog name, GetSchema returns the columns of the "user" table if available in the catalog (database). The second restriction is the name of a schema that the GetSchema method should search in. The third restriction is the name of a table that the GetSchema method should search in. At last, you can set column name pattern as described in the "Tables" collection.
ColumnSetColumns	3	Beginning with the .NET Framework version 3.5 SP1 and SQL Server 2008, the ColumnSetColumns schema collection has been added to support sparse columns. ColumnSetColumns is not supported in earlier versions of .NET Framework and SQL Server. The ColumnSetColumns schema collection returns the schema for all columns in a column set.
Databases	1	The Databases collection has been added to retrieve the following database information: Name of the database, Database ID and Creation Date of the database.
DatasourceInformation	0	Returns information about the data source.
DataTypes	0	Returns information about data types supported by the data source.
ForeignKeyColumns	2	Returns a list of columns used by foreign keys in the database. Restricts it with the database name and the table name.
ForeignKeys	4	Returns the list of columns that participate in foreign keys. The first restriction for this collection is the name of the database. The second restriction is the table name mask. The third restriction is the key pattern.
Functions	2	Returns a list of stored functions on the server. Restricts it by the database name and the function name.
IndexColumns	5	Returns a list of indexed columns in the database, their type and some extra information. Restricts it with the database name, table name, index name pattern, and the column name pattern.
Indexes	4	Returns a list of indexes and their details. The first restriction is the database name The second restriction is the table name. The last restriction is the index name pattern.
MetaDataCollections	0	Returns this list. It is the same as using the GetSchema() method without parameters.
PrimaryKeys	2	Returns a list of columns that participate in primary keys. The first restriction for this collection is the name of a database. The second restriction is the table name.
Procedures	4	Returns a list of stored procedures on the server. Restricts it by the database name and the procedure name.
ProcedureParameters	4	Returns information on all procedure parameters in the database.
ReservedWords	0	Lists all reserved words used in the server.
Restrictions	0	Returns a list of possible restrictions and their default values for the metadata collections.
Tables	4	GetSchema("Tables") returns a list of tables in the current database. The first restriction for this collection is the database name. If specified, the method returns all tables within the database. The second restriction is the table name mask. You can use wildcards '%' (any number of characters) and '_' (one character) to retrieve the names of tables that match the mask.
UniqueKeys	2	Returns a list of columns that participate in unique keys. The first restriction for this collection is the database name. The second restriction is the table name.
UserDefinedTypes	2	Returns information about the user defined types.
UserPrivileges	1	Lists all users and their privileges on the server. When restricted by a username, returns information about a specific user.
Users	1	Lists all users on the server. When restricted by username, it returns information about a specific user.
ViewColumns	4	Returns a list of columns used by views in the database. Restricts it with the database name, the table name and the column name.
Views	3	GetSchema("Views") returns a list of views in the current database. The first restriction for this collection is the name of the database. The second restriction is the view name mask.

Samples

The following code fragment is an elegant way to detect the existence of a table in the Test database.

The following sample shows how to retrieve column information from a table and render it to a console.

static void GetTableInfo(SqlConnection myDbConnection, string tableName)
{
  myDbConnection.Open();
  DataTable myDataTable = myDbConnection.GetSchema("Columns", +
  new string[] { "Test", tableName });
  for (int i = 0; i < myDataTable.Columns.Count; i++)
  {
    Console.Write(myDataTable.Columns[i].Caption + "\t");
  }
  Console.WriteLine();
  foreach (DataRow myRow in myDataTable.Rows)
  {
    foreach (DataColumn myCol in myDataTable.Columns)
    {
      Console.Write(myRow[myCol] + "\t");
    }
    Console.WriteLine();
  }
  myDbConnection.Close();
}

Visual Basic


Public Sub GetTableInfo(ByVal myDbConnection As SqlConnection, ByVal tableName As String)
  myDbConnection.Open()
  Dim restrictions() As String = {"Test", tableName}
  Dim myDataTable As DataTable = myDbConnection.GetSchema("Columns", restrictions)
  Dim i As Int32
  For i = 0 To myDataTable.Columns.Count - 1
    Console.Write(myDataTable.Columns(i).Caption & Chr(9))
  Next
  Console.WriteLine()
  Dim myRow As DataRow
  Dim myCol As DataColumn
  For Each myRow In myDataTable.Rows
    For Each myCol In myDataTable.Columns
      Console.Write(myRow(myCol) & Chr(9))
    Next
    Console.WriteLine()
  Next
  myDbConnection.Close()
End Sub

The following sample demonstrates how to generate the SQL CREATE TABLE statement basing on metadata retrieved with the GetSchema method. The generated script will work with all database management systems that support the ANSI standard. Only the column name and the type are included in the script.

static void GetCreateTable(SqlConnection myDbConnection, string tableName)
{
  //Open the connection
  myDbConnection.Open();
  //Fill DataTable with columns information
  DataTable myDataTable = myDbConnection.GetSchema("Columns", +
  new string[] { "Test", tableName });
  string queryText = "CREATE TABLE " + tableName + " (\n";
  string fieldLine;
  DataRow myRow;
  //For every row in the table
  for (int i = 0; i < myDataTable.Rows.Count; i++)
  {
    //Get column name and type
    myRow = myDataTable.Rows[i];
    fieldLine = myRow[0] + " " + myRow[1];
    //Add comma or closing bracket
    if (i < myDataTable.Rows.Count - 1)
    {
      fieldLine = fieldLine + ",\n";
    }
    else
    {
      fieldLine = fieldLine + ")";
    }
    //Add new column to script
    queryText = queryText + fieldLine;
  }
  Console.WriteLine(queryText);
  //Close the connection
  myDbConnection.Close();
}

Visual Basic


Public Sub GetCreateTable(ByVal myDbConnection As SqlConnection, ByVal tableName As String)
  'Open the connection
  myDbConnection.Open()
  Dim restrictions() As String = {"Test", tableName}
  'Fill DataTable with columns information
  Dim myDataTable As DataTable = myDbConnection.GetSchema("Columns", restrictions)
  Dim queryText As String = "CREATE TABLE " + tableName + " (" + System.Environment.NewLine
  Dim fieldLine As String
  Dim myRow As DataRow
  Dim i As Int32
  'For every row in the table
  For i = 0 To myDataTable.Rows.Count - 1
    'Get column name and type
    myRow = myDataTable.Rows(i)
    fieldLine = myRow(0) & " " & myRow(1)
    'Add comma or closing bracket
    If (i < myDataTable.Rows.Count - 1) Then
      fieldLine = fieldLine + "," + System.Environment.NewLine
    Else
      fieldLine = fieldLine + ")"
    End If
    'Add new column to script
    queryText = queryText + fieldLine
  Next
  Console.WriteLine(queryText)
  'Close the connection
  myDbConnection.Close()
End Sub

Also, you can get metadata for the query result set using the GetSchemaTable method of SqlDataReader and the ShemaTable property of SqlDataTable classes.

How To Use

GetSchema Method Reference

Samples

See Also