WCF-SQL Adapter Table Operations

Monday, October 3, 2011

post by Steef-Jan-Wiggers

In this post I like to show all table operations possible with WCF-SQL Adapter. The WCF based SQL Adapter can be used to connect BizTalk applications to SQL Server. WCF-SQL Adapter is part of BizTalk Server Adapter Pack 2.0, but can also be downloaded separately. To work with the adapter you will need to install the WCF LOB Adapter SDK SP2 before installing this adapter. Something that is overlooked easily, which also accounts for the fact that this adapter is offered as a supplement to BizTalk Server 2009. You must have a valid license for the BizTalk Server in order to use the SQL adapter and be eligible for support and to receive updates.
Important to note here are the supported operating systems:
  • Windows Server 2003 Service Pack 2
  • Windows Server 2008
  • Windows Vista
  • Windows XP Service Pack 3
  • Windows XP SP3
  • Windows Server 2003 SP2
  • Windows Server 2003 R2
  • Windows Server 2008
  • Windows Vista
  • and required is .NET 3.5 SP1.
The Adapter Metadata wizard is supplied through WCF LOB Adapter SDK and necessary to generate schema’s through Visual Studio .NET.
The SQL adapter can be used in BizTalk either as a WCF-Custom port or a WCF-SQL port. If you want to use the SQL adapter through a WCF-Custom port, you do not need to add the WCF-Custom port to the BizTalk Server Administration console, because the WCF-Custom port is added to the BizTalk Server Administration console by default. However, if you want to use the SQL adapter through a WCF-SQL port, you must first add the WCF-SQL adapter to the BizTalk Server Administration console. To add the WCF-SQL Adapter you need to start the BizTalk Server Administration Console and than perform the following steps:
1. In the console tree, expand the BizTalk Group, expand Platform Settings, and then clickAdapters.
2. Right-click Adapters, point to New, and click Adapter.
clip_image001
3. In the Adapter Properties dialog box, specify a name for the adapter and from the Adapter list, select WCF-SQL.
clip_image002
4. Click Ok.
Having the adapter in place you can use it for your solutions. You can download sample from Microsoft related SQL Adapter through BizTalk Adapter Pack 2.0 samples. There you will find the Performing Operations on Tables sample that shows how to perform operations on tables in SQL Server, but this only shows you the select operation (i.e. download is called SelectTable.exe).
To perform operations on SQL Server by using the WCF-SQL adapter with BizTalk Server, you must perform a set of design-time and run-time tasks using Visual Studio and BizTalk Server Administration console respectively. These high level tasks to create a message based solution are:
  • Create BizTalk project, and generate schema.
  • Create messages instances.
  • Build and deploy the BizTalk project.
  • Configure the application.
  • Start the application.
Downloaded sample executes the SELECT operation on the EMPLOYEE table in a SQL Server database. This sample performs the following operations:
  • Picks up the request message to execute the SELECT operation from a predefined file location.
  • Sends the request to the SQL Server database to perform the operation.
  • Receives the response from the SQL Server database and copies the response at another predefined file location.
Download sample contains code and sqlscript is provided to create the database. I like to show all the CRUD operations (i.e. CREATE, REQUEST, UPDATE and DELETE). You can create the database using SQL Server Management Studio and load/execute the script (2005 or 2008 found in SelectTable solution sqlscripts folder). Other step to perform is creating a BizTalk project.

BizTalk Project and generate schema (‘s)

First, we need the XML schemas, which represent our data source. By installing the WCF LOB Adapter SDK SP2, you will have a metadata browser built into Visual Studio. NET at your disposal. You can access this from a BizTalk project by right-clicking the project, choosing Add then Generated Itemsand selecting Consume Adapter Service.
clip_image002
This action opens a new window that provides us the interface to connect to, browse, and select database objects for service interaction. The very first thing we need to do here is choose thesqlBinding as the service binding, and then configure a connection string. The simplest working connection string consists of an Initial Catalog value for the database, and a valid Server name entry. Note that the adapter now uses a connection string in the form of a URI instead of the traditional Data Source=;Initial_Catalog=;User_ Id=;Password= style.
clip_image002[6]
Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories.
image
On the root "/" character a range of un-typed generic functions are displayed, which can be exploited. Click on tables and you will see a number of Tables, select Employee.
image
Click the Delete, Insert, Select and Update statement in available categories and operations. Click the Add button to add the operations.
image
After clicking the Ok button at the bottom, schema (and a single binding file) is added to our associated BizTalk project in Visual Studio.NET.
image
The binding can be used later, when solution is deployed and ports have to be configured for SQL Server (i.e. Send Port).

Create message instances

Next step is to generate messages from generated schema, which can be routed from Recieve location in Receive Port to a send port with WCF-SQL Adapter (messaging based solution). Generating the first message is easy, as you can right click on the TableOperation.dbo.Employee.xsd and selectGenerate Instance and an instance of top element is created (i.e. delete).
image
You will get a instance of message as depicted below:
image
By opening up the TableOperation.dbo.Employee.xsd in file-editor you can move the next operation to top, so you can then in Visual Studio generate the next instance for an operation or you can use XML-Editor inside Visual Studio (closing schema, right click and select open with… and then select XML Editor) to move elements around. In end you will have four xml instance for TableOperations, save to file as xml (type all files, encoding UTF-8!).
Select:
<ns0:Select xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee%22>
<ns0:Columns></ns0:Columns>
<ns0:Query></ns0:Query>
</ns0:Select>
Insert:
<ns0:Insert xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee%22>
<ns0:Rows>
<ns1:Employee xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22>
<ns1:Employee_ID></ns1:Employee_ID>
<ns1:Name></ns1:Name>
<ns1:DOJ></ns1:DOJ>
<ns1:Designation></ns1:Designation>
<ns1:Job_Description></ns1:Job_Description>
<ns1:Photo></ns1:Photo>
<ns1:Rating></ns1:Rating>
<ns1:Salary></ns1:Salary>
<ns1:Last_Modified></ns1:Last_Modified>
<ns1:Status></ns1:Status>
<ns1:Address></ns1:Address>
</ns1:Employee>
</ns0:Rows>
</ns0:Insert>
Update:
<ns0:Update xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee%22>
<ns0:Rows>
<ns0:RowPair>
<ns0:After>
<ns1:Employee_ID xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Employee_ID>
<ns1:Name xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Name>
<ns1:DOJ xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:DOJ>
<ns1:Designation xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Designation>
<ns1:Job_Description xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Job_Description>
<ns1:Photo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Photo>
<ns1:Rating xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Rating>
<ns1:Salary xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Salary>
<ns1:Last_Modified xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Last_Modified>
<ns1:Status xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Status>
<ns1:Address xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Address>
</ns0:After>
<ns0:Before>
<ns1:Employee_ID xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Employee_ID>
<ns1:Name xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Name>
<ns1:DOJ xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:DOJ>
<ns1:Designation xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Designation>
<ns1:Job_Description xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Job_Description>
<ns1:Photo xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Photo>
<ns1:Rating xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Rating>
<ns1:Salary xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Salary>
<ns1:Last_Modified xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Last_Modified>
<ns1:Status xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Status>
<ns1:Address xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22></ns1:Address>
</ns0:Before>
</ns0:RowPair>
</ns0:Rows>
</ns0:Update>
Delete:
<ns0:Delete xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee%22>
<ns0:Rows>
<ns1:Employee xmlns:ns1="http://schemas.microsoft.com/Sql/2008/05/Types/Tables/dbo%22>
<ns1:Employee_ID></ns1:Employee_ID>
<ns1:Name></ns1:Name>
<ns1:DOJ></ns1:DOJ>
<ns1:Designation></ns1:Designation>
<ns1:Job_Description></ns1:Job_Description>
<ns1:Photo></ns1:Photo>
<ns1:Rating></ns1:Rating>
<ns1:Salary></ns1:Salary>
<ns1:Last_Modified></ns1:Last_Modified>
<ns1:Status></ns1:Status>
<ns1:Address></ns1:Address>
</ns1:Employee>
</ns0:Rows>
</ns0:Delete>

Build and deploy the BizTalk project

Only schemas are required for a messaging only scenario with WCF-SQL Adapter to perform operations on SQL Server. Therefore after assigning a strong name and application name to BizTalk project it can be build and deployed.
image

Configure the application

After solution has been deployed it is time to configure the ports. FILE ports are easy and focus here will be configuring the WCF-SQL Send Port. Through BizTalk Management Console one can browse to SelectTable application and right click the application and select Import Bindings … and navigate to file called WcfSendPort_SqlAdapterBindings_Custom.bindinginfo.xml, select and click Open. You will notice that a Send Port will be created automatically.
image
If you look at created send port and configuration you will notice that everything is there. I can tell that this will not work for all the operations as I experience the following error described in this blog post:Why does the Adapter say ‘Action is not understood’ even though I am using the binding file generated by the ‘Consume Adapter Service’ wizard? I choose to delete three operations and create three similar send ports based on first generated send port. Next steps are setting filters on WCF-Send Port and File Send Port. Configuration is as follows:
Port NameTypeFilter
RcvPortTableOperationsFILE
SndDeleteOperationWCF-SQLBTS.MessageType ==http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee#Delete
SndInsertOperationWCF-SQLBTS.MessageType ==http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee#Insert
SndSelectOperationWCF-SQLBTS.MessageType ==http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee#Select
SndUpdateOperationWCF-SQLBTS.MessageType ==http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee#Update
SndOperationResponseFILEBTS.MessageType ==http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee#SelectResponse
OR
BTS.MessageType ==http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee#SelectResponse

OR
BTS.MessageType ==http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee#SelectResponse

OR
BTS.MessageType ==http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Employee#SelectResponse

Start the application

In the end your messaging solutions can be depicted as below. After configuration you can start BizTalk application.
image
Select Operation:
image
Response:
image
Insert Operation:
image
Response:
image
Results so far if you query Employee Table are:
image
Update Operation:
image
Response:
image
Result after update if you query Employee Table again are:
image
Last operation will be delete.
Delete Operation:
image
Response:
image
Result after delete if you query Employee Table again are:
image
I hope with this post I was able to show all operations (CRUD) on table. The sample from MSDN shows only SELECT operation and here I have extended on that, showing all operations and how to setup WCF-SQL Adapter and steps to do, before you can execute all operations on a SQL Table.

No comments:

Post a Comment

Post Your Comment...