’Hide ColumnValue text box and label.
TextBox4.Visible = False
Label4.Visible = False
End Sub
Private Sub Button1_Click(ByVal sender As System.Object,
_
ByVal e As System.EventArgs) Handles Button1.Click
’Hide ColumnValue text box and label.
Label4.Visible = False
TextBox4.Visible = False
’Pass database name and table name from text boxes on
’the form to the RowCount Web method.
Dim adbname As String = TextBox1.Text
Dim atablename As String = TextBox2.Text
Dim myRowCount As Integer = _
xws1.RowCount(adbname, atablename)
’Make the RowCount label and text box visible
’before populating the text box with a value
’from the RowCount Web method.
Label3.Visible = True
TextBox3.Visible = True
TextBox3.Text = myRowCount.ToString
End Sub
Private Sub Button2_Click(ByVal sender As System.Object,
_
ByVal e As System.EventArgs) Handles Button2.Click
’Hide RowCount text box and label.
Label3.Visible = False
TextBox3.Visible = False
’Pass database name and table name from text boxes on
’the form to the RowCount Web method.
Dim adbname As String = TextBox1.Text
Dim atablename As String = TextBox2.Text
Dim myRowCount As Integer = _
xws1.RowCount(adbname, atablename)
’Print out the maximum number of rows as part of a pr
ompt
’for a selected row from a user.
Dim strInputMsg = _
"What row to max. of " & myRowCount.ToString & "?
"
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Dim intReturnedRow As Integer = _
CInt(InputBox(strInputMsg, "", "1"))
’Pass database name and table name memory values to t
he
’ColumnValues Web method and strip off leading string
’for column values.
Dim myColumnValues As String = _
xws1.ColumnValues(adbname, atablename)
Dim intToColon = InStr(myColumnValues, ":")
Dim str1 = Mid(myColumnValues, intToColon + 2, _
Len(myColumnValues))
’Dimension array and integer variable for loop.
Dim myVector(myRowCount - 1) As String
Dim intRow As Integer
’Pass string of column values to an array.
For intRow = 0 To myRowCount - 1
myVector(intRow) = _
str1.substring(0, InStr(str1, ",") - 1)
str1 = Mid(str1, InStr(str1, ",") + 2, Len(str1))
Next
’Make ColumnValue label and text box visible before
’passing array value corresponding to user selection
in
’the text box.
Label4.Visible = True
TextBox4.Visible = True
TextBox4.Text = myVector(intReturnedRow - 1)
End Sub
The SQL Se rver 2 0 0 0 W eb Ser vices Toolk it
The Web Services Toolkit simplifies the creation of Web services
based on SQL Server 2000 database objects and templates in IIS
virtual directories. Microsoft built on an earlier approach for
delivering XML functionality from SQL Server with the Web Services
Toolkit— namely, by extending the capability of the IIS virtual
directory so that it can host a Web service. The Web service from
an IIS virtual directory exposes individual database objects and
templates as Web m ethods.
After the creation of a Web service based on an IIS virtual
directory, you still use the same basic approach demonstrated in
the preceding two sections for developing a client application for
your Web service. This section starts by revealing how to design an
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
IIS virtual directory to offer a Web service. The design of the virtual
directory specifies the Web service based on a stored procedure.
The review of a core client application and a simple extension of it
equip you with the skills to build your own solutions for capturing
XML fragm ents returned from Web methods based on database
objects and templates.
Scripting a SQL Se r ver User for a Virt ual Direct ory
Although it isn’t essential to designate a SQL Server user when
specifying an II S virtual directory, it can be useful— especially when
the virtual directory hosts a Web service. Any Web service
emanating from an IIS virtual directory can have a potentially large
number of users. By using a special SQL Server user, you can set
the permissions for the special SQL Server user and be sure that
anyone who connects to the Web service will have permission to
perform the tasks enabled through the exposed Web methods. You
can also limit the ability to perform tasks through the Web service
by lim iting the permission for its special SQL Server user.
N ote
The .NET Framework contains standard security conventions,
including techniques for managing the use of encryption that
your applications m ay require for protecting a user’s identity,
managing data during transm ission, and authenticating data
from designated clients. See the “Cryptography Overview”
topic in the Visual Studio .NET documentation for more detail
on this topic. This topic is a major section within the
“Security Applications” topic, which you might also want to
review.
The following T-SQL script is meant for you to run from Query
Analyzer for the SQL Server 2000 instance that you use for the
remaining samples throughout this chapter. The script is available
among the book’s sample files as ScriptsFor13.sql. The sample is
built around the notion that this is the local SQL Server 2000
instance. If this isn’t the case, you’ll need to adjust the sample
accordingly. The script drops any prior SQL Server login for the
connected SQL Server instance and a prior user for the Northwind
database named vbdotnet1. If you incur error messages because
the user doesn’t exist, simply ignore them because the purpose of
the script is to remove a login or user only if it does exist. After
making sure vbdotnet1 is free for assignment, the script adds a new
user named vbdotnet1 and grants access to the Northwind
database. Recall that the Northwind database is one of the SQL
Server sample databases. The database’s public role grants any
user access to m ost database objects that ship as part of the
database. For exam ple, vbdotnet1 has automatic permission to run
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
all stored procedures, such as the Ten Most Expensive Products
stored procedure, which is one of the built-in user-defined stored
procedures for the database.
Notice that this script uses “/ * ” to mark the beginning of the code
com m ent that stretches over multiple lines, and “* / ” to end it.
/*Run from member of sysadmin fixed server role.
Ignore errors if user does not already exist.
*/
USE Northwind
EXEC sp_revokedbaccess ’vbdotnet1’
EXEC sp_droplogin @loginame = ’vbdotnet1’
GO
Add vbdotnet1 user with known permissions.
EXEC sp_addlogin
@loginame = ’vbdotnet1’,
@passwd = ’passvbdotnet1’,
@defdb = ’Northwind’
EXEC sp_grantdbaccess ’vbdotnet1’
GO
Building a W eb Service in an I I S Virt ua l D irectory
Now that we have a SQL Server user, we can proceed through the
steps for creating an IIS virtual directory. This directory will contain
the contract for a Web service. You can create a new IIS virtual
directory by choosing Programs from the Windows Start menu, then
SQLXML 3.0, and then Configure IIS Support. This opens the IIS
Virtual Directory Management utility for SQLXML 3.0. In order to
open the utility, you must, of course, have already installed Web
Release 3 (SQLXML 3.0). See the “Web Services from the Web
Services Toolkit” section for a URL to download Web Release 3
along with the Web Services Toolkit.
With the IIS Virtual Directory Management utility open, expand the
folder for the local Web server. Then right-click Default Web Site
within the local Web server, choose New, and then choose Virtual
Directory. This opens a multi-tabbed dialog box that lets you set the
properties of a new virtual directory. You can use the New Virtual
Directory Properties dialog box to create the virtual directory by
following these instructions:
1. On the General tab, name the directory Chapter13, and give
the virtual directory the path c: \ inetpub\ wwwroot\ Chapter13.
You can type the path or use the Browse button to navigate to
the folder. Although the utility allows you to create a new
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
folder from within the utility, som e may find it easier to create
the folder before opening the utility.
2. On the Security tab, select the SQL Server radio button. Then
enter vbdotnet1 in the User Name text box and
passvbdotnet1 in the Password text box. Confirm the
password before moving off the tab.
3. On the Data Source tab, accept the default settings of the
local SQL Server and default database for the current login.
4. On the Settings tab, leave Allow Template Queries selected
and also select Allow POST.
5. On the Virtual Names tab, you set up the virtual directory
through which you can deliver Web services. With < New
virtual name> highlighted in the Defined Virtual Names list
box, enter SoapFor13 in the Name text box. Then select soap
from the Type list. Next, in the Path text box, enter the path
for your virtual directory, namely
c: \ inetput\ wwwroot\ Chapter13. Finally click Save to enable
the configuration of your Web service associated with the
SoapFor13 virtual name.
Once you’ve clicked Save, the Configure button is enabled.
6. While still in the Virtual Names tab, click Configure (see Figure
13-13) to select SQL Server stored procedures and user-
defined functions to expose as Web methods. You can also
expose templates through the Web service. Although your
database objects and templates m ust exist before you can
expose them, the Web Services Toolkit doesn’t expose them
until you explicitly configure it to make the Web service offer
Web methods based on a stored procedure, user-defined
function, or template.
Figu re 1 3 - 1 3 . The V irt ual Nam e t ab for t he New Virtual Dire ctory
Proper t ie s dialog box for the SoapFor 1 3 W eb service in the
Chapt er1 3 virt ual dir ect ory.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
7. After you click Configure, the Soap Virtual Name Configuration
dialog box opens so that you can specify items to expose as
Web methods. If you are going to expose a stored procedure
or a user-defined function, designate SP as the Type;
otherwise, select Template to designate a template as the
source for a Web method. You can designate an item by using
the Browse button (…) to browse sources for a Web m ethod in
the Web service hosted by the virtual directory. By clicking
the Browse button with SP selected as the Type, I was able to
pick Ten Most Expensive Products as the source for a Web
method. I accepted the default selection to return the result
set from the stored procedure as XML objects. With this
selection, you can retrieve m ultiple results (or just one) from
a stored procedure. Figure 13-14 shows the dialog box just
before I click Save to expose the stored procedure as a Web
method.
8. Click OK to save the configuration of the Web Service and
close the Soap Virtual Name Configuration dialog box.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figu re 1 3 - 1 4 . The Soap Virtua l N a m e Con figu rat ion dia log box
displaying the settings for t he Ten_ M ost_ Ex pen sive_ Product s W eb
m ethod just before saving them .
You can improve your debugging process by disabling various
caching options.
9. Click the Advanced tab in the New Virtual Directory Properties
dialog box. Consider selecting all three options for disabling
different types of caching. These selections improve the
operation of your Web service, but the caching can be
distracting in some debugging and code updating operations.
10. Click OK to save the settings you’ve chosen and close
the New Virtual Directory Properties dialog box.
Now you’re ready to test the Web service. After you finish
debugging and refining your Web service, restore the caching
features because they speed up the operation of a Web
service in normal operation.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
N ote
The book’s sample files include the Chapter13 virtual
directory folder for reference’s sake. In order to create the
virtual directory so that you can manage it and use it, you’ll
need to follow the instructions for its creation in this section.
When a subsequent section edits the SoapFor13 Web service
hosted by the Chapter13 virtual directory, you’ll need to
follow the steps for that as well.
Building a Client Applica t ion t o Show an XML Fragm ent
Web services created with the SQL Server 2000 Web Services
Toolkit don’t have a built-in test interface. In addition, you connect
them to a client application slightly differently than Web services,
which you build directly with Visual Studio .NET. Nevertheless, the
broad outline of the testing process with a client application is
similar. In both cases, a .wsdl file formally defines the Web service
and specifies any input and outputs associated with individual Web
methods. I n addition, you must create a Web reference in the client
application that points at the Web service.
Create a new form named Form3 in the XMLWebServiceClients
project. Add two label controls. Size the form and controls about as
they appear in Figure 13-15 later in this section. (The form is also
available in the XMLWebServiceClients project among the book’s
sample files.) The arrangement and sizing of the form and its
controls are intended to accommodate the display of the entire XML
fragment returned by the Ten_Most_Expensive_Product s Web
method. Make Form 3 the startup object for the
XMLWebServiceClients project so that the form opens when you
start the project.
In the module behind Form 3, add a Web reference to the
SoapFor13 Web service by choosing Add Web Reference from the
Project menu. In the address box of the Add Web Reference dialog
box, type the following URL with its trailing param eter:
ht tp: / / localhost/ Chapt er13/ SoapFor13?wsdl
Then press Enter. This populates the left pane of the Add Web
Reference dialog box with a representation of the .wsdl file for the
SoapFor13 Web service. The right pane includes a single link with
the text View Contract. Click the Add Reference button to create a
Web reference for use with a proxy variable. If you have been
creating the samples throughout the chapter, the name for this Web
reference in the Web References folder of Solution Explorer is
localhost 3. No matter what its name, the reference should include
an item named SoapFor13.wsdl. This .wsdl file contains the formal
description for the Web service. Any proxy variable based on this
Web reference will enable you to run the
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Ten_Most_Expensive_Product s Web method and display the result
set returned as an XML fragment. The result set from the stored
procedure is available as an XML document fragment because the
example selected this output format in Figure 13-14.
The next listing shows the code behind the form in Figure 13-15. As
you can see, it consists of a single form Load event procedure.
When the form opens, the procedure connects to the SoapFor13
Web service and invokes the Ten_Most _Expensive_Product s Web
method. It collects the XML fragment returned by the method in an
array of Response objects. The Response object is the most basic
kind of object in Visual Studio .NET; this type of object can
accommodate any other kind of object or type. Since the Web
service can present either an XML document or a SqlMessage
object, the application needs Response objects to accommodate
either outcome. The SqlMessage object can return SQL Server error
messages and warnings to an application.
Using an array of objects accommodates the possibility of multiple
result sets from a single stored procedure or template file. Although
this sample has a single result set, the sample’s design illustrates
the For loop syntax for iterating through the members of a
Response object array. A Select…Case statement sends the
Response object to the appropriate code for processing. Because
this is a very simple application, the code just processes an object
containing an XML fragment. The processing consists of a pair of
statements that copy the XML fragment in the Response object to
the Text property of the second label on Form 3. Whenever you
choose to output the result set or sets from a Web method as XML
objects, you’ll have to process the output in this style— that is, with
a Select…Case statement nested within a For loop that iterates
through the objects returned from the Web method.
Private Sub Form3_Load(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
’Declare Web service xws1 as type named soap in
’localhost4 Web reference.
Dim xws1 As New XMLWebServiceClients.localhost3.SoapF
or13()
’Declare object for return from Web service method.
Dim response As New Object()
Dim result As System.Xml.XmlElement
’Declare integer for iterating through multiple
’result sets that the Web service method can return.
Dim int1 As Integer
’Save return from Web service method as an object.
response = xws1.Ten_Most_Expensive_Products
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
’Iterate through result sets.
For int1 = 0 To UBound(response)
Select Case response(int1).GetType().ToString()
Case "System.Xml.XmlElement"
’Pass int1 result set to result and displ
ay
’in list box and Output window.
result = response(int1)
Label2.Text = result.OuterXml
Case Else
’Handles end of result sets and other
’special returns.
End Select
Next
End Sub
Figure 13-15 shows Form 3 open from the XMLWebServiceClient
project. The form contains two labels. The top label has a fixed Text
property assignment. It always shows “XML fragment: ”. The
contents of the bottom text box can change if the ten most
expensive products change because of a price revision, the addition
of new products, or the dropping of existing products. The product
name and unit price values are delimited by opening and closing
tags. Although this format may be convenient for com puters to
process and is readable by humans, it is verbose. That’s because
every value has a pair of tags, and there are additional tags to mark
the beginning and ending of each row (< row> and < / row> ) as well
as the beginning and ending of the XML fragment (< SQLXML> and
< / SQLXML > ).
Figu re 1 3 - 1 5 . Form 3 from t h e XMLW ebServiceClient s proje ct show ing the
ou t put from t he Ten _ M ost_ Expen sive _ Product s W e b m e t h od in t h e
SoapFor1 3 W eb service.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Không có nhận xét nào:
Đăng nhận xét