Document directory
- 2.1. Prepare the stored procedure for obtaining data
- 2.2. Use the Wizard to obtain the schema for receiving data
I. Prerequisites for using wcf_ SQL, wcf_oracle, and wcf_sap adapter for public installation 1. Microsoft. NET Framework 3.5 sp1.2, Microsoft Visual Studio 2008
3. Install the WCF lob adapter SDK SP2
To use the adapter in BizTalk adapter pack 2.0 in vs2008 to develop the BizTalk project, you must install BizTalk add-in for Visual Studio when installing the WCF lob SDK. To install this add-in, you must use custom installation or fully install the WCF lob adapter SDK.
In the customized Installation option, select BizTalk Server addin. Vs2008 must be installed before the WCF lob adapter SDK is installed.
4. Install BizTalk adapter pack 2.0
Ii. Use wcf_ SQL Adapter
Here is an example to illustrate how to use the wcf_ SQL adapter.
1. Example scenarios
System A has an employee table employees. The structure is as follows:
Field |
Type |
Description |
ID |
Int |
Auto-increment ID |
Name |
Nvarchar (50) |
Name |
Age |
Int |
Age |
Gender |
Int |
Gender |
New |
Bit |
ADD? |
When a new employee is added to this table, the new field is set to 1, indicating that the employee is added. This employee table is to be uploaded to the employee table employeestarget of system B. The table structure is the same as that of the employee table, but the new field is missing.
Biztalk needs to use the wcf_ SQL adapter to read new employees in the employee table employees of system A, and set the new field to 0, then, the new employee is written to the employee table employeestarget of system B.
2. receiving location 2.1. Prepare the stored procedure for obtaining data
Create a stored procedure for reading new employees in system:
Alter procedure [DBO]. [getemployees]
As
Begin
-- Obtain records of all new employees
Select name, age, gender from DBO. Employees where new = 1
-- Set the new field of all new employees to 0.
Update DBO. Employees set new = 0 where new = 1
End
2.2. Use the Wizard to obtain the schema for receiving data
In vs2008, create a schema for receiving new employees, right-click the BizTalk project in vs2008, and select "add generate item ":
Select "consume Adapter Service ":
Select "sqlbinding" under select a binding and use the wcf_ SQL adapter.
Click "Configure" in the upper-right corner:
Configure the user name and password for connecting to SQL Server.
In the URI properties tag, set as follows:
Server-set the name or IP address of the SQL Server to be connected
Initialcatalog-name of the database to be connected
Inboundid-serial number of the receiving position
Binding properties label:
As the receiving port, the training mechanism is used to access the database, that is, to read the database regularly at a certain interval.
Inboundoperationtype-- Attributes can be set to polling, typedpolling, and notification.
Polling is a polling method, but this method does not contain the architecture, and typedpolling is also a polling method, which can bring out the architecture. Notification is used to send notifications when data changes.
If you need to poll the data and fl the data architecture, you need to use the typedpolling method.
Pollingintervalinsecond-Polling Interval
Polleddataavailablestatement-The condition for executing the round robin is generally a SELECT statement. This statement should return a single result set, as long as the first column of the first row of the result set must have a value (if it is an empty result assembly error), it should be an integer greater than or equal to 0. If the value is greater than 0, run the SQL command specified by pollingstatement below to start the real round-robin operation. If the value is equal to 0, the round-robin statement is not executed.
In this example, select 1 from DBO. Employees where new = 1
Pollingstatement-The polling command can be an SQL statement or a stored procedure.
In this example, it can be set to Exec DBO. getemployees.
After the configure adapter is complete, return to the main page of adapter settings:
Click Connect. If the connection information settings in configure are correct, the connection status will be displayed: connected.
To serve as the receiving port, BizTalk is equivalent to the server. All services (inbound operations) must be selected in select contract type)
When you select Service (inbound operations), the Select a category below must be empty (except the ellipsis and slash). You have no choice but to click this slash.
Three Optional actions (polling, typedpolling, and notification) will appear on the right side. Their meanings have been mentioned before. Select typedpolling and click "add.
OK to complete the wizard.
Two files are generated in the project. One is the schema generated based on the polling command set earlier, and the other is the binding file that can be imported to the BizTalk receiving location and port. Unfortunately, the binding files generated by the wizard are all bound with wcf_custom instead of the desired wcf_ SQL. In this example, the binding file generated by the Wizard is not used.
Let's take a look at the generated Schema:
Compared with the preceding stored procedure, we can find that the generated schema is the three fields returned by the stored procedure. This shows that the Wizard is directed to the schema of the stored procedure.
This is different from the non-wcf SQL Adapter. When a non-wcf SQL adapter returns data, it must use for XML auto, xmldata return architecture, and the adapter wizard can then fl the architecture. Wcf_ SQL is not required. Generally, the result set returned by the SELECT statement can be directly flushed out of the architecture.