Biztalk 2010

I decided to write a simple kick start tutorial for WCF-SQL Adapter for beginners.

In this tutorial where I just perform single operation insert. A simple BizTalk solution which read a value from file location and insert this value into table.

First of all I create a very simple category table. Which hold two fields. As

USE [Experiments]

GO

 

/****** Object:  Table [dbo].[CategoryWar]    Script Date: 03/25/2012 22:12:13 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[CategoryWar](

[ID] [int] IDENTITY(1,1) NOT NULL,

[CategoryName] [nvarchar](255) NOT NULL,

CONSTRAINT [PK_CategoryWar] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

 

 

Now I create a new solution with Name WCF_SQLAdapter. You can use as you like.

On solution explore, I right click  on add and then  select generate items.

Then select consumerAdapterServices  as follow.

Select SQLBinding from Consume Adapter Services Wizard. Like  as

Then click on Configure Button new popup windows  like that

Inter the Sql server Name. And Database Name in Initial Catalog. And press save button. Now URI look like as

Click on test button to verify the connection established. If connection is successful. Then Category section will below the connected button will populate as select from table all possible option appears at right side as follow.

Some Schemas and bind files generated as follow. Open schema name with

TableOperation.dbo.CategoryWar.xsd

Now we create one more schema, which will used as input to BizTalk solution. Like as

Now I create a map for send value from input schema to Request input message.

I used source input schema. And output schema I used to  insert schema from TableOperation.dbo.CategoryWar.xsd. Map will be like

After that I create new Orchestration with name InputProcessOrchestation.odx.

I create three message with One for input message, and two with Insert Request Method and one for Insert response.  Orchestation will be look like as

It is very simple orchestration, which reading input from file location transform it into request message. And request response call to WCF-SQL adapter and received message drop in other location.

For wcf-sql call I created send request port with specify later option. Remember the name of operation will be the same which will describe in binding. You can later change it. When I deploy the solution I found three ports on BizTalk console. I created two file ports one for receiving input and second for droping output. I import the binding which is created at the time of consuming the SQL Adpater. It will as

Generate instance form input schema and modify it and drop. This solution is works fine for me.  You can download solution from here.