The SQL Adapter sample demonstrates how to use the various features of the SQL adapter included with BizTalk Server 2004, including how to use:

• Stored procedures from an orchestration
• SQL-XML Updategram from an orchestration
• Stored procedures in a receive port
• Normal SQL SELECT statements in a receive port (outside of an orchestration)

The scenario used by this sample is a partial implementation of a loan application process:

1. Loan application documents are received by BizTalk Server.
2. The loan application document is parsed and stored in SQL tables of Loans and Customers.
3. If a customer entry is new, a simple process determines a credit rating for that customer.
4. Based on the customer’s location, the loan is assigned to an Agent for processing.
5. Based on the customer’s credit rating, the loan is assigned an interest rate.
6. A report is periodically generated for Agents showing the loans and customers assigned to them.

This loan process is illustrated in the following diagram.

To download the updated BizTalk Server 2004 SDK, go to http://go.microsoft.com/fwlink/?LinkId=24723.

Location in SDK

<Samples Path>\Adapters\SQLAdapter\

File Inventory

The following table shows the files in this sample and describes their purpose.

To Build and Initialize This Sample

Use the following procedure to build and initialize the SQL Adapter sample.

1. In a command window, change directories (cd) to the following folder:

   <Samples Path>\Adapters\SQLAdapter

2. Run the file Setup.bat, which will perform the following actions:
   • Creates the Input_folder and Result_folder if needed.
   • Creates the database BTS2004_SQL_Adapter_Loans (drops and creates if needed).
   • Creates and populates the tables, and creates stored procedures.
   • Builds and deploys four BizTalk projects:
     • Loan Acceptance orchestration
     • New Customer Processing orchestration
     • Loan Assignment orchestration
     • Agent Report Generator schema
   • Runs scripts to create appropriate ports, binds them to appropriate BizTalk Server artifacts, and starts them.

Note  You should confirm that no errors were reported during the build and initialization process before attempting to run this sample.

To Run This Sample

Use the following procedure to run the SQL Adapter sample.

1. Ensure the files in the SQLAdapter\Sample Data folder are not in read-only mode.
2. Copy one of the files from SQLAdapter\Sample Data to SQLAdapter\Input_Folder.
3. Notice in the SQLAdapter\Result_folder:
   • Accepted_loan_{guid}.xml appears when the Loan Acceptance orchestration is complete.
   • New_customer_{guid}.xml appears when the New Customer Processing orchestration is complete.
   • Assigned_loan_{guid}.xml appears when the Loan Assignment orchestration is complete.
   • Agent_report.xml is created and periodically updated.

Note  New_customer_{guid}.xml will not appear if a customer has already been processed. To clear the Customers table, run Reset_loans.bat.

To Remove This Sample

Use the following procedure to remove the SQL Adapter sample.

1. In a command window, change directory (cd) to the following folder:

   <Samples Path>\Adapters\SQLAdapter

2. Run Cleanup.bat.

Note  The BTS2004_SQL_Adapter_Loans database is not removed by Cleanup.bat; however, the database is destroyed and re-created if you run Setup.bat again.

Remarks

The database schema for the SQL Adapter sample is shown in the following diagram.

The following XML depicts an example of a loan document:

- < LoanApplication >
      <Name FirstName="Tester" LastName="Person" /> 
      <Address Street="1 Microsoft Way" 
                       City="Redmond"
                       State="Washington" /> 
-     <LoanData>
           <RequestedAmount>$100.45</RequestedAmount> 
       </LoanData>
  </ LoanApplication>

In the Loans table, the RequestAmt column uses the SQL money type.

The <RequestedAmount> element in the XML schema is xs:string.

The Stored procedure SP_Save_Loan_info() must accept the value @Amount as nvarchar(50). The stored procedure converts the value using the expression convert(money, @Amount). This is required because the $ (dollar) and € (euro) characters cause XML parsing errors. Hence, the money value is kept as a string until it is passed to the SQL tables.

SQL Server discards the $ or € symbol when it saves the data into a column of type money. Your system should retain the currency type in another column to retain the information for conversion purposes. The $ and € symbols are not saved in the SQL Adapter sample for the sake of simplicity.

See Also

Adapters (BizTalk Server Samples Folder)