[Source: http://geekswithblogs.net/EltonStoneman]
The Adapter Pack 2.0 for BizTalk has been released in public beta recently, and among the WCF Line Of Business adapters it contains the WCF SQL adapter. This exposes SQL Server connections as WCF service endpoints, and lets you connect to a SQL Server source using the standard ServiceModel stack. The adapter pack will be released under the BizTalk brand, but the adapters themselves are not limited to BizTalk – the WCF SQL adapter can be used natively in .NET code.
This is a brief walkthrough covering the SQL Adapter for executing stored procedures, and I'll be covering SQL statements in a subsequent post.
Installation
Installation of the Adapter Pack is straightforward – you'll need .NET 3.5 Service Pack 1 with the latest hotfixes applied, and you'll need to install the WCF LOB Adapter SDK and then the BizTalk Adapter Pack 2.0 Evaluation (the beta version is limited to 120-day use). Note, you do not need to have BizTalk installed, and the tooling to support the WCF LOB adapters runs under Visual Studio 2008 as well as 2005. Help files for all the adapters are included, and although in pre-release form they are detailed and thorough.
Walkthrough: Consuming a Stored Procedure
The Adapter Pack adds a new context menu to code projects in Visual Studio – Add Adapter Service Reference. Run this and you're given a generic form for configuring your WCF LOB adapter. Choose sqlBinding to set up a WCF SQL connection:
Click Configure and you specify the connection configuration that will be used to build the binding. In the case of the WCF SQL adapter, you need to specify:
- Client Credential Type (Windows for integrated authentication);
- Server (database server name);
- Instance (SQL instance name, if configured);
- Initial catalog (the database to connect to).
This will build you a URI of the form: mssql://<server>/<instance>/<initialCatalog>? – with the ending question mark used to separate the core connection details from any configuration options.
Click Connect and you have the option to generate a Client binding, for making outbound requests to SQL Server (executing SQL statements, stored procedures etc.), or a Service binding which will react to inbound calls from SQL Server (for Query Notification or polling). Choose Client and the category view will be populated with a hierarchy of database objects which can be generated as WCF client proxies:
Generated Code
For stored procedure calls, the adapter can create generic proxies for weakly-typed calls returning populated DataSets, or strongly-typed calls which will generate entities representing the return from the call. In this case I've selected a Strongly-Typed Stored Procedure called GetManufacturer; add the selection and with the default options the adapter generates two items:
- App.config – containing the WCF binding configuration;
- SqlAdapterBindingClient.cs – containing the generated entity types and proxy classes.
The full binding configuration for the SQL adapter looks like this:
<system.serviceModel>
<bindings>
<sqlBinding>
<binding name="SqlAdapterBinding" closeTimeout="00:01:00" openTimeout="00:01:00"
receiveTimeout="00:10:00" sendTimeout="00:01:00" maxConnectionPoolSize="100"
encrypt="false" workstationId="" useAmbientTransaction="true"
batchSize="20" polledDataAvailableStatement="" pollingStatement=""
pollingIntervalInSeconds="30" pollWhileDataFound="false" notificationStatement=""
notifyOnListenerStart="true" enableBizTalkCompatibilityMode="true"
chunkSize="4194304" inboundOperationType="Polling" useDatabaseNameInXsdNamespace="false"
allowIdentityInsert="false" enablePerformanceCounters="false"
xmlStoredProcedureRootNodeName="" xmlStoredProcedureRootNodeNamespace="" />
</sqlBinding>
</bindings>
<client>
<endpoint address="mssql://x/y/z?" binding="sqlBinding"
bindingConfiguration="SqlAdapterBinding" contract="TypedProcedures_dbo"
name="SqlAdapterBinding_TypedProcedures_dbo" />
</client>
</system.serviceModel>
- note that the binding contains some familiar WCF settings (sendTimeout, receiveTimeout), but the majority are SQL Server specific connection options. The client element specifies the contract as TypedProcedures_dbo, the ServiceContract interface generated by the adapter, which has a single OperationContract defined:
GetManufacturerResponse GetManufacturer(GetManufacturerRequest request);
The proxy code for the client is all generated, so to invoke the stored procedure in your own code it's a familiar case of instantiating the client and calling the service, and of course you have full IntelliSense on the entity representing the resultset:
It's the content of the generated code that's interesting. The service, request, response and entity objects here are contained in 166 lines of generated code. The entity object is just a plain DTO-style class which implements IExtensibleDataObject to allow access to any returned data that hasn't been mapped, and has a DataContract attribute with the schema name representing the stored procedure. The mapping between the entity properties and the returned columns is done with standard System.Runtime.Serialization attributes, so the ManufacturerId column is represented as:
[System.Runtime.Serialization.DataMemberAttribute()]
public System.Nullable<short> ManufacturerId {
get {
return this.ManufacturerIdField;
}
set {
this.ManufacturerIdField = value;
}
}
- note that this is an optional field in the database table, so it's generated as nullable in the entity. No other flags or code are used to map data, so the WCF SQL adapter is effectively deserializing the resultset from the stored procedure call straight into the DataContract.
The generated code works well and is cleanly produced, but it has a few quirks you may not be happy with. A typed client class is generated for each individual procedure, whereas you might want them grouped into a single class which represents the full suite; and the class names are a bit cumbersome ("StoredProcedureResultSet0", "TypedProcedures_dboClient"). However, the code needed to actually connect to SQL through WCF and map the response is so simple that it's a straightforward task to generate your own code from custom templates.
Potential Usage
After an initial look, the WCF SQL adapter seems to be an attractive option for generating and powering the data access layer of a .NET application, entirely apart from its primary purpose as a BizTalk adapter. It repositions data access as a service call and uses the standard WCF mechanisms of ServiceContract and DataContract for information exchange. Assuming other data providers follow suit, or other WCF-database adapters follow from the community, it's a nice way of isolating your application from the physical database, so swapping to MySQL or Oracle could become a simple matter of changing your WCF binding.
It'll be interesting to see the licensing of the WCF LOB adapters from Microsoft. Currently the availability of a comprehensive suite of adapters is being positioned as one of the attractions of BizTalk as the Integration Server, compared to WF+WCF+Dublin as the Application Server. With the WCF SQL adapter there's a lot of potential take-up as a simpler alternative to the ADO.NET Entity Framework, so if it requires a BizTalk license, there will be room for an open source alternative.