At the formal release of the Microsoft .NET Framework, Bill Gates promoted Web services by demonstrating the SQL Server 2000 Web Services Toolkit, which comes with XML for SQL Server 2000's third Web release (SQLXML 3.0). The Web Services Toolkit is a jewel, letting you graphically create a Web service that exposes database objects such as stored procedures and user-defined functions (UDFs). If you're handy at T-SQL and stored procedures, SQLXML 3.0 turns you into a Web services wizard with practically no learning curve.
SQL Server Magazine has chronicled SQLXML 3.0 since its inception. Brian Moran, for example, described how to get started creating Web services with the initial release of SQLXML 3.0 in his February 21, 2002, SQL Server Perspectives column, "Use the SQL Server 2000 Web Services Toolkit to Get Started with .NET." Since then, Microsoft has released two service-pack updates to SQLXML 3.0. In this article, I take you beyond the basics of building a Web service. The sample telephone-directory Web service application demonstrates how to invoke stored procedures and UDFs by passing parameters and capturing return values. Learning how to build the sample application will equip you to create flexible Web services solutions that expand the uses for existing SQL Server database objects. In addition, some organizations might find the sample application a quick and easy solution for an online telephone directory that runs behind a firewall.
Setting Up
When creating a Web service, you need a server component and a client component. This article tracks the development of a server component from its T-SQL origins through to its packaging by SQLXML 3.0. I also explore how a Visual Basic .NET (VB.NET) Windows Forms client application can use the Web service. You can download all the code necessary for the sample application along with the article's listings at InstantDoc ID 43366.
You can readily use Query Analyzer to create the T-SQL script for a Web service. The downloadable .sql file for this article (SSMWSEmpPhones) constructs a database and populates it with tables, stored procedures, and UDFs. After running the .sql file to create the sample database, invoke the SQLXML 3.0 IIS Virtual Directory Management tool that's available with a Microsoft Management Console (MMC) interface. This tool packages your database application so that it exposes its stored procedures, UDFs, and templates as Web methods for a Web service.
The IIS Virtual Directory Management tool lets you create a virtual directory that acts as a middle tier between your client component and database objects. Within your virtual directory, you make a Simple Object Access Protocol (SOAP) object that describes the Web service. You configure the SOAP object by selecting database objects that you want to expose as Web methods. The virtual directory can connect to the database through a login that you create with permissions appropriate for your Web service. At a minimum, the login must have access to the database that the Web service uses and EXECUTE permission for any database objects your application exposes as Web methods. Let's start by looking at selected elements of the T-SQL code behind the sample application.
T-SQL for a Telephone-Directory Application
The T-SQL code for the phone-directory application includes a set of database objects to let users look up fellow employees' telephone numbers. In addition, by entering their employee ID and password values, employees can use the application to change their own telephone numbers and first and last names—but not other employees' information. Users can also alter their own passwords but not other employees' passwords. An application administrator can modify anyone's password.
The downloadable T-SQL script starts by creating the SSMWSEmpPhones database. You need to update the location of the database file in the script if SQL Server is installed anywhere other than the default location on the C: drive. The script then creates and populates two tables. The EmpPhones table includes five columns for tracking each employee's employeeID number, first name, last name, phone number, and password. The CREATE TABLE script for EmpPhones includes three constraints: one for a primary key based on employeeID and unique and column check constraints for the phone column. A second table, AppVariables, holds the administrator's password.
The heart of the application's database component consists of scripts for three stored procedures and one UDF. The first stored procedure (LikeLname), which the following code creates, lets a user search for a phone number by typing in a string:
CREATE PROCEDURE LikeLname @Start nvarchar(40) AS SELECT Fname, Lname, Phone FROM EmpPhones WHERE Lname LIKE @Start + '%'
LikeLname can return the rows from EmpPhones that include a last name that starts with a user-supplied character string, such as a letter or a combination of letters. The procedure's @Start parameter lets the user specify a starting string.
Listing 1 shows the T-SQL script for the second stored procedure, UpdateEmpPhonesRow, which lets users update EmpPhones column values for first name, last name, and phone. By invoking the stored procedure, a user can update one, two, or three column values—although the stored procedure won't generate an error even if the user doesn't specify any changes. The @IDToEdit and @Password parameters in coordination with the Phone column values let the stored procedure determine whether any row in EmpPhones has an EmployeeID value that matches @IDToEdit and a Password value that matches @Password. The SELECT argument of the EXISTS operation at the top of the procedure identifies unique rows because Phone column values can't be null and they have a unique constraint.
If UpdateEmpPhonesRow finds a row that matches the @IDToEdit and @Password criteria, the procedure selectively tests for non-default values for @NewFname, @NewLname, and @NewPhone. Whenever the procedure detects a non-default value, it invokes the UPDATE statement to assign the non-default parameter value to the appropriate Fname, Lname, or Phone column value.
The telephone-directory application lets users change their passwords by editing the appropriate Password values in the EmpPhones table. Listing 2 shows a UDF and a stored procedure, which validate a new password and update the database with the new, validated password, respectively. By using the ValidateNewPW() UDF to return the new password as a scalar value, the application demonstrates how to expose a UDF as a Web method, capture its returned scalar value, and use that value as an argument for a stored procedure (UpdatePassword). Listing 2 includes the T-SQL script to create both database objects because they work together so intimately. You might have noticed that the application passes user ID and password information without encryption. To keep the examples for building a basic Web service simple, I didn't add encryption, but you'd want to add it to a production application.
Let's walk through the ValidateNewPW() UDF and the UpdatePassword stored procedure to see how they work together. The ValidateNewPW() UDF returns a valid new password if one of its input parameters matches a password column value in EmpPhones or is an administrator's password. An IF...ELSE statement that contains nested IF...ELSE statements implements the logic for the preceding T-SQL statement. The UDF returns the value of its @NewPassword parameter if a user correctly specifies his or her current password with the @OldPassword parameter. The current value of the administrator's password resides in the SuperPassword column of the AppVariables table. If the @OldPassword parameter value doesn't match any Password column value in EmpPhones and the @SuperPassword parameter value doesn't match the administrator's password, ValidateNewPW() returns a string value of Failed.
The UpdatePassword stored procedure, which updates the password validated by the ValidateNewPW() UDF, takes two parameters. The @IDToEdit parameter points to a unique row in EmpPhones. The reason for using this parameter instead of just the phone number is that anyone can know any other person's phone number, but you don't want everyone who knows the administrator's phone number changing the administrator's password, for example. The @NewPassword parameter specifies the new password for the row whose employeeID column value matches @IDToEdit.
Listing 3 shows the final bit of T-SQL code in the .sql file for the SSMWSEmpPhones database; this code sets up a login for use with the application. The login named SSMWSLogin has the minimum set of permissions necessary for running the Web service application because you don't want the Web service application users performing unauthorized functions on your database server. The minimum set of permissions for the Web service includes access to the SSMWSEmpPhones database and permission to execute the stored procedures and UDFs I described.
The Web Service Virtual Directory
Now that you have your database and objects built, the next step is to enable users to interface with the database through a Web service. The virtual directory for a Web service application is the middle tier between a database tier and a client tier. You can think of this middle tier as painting a Web services interface on your SQL Server database. Users can also pass parameters to middle-tier Web methods that perform tasks such as modifying a row in a table. Therefore, the virtual directory must know where the database is and which specific database objects to use for various tasks. The virtual directory must also know how to log in to the database because a login's permissions implicitly designate the Web service's scope of operations. In addition, the virtual directory must implement basic SOAP and Web Services Description Language (WSDL) functionality so that any Web service client can routinely interface with it.
To open the SQLXML 3.0 IIS Virtual Directory Management tool, choose SQLXML 3.0, Configure IIS Support from the Start menu. With this tool, you can either create a new virtual directory or edit the settings for an existing directory. Figure 1 shows the settings for this article's sample Web service on the General tab of a dialog box that lets you create and manage a virtual directory.
On the Security dialog box tab, you specify how Web service users will connect to the database tier of a Web service solution. Specify a login, such as the SSMWSLogin I described earlier, that has the minimum permissions necessary for running the application. This tactic gives you a tool to manage security as you add or drop database objects from an application. For example, I expanded the number of GRANT EXECUTE statements for SSMWSLogin as I exposed new database objects through the Web service solution.
Use the Data Source and Settings tabs to specify which database a virtual directory provides access to and how the virtual directory relates to the database (for this example, the SSMWSEmpPhones database). When setting up a virtual directory for a Web service, you need to select the Allow Post check box on the Settings tab (it's unselected by default). This box lets an application post values to a virtual directory and thereby communicate with the Web service. You can clear any other check boxes on the Settings tab without disrupting your Web service's operation. Note that the maximum size of a post must be large enough to accept the data that your client application passes to your Web service. By default, the maximum post is 100KB. Running empirical tests to find a smaller acceptable value can help you minimize your application's exposure to Denial of Service (DoS) attacks by reducing the amount of content users can pass to your Web service.
On the Virtual Names tab, select SOAP from the Type drop-down box to specify that your virtual directory contains a SOAP object for the Web service that the directory exposes. When you select a SOAP object, the tool automatically populates the Domain name box to match the name you gave the virtual directory on the General tab. However, you must manually enter the Web service name and the path for its folder. You use the Name text box to assign a name to your SOAP object. For example, this project assigns SSMWSSoap as the name for the SOAP object. In the Path text box, enter the local path for the Web service. The lower box in Figure 1 shows the local path I used as I set up the sample Web service for this article. Click Save to commit your settings for the SOAP object in the virtual directory, then click Configure.
Configuring a SOAP object primarily means designating the database objects that your Web service will expose and the format for the values the service will return to a client application. Figure 2 shows the Soap virtual name configuration
dialog box, with two stored procedures and one UDF already added. The LikeLname stored procedure is in the process of being selected as a Web method that has the same name. Use the Browse (...) button next to the SP/Template text box to select the name of a database object that you want to expose as a Web method. When you click the Browse button, the dialog box will show only the objects that the login on the Security tab has EXECUTE permission for.
The output format for the values returned to the client application depends on which Output as radio button you select as well as which database object you select to implement a Web method. The LikeLname stored procedure returns a result set, which the Web service represents as a dataset with one ADO.NET DataTable object corresponding to the result set. For this kind of output, you can choose the Dataset objects or Single dataset Output as radio button. Choosing the Dataset objects button lets you see the syntax for the client application to process multiple returned objects from a Web service. The other three database objects, which I've already selected, have their output formatted as XML objects. In the case of the ValidateNewPW Web method, selecting XML objects returns a scalar value as a string to a client application.
Creating a VB.NET Client
After completing a Web service, you'll frequently build a client to communicate with the Web service. The client application for the telephone-directory Web service consists of three Windows forms in the SSM0304 folder that corresponds to a VB.NET project of the same name. I used Visual Studio .NET 2003 to build the client application. The client application relates to the middle tier of the application through a Web reference. You add the Web reference to the project by choosing Project, Add Web Reference, then entering in the URL box the URL for the Web service followed by ?wsdl. Click Go. When Visual Studio navigates to the Web service and returns the service's description, click Add Reference to add a Web reference to the Visual Studio project for the Web service. Figure 3, shows the Add Web Reference dialog box for this application on my office network. You'll need to change the domain name from cab233a to whatever computer hosts the middle tier for your Web service.
You can run the client application on any computer that can connect to the computer that's running the Web service. Because you're using a Windows Forms client, the client computer needs to have the .NET Framework installed on it. The Microsoft Web page http://msdn.microsoft.com/netframework/technologyinfo/howtoget/default.aspx tells how to obtain the .NET Framework 1.1 Redistributable, which is available for free.
Figure 4 shows Form1, Form2, and Form3 from the client application. The main goal of Form1 is to show the column values for the rows whose Lname values begin with a given string, such as the letter D. Notice that the second row is selected in Form1. Clicking Edit selected row on Form1 opens and populates Form2 with the values from the Fname, Lname, and Phone columns of the row you selected in Form1. Form2 is primarily for editing those values; it requires users to validate their identities by entering the EmployeeID and password column values for the selected row. After correctly entering these values (as in Figure 4), a user can edit the values in the first three text boxes on Form2. Clicking Submit commits the new values in the text boxes to the EmpPhones table.
Clicking Validate new password in Form2 opens Form3 with automatically populated EmployeeID and Old Password text boxes from corresponding entries in Form2. After a user enters a string in the New Password text box and clicks Validate on Form3, the Validate button's Click event procedure assigns the value of the new password to a variable in Form2. If the user doesn't enter a valid value for the EmployeeID or Password text boxes in Form2 before clicking Validate on Form3, this click can still transfer a new password from Form3 to Form2. The user just needs to manually enter valid information in the EmployeeID, New Password, and Admin Password text boxes on Form3 before clicking Validate.
The Code Behind the Forms
Listing 4 shows the VB.NET code behind Form1. The module starts with the module-level declaration of a DataSet object. Within the btnStartswithlookup_Click procedure, two declarations are critical. The first is for a proxy variable, xws1, which points at the Web service through the Web reference. The second is an array of objects that's represented by the response variable. You'll need to replace cab233a in the proxy variable declaration with the domain name of the server hosting your Web service's virtual directory. Because you configured the LikeLname Web method to permit the return of multiple datasets, you need an array of objects to capture the Web method's output.
The rest of the code in the btnStartswithlookup_Click procedure applies the module-level and procedure-level declarations. A Try...Catch statement invokes the LikeLname Web method in the Try clause by passing the Text property value for TextBox1. The Catch clause prints diagnostic information for runtime errors. Besides catching runtime errors, using a Try...Catch statement can provide helpful diagnostic information as you debug an application. After the Try...Catch statement, an assignment sets the das1 variable equal to the first element in the response array. The procedure concludes by using the SetDataBinding method to bind the first (and only) DataTable object in the das1 DataSet to DataGrid1.
The btnEditselectedrow_Click procedure in Listing 4 begins by instantiating an instance of Form2 and opening the instance with the Show method. This method lets users switch between the parent form (Form1) and the child form (Form2) without having to close the child form. With the CurrentRowIndex property for DataGrid1, the procedure captures the index for the currently selected row. Before closing, the procedure assigns the Fname, Lname, and Phone columns from the selected row in DataGrid1 to TextBox controls on the Form2 instance.
Listing 5 displays the code behind Form2. This code builds on practices I used in the code behind Form1 but also involves new techniques. For example, the code starts with a Public Shared declaration for a variable named fldNewPassword. This type of declaration is an easy way to let a child form assign a value to a variable in its parent form. Form3 references the fldNewPassword variable to return the new password to Form2.
The btnSubmit_Click procedure behind Form2 primarily invokes the UpdateEmpPhonesRow method. Notice the syntax for assigning method arguments within the first statement of the Try clause. In particular, a Boolean variable set to True follows each Integer variable type. Without a value of True for each Integer variable, your method invocation fails. In addition, recall from Listing 1 that the method can pass back return values of -1 or 0. A value of 0 signals an invalid employee ID or password argument. The procedure recovers the return value as the first element in the response array and prints a diagnostic message if appropriate.
The btnValidate_Click procedure performs two functions. First, it opens Form3. Second, it copies TextBox property values from Form2 to Form3.
The btnUpdate_Click procedure behind Form2 branches to either invoke the UpdatePassword method or display a diagnostic message. The If clause in the If...EndIf statement that implements the branch is based on fldNewPassword's value. Form3 assigns a valid password to the fldNewPassword variable or gives the variable a string value of Failed. Form3 sets the variable to Failed when it can't validate the new password.
The sole procedure behind Form3, btnValidate_Click, appears in Listing 6. This procedure invokes the ValidateNewPW method, which is based on the ValidateNewPW() UDF that returns a string scalar value. The Web service returns this value as a string variable called response instead of as an element in an array. The procedure uses the value of the response variable to select one of the two radio buttons on the form. The selected button tells you whether the validation succeeded. The procedure concludes by assigning the value of response to the fldNewPassword variable in Form2.
Basing Web Services on Database Objects
By following this article's example, you can make the loosely coupled database, middle-tier, and client layers in a Web service application work together as one integrated solution. The SQLXML 3.0 IIS Virtual Directory Management tool constructs the middle tier as a virtual directory that refers back to objects in a database. In addition, you can create a SOAP object in a Web service, which exposes database objects as Web methods. This capability lets you leverage your T-SQL skills in Web service solutions. The client layer then handles data display and user interactivity, referring back to the database layer through the middle tier. By understanding the details of this article's sample solution, you're equipped to offer Web service solutions on any of your existing SQL Server databases.
