BizTalk 2010 Recipes : Adapters - Calling Stored Procedures

Create a new SQL database and table for which to insert the record. Compile the table definition listed in Listing 1.

Example 1. Example Customer Table Definition

CREATE TABLE [dbo].[Customer] (
    [CustomerID][int] IDENTITY (1,1) NOT NULL,
    [CustomerName][nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Address][nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [City][nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Region][nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [PostalCode][nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
) ON [PRIMARY]

					  

If you do not have a stored procedure to insert individual customer records, compile the stored procedure listed in Listing 2.

Example 2. Example Insert Customer Stored Procedure

if exists  (select * from sysobjects where name = 'BizTalkToCustomerInsert'
and type = 'P')
    drop proc BizTalkToCustomerInsert
go
CREATE procedure BizTalkToCustomerInsert
                    @CustomerName nvarchar(60)
                    , @Address nvarchar(120)
                    , @City nvarchar(60)
                    , @Region nvarchar(60)
                    , @PostalCode nvarchar(60)
AS
/*
**
** Object: BizTalkToCustomerInsert
**
** Description: Inserts records into the Customer table for the SQL adapter
** stored procedure recipe
**
**
*/
BEGIN
    Declare @intReturnCode int
    select    @intReturnCode = 0

    /*Insert records that will be returned to caller*/
    Insert Customer (CustomerName, Address, City, Region, PostalCode)
    Values (@CustomerName, @Address, @City, @Region, @PostalCode)

    /* Set the outbound return code */
    select    @intReturnCode = @@error

    /* return the return code */
    return @intReturnCode
END

go
grant execute on dbo.BizTalkToCustomerInsert to public
go

					  

Create a new BizTalk solution that will contain the components used to insert customers into the Customer table in your SQL database. Next, create a sample message that matches the structure detailed in Listing 3. The schema for this message will be created as part of the solution as defined in the "Task 2: Create BizTalk Solution Artifacts" section of this recipe.

Example 3. Example Customer Input Message

<ns0:Customer_Request xmlns:ns0="http://SQLAdapterStoredProcedure">
  <ns0:BizTalkToCustomerInsert Address="1241 Little Overlook" City="Grand Jct" CustomerName="Ciro
A" PostalCode="81502" Region="CO" />
</ns0:Customer_Request>

					  

Choose Add Generated Items from the Solution Explorer's tool menu. Verify that Add Adapter Metadata is selected.

On the Add Adapter Wizard page, select the SQL adapter, and verify the settings to your BizTalk MessageBox database. Figure 1 shows the initial connection screen.

Figure 1. Using the Add Generated Items Wizard

Click Next, and set the connection string for the database to which you are connecting.

Click Next, and specify the target namespace as http://SQLAdapterStoredProcedure, the port type as Send, the request document root element name as Customer_Request, and the response document root element name as Customer_Response. The wizard should look like Figure 2.

Figure 2. Using the SQL Transport Schema Generation Wizard

On the Statement Type page of the wizard, select Stored Procedure as the type of statement, and click Next.

On the Statement Information page of the wizard, select the BizTalkToCustomerInsert stored procedure from the drop-down enumeration, and leave all the check boxes next to the input parameters empty.

Finally, click the Generate button, because it results in a generated execute script that is listed in the Generated Script field of the wizard. The wizard should resemble Figure 3.

Figure 3. Using the SQL Transport Generation Wizard—the Statement Information page

Click Next and then Finish, completing the wizard. When you complete the wizard, notice that BizTalk creates two artifacts for you: the BizTalk orchestration and SQLService.xsd.

Configure the BizTalk orchestration for an inbound receive and an outbound send. The ports should be configured to receive and to send a message of type SQL_Adapter_Stored_Procedure.SQLService.Customer_Request. Your orchestration should resemble Figure 4. Make sure to set the Activate property on the Receive shape to True.

Figure 4. Configuring the SQL stored procedure orchestration

Build and deploy your BizTalk solution.

To test this solution, you will need to create a physical file receive port to consume the inbound XML file and a SQL send port to send the message to the SQL database. Open the BizTalk Administration Console.

Find the appropriate BizTalk application, right-click the Send Ports folder, and select New→Static One-way Send Port to open the Send Port Properties dialog box. Name the new send port Sample SQL Stored Procedure Send Port. Within the Transport section of the General tab, select SQL from the Type drop-down list.

In the SQL Transport properties, configure the connection string to the appropriate database by clicking the ellipses next to the Connection String field. Additionally, configure the document target namespace as http://SQLAdapterStoredProcedure.

Set Send Pipeline to the Microsoft.BizTalk.DefaultPipelines.XMLTransmit pipeline.

Bind the deployed orchestration to the newly created send and receive ports.