"Create Interoperable Native Web Services" by Roger Jennings
Visual Studio Magazine, October 2005

Readme.txt File for the NativeWebServices.sln and CustomWSDL.sln Visual Studio 
2005 Visual Basic projects. These projects have been tested under Windows 2003
Server SP-1 and Windows XP SP-2 with SQL Server 2005 Standard Edition June CTP.

CAUTION: DON'T REFRESH WEB REFERENCES BEFORE READING STARTUP OPERATIONS BELOW. 
IF YOU REFRESH OR RECREATE THE WEB REFERENCES, YOU MUST ADD AN ATTRIBUTE TO
SEVERAL FUNCTIONS OF REFERENCE.VB.

A. System Requirements:

1. Windows 2003 Server or Windows XP SP-2 is required to provide the Http.sys
listener.

2. Visual Studio 2005 Beta 2 or a later Community Technical Preview that's compatible
with SQL Server 2005 June CTP or later or

3. A Visual Basic Express (VBX) edition that's compatible with SQL Server 2005 June 
CTP or later. You won't be able to autodeploy the GenerateWSDL stored procedure from 
the CustomWSDL class if you use VBX.

4. SQL Server 2005 Developer Edition or higher and June CTP or later. Sql Server
2005 Express Edition doesn't support HTTP endpoints.

5. The Northwind.mdf sample database generated from the Instnwnd.sql script that's
part of the SQL Server 2000 Sample Databases download (SQL2000SampleDb.msi from 
http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en.) 

6. Microsoft SQLXML 3.0 Servic Pack 3 (sqlxml.msi) downloaded and installed from 
http://www.microsoft.com/downloads/details.aspx?FamilyID=51D4A154-8E23-47D2-A033-764259CFB53B&displaylang=en

7. SQL Server Management Studio (SSMS) or SQL Server Express Manager (XM).

The demonstration projects are designed for operation on a single test server. All
database connections use Server=localhost and Web service URLs start with 
http://localhost/... , which you can change, if you want. Search for "localhost"
in the code.

You must be a member of the Windows local Administrators group and a member of the
SQL Server 2005 sysadmins group to install and run the project.

B. Startup Operations

1. Expand the two project .zip files to folders named ...\NativeWebServices and
...\CustomWSDL.

2. The three Web references WSNwindDS (for the NorthwindDS endpoint), WSNwindEP 
(for the NorthwindEP endpoint), and WSNwindSqlXml (for the manually-added soap
virtual name) must be present to permit building the sample project. Don't refresh
the Web references until you add the services.

3. Open the NativeWebServices.sln project and press F5 to build and run it. You'll
receive an exception if the application can't connect to SQL Server 2005 on 
localhost.

IMPORTANT: If you change "localhost" to another host name, you must fix the
SITE = 'localhost' attribute in the CREATE ENDPOINT statements in the following two 
steps.

4. Click the Edit SOAP Endpoints tab and, with the NorthwindDS radio button (default)
selected, click Update Endpoint to create the NorthwindDS endpoint.

5. Select the NorthwindEP radio button, mark the Substitute Simple WSDL check box,
and click Update Endpoint to create the NorthwindEP endpoint.

6. If you changed from localhost, delete and recreate the WSNwindDS and WSNwindEP 
Web references. Use http:\\ServerName\WSNwindDS?wsdl and 
http:\\ServerName\WSNwindEP?wsdlsimple as the Add Web Reference URLs.

Note: You don't need to recreate or refresh the Web references for localhost. The
following SQLXML 3.0 installation steps are optional.

7. Install SQLXML 3.0 SP-3 and run \Programs\SQLXML 3.0\Configure IIS Support to
open the IIS Virtual Directory Management for SQLXML 3.0 MMC snap-in.

8. Expand the ServerName node, right-click Default Web Site, and choose 
New Virtual Directory to open the New Virtual Directory properties dialog.

9. On the General page, type SqlXml30 as the Virtual Directory Name, click the 
Local Path's builder button and create a new folder for the Vdir, such as 
\InetPub\SqlXml30.

10. On the Security page, type a login name and password for a Windows login or an
SQL Server sysadmins account. If you're using localhose you can use your Windows
Administrators account.

11. On the Data Source page, click the bulder button and select the ServerName
for localhost, and then select Northwind as the database. (This assumes you 
installed the sample database previously.)

12. On the Settings page, mark the Allow POST check box.

13. On the Virtual Names page, select <New Virtual Name>, type NwindWS as the 
Name, select soap [sic] as the type, set the path to the folder you added in step
9, type NwindWS as the Web Service Name, and the computer name as the Domain Name.

14. Click Configure to open the Soap [sic] Virtual Name Configuration dialog, 
select <New method mapping>, and accept SP (Stored Procedure as the type).

15. Click the Builder Button and select CustOrderHist, type CustomerOrderHistory
as the Method Name, select Row Formatting: Raw and Output As: Data Objects, 
mark Return Errors as SOAP Faults, and click Save.

16. Repeate step 15 for SalesByCategory (SalesByCategory), Employee Sales by 
Country (EmployeeSalesByCountry), and Ten Most Expnsive Products
(TenMostExpensiveProducts).

17. Click OK twice to close the dialogs and close the snap-in.

18. If the server is remote, delete and recreate the WSNwindSqlXml Web reference
with http://ServerName/sqlxml30/nwindws?wsdl as the URL.

If you recreated or refreshed the Web references, you must add the 
<snoopattribute()> attribute to the following functions of Reference.vb:

Class NorthwindDS and Class NorthwindEP: 
Public Function sqlbatch, Public Function CustomerOrderHistory,
Public Function EmployeeSalesByCountry, Public Function SalesByCategory,
Public Function TenMostExpensiveProducts

Class NwindWS (NwindSqlXml30)
Public Function CustomerOrderHistory, Public Function EmployeeSalesByCountry, 
Public Function SalesByCategory, Public Function TenMostExpensiveProducts

Add the attribute to each of the preceding functions, as typified by:

<SnoopAttribute()> _
<System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://ServerName/SqlXml30/NwindWS/CustomerOrderHistory", RequestNamespace:="http://OAKLEAF-MS16/SqlXml30/NwindWS", ResponseNamespace:="http://OAKLEAF-MS16/SqlXml30/NwindWS", Use:=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle:=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)> _
Public Function FunctionName(<System.Xml.Serialization.XmlElementAttribute(IsNullable:=True)> ByVal CustomerID As String) As <System.Xml.Serialization.XmlArrayItemAttribute("SqlResultCode", GetType(Integer), [Namespace]:="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types/SqlResultStream", IsNullable:=False), System.Xml.Serialization.XmlArrayItemAttribute("SqlXml", GetType(System.Xml.XmlNode), [Namespace]:="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types/SqlResultStream", IsNullable:=False), System.Xml.Serialization.XmlArrayItemAttribute("SqlRowSet", GetType(System.Data.DataSet), [Namespace]:="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types/SqlResultStream", IsNullable:=False), System.Xml.Serialization.XmlArrayItemAttribute(GetType(SqlMessage), [Namespace]:="http://schemas.microsoft.com/SQLServer/2001/12/SOAP/types/SqlResultStream", IsNullable:=False)> Object()

The <SnoopAttribute()> attribute intercepts, formats, and displays the SOAP
request and response messages on the second tab page.

C. Using the NativeWebServices Project

1. Click the Return DataSets or SqlRowSets tab and, with the Native Web Services
(Complex Schema) radio button selected, click each of the four stored-procedure 
Web method radio buttons to test the NorthwindDS endpoint and its Web methods, 
which display the data from their SOAP response message in a DataGridView.

2. Select the Ad Hoc Batch Query radio button and click Execute to execute the
SQL statement from the text box.

3. Click the SOAP Messages tab to display the SOAP request and response messages 
for the last-executed Web method.

4. Repeat steps 1 through 3 with Native Web Services (Simple Schema) selected. In
this case the text box displays the SqlRowSet XmlNode elements.

5. If you installed and configured SQLXML 3.0, select the SQL XML 3.0 SOAP Virtual
Name option and repeat steps 1 and 2. The Ad Hoc Batch Query option is disabled.

6. Compare execution times with the high-resolution timer provided by the 
stopwatch.vb class.

7. Edit the NorthwindDS or NorthwindEP endpoint definition code and click Update
Endpoint to recreate either endpoint. When you click Update Endpoint, the WSDL 
file generated appears on the View Dynamic WSDL page.

The View Static WSDL page displays the local copy of the WSDL document for the
endpoint you select in step 7.

D. Installing and Evaluating the GenerateWSDL SQLCLR Stored Procedure

1. Open the SQL Server 2005 Surface Area Configuration tool, select Surface Area
Configuration for Features, select CLR Integration and mark the Enable CLR
Integration check box, if it isn't marked.

2. Open the CustomWSDL.sln SQL Server project in VS 2005, and build and deploy it.

3. If you're using VBX, execute the following script (InstCustWSDL.sql) from
XM or SSMS:

USE Northwind 
GO

-- Drop GenerateWSDL procedure if it exists.
IF (SELECT count(*) FROM sysobjects WHERE name = 'GenerateWSDL') = 1 
DROP PROCEDURE GenerateWSDL
GO

-- Drop CustomWSDL assembly if it exists.
IF (SELECT count(*) FROM sys.assemblies WHERE name = 'CustomWSDL') = 1 
DROP ASSEMBLY CustomWSDL
GO

-- Update the path to the compiled assembly as necessary.
CREATE ASSEMBLY CustomWSDL FROM 'E:\Fawcette\SOAP\CustomWSDL\bin\Debug\CustomWSDL.dll'
GO 

-- Create a stored procedure to map to the common lanugage
-- runtime (CLR) method As with any other SQL Server stored procedure
-- that maps to a CLR method, the actual stored procedure name 
-- ('GenerateWSDL') can be arbitrarily specified.
CREATE PROCEDURE GenerateWSDL
(
@EndPointID as int,
@IsSSL as bit,
@Host as nvarchar(256),
@QueryString as nvarchar(256)
@UserAgent as nvarchar(256)
)
AS EXTERNAL NAME CustomWSDL.[MSSql.CustomWSDL].GenerateWSDL
GO

-- USE master
-- GO
-- Follow the security guidelines set up for your environment.
-- The following example is meant to be used for development or 
-- testing purposes only (and won't run from Northwind).
-- GRANT EXEC on myWSDL to [PUBLIC]
-- GO

Note: You can verify the presence of the GenerateWSDL stored procedure by 
expanding Object Explorer's Databases\Northwind\Programmability\Stored Procedures
node and verifying the presence of dbo.GenerateWSDL.

4. Click the Edit SOAP Endpoints tab, scroll to the WSDL = DEFAULT parameter,
and change it to WSDL = 'Northwind.dbo.GenerateWSDL' to substitute the GenerateWSDL
SQLCLR stored procedure.

5. Mark the Substitute Simple WSDL Document check box and click Update Endpoint.

6. Open IE, type http://localhost/wsnwindep?wsdleverett as the address, and 
verify that the stored procedure returns the appropriate WSDL file.

7. Repeat step 6 with http://localhost/wsnwindep?wsdljbuilder, 
http://localhost/wsnwindep?wsdleglue, http://localhost/wsnwindep?wsdl, and
http://localhost/wsnwindep?wsdlsimple.

Watch for a future illustrated version of this Readme.txt file at 
http://www.oakleaf.ws/nativewebservices/

--rj


