Architecture in ADO.NET 2.0

Release Date: 09/19/2004

| Update Date: 09/19/2004

Bob BeautyMindevelopmentor

Applicable to: Microsoft ADO.NET 2.0MICROSOFT Visual Studio 2005C # Programming Language

Summary: Understand the enhancement support for accessing metadata from your data source in ADO.NET.

Download the related schemaptample.exe sample code.

This page

In-depth understanding of the new public metadata API who needs metadata? What kind of metadata can I get? RestrictionsDataSourceInformation customized and extended metadata user-defined summary: the final part of metadata support

In-depth understanding of new public metadata API

In my previous article, I pointed out that the Visual Studio 2005 Server Explorer now uses a dialog box containing the .NET data provider (not the OLE DB provider) list to prompt connection information. When you determine a connection string and add a data connection, each data connection also displays a tree structure about the information of the database object (such as the table, view, and stored procedure) by connection. But where is this information from? Is it that Visual Studio hardcodes for some data providers to generate this information, and if I write my own data provider or buy one from a third party, leave me an empty node? No, not this in Visual Studio 2005. All these useful information will be provided due to the new architecture API in ADO.NET 2.0. I don't know if this is a method of Visual Studio to do it, but here is the code for obtaining a list of tables in a database using the new API.

// uses a ADO.NET 2.0 named connection string in config file // uses ADO.NET 2.0 ProviderFactory and base classes // see previous article public static void GetListOfTables (string connectstring_name) {ConnectionStringSettings s = ConfigurationSettings.ConnectionStrings [connectstring_name]; DbProviderFactory f = DbProviderFactories.GetFactory (s.ProviderName); using (DbConnection conn = f.CreateConnection ()) {conn.ConnectionString = s.ConnectionString; conn.Open (); DataTable t = conn.GetSchema ( "Tables"); t .Writexml ("Tables.xml");}}

Back to top

Who is the metadata?

Metadata is part of each data to access the API. Although the main user of metadata is a tool like Visual Studio or a code generator like Deklarit, they are not the only user. Application package designers may allow end users to define an application by adding a new table or none to existing tables. When the end user changes the database architecture, a general query and modification tool can use metadata in maintenance, backup, and other application functions to include the user's new table, just like they are the built-in table included with the application. Programmers can use metadata to write their own custom class from System.Data.common.dbcommandBuilder and create insert, update, and delete commands for using DataSet. The multi-database application (that is, the builder designed to run on a user-selected database) can use metadata as much as possible to maintain the public code library, and optimize the data access code when needed. By the general metadata API is a good API that allows each user to use a database-specific API. This way, tool writers can maintain a manageable code library. Such an API must also be very flexible because there are four obstacles to the publication of metadata in writing a general API.

1. The metadata collection and information is different in the database. For example, SQL Server users may want to disclose a collection of link servers, but Oracle users may interested in information about Oracle sequences. 2. Not only in different database products, but even in different versions of the same database, the basic system table for storing public database metadata is different. For example, SQL Server 2005 uses a new table (eg, sys.tables) on a "SYS" schema (for example, sys.tables), while SQL Server previously versions use metadata tables (such as sysObjects £? To store the same data. 3. Different programs may have different metadata views. In one example, many programmers complain that the list of tables in the Oracle database is too long because most of the metadata API mixed with the user table with the user table. They Want to make a short list that consists only by the table defined. 4. Do not support metadata at all, how much metadata is provided, should completely depend on the writer of the provider.

Most database APIs provide a standard metadata set, which is supported by all providers, and allows provider writers to add new metadata tables. This is consistent with the method used using the ANSI SQL standard. A part of the standard for solving this problem is part 11 of Schema Schemata (Information_Schema, and Definition_SChema). ANSI SQL Information_schema defines a metadata view set that is supported by a compatible database. But even if this specification, there is also a way to solve these problems. It declares:

"Implementors can freely add additional tables to INFORMATION_SCHEMA or add additional columns to predefined information_schema tables."

The OLE DB is used as an example of a data that is consistent with the ANSI SQL standard concept, which defines a series of metadata named "Arrange Row Collection". It starts from a predefined set of information_schema and adds OLE DB-specific columns to each row. ADO.NET 2.0 provides an even more powerful and more flexible mechanism to disclose metadata.

Back to top

What kind of metadata can I get?

ADO.NET 2.0 Allows the provider writers to disclose five different types of metadata. These major metadata - "metadata collection" or "category" - enumerated in the System.Data.Common.dbmetadtacollectionNames class.

Metadatacollections - a list of available metadata collections. ? Restrictions - For each metadata collection, there is a batch of qualifiers that can be used to limit the range of schema information being requested. • DataSourceInformation - Information about the database instance referenced by the data provider. DataTypes - a set of information about each data type supported by the database. RESERVEDWORDS - Suitable for reserving words for this database query language. Usually "query language" is equivalent to a dialect of SQL. Metadatacollections is the name of the Information_Schema collection, such as "Table", Column, or "Primary Key". However, if DBConnection.getschema is used, these metadata categories are also considered to be metadata. This means that in terms of code, these sets can be obtained as ordinary metadata.

// gets information about database Views Table t1 = conn.GetSchema ( "Views"); // gets information about collections exposed by this provider // this includes the five "meta-collections" described above Table t2 = conn.GetSchema (DbMetaDataCollectionNames .MetaDataCollections); // gets information about the Restrictions meta-collection Table t3 = conn.GetSchema (DbMetaDataCollectionNames.Restrictions); // No argument overload is same as asking for MetaDataCollections Table t4 = conn.GetSchema ();

Two of the five metadata collections are further explained further.

Back to top

Restrictions

Restrictions can be used to limit the number of returned metadata. If you are familiar with Ole DB or ADO, the term "restriction" means the same content in those APIs. As an example, let's use the metadatacolection "column", which is a collection of column names in the table. This collection can be used to get all columns in all tables. However, this collection of columns will be restricted by the database name, owner / architecture or table. Each metadata set can have a different number possible limit, and each restriction has a default value. In the example below, this is an XML representation of a restriction of the column data:

Listing 1. Restrictions on column column (XML format)

Columns

Catalog

Table_catalog

1

Columns

Owner

Table_schema

2

Columns

TABLE

Table_name

3

Columns

Column

COLUMN_NAME

4

Restrictions is specified using an overloaded dbconnection.getschema. These restrictions are designated as an array. You can specify an array as the entire restriction collection or a subset of groups, because "RestrictionNumBers" usually develops to the maximum limit from the minimum limit. Use null values ​​to the limit value you want to omit (not database null, but .NET NULL, or Noth in Visual Basic .NET). E.g:

// restriction string array string [] res = new string [4]; // all columns, all Tables OWNED by dbo res [1] = "dbo"; datatable t1 = conn.getschema ("columns", res); / / CLEAR Collection for (INT i = 0; i <4; i ) RES [i] = null; // all columns, all tables named "authors", any owner / schema res [2] = "authors"; DataTable T2 = Conn.getschema ("Column", RES); // Clear Collection for (INT i = 0; I <4; I ) RES [i] = null; // columns named au_lname // all tables named "authors", Any Owner / Schema Res [2] = "authors"; res [3] = "au_lname"; DataTable T3 = conn.getschema ("Column", RES); // Clear Collection for (INT i = 0; i <4 i ) RES [i] = null; // columns named au_lname // any Tables, Any Owner / Schema Res [3] = "name"; DataTable T4 = Conn.getschema ("Column, RES); you don't need to specify The entire restriction array. In the above case, here you just want to see the columns in the table owned by "DBO", you can specify an array with two rather than all four members. Also note that a empty string is specified as a limit to specify a NULL (Nothing in Visual Basic .NET) value is different. You don't have to remember these restrictions, you can always query them, just like any other collection. "Restrictions" collection itself is not allowed, but because the information is extracted into the DataTable, you can use a DataView to provide similar features, as shown below.

DataTable tv = conn.GetSchema (DbMetaDataCollectionNames.Restrictions); DataView v = tv.DefaultView; // show restrictions on the "Columns" collection, sorted by number v.RowFilter = "CollectionName = 'Columns'"; v.Sort = " Restrictionnumber "; for (int i = 0; i

Back to top

The DataSourceInformationDataSourceInformation set provides information about the current database (data source) instance for the query generator. Although this collection can include any content required to provide programs, in the Microsoft Provider (SQLClient, OracleClient, OLEDB, ODBC), this collection contains similar information. Here is the information you receive by default.

Table 1. DataSourceInformation value format / meaning CompositeIdentifierSeparatorPattern multiple names (click on Pubs.dbo.authors) DataSourceProductName database name DataSourceProductVersion database name DataSourceProductVersion database name. Note that this is the version of the database instance that is currently accessed through DBConnection. DataSourceProductVersionNormalized? GroupByBehavior enumeration, System.Data.Common.GroupByBehavior IdentifierPattern regular expression string IdentifierCase enumeration, System.Data.Common.IdentifierCase Boolean value OrderByColumnsInSelect, you should ORDER BY columns ParameterMarkerFormat instructions in a SELECT statement by default Parameter tags Start with a special character (such as @ @) parameterMarkerPattern for creating parameters, the maximum length of parameter is used to create a regular expression string for creating parameters QuoteDitIfierPattern quoteDIndIndIfierPattern regular expression character string QuotedIdentifierCase enumeration, System.Data.Common.IdentifierCase StatementSeparatorPattern regular expression string StringLiteralPattern regular expression string SupportedJoinOperators enumeration, System.Data.Common.SupportedJoinOperators

There are too many information for specific database dialects to generate SQL, don't you think so? There is also a message I want to say, that is, the provider uses a named parameter or location parameter in the parameterized query. In the previous article on writing the code independent of the provider, I will discuss the name and location parameters as two ways to write parameterized commands.

Back to top

Custom and extend the metadata

Since we have seen the basic metadata provided and you can find our way around dbconnection.getschema (), let's discuss how to provide program writers to customize the metadata in a simple declaration format, and how programmers can hook to That format. This discussion is related to the metadata complexity of the article: How to provide a different view of the DB version of the database and how to handle different users may require the same metadata.

First, let us point out that metadata support is completely optional. The provider does not have to support DBConnection.getschema, which triggers NotSupportedException. In addition, if the provider writer selects supports DBConnection.getschema, only the MetadACollections category is required. The provider can choose not to provide any or all of the other 4 categories of information.

Second, each provider can disclose different information of the same metadata collection. For example, the structure of the TABLES collection is completely dependent on the provider. For example, the SQLClient provider discloses 4 information items in the Tables collection: table_catalog, table_schema, table_name, and table_type. The OracleClient provider only discloses 3 information items (Owner, Table_name, and Type) because the Oracle database does not contain multiple directories. The number of limitations and restrictions for each provider can be different. Taking the situation in the table as an example, the SqlClient provider supports four restrictions, while the OracleClient provider only supports 2. Restrictions do not have to occur in any specified order. Therefore, it is different from the OLE DB and the ODBC API, there is no metadata structure, a large number of metadata or metadata sequences that are delegated to manage. The provider can freely disclose any related metadata. However, if a specific application (such as Visual Studio) requires all .NET data providers in an application, it is possible to obtain this behavior through the standard behavior of the provider. We will further discuss this problem in the following user custom section. The provider writer can directly encode the metadata logic to their provider, each providing a different internal algorithm for obtaining similar metadata. For example, when implementing the ISChemarowset method of OLE DB, this is the method of completing it in the past. However, in ADO.NET 2.0, there are some base classes in the system.data.providerbase namespace to provide program writers available. 4 available Microsoft Provo uses these base classes, so they all implement architectures in a similar approach. I will use this implementation, I hope most programmers who write providers like DataDirect technology, while other provider writers also use it.

The base class of the public metadata is DBMetAdataFactory. A provider that implements a subclass of it uses an XML file to define its metadata extraction behavior. These files are resource embedded in System.Data.dll and System.Data.OracleClient.dll, and you can view the original XML file by running Ildasm.exe from the command line.

> ildasm.exe system.data.dll /out:dummy.il

Check out the XML resource file generated from ILDASM, such as the other layer of the onion. This file enumerates a supported collection and information contained in each metadata collection (through the architecture), and it looks like the output of DataSet.WriteXML methods using Dataset.writeXML. The most interesting part is the minimum in all metadata sets except the PopulationMechanism / Population in DataSourceInformation and Metadtacollections elements.

Use the MinimumVersion / Maximumversion to allow the provider writer to specify which metadata queries to perform for different versions of the database. By using a single Metadatacollection's multiple elements, you can make GetSchema different from different versions of database. For an obvious example, you can use each version of SQL Server 2005 with previous versions of SQL Server. Here is an example of using the MinimumVersion from SQL Server metadata resources (system.data.sqlclient.sqlmetata): Listing 2. Data type XML metadata item in the data type collection

xml

25

2147483647

System.string

False

False

False

False

True

True

True

False

09.00.000.0

False

This defines information about the SQL Server data type XML. MINIMumVersion indicates that this data type is only available when using SQL Server 2005. If you ask SQLConnection.getschema to provide a database-supported data type list, only SQL Server 2005 version of the database (SQL Server 2005 is version 9, current Beta 2 is 09.00.852.2) This will report to the XML data type.

PopulationMechanism and populationString are places where the work is started for a collection that is usually published by INFORMATION_SCHEMA (such as table, view, or stored procedure). In this implementation, three populationMechanisms: DataTable, SqlCommand, and PrepareCollection are used in this implementation. DataTable is used to fill metadata collection. Using DATATABLE means that information for filling a collection is in the XML resource file itself. In each case, PopulationString is the name of the generated DataTable when the XML resource file is loaded into a .NET Dataset. SQLCommand means providing a provider to use a DBCommand instance to issue a command to the database. If you look at a PopulationString that is generated by SqlCommand:

Listing 3. Database (directory) item in SQL Server - Metadatacollection

Databases

1

1

SQLCommand

Select Name As Database_name, DBID, CRDATE AS CREATE_DATE ASDATE} where name = {0}

When it is limited to use in dbconnection.getschema, it is easy to infer a string replacement will be applied to the "basis query". If there is no restriction being specified, then the predicate will be stripped out of the query.

When the value of PopulationMechanism is prepareCommand, the provider writer can use the custom mechanism. There is a prepareCommand method for DBMetadataFactory. If it is overwritten by provider, you can be encoded to use any custom semantics selected by the provider. This mechanism is used to generate a DataTypes metadata collection in SqlClient. PrepareCommand's SQLMetAdataFactory subclass implementation first reads the built-in data type supported by SQL Server from the DataTable, just like other metadata collection; then, if the database is SQL Server 2005, use custom logic to add user-defined types. Go to the collection. (Note: SQL Server 2005 can disclose the .NET class as a user-defined type. For more information, see Chapter 5 of the A First Look at SQL Server 2005 for Developers.) Back to top

Custom

In addition to providing a program custom mechanism, there is also a hook that allows programmers to customize the schema information on each application! DBConnectionFactory CreateMetadataFactory will refer to the application profile before loading embedded resources. Each provider can use any of its choice to implement CreateMetAdataFactory to get an XML stream for its DBMetAdataFactory, but the four Microsoft Provo follows a common mode. Each Microsoft Provider will look up a application configuration setting (such as system.data.sqlclient) named by the provider itself. In this setup element you can add or delete the name value pair. DBMetAdataFactory looks up a name "metadataxml". The value corresponding to the special name is the name of a file. This is a simple file name - this file must exist in the Config subdirectories of the .NET installation location. This is the directory where Machine.config and security configuration settings are located. This file must contain the entire architecture configuration information set, not just those changes.

For several reasons, you can use this mechanism for providing this mechanism. For example, you can change the architectural query in the OracleClient provider to use the "User" directory view instead of the "all" directory view. Because the "User" view does not contain information about the internal database table, for example, the table list will be short and easier to use. Another example may include encoding an output metadata XML file for all .NET data provider, which provides you with a consistent standard metadata set, which may just match the SQL-99 Information_Schema view. This may just fit your app.

A more specific example is if I want to disclose information about the metadata collection of SQL Server Service Broker in SQL Server 2005. These collections can include Queue, Service, Contract, and message types. I will start from embedded XML resource files and use information on my new collection. If the name of this file is SQLBROKERAWAR.XML, I will install this file, and my application configuration file will be as follows:

This is all the steps. With this setting, I can write code - in which Service Broker metadata is an integral part of the built-in metadata available for the client. For all queues, the code may be as follows:

using (SqlConnection conn = new SqlConnection (connstring)) {conn.Open (); // this includes Service Broker metadata collections Table t = conn.GetSchema (DbMetaDataCollectionNames.MetaDataCollections); // get all the queues in my database Table queues = CONN.GETSCHEMA ("Queues");} is really cool! This article contains an example of code to add Service Broker metadata. Although this is a very powerful function, it is still possible to be abused. Keep in mind that you need to distribute metadata XML files to each application that uses it, and persists that the system administrator is installed in the Config directory. And you need to maintain it with each new version of the published provider. Because one of the reasons why the general metadata API can have a consistent metadata across the database and the application, this feature should not be used for no reason. It is also necessary to pay attention to this point you cannot provide a custom implementation of PrepareCommand.

With regard to custom, you may have already guessed that customization and resources are different for use in OLE DB and ODBC's bridge providers. When you use these providers, the default ODBC or OLEDB XML resource file is used, then you can not only customize the primary ODBC or OLEDB architectural behavior, but also customize your behavior based on each provider. If you want to specify your own provider or driver, the name attribute used in the add / delete set sub-element cannot be metadataXML, but should be [ProvidersHortName]: metadataXML. If you want your file to become the default file of an OLEDB or ODBC data provider, you can even specify the name of DefaultMetAdataXML.

Back to top

Summary: the final part of metadata support

Before the end, I just want to talk about other two metadata expansions from DBCONNECTION.GETSCHEMA. DBCommandBuilder contains two properties, QuoteIdentifier, and UNQUOTEIDENTIDENTIFIER, which allow you to customize the identifier in commands generated by CommandBuilder. For example, depending on your session settings, you can use dual quotes (") or parentheses ('[' and ']') in SQL Server (" ['and'] ') to reference the identifier. Finally, in the system.data.sql namespace The SQLMetadata class is used to disclose metadata in SQL Server DataReader and allow you to set this metadata in Parameters used by SQLCommand. Although not anywhere is very close, this metadata is conceptual with DataTypes metadata The metadata disclosed in the collection is similar to the SQLMetadata to the SQLClient data provider and the SQL Server 2005 included In-Database SQLServer data provider. Sqlmetadata increases the currently disclosed row collection metadata exposed by the SqlDataReader.getschematable () method. .

At this point you are likely to agree that the ADO.NET 2.0 yuan data infrastructure is the most powerful, flexible and customizable structure you have ever seen. It develops ADO.NET to become a database API of a full-scale object. Tool suppliers, provider writers and Visual Studio data tool users will be happy to dance on the street - we see it.

Bob Beautiful lecturer, course authors and database courses. As a architect, programmer and administrator with data-centric distributed systems, he has more than 25 years of experience. He has always written an article from ADO.NET, OLE DB and SQL Server for magazines such as Microsoft Systems Journal and SQL Server Magazine, and is the author of the A First Look at SQL Server 2005 for Developers and Essential ADO.NET. Go to the original English page

Back to top