WCF-SQL Adapter Stored Procedure

In this post I would like to show a different way to insert a record inside a SQL Table using a stored-procedure with WCF-SQL Adapter. Last year I wrote a post on WCF-SQL Table Operations, which was/is very useful for a lot BizTalk pro’s. For BizTalk 2010 you can find Adapter Pack Samples through BizTalk Server Development Center. On that page you can find a sample called SQL Adapter –> Using the adapter with BizTalk Server –> Invoking Stored Procedures. If you download this sample you have to run executable and unzip content in a folder. Then in that folder read the Readme.

Following through the readme file you will come to a point that you have to open the solution with Visual Studio and you will see the orchestration displayed below:

image

This is very basic (not really useful using an orchestration) and through messaging one can accomplished just the same. I did deploy this sample, but without the orchestration (I deleted it). I only needed the schema’s. I named the application StoredProcedure. Schema used to call stored procedure is displayed below:

image

After deploying the sample I imported the binding file accompanied with this sample called BINDINGS.xml. Receive and send ports will be created and only thing I had to do is adding filters to send ports, change database name and credentials.

Filter added for message type: “http://schemas.microsoft.com/Sql/2008/05/Procedures#ADD_EMP_DETAILS”

image

Change Address (URI) with appropriate server- and database name.

image

Filter added for message type: “http://schemas.microsoft.com/Sql/2008/05/Procedures#ADD_EMP_DETAILSResponse”

image

Next step is to enable the receive location and start sendports. Copy message to folder, message looks like this:

image

With response:

image

In database you will see newly added record.

image

As you can see through messaging one can call stored procedure with WCF-SQL Adapter. With maps inside port one can map incoming message and response message to desired format. I will delve into this next time, with some alternative ways.

Cheers.

Technorati:

Comments

Anonymous said…
Hi Steef,

I am using BizTalk 2010 WCF SQL adapter. Can you please help me with a similar example but changing the SQL response message format to my desired format. The SQL response is quite big with so many unwanted tags (as per the response schema).

Thanks,
Sumit
11:49 PM
Steef-Jan Wiggers said…
Sure,

Send over your project to SteefJan at msn.com.
1:00 AM
Anonymous said…
Hi Steef,

Hope you have got my email with the project (steefjan@msn.com)

Thanks,
Sumit
10:12 PM
Steef-Jan Wiggers said…
Yes, I got your email however I got into a lot of other stuff (work) and was holiday. Sorry for the delay.
2:23 PM
Unknown said…
hi steef,

i am trainee in biztalk plz help in finding some scenario for my use at kirankumar.reddy09@gmail.com
10:34 AM
Post a Comment

Popular posts from this blog

DTAP Strategy: Pricing and Licensing

Image
To implement BizTalk solutions it is a good practice to have an DTAP environment in place. DTAP stands for Development, Test, Acceptance and Production. Development and test environments are equal (mirrored) and same account for acceptance and production. Deploying DTAP environments is easy, because there are many factors influencing outcome of DTAP. One factor is pricing and licensing costs. BizTalk is available in different editions and since BizTalk depends on operating system and database there are some configuration options to. Here is an overview of possible configurations for DTAP environments, hence there is a nice BizTalk Feature Dependency Matrix. Besides features, there are non-functional requirements to reckon with like fail-over, availability and scalability. On Microsoft Pricing and Licensing FAQ section of BizTalk I found this on non functional side. In a large BizTalk Server 2006 R2 deployment, two key considerations ensure redundancy in all areas of the architect
Read more

Table Operation on Oracle 11g XE with OracleDbBinding

Image
Last year I did a post on table operations on SQL Server Table with WCF-SQL Adapter from BizTalk on SQL. Now I like to do similar thing on table in Oracle 11g Express database using the Oracle Binding from BizTalk Adapter Pack 2010. Oracle 11g Express Edition is available through OTN. For downloading software you will need to have an account! It is important that when accessing Oracle you have appropriate client installed. This can sometimes be a hassle if you do not know, which client you need. When you trying to connect to Oracle you will get error messages like: Connecting to the LOB system has failed. Could not load file or assembly 'Oracle.DataAccess, Version=2.111.7.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.. This can be pretty annoying, but there is documentation that can help you going the right direction. In installation documentation I read that supported versions are: Oracle d
Read more

BizTalk Server 2010 Exam : How to prepare?

Image
In previous post I yesterday I wrote that new BizTalk 2010 exam is out. And you may figure how to prepare as in preparation material nothing is mentioned.Let’s look at what skills are measured. In a nutshell these topics are: Configuring messaging architecture (setup, manage ports, configure adapters, implement messaging solutions); Developing BizTalk Artefacts (building orchestrations, create schema’s, maps and pipelines); Debugging and Exception Handling (debug orchestrations, validate and test artefacts like schema’s, maps, pipelines); Integrating Web Services and Windows Communication Foundation (WCF) Services (WCF Adapters); Implementing Extended Capabilities (this is the BAM, BRE, RFID and EDI part); Deploying, Tracking, and Supporting a BizTalk Solution (Installation in different kinds of scenario’s, deployment of applications, bindings). You can find a lot of resources that can help in preparation through BizTalk Development Center like the BizTalk tutorials for EA
Read more