Browse, search, and get SQL Server metadata
The metadata that Microsoft BizTalk Adapter for SQL Server surfaces from the SQL Server database describes the message structure for communicating with the SQL Server database using the adapter. The SQL adapter supports two interfaces for retrieving metadata.
MetadataExchange provided by Windows Communication Foundation (WCF). WCF provides a metadata-exchange endpoint for all WCF bindings, which enables clients to get metadata from the SQL Server database.
IMetadataRetrievalContract provided by the Microsoft Windows Communication Foundation (WCF) Line of Business (LOB) Adapter SDK, which supports the metadata browsing and searching capabilities of the adapter.
The SQL adapter surfaces the SQL Server database artifacts and respective operations that the adapter clients can invoke. The adapter also surfaces operations (such as ExecuteNonQuery, ExecuteReader, and ExecuteScalar) that can be used to perform specific operations on the SQL Server database. These operations are discussed later in this topic.
Note
The SQL adapter surfaces artifacts in all the schemas in the SQL Server database that the currently connected user has access to. This implies that apart from the default schema (dbo), the adapter clients can also perform operations on artifacts in other schemas in the SQL Server database provided that the user credentials used to connect using the SQL adapter has access to those schemas in the SQL Server database. For information about a schema in SQL Server database, see https://go.microsoft.com/fwlink/?LinkId=130148.
You can use the adapter clients to browse, search, and retrieve metadata by:
Creating a BizTalk project in Visual Studio
Using the WCF service model
Using the WCF channel model
When using a BizTalk project, you must use the Consume Adapter Service BizTalk Project Add-in or Add Adapter Metadata Wizard to generate metadata for the operations that you want to perform on the SQL Server database. When using the WCF service model, you must use the Add Adapter Service Reference Visual Studio Plug-in to generate the proxy classes for performing operations on the SQL Server database. For more information about browsing, searching, and retrieving metadata using Consume Adapter Service Add-in, Add Adapter Metadata Wizard or Add Adapter Service Reference Plug-in, see Get metadata for SQL Server operations in Visual Studio using the SQL adapter.
Browsing Metadata
The SQL adapter enables adapter clients to browse database tables, views, stored procedures, and functions that are available in the SQL Server database. As part of the metadata browse operation, the adapter also surfaces the operations that can be performed on the SQL Server database, including some custom operations supported by the adapters. These operations are available from Consume Adapter Service Add-in, Add Adapter Metadata Wizard or Add Adapter Service Reference Plug-in. The SQL adapter surfaces the following operations:
The operations on tables, views, procedures, scalar functions, and table valued functions. For example, the SQL adapter may surface Insert, Update, Select, and Delete operations for the EMPLOYEE table.
The Set<column name> operation for tables and views that enables adapter clients to write large data values in a streaming fashion. The Set operation is only returned for those tables and views that contain columns with any of the following data types: Varchar(Max), Nvarchar(Max) or Varbinary(Max). For more information, see Operations on tables and views that contain large data types using the SQL adapter.
The ExecuteNonQuery, ExecuteReader, and ExecuteScalar operations that enable adapter clients to execute arbitrary SQL statements in SQL Server. For more information about these operations, see Support for ExecuteNonQuery, ExecuteReader, and ExecuteScalar Operations.
The Polling and Notification operations to receive inbound messages from SQL Server. For information about the Polling operation, see Support for Inbound Calls Using Polling; for information about the Notification operation, see Considerations for Receiving Query Notifications Using the SQL adapter.
For more information about how the metadata is categorized, see Metadata Node IDs.
Searching Metadata
With the SQL adapter, it is possible to perform a search query on the SQL Server database by using the SQL Server search expressions that are compatible with the LIKE operator. For example, adapter clients can use a search expression such as “EMP%” to obtain tables starting with EMP. The adapter converts this to the following SQL query:
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE 'EMP%'
The following table lists the special characters that can be used for search and their interpretation by the SQL adapter.
| Special character | Interpretation |
|---|---|
| _ (underscore) | Matches exactly one character. For example, “A_” matches “AB”, “AC”, “AD”. |
| % (percentage) | Matches zero or more characters. For example, “A%” matches “A”, “AB”, “ABC”. |
| [ ] | - Escapes the special meaning of _ and %. - Specifies a range or set of characters to be present. For example: - %[%]% matches all names that include a % symbol. - [a-f] matches all names that have characters between and including ‘a’ and ‘f’. - [abc] matches all names that have characters ‘a’, ‘b’, and ‘c’. |
| [^] | Specifies a range or set of characters not to be present. For example: - [^a-f] matches all names that do not have characters between and including ‘a’ and ‘f’. - [^abc] matches all names that do not have characters ‘a’, ‘b’, and ‘c’. |
Important
The metadata search scope is restricted to the level immediately under the node at which the search operation is performed. For example, to search for a scalar function, you must be searching under /Scalar Function/[Schema]. Multi-level search is not supported.
Retrieving Metadata
When retrieving metadata, the SQL adapter can extract metadata under a schema, including all or a subset of database objects with the respective object and operation parameters. The adapter presents the entities from the SQL Server database as element names in XML. Because underscores are the only permissible special characters that can be included, all other special characters in the element names are encoded using underscores. For example, emp$name is encoded as emp_x0024_name.
See Also
Overview of BizTalk Adapter for SQL Server Understand BizTalk Adapter for SQL Server Get metadata for SQL Server operations in Visual Studio using the SQL adapter