88 Part II Message Endpoints
With this simple class, you can pass in the URL and return a string contain-
ing the XML from the database in one smooth operation. The database does not
even have to reside on your own domain!
This is a great way to retrieve Web content across domains and allows you
to expose a database endpoint via HTTP. This technique is also excellent for
cross-platform access.
Summary
This section demonstrated how to set up a SQL Server virtual directory, query it
using a URL and a template, and use XPath to query that same virtual directory.
You also learned how to create a class to retrieve data by using only a URL. This
can be an effective way of exposing a SQL Server database as an endpoint.
SQL Endpoints in Microsoft BizTalk Server 2004
Microsoft BizTalk Server is another application that can make use of database
endpoints. This section will look at how to connect the Pet Shop database to a
BizTalk Server orchestration by using a generated database endpoint. Commu-
nicating directly with a SQL Server instance from BizTalk Server opens another
portal for you to initiate processes from your database and to have those pro-
cesses manipulate data in the database.
Problem
Once you have placed business process logic into BizTalk Server, you need to
activate that logic from a database endpoint. This chapter has presented other
methods of achieving this, but BizTalk Server warrants exploring because it
does much of the work for you and allows you to devote your time to building
the process instead of building code to communicate with the database.
Solution
BizTalk Server 2004 comes with a great tool that allows you to generate SQL
XML schemas automatically. We will demonstrate how to create a SQL Server
endpoint to activate a BizTalk Server orchestration by using this tool. We will
also create an orchestration for outputting the resulting data to a file on the file
system.
Start by adding the following stored procedure to the Pet Shop database.
Then follow the steps in this next list.
Chapter 4 Database Endpoints 89
Note This section assumes you have completed the basic setup for
BizTalk Server 2004.
Sp_SelectInven
CREATE PROCEDURE dbo.sp_SelectInven AS
Select itemid, qty from inventory
for xml auto, xmldata
GO
1. Create an empty BizTalk Server project in Microsoft Visual Studio.
2. Right-click the project in Solution Explorer. Select Add and then
choose Add Generated Items. Click Open.
3. In the Add Adapter Wizard dialog box, select SQL and click Next, as
shown in Figure 4-3.
F04LS03
Figure 4-3 Generating a SQL adapter.
4. In the Database Information page of the SQL Transport Schema Gen-
eration Wizard, click the Set button.
5. In the Data Link Properties dialog box (see Figure 4-4), select your
server, set your login, and set the database to Pet Shop.
6. Click Test Connection, and if the test is successful, click OK twice.
Then click Next.
90 Part II Message Endpoints
F04LS04
Figure 4-4 Setting up the connection.
7. Enter the target namespace: http://SQLReceiveSample.
8. Select the Receive Port option to designate the port type.
9. Enter the root element name (SQLEndPointRoot), as shown in
Figure 4-5. Click Next.
F04LS05
Figure 4-5 Adding the namespace and root element.
10. Select the Stored Procedure option. In the Stored Procedure combo
box, select Sp_SelectInven. Click Generate, Next, and then Finish.
The wizard will generate your schema as well as your orchestration.
Chapter 4 Database Endpoints 91
11. Add a port to the port surface named SQLReceivePort. Choose Use
An Existing Port Type, and then choose I Will Always Be Receiving
Messages On This Port.
12. In the port binding, choose Specify Later.
13. Add another port and name it FileSendPort.
14. Choose to create a new port type. Set its name as FileEndPoint.
Choose I Will Always Be Receiving Messages On This Port.
15. In the port binding, choose Specify Later.
16. Add a receive shape and a send shape.
17. In the Orchestration View window (a dockable window such as
Solution Explorer), right-click the Messages icon and select New
Message.
18. A new message object named Message_1 will appear. In the
Message_1 Properties window, select Message Type/Schemas/
SQLEndPoint.SQLService.
19. In the Properties window for the Send and Receive shapes, set the
message property to Message_1. For the Receive shape, select
Operation/SqlEndPoint. For the Send shape, select Operation/
FileEndPoint.
20. Set Activate Equal to True on the Receive shape. Your completed
orchestration should look similar to the one in Figure 4-6.
F04LS06
Figure 4-6 The completed orchestration.
21. Build the project, and then deploy it. (The project must be strongly
named using the sn.exe utility. Type sn -I KeyFileName.snk. If you
right-click the project name in Solution Explorer, choose Properties,

Get Enterprise Integration Solutions now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.