Better Living Through Endpoints

By Michael Coles

SQL Server 2005 makes creating efficient and secure T-SQLbased Web Services easier than ever. SQL Server’s newest T-SQL extensions allow you to expose plain old stored procedures and user-defined functions as HTTP/SOAP Web Service methods directly from your SQL Server. In this article I will discuss the basics of setting up HTTP/SOAP endpoints and accessing them from .NET client applications. Web Services SQL Server 2000 provided some support for Web Services through SQLXML 3.0 and IIS. The SQLXML method, however, required a lot of IIS configuration, making it a bit of a hassle just to get up and running. The .NET Framework simplified things considerably by allowing developers to quickly and easily create Web Services that could connect to SQL Server, execute stored procedures or user-defined functions, and return result sets in SOAP format. While .NET Web Services made development easier, they still involved a considerable amount of overhead in opening connections to SQL Server, sending requests, and retrieving and formatting the results. Figure 1 is a high level overview of the .NET-to-SQL Server Web Service model.

Figure 1. .NET-to-SQL Server Web Service Model

In this model the Web Service sits between the client and SQL Server accepting SOAP requests, connecting to SQL Server, executing T-SQL statements and queries, formatting SQL Server results using SOAP, and finally returning those results to the client. SQL Server 2005 has the ability to expose server-side stored procedures and user-defined functions as Web Service methods via HTTP/SOAP Endpoints. Figure 2 is a high-level overview of the SQL Server HTTP/SOAP Endpoint model.

Figure 2. SQL Server 2005 HTTP/SOAP Endpoint model

The SQL Server HTTP/SOAP Endpoint model “eliminates the middleman,” so to speak. In this model, SQL Server exposes HTTP/SOAP Endpoints that can be accessed as Web Service methods by Web Service consumers. HTTP/SOAP Endpoints have several advantages over both SQLXML and .NET Web Services, including the following: • HTTP/SOAP Endpoints allow your client applications to retrieve results directly from SQL Server, eliminating the overhead of explicitly opening, maintaining, and closing connections to SQL Server. • HTTP/SOAP Endpoints are very easy to set up, configure, and consume from client applications. • Security for HTTP/SOAP Endpoints is built into the SQL Server security model, making your web service methods more secure than ever. • Because it uses the HTTP API and Http.sys kernel mode driver, SQL Server can achieve better performance than Web Services hosted in the IIS 5.1 process space. For IIS 6 (on Windows Server 2003), this may not be as big an advantage since IIS 6 has built-in support for the HTTP API. Now that we have discussed SQL Server HTTP/SOAP Endpoints, it is time to actually create one. Defining the Methods As I mentioned previously, HTTP/SOAP Endpoints allow you to expose stored procedures and user-defined functions as Web Service methods. The first step is to create some stored procedures. The stored procedures and sample code in this article are designed to work in the AdventureWorks sample database. The first stored procedure, Sales.GetFullCustomerList, retrieves a full list of all customers from the AdventureWorks database. Listing 1 shows the Sales.GetFullCustomerList procedure.

Listing 1. The Sales.GetFullCustomerList stored procedure USE AdventureWorks;

GO
CREATE PROCEDURE Sales.GetFullCustomerList AS
BEGIN
— Get all “Individual” customers first
SELECT cust.CustomerID, cust.AccountNumber,
cust.CustomerType,
COALESCE(con.LastName, ‘’) + N’, ‘ +
COALESCE(con.FirstName, ‘’) + N’ ‘ +
COALESCE(con.MiddleName, ‘’) AS Name
FROM Sales.Customer cust
INNER JOIN Sales.Individual ind
ON cust.CustomerID = ind.CustomerID
INNER JOIN Person.Contact con
ON ind.ContactID = con.ContactID
WHERE cust.CustomerType = N’I’
UNION
— Union them with all “Store” customers
SELECT cust.CustomerID, cust.AccountNumber,
cust.CustomerType,
sto.Name
FROM Sales.Customer cust
INNER JOIN Sales.Store sto
ON cust.CustomerID = sto.CustomerID
WHERE cust.CustomerType = N’S’
ORDER BY Name;
END;
GO

This stored procedure simply retrieves the CustomerID, AccountNumber, CustomerType, and Name of all customers stored in the database. Retrieving AdventureWorks “Store” customer information (CustomerType = ‘S’) and “Individual” customer information (CustomerType = ‘I’) requires two separate queries, so this procedure performs both queries and UNIONs the results together. The second stored procedure, Sales.GetOrderHeaders, accepts a customer ID number and returns order header information for all orders placed by that customer. Listing 2 is the Sales.GetOrderHeaders procedure.

Listing 2. The Sales.GetOrderHeaders procedure

CREATE PROCEDURE Sales.GetOrderHeaders (@CustomerID INT)
AS
BEGIN
— Get order header information for passed-in customer ID
SELECT soh.SalesOrderID,
soh.OrderDate,
soh.TotalDue
FROM Sales.SalesOrderHeader soh
WHERE soh.CustomerID = @CustomerID;
END;
GO

The third and final stored procedure, Sales.GetOrderDetails, accepts a sales order ID number and returns all order detail lines for that order. Listing 3 is the code for the Sales.GetOrderDetails procedure.

Listing 3. Sales.GetOrderDetails procedure

CREATE PROCEDURE Sales.GetOrderDetails (@SalesOrderID INT)
AS
BEGIN
— Get order detail information for customer ID
SELECT prod.Name AS ProductName,
sod.OrderQty,
sod.UnitPrice,
sod.UnitPriceDiscount AS PercentDiscount,
sod.LineTotal
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product prod
ON sod.ProductID = prod.ProductID
WHERE sod.SalesOrderID = @SalesOrderID
ORDER BY sod.SalesOrderID, sod.SalesOrderDetailID;
END;
GO

These are very simple stored procedures, and the stored procedures in a real application could potentially be much more complex. You can include all of the normal T-SQL control-of-flow statements, multiple SELECT and/or DML statements, temporary tables, and even OUTPUT parameters. You can even return multiple result sets from a single stored procedure. For demonstration purposes, these simple stored procedures will suffice. Tip In addition to stored procedures, you can also expose scalar user defined functions as Web Service methods. You cannot, however, expose multistatement or inline table-valued functions. The work-around is to turn your table-valued function into a stored procedure that returns a result set. Once the stored procedures and/or user-defined functions are in place, it is time to expose them to clients through the power of HTTP/SOAP Endpoints. Creating an HTTP/SOAP Endpoint SQL Server 2005 introduces new statements to create, alter, and drop endpoints, using either the TCP or HTTP transport protocols. TCP Endpoints are used by database mirroring and Service Broker. However, we are concerned with HTTP Endpoints, which carry a SOAP payload type. To create an HTTP/SOAP Endpoint, we’ll use the CREATE ENDPOINT statement shown in Listing 4.

Listing 4.

CREATE ENDPOINT statement
CREATE ENDPOINT AwOrdersEndpoint
STATE = STARTED AS HTTP (
PATH = N’/AwOrders’,
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
CLEAR_PORT = 888,
SITE = N’*’)
FOR SOAP (
WEBMETHOD ‘GetFullCustomerList’
(NAME = N’AdventureWorks.Sales.GetFullCustomerList’,
FORMAT = ROWSETS_ONLY),
WEBMETHOD ‘GetOrderHeaders’(
NAME = N’AdventureWorks.Sales.GetOrderHeaders’,
FORMAT = ROWSETS_ONLY),
WEBMETHOD ‘GetOrderDetails’(
NAME = N’AdventureWorks.Sales.GetOrderDetails’,
FORMAT = ROWSETS_ONLY),
DATABASE = N’AdventureWorks’,
WSDL = DEFAULT);
GO

The first part of the CREATE ENDPOINT statement defines the general options for the endpoint as a whole. It looks like this:

CREATE ENDPOINT AwOrdersEndpoint
STATE = STARTED
AS HTTP (
PATH = N’/AwOrders’,
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR),
CLEAR_PORT = 888,
SITE = N’*’)

The T-SQL identifier for the endpoint is AwOrdersEndpoint. It is created in the STARTED state, meaning it will immediately begin actively listening for client connections. HTTP is defined as the transport protocol for this particular endpoint, and the HTTP-specific arguments are set as follows: • The SQL Server AUTHENTICATION argument is set to INTEGRATED. This argument indicates the user’s SQL Server login credentials. • The SITE is set to ‘*’, telling the endpoint to listen for all possible host names for the server that are not explicitly reserved. The ‘*’ SITE specification tells the endpoint to respond to requests whether the hostname portion of the address is ‘localhost’, ‘127.0.0.1’, the server’s IP address, the computer’s name, or even any Internet hostnames assigned to the server’s IP (like ‘www.mywebserver.com’). • The PATH argument specifies the absolute path portion of the endpoint address. Because it is an absolute path, it must begin with a forward slash. In this particular instance I have chosen to set the PATH to ‘/AwOrders’. • The PORTS argument for the example is set to CLEAR. It can also be set to SSL for secure HTTPS requests; or it can be set to a combination of (CLEAR, SSL), in which case the protocol used is determined by the client’s incoming request. • CLEAR_PORT is set to 888 in this case. The default value for this argument is 80, but I assigned a different port number to avoid conflicts with my local installation of IIS. These options specify the full path to the SQL Server-hosted endpoint. Once the endpoint is created, SQL Server will automatically generate a Web Services Description Language (WSDL) document which can be retrieved at this address. To see the WSDL document for this endpoint, simply point Internet Explorer to: http://localhost:888/AwOrders?wsdl The WSDL document generated looks like Figure 3.

Figure 3. WSDL Document for Sample Endpoint Tip

The WSDL document is a World-Wide Web Consortium (W3C) XML-SOAP standard for describing Web Service interfaces and methods. The WSDL format that SQL Server generates is a newer format that might not be compatible with legacy systems. To ensure backwards compatibility, SQL Server can also generate a “simple” WSDL document that follows an older standard. To access the “simple” WSDL document, append “?wsdlsimple” to the endpoint address. To use our sample from above, you could point IE to the following address to retrieve the “simple” WSDL document: http://localhost:888/AwOrders?wsdlsimple The FOR SOAP clause of the CREATE ENDPOINT statement specifies a SOAP payload type, specifies the methods to be exposed, and defines all of the SOAP-specific arguments. The FOR SOAP clause looks like this:

FOR SOAP (
WEBMETHOD ‘GetFullCustomerList’(
NAME = N’AdventureWorks.Sales.GetFullCustomerList’,
FORMAT = ROWSETS_ONLY
),
WEBMETHOD ‘GetOrderHeaders’(
NAME = N’AdventureWorks.Sales.GetOrderHeaders’,
FORMAT = ROWSETS_ONLY
),
WEBMETHOD ‘GetOrderDetails’
(
NAME = N’AdventureWorks.Sales.GetOrderDetails’,
FORMAT = ROWSETS_ONLY
),
DATABASE = N’AdventureWorks’,
WSDL = DEFAULT
);

Although there are many options that can be set in this section, I have kept it simple by going with the defaults where it makes sense. I start the SOAP-specific section by defining three Web Methods named ‘GetFullCustomerList’, ‘GetOrderHeaders’, and ‘GetOrderDetails’. Each of these Web Methods is assigned to a stored procedure by the NAME argument. I specifically set the FORMAT of each Web Method to ROWSETS_ONLY. This specifically excludes SQL Server informational messages from my result sets. Tip Unless you have a specific reason for doing otherwise, you should set the FORMAT for stored procedure Web Methods to ROWSETS_ONLY. If you leave it at the default value of ALL_RESULTS, SQL Server will return row counts, informational messages, and warnings with your result sets. Scalar user-defined functions, on the other hand, should be set to the default ALL_RESULTS format. After the definition of the Web Methods come the SOAPspecific arguments. In this instance I have set the following SOAP-specific options: • The DATABASE option is set, telling the Web Service to execute in the context of the ‘AdventureWorks’ database. • Setting WSDL to DEFAULT tells SQL Server to generate a default WSDL document for the Web Service. That is the basic setup and configuration for SQL Server HTTP/SOAP Endpoints. To review: 1. Create your stored procedures and/or scalar userdefined functions, 2. Use CREATE ENDPOINT to expose the procedures and user-defined functions as Web Methods. That is all there is to it! Of course, having a Web Service is cool and all, but it does not do you much good without a client application - a Web Service consumer - to actually use it. So the next step in our example is to build a functional C# Web Service consumer. The Web Service Consumer Creating Web Service consumers with Visual Studio 2005 is a relatively painless operation. I was able to get this example client up and running in about five minutes. The first step is to create a simple C# Windows Application. I will call this one the AdvWorksOrderViewer, as shown in Figure 4.

Figure 4. Creating the Windows Application project in Visual Studio

The second step is to add some controls to the Windows Form. For this sample I will add three main controls to the form: 1. a ComboBox to display a list of AdventureWorks customers, 2. a TreeView to show a list of orders for the selected customer, and 3. a DataGridView to show the order details for the selected order. Figure 5 shows the Windows Form with the controls added to it. See following page.

Figure 5. AdventureWorks Order Viewer with controls added

Adding a reference to the SQL Server HTTP/SOAP Endpoint is easy. Just right-click on the project name in the Solution Explorer and select Add Web Reference... from the pop-up menu. This step is shown in Figure 6.

Figure 6. Add Web Reference... from pop-up menu

Once the Add Web Reference window pops up, I entered the full URL to the HTTP/SOAP Endpoint WSDL document in the URL box, and then pressed GO. The URL for this sample is http://localhost:888/AwOrders?wsdl. The description box showed all the methods exposed by the endpoint. I also assign the endpoint a .NET Web Service proxy class name that I could access from within my .NET code. For this example I changed the default localhost to AwOrdersProxy. Finally, I hit Add Reference to add the Web Reference to the project. Figure 7 shows the Add Web Reference window.

Figure 7. Add Web Reference window

The final step is to use C# code to “wire up” the Windows Form to the HTTP/SOAP Endpoint. The Load event for the Windows Form creates an instance of the Web Service proxy class and assigns it the current user’s authorization credentials from System.Net.CredentialCache.DefaultCredentials. Then it calls the GetFullCustomerList() Web Method, and binds the result to the ComboBox. Listing 5 shows the Form Load event code.

Listing 5. Form Load event code

private void Form1_Load(object sender, EventArgs e)
{
AwOrdersProxy.AwOrdersEndpoint aop = new
AwOrdersProxy.AwOrdersEndpoint();
aop.Credentials = CredentialCache.DefaultCredentials;
cboCustomer.DisplayMember = @”Name”;
cboCustomer.ValueMember = @”CustomerID”;
cboCustomer.DataSource = aop.GetFullCustomerList().Tables[0];
}

The ComboBox on the form will be used to update the TreeView control. Whenever the user changes the ComboBox selection, the TreeView will be populated with a list of orders for the selected customer. To do this, we’ll hook into the ComboBox’s SelectedIndexChanged event. Like the Form Load event, this code starts by creating an instance of the Web Service proxy class and setting the authorization credentials. Then it calls the GetOrderHeaders Web method and populates the TreeView with the results.

Listing 6. ComboBox SelectedIndexChanged event code

private void cboCustomer_SelectedIndexChanged(object sender,
EventArgs e)
{
AwOrdersProxy.AwOrdersEndpoint aop = new
AwOrdersProxy.AwOrdersEndpoint();
aop.Credentials = CredentialCache.DefaultCredentials;
DataSet ds = aop.GetOrderHeaders((int)cboCustomer.SelectedValue);
tvwOrders.Nodes.Clear();
tvwOrders.Nodes.Add(cboCustomer.Text);
foreach (DataRow dr in ds.Tables[0].Rows)
{
TreeNode tn = new TreeNode();
tn.Text = @”#” + dr[“SalesOrderID”].ToString() + @”, “ +
string.Format(“{0:d}”, dr[“OrderDate”]) + @”, (“ +
string.Format(“{0:C}”, dr[“TotalDue”]) + @”)”;
tn.Tag = (int)dr[“SalesOrderID”];
tvwOrders.Nodes[0].Nodes.Add(tn);
}
tvwOrders.Nodes[0].ExpandAll();
ds.Dispose();
}

Changing the selection on the ComboBox updates the TreeView, and selecting an order on the TreeView updates the DataGridView with the details for that order. Similar to the other events, the TreeView’s AfterSelect event creates a Web Service proxy instance and sets the authorization credentials. It then calls the GetOrderDetails Web Method to retrieve a list of detail lines for the selected order and binds the results to the DataGridView.

Listing 7. TreeView AfterSelect event code

private void tvwOrders_AfterSelect(object sender, TreeViewEventArgs e)
{
if (tvwOrders.SelectedNode != null && tvwOrders.SelectedNode.Tag !=
null)
{
AwOrdersProxy.AwOrdersEndpoint aop = new
AwOrdersProxy.AwOrdersEndpoint();
aop.Credentials = CredentialCache.DefaultCredentials;
DataSet ds = aop.GetOrderDetails((int)tvwOrders.SelectedNode.Tag);
dgOrderDetails.DataSource = ds.Tables[0];
ds.Dispose();
}
else
{
dgOrderDetails.DataSource = null;
}
}
Figure 8 shows the HTTP/SOAP Endpoint client in action.

Figure 8. Running the HTTP/SOAP Endpoint client

Conclusion

SQL Server 2005 HTTP/SOAP Endpoints make exposing your T-SQL stored procedures and user-defined functions as Web Service methods a snap. The tight integration with SQL Server, T-SQL, and the HTTP API makes HTTP/SOAP Endpoints a secure, scalable, and efficient alternative to other methods of creating Web Services that access SQL Server. This article is designed to provide an introduction to SQL Server 2005’s HTTP/SOAP Endpoint model, including sample T-SQL and C# code. The full code download, including the T-SQL scripts from this article and the HTTP/SOAP Endpoint client example in both C# and VB versions, is available as a ZIP file from http://www.sqlserverstandard.com/downloads/200703/mcoles.zip.

Author Bio

Michael Coles is a SQL Server developer and .NET programmer. He is a regular contributor to SQL Server Central, and author of the Pro T-SQL 2005 Programmer’s Guide (http://www.amazon.com/gp/product/159059794X/ref=cm_arms_als_dp/105-4294834-4745231, Apress).