Dela via

  • Artikel

Sending SOAP Requests by Using Visual Studio 2005 Client (Visual Basic)

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.

The example in this topic is similar to the example provided in Sending SOAP Requests by Using Visual Studio 2005 Client (C#). The only difference is that this example is written in Visual Basic. Therefore, only the required code and the steps to create the example are provided in this topic.

Creating a Working Sample

Because of the many steps in creating this sample, the application is separated into four parts:

  • Part 1: Execute the stored procedure.

  • Part 2: Execute the SQL query without parameters.

  • Part 3: Execute the SQL query with parameter.

  • Part 4: Execute the user-defined function.

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

To create the required proxy class

  1. Create a folder on the computer for this application.

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

  3. Click New Project.

  4. Select Visual Basic Projects as the Project Type.

  5. In the Name text box, specify NativeSOAPApp2.

  6. In the Location text box, specify a folder path where you want to save the project.

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

  8. 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, and select Add Web Reference.

  9. In the Address box, type https://Server/sql?wsdl, where Server is the name of the server, and then press ENTER. If the WSDL generation succeeds, you will see the endpoint description. At this point, click AddReference. This creates the required proxy classes so that you can call the methods in the WSDL document.

Part 1: Execute the Stored Procedure

In this part, the application requests the GetCustomerInfo Web method.

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

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

  3. 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.

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

  5. Double-click ExecSP.

  6. Copy the code from Visual Basic Code Listing for ExecSP to this function.

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

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

Part 2: Execute the SQL Query Without Parameters

In this part client application executes an ad hoc query (FOR XML query) that retrieves three 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 ExecBatchFindAllEmps.

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

  3. Double-click ExecBatchFindAllEmp.

  4. Copy the code from Visual Basic Code Listing for FindAllEmpsto 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 3: 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 the 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 ExecBatchFindAnEmp.

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

  3. Double-click ExecBatchFindAnEmp.

  4. Copy the code from Visual Basic Code Listing for FindAnEmp 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 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 the 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 ExecUDFReturningScalar, and

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

  3. Double-click ExecUDFReturningScalar.

  4. Copy the code from Visual Basic Code Listing for ExecUDF 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."

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 you remove this line and replace it with code that implements WS-Security headers to the provided 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 code

  • Save the whole project.

  1. Open a command prompt and locate the folder where the project is saved.

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

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

    \WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx\vbc.exe /out:NativeSOAPApp2.exe /r:System.dll /r:System.Data.dll /r:System.Xml.dll /r:System.Web.Services.dll /r:Microsoft.VisualBasic.dll /r:System.Drawing.dll /r:System.Windows.Forms.dll /m:Form1 Form1.vb Reference.vb

    xxxxx is the folder number corresponding to the version of the .NET Framework.

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

    Note

    If you are 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.