In-Depth

Expand Web Apps With SODA and WCF

Simplify and augment SCM-style apps using SODA, SQL Server 2005, and Windows Communication Foundation.

Technology Toolbox: C#, SQL Server 2005

Business-to-business (B2B) systems are gaining acceptance nowadays for enabling the ability to exchange information between organizations programmatically. These systems make it possible to expose the business functions of organizations as services using standard Web technologies to the world-wide market, as well as to outsource support activities while concentrating on core processes that letyou establish relationships with partners around the world cheaply. I'll show you how to take advantage of this architecture to create a solution that supports common business processes between two enterprises in the supply chain management (SCM) environment using emerging Microsoft technologies. The benefit of this approach is that it lets you simplify your processes, while also expanding your ability to interact with customers and others in your supply chain.

Supply chain management is the process of planning, implementing, and controlling the operations of the supply chain such as sourcing, procurement, conversion, and logistics management activities. It deals with the flow of products and information between a supply chain member's organizations. To select the best supplier, distributor, and customer, you need to have the right information at the right time. The advent of information technologies has lowered the cost of procuring and sharing information significantly.

This article's example follows a common business scenario. A wholesaler wants to expose a list of its products and the associated prices in order to find long term partners. Several retailers use this information to make purchasing decisions based on economic analysis methods, such as cost-benefit analysis, cost-utility analysis, and replenishment forecasts. If a given retailer wants some of these products in the stock, then a relationship is established with the wholesaler and a purchase order is sent. The main requirement of the solution is to develop and deploy an e-Procurement system supporting the concepts of the interorganizational systems where enterprise information systems within each organization in the supply chain communicate and exchange business information with each other in a standard way and across security boundaries.

I will explain how to fulfill this requirement using Microsoft technologies. For example, I'll walk you through the development of the server-side code using Microsoft SQL Server 2005 to expose some application logic written in T-SQL as a Web service, as well as how to consume this service using a client hosted in Windows Communication Foundation (WCF). I'll also walk you through how to create the graphical user interface of the client.

In the article, "Integrate Windows Apps With Oracle," [VSM August, 2007], I addressed the foundations of Web services protocols. Virtually any enterprise has database systems as the primary persistent medium to store the operations data, as well as a lot of application logic written in proprietary SQL extensions such as PL/SQL in Oracle Database and T-SQL in Microsoft SQL Server. With this infrastructure already in place, it's possible to apply the principles of Service-Oriented Database Architectures (SODA) as an extension to Service-Oriented Architecture (SOA), integrating your systems at the data layer. The solution described in this article takes advantage of this new and elegant approach.

When analyzing the foundation of SOA apps, you realize quickly that the main component is the data that is received, processed, and sent by services around their operations. Enterprise applications deal with business data all the time, which is why Microsoft SQL Server 2005 includes new database features such as a SODA service provider that becomes a full application server, not only hosting database objects such as tables, stored procedures, and user-defined functions, but also for serving as a runtime environment for Web services. This SODA service provider implements several important features, including endpoint support for several communication mechanisms such as sockets, HTTP, and SOAP. The service provider also provides a process service request, which lets you receive, process, and perform transforms on incoming data. (Note that this feature uses the filter-pipe design pattern.) The service provider can also function as a Service Logic Host; that is, it enables you to manage the entire lifecycle of your services, as well as manage runtime services such as pooling resources, activation, and scalingout logical processing.

Develop the Server-Side Code
You must install the HTTP listener to develop and execute this server-side application successfully. You host the listener in the kernel-mode file Http.sys, which gets installed only with Windows XP Service Pack 2 and Windows 2003 editions.

SQL Server 2005 registers with the HTTP listener to use a portion of the URL namespaces. When the listener receives a request associated with one of its URLs, it routes the request directly to the endpoint defined by SQL Server 2005, which processes the payload and returns a response to the listener. The listener then forwards the response to the client. Note that this configuration doesn't require that you install IIS.

Begin by firing up the SQL Server Management Studio IDE and connecting to your local SQL Server 2005 instance. Next, open a query window and point it at the AdventureWorks sample database.

To keep things simple, the sample uses only two stored procedures. The first stored procedure gets a list of products and their prices, while the second creates a purchase order for the selected item type. The structure definitions of the business entities are in the Production.Product table, which represents the product entity; as well as in the Purchasing.PurchaseOrderHeader table and Purchasing.PurchaseOrderDetail table, which represent the PurchaseOrder entity.

Next, create the T-SQL application logic (see Listing 1). The first statement creates a stored procedure that selects the attributes productID, Name, and ListPrice for each product. The second statement creates a stored procedure that lets you receive as input parameters several important fields associated with the purchase order entity and inserting the data into the Purchasing.PurchaseOrderHeader table. The stored procedure then gets the auto-generated unique number associated with the row and inserts it into the related table Purchasing.PurchaseOrderDetail. Both tables are important to represent the schema of the purchase order business entity. Some attributes of this entity are calculated, including SubTotal (the subtotal) from the multiplication of nOrderQty (quantity of items ordered) and mnUnitPrice (the price for each item).

For SQL Server 2005 to be able to listen for SOAP requests, you must set up the database system as Web service application server, and then define the endpoints using the create endpoint statement that specifies the methods exposed, the message schema, and the binding mechanism to access.

The general syntax of the create endpoint statement is simple enough:

create endpoint endpoint_name 
	[authorization login]
	state={started | stopped | disable}
	as {tcp | http}
(
	<transport protocol specific setup>
)
for {soap | tsql | service_broker, 
	database_mirroring}
(
	<platform specific setup>
)

You can configure your endpoint in multiple ways. For more information, please refer to the SQL Server Online Help.

Now, it's time to create a SOAP endpoint and bind the two procedures to it. Go to the query window and enter the code to perform this task (see Listing 2). The syntax of the last statement begins with the create endpoint clause and gives a name to the endpoint (eprocurement_purchasing). The authorization setting specifies a valid login that is assigned ownership of the endpoint (the system administrator, in this case). If this value isn't specified, then the ownership is assigned to the caller. The state setting (started) declares the endpoint is listening and responding to requests.

The as http clause indicates that you need to use HTTP transport protocol for carry over messages. The URL for accessing this point is the concatenation of the site setting (the server name where the Web service is hosted) and the path (virtual path) setting. In this case, the URL is http://localhost/purchasing. In order to adapt this example to your own scenario you must replace the placeholder localhost with the actual server name. The authentication setting specifies the authentication method you need to use. There are a variety of authentication mechanisms available, including digest, basic, NTLM, Kerberos, and integrated--but try to use Kerberos, if possible. NTLM is an acceptable alternative. The ports setting specifies what port type to use. The clear option accepts only http requests and SSL requiring HTTPS.

The for soap clause determines which methods the Web service exposes, the message schema, and the binding mechanisms to use for exchanging information with the service. The webmethod setting specifies the Web methods and their underlying stored procedures and user-defined functions following the traditional naming schema database.owner.object_name. In this case, the SelectProduct Web method is associated with the spSelectProduct stored procedure, and the CreatePurchaseOrder Web method is associated with the sp-CreatePurchaseOrder stored procedure--both of which reside in the AdventureWorks database. The schema setting determines whether inline XSD schema will be returned in SOAP responses.

When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to this endpoint. You must grant connect permissions for other users:

Use master;

grant connect on 
	endpoint::eprocurement_purchasing
	to new_user;

Customers on non-Microsoft platforms that want to consume this Web service must connect using either BASIC or SQL Auth mechanisms. However, these mechanisms require securing the channel using the SSL protocol.

So far, you have exposed the application logic written in T-SQL as a Web service with a SOAP endpoint. The next step is to enable the customer to consume your service.

Develop the Client Side Components
To complete this solution you need to include several client-side components that interact with the server-side services. One component is the Windows Forms application that consumes the service through WCF technology. Open VS .NET, select File | New | Project, and navigate in the Project Types tree to the Windows node. Select Windows Application from Template, and then enter descriptive names for the project, solution, and directory where you'll store the underlying files.

Next, open a command window, go to the directory where the project files reside, and execute the "svcutil http://localhost/purchasing?wsdl" command that refers to the metadata description of the services. In this case, the service is hosted on the local machine. The svcutil.exe tool lets you create the WCF proxy classes, contracts, and configuration files for accessing the Web services. The WCF proxy creation isn't integrated with VS .NET, so you have to open a command window and call the svcutil.exe tool outside of your development environment.

Next, include the project the file that contains the generated proxy. Go to Solution Explorer, and click on the Show all Files and Refresh buttons to display the generated files. Right-click on the eprocurement_purchasing.cs file, and select Include In Project option. You must add a reference to the System.ServiceModel assembly containing the objects that implement the communication mechanism of WCF technology.

Finally, add an application configuration file (App.config) to your project, and copy all the content from output.config. The configuration file contains the information about the remote services, endpoints, address, contract, operations, and bindings. You need to customize this file to meet the integrated authentication requirement (see Listing 3).

The next step is to design the user interface. Add several Label controls to show descriptive messages to the user, and then add a ComboBox control to show several products for the user to choose from. When a user selects a product, the underlying price is updated automatically in the Label control, m_lbPrice. You should also add several TextBox controls to get the purchase order's critical data, a DateTimePicker control to gather the ship date, and a Button control to invoke the creation of a purchase order.

When the application's main form (Form1) is created through its constructor, the Web Service endpoint in SQL Server 2005 is invoked to get the list of products and the underlying information through the client proxy, objProxy, and its operation, SelectProduct. You use the .NET Framework Windows Forms databind mechanisms to bind properties of the Windows Forms Controls m_cbProducts (ComboBox) and m_lbPrice (Label) to properties of the object m_dtActualProduct that represent the business entity, Product (see Listing 4).

Invoking the creation of the purchase order requires that you implement the method button1_Click to handle the OnClick event associated with the command button. The business logic is straightforward. All you do is create an instance of the WCF proxy class eprocurement_purchasingSoapClient (objProxy) and call the operation CreatePurchaseOrder, passing it the appropriate parameter's values gathered from the main Windows Form. At this point, you're ready to fire up an instance of the application for testing the solution (see Figure 1).

Now you have a template for creating IOS-based apps using the principles of Service-Oriented Architecture (SOA), Service-Oriented Database Architecture (SODA), and Microsoft technologies specifically SQL Server 2005 and Windows Communication Foundation. The solution described is highly flexible, and you can adapt this article's example to fit a number of scenarios, including your own. For example, you might create your stored procedures using T-SQL and expose this business logic as a Web service to be consumed inside and outside your organization. Be sure to check out the online sample application and pick it apart for inspiration.

comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.