WCF SQL operations

by | Nov 22, 2012 | BizTalk Community Blogs via Syndication | 0 comments

Normal
0

false
false
false

EN-AU
X-NONE
X-NONE

MicrosoftInternetExplorer4

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
line-height:115%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;
mso-fareast-language:EN-US;}

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

Submit a Comment