WCF SQL operations

When adding WCF sql ports to BizTalk, you can add a whole host of different types of operation with SQL and it’s incredibly powerful. 

The problem is with all these options, the wizard rarely makes a binding file for you, it will make the schemas, but the binding file contains that extra little bit of magic…

The key to all of this in the SOAP action header you specify on the port.

There are your basic ones:

TableOp/Insert/dbo/Employee

TableOp/Update/dbo/Employee

If you read this, it will do a table operation and insert or update employee record(s) in the employee table.

Then there is of course stored procedures for both send and receive ports. 

TypedProcedure/dbo/StoredProcedureName

Simple enough, however maybe you have a few operations you want to do, you want to go for a more composite operation. 

Your Soap action can be CompositeOperation

The message you send can do a whole host of things, for example, insert into a table, and when done, run a stored procedure.

You have your insert schema, and your execute stored procedure schema, that the wizard generates, now bundle this into the following structure and all of a sudden a whole world of opportunities opens up.

<xs:element name="Request">

    <xs:complexType>

      <xs:sequence>

        <xs:element ref="ns0:Insert" />

        <xs:element ref="ns1:CompareEmployee" />

      </xs:sequence>

    </xs:complexType>

  </xs:element>

  <xs:element name="RequestResponse">

   <xs:complexType>

    <xs:sequence>

     <xs:element ref="ns0:InsertResponse" />

     <xs:element ref="ns1:CompareEmployeeResponse"/>

    </xs:sequence>

   </xs:complexType>

  </xs:element>

Then there are the Generic operations, vastly undocumented, for example a SQL reader, that can execute SQL against the database and return the results of the query…

Your Soap Action header would be: GenericOp/ExecuteReader

The schema looks like this:

  <ExecuteReader xmlns="http://schemas.microsoft.com/Sql/2008/05/GenericTableOp/">

    <Query>[PL/SQL STATEMENT1];[PL/SQL STATEMENT2];…</Query>

  </ExecuteReader>

Have a look at some of the more unknown options available, and you start to see the power in all of this. 

Operation	Soap Action  Header
ExecuteNonQuery Request	GenericOp/ExecuteNonQuery
ExecuteNonQuery Response	GenericOp/ExecuteNonQuery/response
ExecuteReader Request	GenericOp/ExecuteReader
ExecuteReader Response	GenericOp/ExecuteReader/response
ExecuteScalar Request	GenericOp/ExecuteScalar
ExecuteScalar Response	GenericOp/ExecuteScalar/response

Refer to this link for more: http://msdn.microsoft.com/en-us/library/dd788372%28v=bts.10%29.aspx