• Article

Sending SOAP Requests by Using Visual Studio 2005 Client (C#)

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

This topic describes a sample application in which a Visual Studio 2005 client sends SOAP requests to an instance of SQL Server and processes the SOAP response. The requests are sent for the endpoint methods created in Sample Applications for Sending Native XML Web Services Requests.

To use this application, we recommend that you be familiar with the conceptual and procedural information provided in Setting the Server to Listen for Native XML Web Services Requests and Writing Client Applications.

Note

This sample application requires some initial setup. Before you continue with the sample application, follow the steps described in Sample Applications for Sending Native XML Web Services Requests.

Sending SOAP Requests

In this application, you set up a form with the buttons and text boxes required to send SOAP requests to the endpoint.

When a SOAP request for the GetCustomerInfo stored procedure is sent, the Customer ID value provided in a text box is passed in as an input parameter. If no value is specified in the text box, an empty string is assumed, and customers that have an empty string as their CustomerID value are returned. If NULL (null) is entered, customers that have a CustomerID of NULL are returned. Note when this occurs, the xsi:nil = 'true' attribute is automatically added in the SOAP request to indicate a NULL parameter value.

The GetCustomerInfo stored procedure is designed to show how to handle input, output parameters, error messages, and results. The application also sends SOAP requests for the user-defined function. Additionally, it sends SOAP requests for the following ad hoc queries:

  • SELECT query to retrieve all employees.

  • SELECT query to retrieve an employee whose ID is provided as input.

Processing the SOAP Responses

When the results of a SOAP request are returned as an object array, the client application identifies the type of each object in the array and then processes the object accordingly. The application then displays the following information about each object array element:

  • The data type of the value in the array element.

  • The value: result set, return code, output parameter values, and so on.

Creating a Working Sample

Because of the many steps that you must follow to create this sample, the application is separated into five parts:

  • Part 1: Execute the GetCustomerInfo stored procedure

  • Part 2: Execute SP

  • Part 3: Execute SQL query with no parameters

  • Part 4: Execute SQL query with parameters

  • Part 5: Execute the user-defined function

Each part provides a set of instructions. At the end of each part, you can test the application.

Part 1: Execute GetCustomerInfo Stored Procedure

  1. On the client computer, from the Microsoft Visual Studio 2005 program group, start Visual Studio 2005.

  2. Click New Project.

  3. Select Visual C# Projects as the Project Type.

  4. Specify NativeSOAPApp1 as the project name

  5. Specify the location where you want to save the project.

  6. Select Windows Application as the template, and then click OK.

  7. In the Solution Explorer window, right-click References, and then select Add Web Reference. Another way to add a Web reference is from the Project menu, select Add Web Reference.

  8. In the Address box, type https://Server/sql?wsdl, and then press ENTER. Note that you must enter the server name in the URL.

  9. Click Add Reference. This creates the required proxy classes so that you can call the methods in the WSDL document.

Part 2: Execute SP

  1. On the View menu, click Toolbox to open the toolbox in the Form1.cs [Design] pane. Optionally, you can press CTRL + ALT + X to open the Toolbox.

  2. In Form1, add a text box (textBox1), a button (button1), and a list box (listBox1).

  3. Right-click the text box, and then select Properties. Change the Text value from textBox1 to 1. This is the default Customer ID value.

  4. Right-click button1, and then select Properties.

    1. Change the Text property value from button1 to ExecSP.

    2. Change the (name) property value to ExecSP.

  5. Right-click the list box (listBox1), and then select Properties. Change the HorizontalScrollbar property value to True.

  6. Double-click ExecSP.

  7. Copy the code from C# Code Listing for ExecSP to this function.

  8. Update the code. Change references to server by the host name identified when the endpoint was created by using CREATE ENDPOINT.

  9. Save and compile the project. For more information, see the previous section "Compiling the Code."

Part 3: Execute the SQL Query Without Parameters

This part of the client application executes an ad hoc query (FOR XML query) that retrieves employees from the Employee table in AdventureWorks2008R2 database.

  1. In Form1, in the [Design] tab, add another button (button1).

  2. Right-click this new button, and then select Properties.

    1. Change the Text property value from button1 to BatchQueryFindAllEmps.

    2. Change the (name) property value to BatchQueryFindAllEmps.

  3. Double-click BatchQueryFindAllEmps.

  4. Copy the code from C# Code Listing for BatchQueryFindAllEmps to this function.

  5. Update the code. Change references to server by the host name identified when the endpoint was created by using CREATE ENDPOINT.

  6. Save and compile the project. For more information, see the previous section "Compiling the Code."

Part 4: Execute the SQL Query with Parameters

This part is similar to the previous part except that the SOAP request for the ad hoc query includes a query parameter. The FOR XML query retrieves employee information for the specified employee ID.

  1. In Form1, in the [Design] tab, add a button (button1).

  2. Right-click this new button, and then select Properties.

    1. Change the Text property value from button1 to BatchQueryFindAnEmp.

    2. Change the (name) property value to BatchQueryFindAnEmp.

  3. Double-click BatchQueryFindAnEmp.

  4. Copy the code from C# Code Listing for BatchQueryFindAnEmp to this function.

  5. Update the code. Change references to server by the host name identified when the endpoint was created by using CREATE ENDPOINT.

  6. Save and compile the project. For more information, see the previous section "Compiling the Code."

Part 5: Execute the User-defined Function

In this part, the client application sends a SOAP request for the UDFReturningScalar Web method. This Web method corresponds to a user-defined function that returns an integer value.

  1. In Form1, in the [Design] tab, add a button (button1).

  2. Right-click this new button, and then select Properties.

    1. Change the Text property value from button1 to ExecUDFReturningScalar.

    2. Change the (name) property value to ExecUDFReturningScalar.

  3. Double-click ExecUDFReturningScalar.

  4. Add the code in C# Code Listing for ExecUDFReturningScalar to this function.

  5. Update the code. Change references to server by the host name identified when the endpoint was created by using CREATE ENDPOINT.

  6. Save and compile the project. For more information, see the previous section "Compiling the Code."

  7. Click ExecUDFReturningScalar. The return value from the user-defined function is displayed in the list box.

Because the endpoint specifies Integrated Authentication, the following line appears in the code: proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;

If you use SQL Server authentication, we recommend that remove this line and replace it with code that implements WS-Security headers to provide SQL Server-based authentication credentials. For more information, see SQL Server Authentication over SOAP.

Note

If you are using either SQL Server authentication or Basic authentication, SQL Server requires that you use Secure Sockets Layer (SSL) for secure encrypted transmission of user credentials that would otherwise be visible as clear text. For more information about how to enable SSL to use with HTTP endpoints, see Configuring Certificate for Use by SSL.

Compiling the Code

SQL Server installs the .NET Framework 2.0 as part of its installation process. These compilation instructions use the latest .NET Framework. (This is the highest number in the \WINDOWS\Microsoft.NET\Framework folder.)

To compile the sample code

  1. Save the whole project.

  2. Open command prompt, and locate the folder where the project is saved.

  3. From this folder, copy the Reference.cs file from Web Reference\Server subfolder. (For example, copy "Web Reference\Server\Reference.cs".) The Form1.cs and the Reference.cs files should be in the same folder.

  4. Compile the code and specify the name of your executable (.exe) file. For example, if the name of the executable is NativeSOAPApp1.exe, the command line would be as follows:

    \WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\csc.exe /out:NativeSOAPApp1.exe Form1.cs Reference.cs

    xxxxx is the folder number that corresponds to the version of the .NET Framework.

    This creates the executable (NativeSOAPApp1.exe) in the current directory.

If you using an earlier version of the .NET Framework, you may receive a run-time error. If this occurs, try updating the GetCustomerInfo stored procedure by adding the FOR XML AUTO clause at the end of the SELECT statement as shown:

SELECT TOP 3 SalesOrderID, OrderDate 
FROM   SalesOrderHeader
WHERE  CustomerID = @CustomerID
FOR XML AUTO

The results of executing the stored procedure are displayed in the list box.