Elton Stoneman

[Source: http://geekswithblogs.net/EltonStoneman]

Following on from my post on Using the WCF SQL Adapter in .NET: Calling Stored Procedures (see that post for download and installation instructions for the WCF LOB adapter pack), this one looks at using the adapter to execute SQL statements on database objects.

Walkthrough: Executing SQL Statements

Add Adapter Service Reference generates separate entity and client classes for each table you select, and separate request and response classes for each table operation. To generate proxies for executing SQL statements against a table, choose the operations from the Tables view of the hierarchy:

Selecting the Insert and Select operations against the BikeTypes table will generate the following class structure:

The client for connecting to SQL Server is a standard WCF client class, inheriting from ClientBase and specifying the ServiceContract as its channel – in this case the interface is TableOp_dbo_BikeTypes which has operation contracts representing the Insert and Select statements. The entity representing the database table implements IExtensibleDataObject and provides DataMember-flagged properties for each table column; an array of entities is used as the input for the Insert operation, and the return for the Select operation (wrapping the underlying use of generated Request and Response classes).

The Request and Response classes are flagged with MessageContract attributes. Message contracts are less commonly seen than DataContract and ServiceContract, but they allow you finer control over the messages sent and received by the adapter, including the ability to specify whether data is to be serialized in the header or body of the message (see Using Message Contracts). In the generated classes, MessageContract is used to specify the wrapper name and namespace, which puts the message payload within a defined element in the SOAP body. The following attribute:

[System.ServiceModel.MessageContractAttribute(WrapperName="Select", WrapperNamespace="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/BikeTypes", IsWrapped=true)]

- generates a SOAP message for the SelectRequest class which looks like this:

<s:Envelope xmlns:a="http://www.w3.org/2005/08/addressing" xmlns:s="http://www.w3.org/2003/05/soap-envelope">

<s:Header>

<a:Action s:mustUnderstand="1">TableOp/Select/dbo/BikeTypes</a:Action>

<a:MessageID>urn:uuid:9a5fe359-e988-4504-96a9-b9b721d00502</a:MessageID>

<a:ReplyTo>

<a:Address>http://www.w3.org/2005/08/addressing/anonymous</a:Address>

</a:ReplyTo>

</s:Header>

<s:Body>

<Select xmlns="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/BikeTypes">

<Columns>*</Columns>

<Query>WHERE BikeTypeCode LIKE '%R%'</Query>

</Select>

</s:Body>

</s:Envelope>

Note that the SOAP action is the Node Id for the operation from Add Adapter Service Reference. The request message contains Columns and Query elements, which are used in the call to refine the size and content of the resultset. In code you specify the Columns property with "*" to return all, or a comma-separated list of column names (which should be listed in the same order as defined in the table). Query can be null, empty or contain a WHERE clause to restrict the results:

TableOp_dbo_BikeTypesClient client = new TableOp_dbo_BikeTypesClient();

client.Open();

BikeTypes[] allBikeTypes = client.Select("*", null);

BikeTypes[] bikeTypeDescriptions = client.Select("BikeTypeDescription", string.Empty);

BikeTypes[] likeRBikeTypes = client.Select("*", "WHERE BikeTypeCode LIKE '%R%'");

A populated Query property will limit the number of items returned. A populated Columns property will limit the number of populated elements in the response, so the typed object will have null values for any unmapped columns.

For the insert, you pass an array of populated entities to the call:

List<BikeTypes> bikeTypes = new List<BikeTypes>();

bikeTypes.Add(new BikeTypes());

bikeTypes[0].BikeTypeCode = "NEW";

bikeTypes[0].BikeTypeDescription = "New Type";

TableOp_dbo_BikeTypesClient client = new TableOp_dbo_BikeTypesClient();

client.Open();

client.Insert(bikeTypes.ToArray());

If you want to write identity values for tables which have an identity column, you can specify AllowIdentityInsert in the binding configuration. Otherwise, any specified columns have the value from the entity inserted; null values have NULL inserted. The return contains an array of long values containing the identity of the inserted rows – unless the table does not have an identity column, in which case the return is null.

Similarly the adapter can generate request and response classes for Update and Delete statements, which follow the same pattern. The generated stack has the same benefits as with the stored procedure calls – the code is very light and it uses the standard WCF stack, so if you'd rather generate or hand-craft your own connections, that's a definite option. I'll explore it in a later post.