BizTalk Adapter Pack 2.0: SQL Adapter

BizTalk Adapter Pack 2.0 Eval has been released a while ago. This adapter enables you to send and receive messages to and from a line-of-business (LOB) system. The primary design goal of adapters is to facilitate the exchange of business documents between trading partners or systems inside your enterprise. The adapters provide the following advantages to clients:

• Consistent design-time experience. The adapters provide a common and user-friendly design time experience for browsing, searching, and retrieving metadata of LOB artifacts.
• Varied programming options. The adapters provide a choice of programming model including Windows Communication Foundation (WCF) Channel Model, WCF Service Model, ADO.NET or through BizTalk orchestrations.
• Uniform experience across LOBs. The adapters standardize on using the WCF and Microsoft Windows Communication Foundation (WCF) Line of Business (LOB) Adapter SDK and hence provide a uniform experience of gaining access to any LOB system.

Supported Operating Systems:

• Windows Server 2003 R2 Enterprise Edition (32-Bit x86)
• Windows Server 2003 R2 Enterprise x64 Edition
• Windows Server 2003 Service Pack 2
• Windows Server 2008 (SP2!!)
• Windows Vista
• Windows XP Service Pack 3

I have a new VM-machine using VMWare worksation on my new Pavalion QuadCore Desktop PC (x64) at home with 8Gb RAM. My VM Machine has 2 Gb Ram and 2 CPU assigned to it. On Machine there is Windows Server 2008 Standard Edition SP2, SQL Server 2008 Developer Edition, VS 2008 Team Edition SP1 and BizTalk Server 2009. I wanted to try out this adapter pack, after having a discussion with Richard a while ago. Do not forget to download and install WCF SDK SP2 first.  Procedure I created is shown below:

	USE [AdventureWorks2008]
	GO
	/****** Object: StoredProcedure [dbo].[uspGetEmployeeDetail] Script Date: 07/13/2009 20:59:57 ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	-- =============================================
	-- Author: Steef-Jan Wiggers
	-- Create date: 14-09-2009
	-- Description: Get detail information of employee
	-- based on national id number
	-- =============================================
	ALTER PROCEDURE [dbo].[uspGetEmployeeDetail]
	-- Add the parameters for the stored procedure here
	@NationalIDNumber As nVARCHAR(15)
	AS
	BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- Insert statements for procedure here
	SELECT Person.Person.FirstName, Person.Person.MiddleName, Person.Person.LastName, HumanResources.Employee.JobTitle,
	HumanResources.Employee.MaritalStatus, HumanResources.Employee.Gender, Person.Address.AddressLine1, Person.Address.City, Person.Address.PostalCode,
	Person.EmailAddress.EmailAddress, Person.PersonPhone.PhoneNumber, HumanResources.Employee.NationalIDNumber
	FROM HumanResources.Employee INNER JOIN
	Person.Person ON HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID INNER JOIN
	Person.BusinessEntityAddress ON HumanResources.Employee.BusinessEntityID = Person.BusinessEntityAddress.BusinessEntityID INNER JOIN
	Person.Address ON Person.BusinessEntityAddress.AddressID = Person.Address.AddressID INNER JOIN
	Person.EmailAddress ON Person.Person.BusinessEntityID = Person.EmailAddress.BusinessEntityID INNER JOIN
	Person.PersonPhone ON Person.Person.BusinessEntityID = Person.PersonPhone.BusinessEntityID
	WHERE HumanResources.Employee.NationalIDNumber = @NationalIDNumber
	END

view raw uspGetEmployeeDetail hosted with ❤ by GitHub

Next thing I did was creating a Windows application with Visual Studio. I created a form with one textbox for NationalIDNumber. It looks like this:

To make it work with data from SQL Server I opened up add adapter service reference and selected necessary binding and configuration data.

Click Connect.

Finally I selected the stored-procedure I created.

Click Ok and SqlAdapterBindingClient.cs is created. When click test on my Test client the code below is executed.

	private void buttonTest_Click(object sender, EventArgs e)
	{
	//////////////////////////////////////////////////////////////////////
	// CREATING THE CLIENT
	///////////////////////////////////////////////////////////////////////
	Procedures_dboClient client = new Procedures_dboClient("SqlAdapterBinding_Procedures_dbo");
	//Windows Authentication
	///////////////////////////////////////////////////////////////////////
	// OPENING THE CLIENT
	///////////////////////////////////////////////////////////////////////
	try
	{
	Console.WriteLine("Opening Client...");
	client.Open();
	}
	catch (Exception ex)
	{
	Console.WriteLine("Exception: " + ex.Message);
	throw;
	}
	///////////////////////////////////////////////////////////////////////
	// EXECUTING THE GET_EMP_DETAILS STORED PROCEDURE
	///////////////////////////////////////////////////////////////////////
	DataSet[] dataArray;
	int returnCode;
	try
	{
	Console.WriteLine("Calling a stored procedure...");
	dataArray = client.uspGetEmployeeDetail(textBoxNationalIDNumber.Text, out returnCode);
	}
	catch (Exception ex)
	{
	Console.WriteLine("Exception: " + ex.Message);
	throw;
	}
	Console.WriteLine("The details for the employee with NationalIDNumber are: {0}", textBoxNationalIDNumber.Text);
	Console.WriteLine("*************************************************");
	textBoxResult.Text = dataArray[0].Any1.FirstChild.InnerXml;
	/////////////////////////////////////////////////////////////////////////
	// CLOSING THE CLIENT
	/////////////////////////////////////////////////////////////////////////
	client.Close();
	Console.WriteLine("Press any key to exit...");
	Console.ReadLine();
	}

view raw SQL Adapter hosted with ❤ by GitHub

Result of execution is a xml data of employee with NationalIDNumber 20269531:

Sample I showed here is derived from Microsoft website with a lot examples for this adapter pack.

I downloaded this sample. I had fun trying it out my code and looking at the sample. I am going to try to do an example for Oracle on VM later on or after my holiday. At a customer I am doing a project now has an Oracle/Oracle eBusiness Suite present (11g), but not a suitable development unfortunately to have go with adapter pack 2.0. I therefore had to try it in my own lab.

Technorati:BizTalk Server 2009