GetSchema Override (ADO.NET)
Standard schema collections, such as tables, columns, stored procedures, and views, mean different things depending on the back-end data source. For example, the way that Microsoft Office Access defines what a stored procedure is differs from the way SQL Server or Oracle does. SQL Server has the concept of a databases collection, but Access and Oracle do not. Even the concept of a table does not apply to some ADO.NET data providers and data sources. Some schema collections are incomplete, such as SQL Server, which does not contain a collection for primary keys.
Starting with the .NET Framework version 2.0, developers can override an existing schema implementation by providing their own collections. You can also create a Data Designer Extensibility (DDEX) provider that enables you to expose third-party data source objects in Visual Studio by specifying your own schema format and content. DDEX providers let you expose objects derived from almost any data source.
Note
In addition to making the integration easy, DDEX also provides a common model for all data providers, which simplifies the implementation and provides a common user experience. For more information, see Introduction to Visual Studio Data Designer Extensibility (DDEX) and the Data Designer Extensibility (DDEX) SDK.
Overriding Schema Information
It is not necessary to create a DDEX provider in order to override a particular schema implementation. You can provide an XML file that describes both the collections and their population mechanism and place it in the CONFIG directory for the version of the .NET Framework you are using. You then add a setting to your application's configuration file. This will override the default schema collection when GetSchema is called.
If the CONFIG folder does not exist for the latest version of the .NET Framework you are using, place the XML file in the CONFIG folder of the highest-numbered version of the .NET Framework that does. The CONFIG folder contains configuration files for the .NET Framework, such as machine.config and security.config. .NET Framework configuration files are described in the topics Configuration File Schema for the .NET Framework and Configuration Files.
For 32-bit operating systems, add the XML file to the CONFIG folder for the highest version of the .NET Framework that exists:
%windir%\Microsoft.NET\Framework\<.Net Version>\CONFIG
For 64 bit operating systems, add the XML file in both of the following locations:
%windir%\Microsoft.NET\Framework\<.Net Version>\CONFIG
%windir%\Microsoft.NET\Framework64\<.Net Version>\CONFIG
.gif "Security note") Security Note: |
|---|
Only members of the Windows Power Users and Administrators groups can override these locations on NTFS-enabled drives. |
Add the following content to your application's configuration file. The name attribute must be specified as "MetaDataXml" and the value attribute is the name of the XML file, which can be anything you choose.
<system.data.sqlclient>
<settings>
<add name="MetaDataXml" value="SqlClientSchema.xml" />
</settings>
</system.data.sqlclient>
The XML File
The XML file must describe all the collections and their population mechanism. You must provide the entire XML file; you cannot override only certain collections. The following XML file fragment is a partial listing of SqlClientSchema.xml that shows the syntax used to add a PrimaryKeys collection. In the case of SQL Server, the contents of the XML file will vary, depending on whether you are using SQL Server 2005 or SQL Server 2008, which introduced new collections. The SqlClientSchema.xml files can be found in the GetSchema Override sample on the ADO.NET Documentation Samples CodeGallery page.
<MetaDataCollections>
<CollectionName>PrimaryKeys</CollectionName>
<NumberOfRestrictions>4</NumberOfRestrictions>
<NumberOfIdentifierParts>3</NumberOfIdentifierParts>
<PopulationMechanism>SQLCommand</PopulationMechanism>
<PopulationString>select CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA,
CONSTRAINT_NAME, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
CONSTRAINT_TYPE, IS_DEFERRABLE, INITIALLY_DEFERRED from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS where (CONSTRAINT_CATALOG =
@Catalog or (@Catalog is null)) and (CONSTRAINT_SCHEMA = @Owner or
(@Owner is null)) and (TABLE_NAME = @Table or (@Table is null))
and (CONSTRAINT_NAME = @Name or (@Name is null)) and
CONSTRAINT_TYPE = 'PRIMARY KEY' order by CONSTRAINT_CATALOG,
CONSTRAINT_SCHEMA, CONSTRAINT_NAME</PopulationString>
</MetaDataCollections>
<Restrictions>
<CollectionName>PrimaryKeys</CollectionName>
<RestrictionName>Catalog</RestrictionName>
<ParameterName>@Catalog</ParameterName>
<RestrictionDefault>CONSTRAINT_CATALOG</RestrictionDefault>
<RestrictionNumber>1</RestrictionNumber>
</Restrictions>
<Restrictions>
<CollectionName>PrimaryKeys</CollectionName>
<RestrictionName>Owner</RestrictionName>
<ParameterName>@Owner</ParameterName>
<RestrictionDefault>CONSTRAINT_SCHEMA</RestrictionDefault>
<RestrictionNumber>2</RestrictionNumber>
</Restrictions>
<Restrictions>
<CollectionName>PrimaryKeys</CollectionName>
<RestrictionName>Table</RestrictionName>
<ParameterName>@Table</ParameterName>
<RestrictionDefault>TABLE_NAME</RestrictionDefault>
<RestrictionNumber>3</RestrictionNumber>
</Restrictions>
<Restrictions>
<CollectionName>PrimaryKeys</CollectionName>
<RestrictionName>Name</RestrictionName>
<ParameterName>@Name</ParameterName>
<RestrictionDefault>CONSTRAINT_NAME</RestrictionDefault>
<RestrictionNumber>4</RestrictionNumber>
</Restrictions>
Example Code
The following code listing shows how to use the custom SqlClientSchema.xml file to override the default GetSchema operations and return a PrimaryKeys collection. Note that you must set a reference to the System.Configuration component in your project to work with configuration files. You can download the complete example from the ADO.NET Documentation Samples CodeGallery page.
The code retrieves the connection string from the application's configuration file and connects to the SQL Server database, as shown in this file fragment. You will need to modify the connection string to specify a valid SQL Server instance and valid database.
<configuration>
<connectionStrings>
<add name="SQLServerConnection"
providerName="System.Data.SqlClient"
connectionString="Persist Security Info=False;Integrated
Security=true;database=testDB;server=TestServer;" />
</connectionStrings>
<system.data.sqlclient>
<settings>
<add name="MetaDataXml" value="SqlClientSchema.xml" />
</settings>
</configuration>
Once the connection is opened, the code uses the GetSchema method of the SqlConnection to retrieve the schema collection into a DataSet. The code then iterates through the rows in the DataSet and displays the schema information in the Console window.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
class Program
{
static void Main()
{
try
{
// Example assumes a valid connection string named
// SQLServerConnection in the app.config file
using (SqlConnection connection = new SqlConnection(
ConfigurationManager.ConnectionStrings
["SQLServerConnection"].ConnectionString))
{
connection.Open();
Console.WriteLine("Enumerate MetaDataCollections");
Console.WriteLine("=============================");
DataTable table = connection.
GetSchema("MetaDataCollections");
foreach (DataRow row in table.Rows)
{
Console.WriteLine(" {0}", row[0].ToString());
}
Console.WriteLine();
Console.WriteLine("Enumerate PrimaryKeys Collection");
Console.WriteLine("================================");
table = connection.GetSchema("PrimaryKeys");
foreach (DataRow row in table.Rows)
{
Console.Write(" {0}", row[0].ToString());
for (int i = 1; i < table.Columns.Count; i+)
Console.Write(" {0}", row[i].ToString());
Console.WriteLine();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
}
Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Text
Imports System.Data.SqlClient
Imports System.Configuration
Module Module1
Sub Main()
'' Example assumes a valid connection string named
'' SQLServerConnection in the app.config file
Using connection As New SqlConnection(ConfigurationManager _
.ConnectionStrings("SQLServerConnection").ConnectionString)
Try
connection.Open()
Console.WriteLine("Enumerate MetaDataCollections")
Console.WriteLine("=============================")
Dim table As DataTable = _
connection.GetSchema("MetadataCollections")
Dim row As DataRow
For Each row In table.Rows
Console.WriteLine(" {0}", row(0).ToString())
Next
Console.WriteLine()
Console.WriteLine("Enumerate PrimaryKeys Collection")
Console.WriteLine("================================")
table = connection.GetSchema("PrimaryKeys")
Dim i As Integer
For Each row In table.Rows
Console.Write(" {0}", row(0).ToString())
For i = 1 To table.Columns.Count - 1
Console.Write(" {0}", row(i).ToString())
Next i
Console.WriteLine()
Next
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
Console.ReadLine()
End Using
End Sub
End Module
If the XML file that is specified in the configuration file cannot be found, an exception will be thrown. If an XML file is not specified, the default schema collection will be returned.
The query will always return schema information from the current database. The GetSchema call will not change the database, even if you provide the a value for CONSTRAINT_CATALOG as a restriction value. This is because the INFORMATION_SCHEMA.TABLE_CONSTRAINTS system table is scoped per database, not per server. If you want to retrieve schema information from a different database, you must explicitly change the database by running the USE <databasename> Transact-SQL command.