Chapter 14: Working with SQL Server


While most of the relationship between a developer and an SQL Server relates to querying or saving data, SQL Server 2005 created a new way of working with databases. SQL Server 2005 added integration with the .NET Framework. This provided two main benefits. First, you can use Visual Basic to create elements in the database, such as user-defined types, stored procedures, and functions. These objects may work alone or in concert with the normal Transact-SQL objects. Second, you can expose Web Services from your databases, enabling .NET and other client applications to execute code on the database.

Transact-SQL (T-SQL), while a well-featured language, lacks a number of features that are common in general-purpose languages such as Visual Basic. VB includes better support for looping and conditional statements than T-SQL. In addition, the .NET Framework is available for use with Visual Basic, meaning you have access to tools for network access, string handling, mathematical processing, internationalization, and more. If your stored procedures need access to features such as these, then it may be beneficial to look at using VB, not T-SQL.

This chapter describes how and, possibly more important, why you can use Visual Basic to create these objects in SQL Server 2005. It covers how you can adapt existing data access code to run within the database, and leverage the common language runtime. Finally, the chapter describes the new capability of hosting Web Services within SQL Server, and how you can create these Web Services using Visual Basic.

CLR Integration in SQL Server 2005

As the Developer Division within Microsoft worked on .NET Framework 2.0 and Visual Basic 2005, other teams within SQL Server were working on the new version of SQL Server. The SQL teams wanted to leverage the Framework. However, at the time, the ability to host the .NET Framework was limited. The two groups worked together to better identify hosting APIs, with the result that they were able to integrate the common language runtime into SQL Server 2005. This integration means that developers can use Visual Basic code within the context of SQL Server. It is no longer true that success as a DBA is dependent on knowing T-SQL. In addition, it is no longer necessary for the data access code to be written outside the database. The benefit to both groups is more flexibility in choosing a development language, and more capabilities for your database programming.

CLR integration is disabled by default on SQL Server 2005. This is a safety measure, as most users do not need the features it provides. By not enabling it, there’s one less avenue for attack by hackers. In order to enable creating SQL objects using Visual Basic, you need to enable the integration. This is done by executing the SQL statement that follows in a query window in the SQL Management Console. Note that this is not a decision that should be taken lightly. Enabling any feature means that hackers also have the feature available to them, so enabling a feature as powerful as CLR integration means that if compromised, then your server can become a dangerous tool. Here is the code:

 sp_configure 'clr enabled', 1 GO RECONFIGURE GO

Now that you’ve been scared away from enabling CLR integration, it is an incredibly useful tool in some circumstances. T-SQL, for all of its power, is a relatively limited language compared with Visual Basic. It lacks many of the conditional or looping constructs that developers are used to, such as the with statement. In addition, debugging has traditionally been fairly weak with T-SQL. Finally, the ability to use external libraries in T-SQL is limited. You can get around these limits by using Visual Basic to replace T-SQL when appropriate.

Deciding between T-SQL and Visual Basic

Once you have enabled CLR integration with your database, your next set of decisions revolves around when to use T-SQL and the native services of SQL Server versus when to use Visual Basic and the .NET Framework. Your final choice should be based on the needs of the application, rather than because a technology is new or interesting. The following table outlines some of the common scenarios for building applications with SQL Server:

Open table as spreadsheet

Scenario

T-SQL

Visual Basic

User-defined types

Generally should be the first, if not only, choice

Can be used if you will need to integrate with other managed code, or if the UDT will need to provide additional methods. Also a good idea if the UDT will be shared with external VB code.

Functions and stored procedures

Use when the code is to process data in bulk, or with little procedural code.

Use if the code requires extensive procedural processing or calculations, or if you need access to external libraries, such as the .NET Framework

Extended stored procedures

Typically the main method used to provide new functionality to SQL Server. For example, the xp_send-mail procedure enables sending e-mail from T-SQL. Generally, extended stored procedures should be avoided in favor of creating the proced ures in managed code. This is partly due to the complexity involved in creating secure extended procedures, but mostly because they will be removed from a future version of SQL Server.

Use if you need access to external code or libraries, such as the .NET Framework. Depending on your needs, the code may be limited to working within the context of SQL Server, or it may access external resources, such as network services. The benefits of better memory management and security make VB a better choice for creating these extended stored procedures.

Code location

T-SQL code can only exist within SQL Server. This enables optimizations of queries.

VB code may exist either within SQL Server or on the client. This may mean that you can typically take code from the client and adapt it for running within SQL Server. The code would then execute closer to the data, generally increasing performance. In addition, the hardware running SQL Server is typically more efficient than the average desktop, resulting in code that executes faster.

Web Services

T-SQL supports the creation of Web Services to make any function or stored procedure available via SOAP.

Functions and stored procedures written in VB may be exposed as Web Services from SQL Server. The better support for XML handling and procedural logic may mean that it is easier to create these Web Services in Visual Basic.

XML handling

T-SQL has been extended to provide some capability in reading and writing XML. These extensions only provide the capability to work with the XML as a whole, however.

Provides excellent XML handling, both for working with the document as a whole and via streaming APIs. Generally, if you need to do a lot of XML handling, using Visual Basic will make your life a lot easier.

Creating User-defined Types

One feature of SQL Server that does not usually get the attention it deserves is the capability to create user-defined types (UDTs). These enable the developer to define new types that may be used in columns, functions, stored procedures, and so on. They can make database development easier by applying specific constraints to values, or by identifying the intent of a column. For example, when presented with a table containing a column of type varchar(11), you may still be unsure as to the purpose of the value, but if that column is instead of type ssn, you would recognize this (if you are in the United States) as a social security number.

With SQL Server 2005, you can create UDTs using Visual Basic. In addition to the normal benefits of user-defined types, UDTs written in VB have an additional benefit - they may also provide functionality in the form of methods. This means that you can essentially extend the functionality of your database by providing these methods.

UDTs written using Visual Basic are implemented as structures or classes. Because Visual Studio defaults to creating UDTs as structures, this will be assumed here, but keep in mind that you can also create them as classes. The properties or fields of the structure become the subtypes of the UDT. Public methods are also accessible, just as they would be in a VB application.

In addition to the normal code used when writing structures, you must also implement other items to make your UDT work with SQL Server. First, your structure should have the attribute Microsoft .SqlServer.Server.SqlUserDefinedType. This attribute identifies the structure as being a SQL Server UDT. In addition, marking the class with the Serializable attribute is highly recommended. The SqlUserDefinedType attribute has a number of parameters that provide information affecting how SQL Server works with the type. These parameters are described in the following table:

Open table as spreadsheet

Parameter

Value

Description

Format

Native or UserDefined

Identifies the serialization format. If you use Native (the default when you create your UDT with Visual Studio), it uses the SQL Server serialization model. If you set it to UserDefined, you must also implement Microsoft.SqlServer.Server.IBinarySerialize. This interface includes methods for reading and writing your data type. Generally, using Native is safe enough, unless your datatype requires special handling to avoid saving it incorrectly. For example, if you were storing a media stream, you would likely set that as UserDefined to avoid writing the stream incorrectly.

IsByteOrdered

Boolean

True if the data is stored in byte order, false if it is stored using some other. If this is true, you can use the default comparison operators with the type, as well as use it as a primary key. If it is possible to define one instance of this UDT as being larger than another, then IsByteOrdered is likely true. If it is not, such as with a latitude value, then IsByteOrdered is false.

IsFixedLength

Boolean

This should be set to true if all instances of this type are the same size. If the UDT includes only fixed-size elements, such as int, double, or char(20), then this is true. If it includes variable-sized elements, such as varchar(50) or text, then it should be false. This is a marker to enable optimizations by the SQL Server query processor.

ValidationMethodName

String

Name of a method to be used to validate the data in the UDT. This method is used when loading the UDT, and should return true if the data is valid.

MaxByteSize

Integer, with a maximum of 8000

Defines the maximum size of the UDT, in bytes

In addition to this attribute, each user-defined type also needs to implement the shared method Parse, and the instance method ToString. These methods enable conversion between your new datatype and the interim format, SqlString. Finally, while not a requirement, you should also implement INullable in your structure. This interface requires the addition of the IsNull property. This enables your UDT to deal with null values, either stored in the database or passed from the client.

Tip 

If you are using Visual Studio to create your UDTs, it’s best to create and debug all of your UDTs before you begin to use them, especially if you need to use them in any table columns. This is because VB drops all of the objects you create in a SQL Server project when deploying your project. If you have any tables that use any user-defined types, you will be unable to drop the UDT and therefore deploy the changes you’ve made. If you need to make changes, you may receive an error similar to “Cannot drop type 'Location’ because it is currently in use.” This error causes the deploy step of your project to fail. If this happens, change the column type or temporarily drop the table. You can then redeploy the UDT as needed. Don’t forget to change the column back or re-create the table.

While you can write code that will integrate with SQL Server using any DLL, Visual Studio provides the SQL Server Project (see Figure 14-1). This project type generates a DLL, but also connects the DLL to the database.

image from book
Figure 14-1

When you create a new SQL Server Project, Visual Studio prompts you to identify the database that will host the DLL. At this point, you can either select an existing database connection or create a new one. Visual Studio also asks if you want to enable SQL/CLR debugging on the connection. Typically, you will want to enable this on development servers. However, keep in mind that when debugging, the server is limited to the single connection. Once the project is created, you can add the various database types via the Project menu. Deploying the project loads the created DLL into the database. You can confirm that it is loaded by looking at the Assemblies folder in the Server Explorer (see Figure 14-2).

image from book
Figure 14-2

The following code example shows a simple Location user-defined type written in Visual Basic. This type identifies a geographic location. We will use it throughout the remainder of the chapter to track the location of customers and stores. The Location type has two main properties, Latitude and Longitude. Create this file by selecting New User-defined Type from the Project menu. Here is the code:

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _ Public Structure Location     Implements INullable     Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull         Get             If Me.Latitude = Double.NaN OrElse Me.Longitude = Double.NaN Then                 _isNull = True             Else                 _isNull = False             End If              Return _isNull         End Get     End Property     Public Shared ReadOnly Property Null As Location         Get             Dim result As Location = New Location             result._isNull = True             result.Latitude = Double.NaN             result.Longitude = Double.NaN             Return result         End Get     End Property     Public Overrides Function ToString() As String         Return String.Format("{0}, {1}", Latitude, Longitude)     End Function     Public Shared Function Parse(ByVal s As SqlString) As Location         If s.IsNull Then             Return Null         End If          Dim result As Location = New Location         Dim temp() As String = s.Value.Split(CChar(","))         If (temp.Length > 1) Then             result.Latitude = Double.Parse(temp(0))             result.Longitude = Double.Parse(temp(1))         End If         Return result     End Function     Public Function Distance(ByVal loc As Location) As Double         Dim result As Double         Dim temp As Double         Dim deltaLat As Double         Dim deltaLong As Double         Const EARTH_RADIUS As Integer = 6378   'kilometers         Dim lat1 As Double         Dim lat2 As Double         Dim long1 As Double         Dim long2 As Double          'convert to radians         lat1 = Me.Latitude * Math.PI / 180         long1 = Me.Longitude * Math.PI / 180         lat2 = loc.Latitude * Math.PI / 180         long2 = loc.Longitude * Math.PI / 180         'formula from http://mathforum.org/library/drmath/view/51711.html         deltaLong = long2 - long1         deltaLat = lat2 - lat1         temp = (Math.Sin(deltaLat / 2)) ^ 2 + _             Math.Cos(lat1) * Math.Cos(lat2) * (Math.Sin(deltaLong / 2)) ^ 2         temp = 2 * Math.Atan2(Math.Sqrt(temp), Math.Sqrt(1 - temp))         result = EARTH_RADIUS * temp          Return result     End Function     Private _lat As Double     Private _long As Double     Private _isNull As Boolean     Public Property Latitude() As Double         Get             Return _lat         End Get         Set(ByVal value As Double)             _lat = value         End Set     End Property     Public Property Longitude() As Double         Get             Return _long         End Get         Set(ByVal value As Double)             _long = value         End Set     End Property End Structure

In addition to the Latitude and Longitude properties, the Location type also defines a Distance method. This is used to identify the distance between two locations. It uses the formula for calculating the distance between two points on a sphere to calculate the distance. This formula is better described at the “Ask Dr. Math” forum. As the Earth is not a perfect sphere, this calculation should only be considered an estimate, but it is close enough for our needs.

Now that you have created the Location type, you can use it in the definition of a table. This example creates part of an e-commerce application to demonstrate the use of Location and other SQL Server features.

Imagine if you will that you are creating an application for an online store that also has brick and mortar locations. When a customer orders a product, you must obviously ship it from some location. Major online sellers likely have large warehouses that they can use to fulfill these orders. However, they are generally limited to shipping from these warehouses. Other companies also have physical stores that stock many of the items available for order. Wouldn’t it make sense that if one of those stores has stock and is closer to the customer, then you would use the stock in the store to fulfill the order from the website? It would save on shipping costs, and it would get the product to the customer faster. This would save you money, and lead to happier customers who are more likely to order from you again. This hypothetical scenario would likely be called into play many times throughout the day; therefore, moving it to a stored procedure would be useful to improve performance. The calculations would be closer to the data, and the database server itself could perform optimizations on it if needed.

Open the database using SQL Management Studio or the Server Explorer in Visual Studio. Create a table called Stores. This table will be used to track the physical store locations. See Figure 14-3 for the layout of this table. Note that the new Location datatype should appear at the bottom of the list of datatypes; it is not inserted in alphabetical order.

image from book
Figure 14-3

The id column is defined as an identity column, and is the primary key. Don’t bother adding any data to the table yet, unless you know the appropriate latitude and longitude for each location. We’ll create a function for calculating the location in a moment.

In addition to the Stores table, create two other tables, one for products (see Figure 14-4), and the other to track the stock (see Figure 14-5) available in each store.

image from book
Figure 14-4

image from book
Figure 14-5

As with the Stores table, the id column is an identity field. The Name field will represent the name of the product, and Price will reflect the unit price of each item. A typical product table would likely have other columns as well; this table has been kept as simple as possible for this example.

The Stock table will provide the connection between the Stores and Products tables. It uses the combination of the two primary keys as its key. This means that each combination of store and product will have a single entry, with the quantity of the product per store.

Now that the tables are in place for the sample, we’ll turn our attention to creating a way to determine the location, using a SQL Server function written in Visual Basic.

Creating Functions

Functions are a feature of SQL Server enabling a simple calculation that returns either a scalar value or a table of values. These functions differ from stored procedures in that they are typically used to perform some calculation or action, rather than specifically acting on a table. You can create functions in either T-SQL or Visual Basic.

When creating functions with Visual Basic, you define a class with one or more methods. Methods that you want to make available as SQL Server functions should be marked with the Microsoft.SqlServer.Server.SqlFunctionAttribute attribute. SQL Server will then register the methods, and they may be used in your database. The SqlFunction attribute takes a number of optional parameters, shown in the following table:

Open table as spreadsheet

Parameter

Value

Description

DataAccess

Either DataAccessKind.None or DataAccessKind.Read

Set to DataAccessKind.Read if the function will access data stored in the database.

SystemDataAccess

Either SystemDataAccessKind .None or SystemDataAccessKind .Read

Set to SystemDataAccessKind.Read if the function will access data in the system tables of the database.

FillRowMethodName

String

The name of the method that will return each row of data. This is only used if the function returns tabular data.

IsDeterministic

Boolean

Set to true if the function is deterministic - that is, if it will always produce the same result, given the same input and database output. (A random function would obviously not be deterministic). The default is false.

IsPrecise

Boolean

Set to true if the function does not use any floating point calculations. The default is false.

TableDefinition

String

Provides the table definition of the return value. Only needed if the function returns tabular data.

By default, SQL Server 2005 loads Visual Basic objects into a safe environment. This means that they cannot call external code or resources. In addition, Code Access Security (CAS) limits access to some aspects of the .NET Framework. You can change this behavior by explicitly setting the permission level under which the code will run. The following table outlines the available permission levels:

Open table as spreadsheet

Permission Level

Safe

External

Unsafe

Code access

Limited to code running within SQL Server context

Ability to access external resources

Unlimited

Framework access

Limited

Limited

Unlimited

Verifiability

Yes

Yes

No

Native code

No

No

Yes

Use the minimum permission level needed to get your code to run. Typically, this means you need only the Safe level. This permission level provides access to the libraries providing data access, XML handling, mathematical calculations, and other commonly needed capabilities.

If you need access to other network resources, such as the capability to call out to external Web Services or SMTP servers, enable the External permission level. This also provides all the capabilities provided by the Safe permission level.

You should only enable the Unsafe permission level in the rarest of occasions, when you need access to native code. Code running within this permission level provides full access to any code available to it, so it may represent a security hole for your application.

If you attempt to deploy a VB DLL that requires external access, you will receive this lengthy - but not entirely helpful - error message:

 CREATE ASSEMBLY for assembly 'FooStore' failed because assembly 'FooStore' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.

The error message provides the steps required to enable external access. At this point, you have two options:

  • Provide the External Access Assembly permission to the user account associated with the database owner. You should not do this unless the second option is not possible. This creates a dangerous security hole in your database. It would mean that any Visual Basic code running on the server would have external access permissions, and complete access to the database.

  • Sign the assembly and create an account that uses this signature. Then provide the External Access Assembly permission to that account. This is the preferred method for enabling safe external access by a Visual Basic assembly. By signing your assembly and giving the assembly (and the user id associated with the signature) permission, you are limiting the amount of code that can access other servers.

The following steps outline how to provide external access permissions to a VB assembly using Visual Studio. First, set the permission level to External (see Figure 14-6) and provide a name for the owner of the assembly. This is done using the database page of the project’s property pages.

image from book
Figure 14-6

Once you have enabled external access for your Visual Basic code, you need to sign your assembly. Sign the assembly on the Signing tab of the properties dialog (see Figure 14-7). Use an existing key file or create a new one.

image from book
Figure 14-7

Once you have signed and built the assembly, the next steps are to create a key in the database based on the signature of the assembly and create a user who will be associated with the key. This is done using a T-SQL query. Run the following query in SQL Management Studio:

 USE master GO CREATE ASYMMETRIC KEY FooStoreKey   FROM EXECUTABLE FILE = 'C:\FooStore.dll' GO CREATE LOGIN FooUser   FROM ASYMMETRIC KEY FooStoreKey GRANT EXTERNAL ACCESS ASSEMBLY TO FooUser GO

Creating a new asymmetric key must be done from the master database. The DLL listed in the FROM EXECUTABLE FILE clause should be the DLL you just created in Visual Basic; adjust the path in the SQL statement to match the location of your DLL. Once the key is created, you can create a new login based on this key, and provide that user with external access. You should also add that login to the database and give it permission to access the desired objects.

Now that the assembly can access external sites, you are ready to begin coding the function that will convert the addresses to latitude and longitude (that is, geocode the address). Numerous companies sell databases or services that provide this capability, but Yahoo! has a free Web Service that geocodes addresses (see the Resources section at the end of this chapter for the URL). It can be called up to 5,000 times a day, more than enough for this sample (but probably not enough for a real store).

The Geocode service is accessed by sending a GET request to http://api.local.yahoo.com/ MapsService/V1/geocode with the following parameters:

Open table as spreadsheet

Parameter

Description

appid

(Required) The unique string used to identify each application using the service. Note that this parameter name is case sensitive. For testing purposes, you can use YahooDemo (used by the Yahoo samples themselves). However, your own applications should have unique application IDs, which you can register at http://api.search.yahoo.com/webservices/register_application.

street

(Optional) The street address you are searching for. This should be URL-encoded. such as <, /, >, etc., should be replaced with their equivalent using '%##’ notation.

city

(Optional) The city for the location you are searching for. This should be URL-encoded, although this is really only necessary when the city name contains spaces or high ASCII characters.

state

(Optional) The U.S. state (if applicable) you are searching for. Either the two-letter abbreviation or full name (URL-encoded) will work.

zip

(Optional) The U.S. zip code (if applicable) you are searching for. This can be in either 5-digit or 5+4-digit format.

location

(Optional) A free-form field of address information containing the URL-encoded and comma-delimited request. This provides an easier method for querying, rather than setting the individual values listed above, e.g., location=1600+Pennsylvania+Avenue+NW,+Washington,+DC

The following code shows the full source for the fnGetLocation function:

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Xml Imports System.Text Partial Public Class UserDefinedFunctions     'Replace YahooDemo with your key     Private Const YAHOO_APP_KEY As String = "YahooDemo"     Private Const BASE_URL As String = _         "http://api.local.yahoo.com/MapsService/V1/geocode"     <Microsoft.SqlServer.Server.SqlFunction()> _     Public Shared Function fnGetLocation(ByVal street As SqlString, _         ByVal city As SqlString, _         ByVal state As SqlString, _         ByVal zip As SqlString) As Location         Dim result As New Location         Dim query As New StringBuilder         'uses Yahoo geocoder to geocode the location         'limited to 5000 calls/day                  'construct URL         ' URL should look like:         '   http://api.local.yahoo.com/MapsService/V1/geocode?         '       appid=YahooDemo&street=701+First+Street&city=Sunnyvale&state=CA         query.AppendFormat("{0}?appid={1}", BASE_URL, YAHOO_APP_KEY)         If Not street.IsNull Then             query.AppendFormat("&street={0}", street)         End If         If Not city.IsNull Then             query.AppendFormat("&city={0}", city)         End If         If Not state.IsNull Then             query.AppendFormat("&state={0}", state)         End If         If Not zip.IsNull Then             query.AppendFormat("&zip={0}", zip)         End If         'Debug.Print(query.ToString())         'send request         Using r As XmlReader = XmlReader.Create(query.ToString())             'parse output             While r.Read                 If r.IsStartElement("Latitude") Then                     ' longitude directly follows latitude in the result xml                     result.Latitude = Double.Parse(r.ReadElementString)                     result.Longitude = Double.Parse(r.ReadElementString)                     Exit While                 End If             End While         End Using         Return result     End Function End Class

Most of the code in the preceding sample is used to create the appropriate URL to create the query. The query should look as follows:

 http://api.local.yahoo.com/MapsService/V1/geocode?appid=YahooDemo&street=701+ First+Street&city=Sunnyvale&state=CA&country=USA

While the YahooDemo appid will work for testing, there is a good chance that it will not work at times. As the query is limited to 5,000 requests for each appid, if too many people call the geocoder in one day, the request will fail. Therefore, you should request your own appid for testing, and replace the preceding appid with your own. You can request your own appid at the following Web page:

  • http://api.search.yahoo.com/webservices/register_application

Tip 

You probably noticed that the preceding code uses a StringBuilder to construct the query. Why not simply concatenate strings to create the query? There are a number of reasons, but the most important is performance. As strings in Visual Basic are immutable, concatenation requires the creation of new strings each time. For example, the simple expression Dim s As String = “Hello” & “world” actually requires three strings, two of which are immediately discarded. The StringBuilder class has been built to avoid this repeated creation and disposal of objects, and the resulting code has much better performance than what you could achieve with simple concatenation.

Once the query is constructed, an XmlReader is used to execute the query. The resulting XML from a call to Yahoo!’s geocoder looks like the following:

 <?xml version="1.0" ?> <ResultSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns="urn:yahoo:maps"     xsi:schemaLocation="urn:yahoo:maps       http://api.local.yahoo.com/MapsService/V1/GeocodeResponse.xsd">   <Result precision="address"       warning="The exact location could not be found,         here is the closest match: 701 First Ave, Sunnyvale, CA 94089">     <Latitude>37.416384</Latitude>     <Longitude>-122.024853</Longitude>     <Address>701 FIRST AVE</Address>     <City>SUNNYVALE</City>     <State>CA</State>     <Zip>94089-1019</Zip>     <Country>US</Country>   </Result> </ResultSet> <!ws02.search.scd.yahoo.com uncompressed/chunked Mon Dec  4 16:33:28 PST 2006 >

While you could load all of this into an XmlDocument for processing, the XmlReader is generally faster. In addition, because all that is really needed are the two values for latitude and longitude, using the XmlReader enables the code to extract these two values quickly, and without the overhead of loading all the other data. The XmlReader class implements IDisposable, so ensure the correct handling and disposal of the class either by setting the object to nothing in a Try...Finally block or by employing the Using statement:

 Using r As XmlReader = XmlReader.Create(query.ToString())     'parse output     While r.Read         If r.IsStartElement("Latitude") Then             ' longitude directly follows latitude in the result xml             result.Latitude = Double.Parse(r.ReadElementString)             result.Longitude = Double.Parse(r.ReadElementString)             Exit While         End If     End While End Using

As shown in Chapter 11, you create the XmlReader using the shared Create method. This method has a number of overridden versions. In this case, the string version of the URL is used to create the XmlReader. The code then loops through the resulting XML until the start element for the Latitude element is found. As we know, the two values are next to each other; the code may then access them and stop reading. Figure 14-8 shows testing this new function in SQL Server Management Studio.

image from book
Figure 14-8

Using the User-defined Function

Even though the fnGetLocation function is written in Visual Basic, you can still use this function from T-SQL. This means that you can use either VB or T-SQL for a given SQL Server object, whichever is better suited to the scenario. The following code shows the procedure used to insert new stores. This procedure is written in T-SQL, but it calls the function written in Visual Basic. Alternately, you could create an insert trigger that calls the function to determine the location of the store. Here is the procedure code:

 CREATE PROCEDURE dbo.procInsertStore (   @name nvarchar(50),   @street nvarchar(512),   @city nvarchar(50),   @state char(2),   @zip varchar(50) ) AS   /* need to populate location */   DECLARE @loc AS Location;   SET @loc = dbo.fnGetLocation(@street, @city, @state, @zip);   INSERT INTO Stores (Name, Street, City, State, Zip, GeoLocation)       OUTPUT INSERTED.id   VALUES (@name, @street, @city, @state, @zip, @loc);   RETURN @@IDENTITY 

The stored procedure uses the function and user-defined type just as it would use the same objects written in T-SQL. Before storing the store data, it calls the Web Service to determine the latitude and longitude of the location, and then stores the data in the table.

Now you can add data to the three tables. Add a few stores (see Figure 14-9) using the stored procedure. The actual data is not that important, but having multiple stores relatively close to one another will be useful later.

image from book
Figure 14-9

Similarly, add a number of items to the Products table (see Figure 14-10). Again, the data itself is not important, only that you have a variety of items from which to choose.

image from book
Figure 14-10

Finally, add the data to the Stock table (see Figure 14-11). There should be a single entry for each combination of store and product. Make certain that you have a variety of quantities on hand for testing.

image from book
Figure 14-11

Now that you have some data to work with, and a function for determining the latitude and longitude of any address, you’re ready to examine how to create a stored procedure in Visual Basic to locate the nearest store with available stock to the customer.

Creating Stored Procedures

A feature that has received a great deal of fanfare with SQL Server 2005 is the capability to create stored procedures in Visual Basic 2005 (or rather, in any .NET language). This feature supplements the longstanding use of T-SQL as the language for stored procedures. At the same time, it raises more questions: When should you use T-SQL, and when should you use Visual Basic? Is Visual Basic suitable for all stored procedures?

As with most questions of this type, the answer is a resounding, “It depends.” T-SQL is a powerful language for dealing with sets of information, which are groups of items you can define using some criteria (i.e., the result of a SELECT statement). Visual Basic lacks any real functionality for dealing with sets, at least until LINQ is released. This provides us with at least some of the answers to the questions posed above.

T-SQL is the better choice when you must deal with a set of data. It is a great language for doing bulk changes to a list of items, or for rapidly finding all items that match some criterion - for example, to change the area codes of some of the phone numbers stored, or to find all the customers who have purchased more than 10 items in the last 30 days. Doing the same tasks using VB would lead to long looping tests that would need to walk through all the items stored.

Just as with user-defined types and functions, you identify methods as being stored procedures with an attribute. In the case of stored procedures, this is Microsoft.SqlServer.Server.SqlProcedure - Attribute. This attribute is basically a marker attribute; there are no additional parameters that have any dramatic effect on the behavior of the code.

When creating a stored procedure in Visual Basic, keep a few considerations in mind. First, and likely most important, is the context. You are no longer running as a separate piece of code, but within SQL Server. Tasks that require long processing mean that whatever resources you are using will be unavailable to other code. This could cause your database to become less responsive, leading to more slowdowns. Therefore, always remain conscious of the resources you are using, and the amount of time you lock them.

The second major change when creating stored procedures in VB is the connection to the data. When writing standalone VB code that accesses data, you need to create a connection to a class that implements IDbConnection - frequently, SqlConnection or OleDbConnection. The connection string used identifies the database, user id, and so on. However, in a stored procedure, you are running within the context of SQL Server itself, so most of this information is superfluous. This makes connecting to the datasource much easier:

 Using connection As New SqlConnection("context connection=true") ...'work with the data here End Using

The connection string is now reduced to the equivalent of “right where the code is running.” The user id, database and other parameters are implied by the context in which the code is running.

Once you have connected to the database, the rest of the code is basically the same as you are used to performing with other ADO.NET code. This means that migrating code that accesses SQL Server to run as a stored procedure is fairly easy: change the connection string used to connect to the database, and add the SqlProcedure attribute.

Returning Data from a Stored Procedure

Once you have performed whatever manipulation is required to get your data, you obviously need to send it back to the user. With normal ADO.NET, you would create a DataSet or SqlDataReader and use the methods and properties of the class to extract the data. However, the data access code running within a stored procedure is running in the context of SQL Server, and the stored procedure must behave in the same way as other stored procedures. Therefore, you must change your code slightly to accommodate this behavior.

When returning data using ADO.NET, you typically have a few options. The first option depends on whether you need to return a single value or one or more rows of data.

Returning a Single Value

If you are returning a single value from the stored procedure, create your stored procedure as a subroutine. The data you return should be a ByRef parameter of the subroutine. Finally, you need to mark this parameter as an out-parameter using the System.Runtime.InteropServices.Out attribute. For example, if you were attempting to create a stored procedure that returned the total value of all the items available at a selected store, you would create it similar to the procedure shown here:

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Runtime.InteropServices Partial Public Class StoredProcedures     <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub procGetStoreInventoryValue(ByVal storeID As Int32, _         <Out()> ByRef totalValue As Single)         Dim query As String = "SELECT SUM(Products.Price * Stock.Quantity) " & _             "FROM Products INNER JOIN Stock ON " & _             "Products.id = Stock.ProductID " & _             "WHERE Stock.StoreID = @storeID"         Using conn As New SqlConnection("context connection = true")            conn.Open()             Using cmd As New SqlCommand(query, conn)                 cmd.Parameters.Add("@storeID", SqlDbType.Int).Value = storeID                 totalValue = CSng(cmd.ExecuteScalar())             End Using         End Using     End Sub End Class 

Tip 

As this stored procedure doesn’t really do any processing of the data, or mathematical calculations, it would probably be best created using T-SQL.

The procedure is fairly basic: It uses the current connection to execute a block of SQL, and returns the value from that SQL. As before, the SqlConnection and SqlCommand values are created using the new Using statement. This ensures that they are disposed of, freeing the memory used, when the code block is completed.

Just as when working with ByRef parameters in other code, any changes made to the variable within the procedure are reflected outside the method. The Out attribute extends this to identify the parameter as a value that needs to be marshaled out of the application. It is needed to change the behavior of the ByRef variable. Normally, the ByRef variable is an In/Out value. You must have it at least available when you make the call. By marking it with the Out attribute, you mark it as not having this requirement.

Returning Multiple Values

Things become slightly more complex if you want to return one or more rows of data. In a sense, your code needs to replicate the data transfer that would normally occur when a stored procedure is executed within SQL Server. The data must somehow be transferred to the TDS (tabular data stream). How do you create this TDS? Fortunately, SQL Server 2005 provides you with a way: via the SqlPipe class. The SqlContext class provides access to the SqlPipe class via its Pipe property. The SqlPipe class has a number of methods that may be used to return data back to the code that called the stored procedure.

Open table as spreadsheet

Method

Description

ExecuteAndSend

Takes a SqlCommand, executes it, and returns the result. This is the most efficient of the methods that may be used to return data, as it does not need to generate any memory structures.

Send(SqlDataReader)

Takes a SqlDataReader and streams out the resulting data to the client. This is slightly slower than the preceding method, but recommended if you need to perform any processing on the data before returning.

Send(SqlDataRecord)

Returns a single row of data to the client. This is a useful method if you are generating the data and only need to return a single row.

Send(String)

Returns a message to the client. This is not the same as a scalar string value, however. Instead, this is intended for sending informational messages to the client. The information sent back may be retrieved using the InfoMessage event of the SqlConnection.

SendResultsStart

Used to mark the beginning of a multi-row block of data. This method takes a SqlDataRecord that is used to identify the columns that will be sent with subsequent SendResultsRow calls. This method is most useful when you must construct multiple rows of data before returning to the client.

SendResultsRow

Used to send a SqlDataRecord back to the client. You must already have called SendResultsStart using a matching SqlDataRecord, or an exception will occur.

SendResultsEnd

Marks the end of the transmission of a multi-row block of data. This can only be called after first calling SendResultsStart, and likely one or more calls to SendResultsRow. If you fail to call this method, then any other attempts to use the SqlPipe will cause an exception.

If all you want to do is execute a block of SQL and return the resulting data, then use the ExecuteAndSend method. (Actually, in this case, you should likely be using T-SQL, but there may be cases that justify doing this in VB). This method avoids the overhead involved in creating any memory structures to hold the data in an intermediate form. Instead, it streams the data just as it would if the procedure were written in T-SQL.

The next most commonly used method for returning data is the version of the Send method that takes a SqlDataReader. With this method, your code can return a block of data pointed at by a SqlDataReader.

This method, as well as the version of Send that takes a SqlDataRecord, is commonly used when some processing of the data is needed before returning. They do require that some memory structures be created, so they are not as fast at returning data as the ExecuteAndSend method.

The version of Send taking a SqlDataRecord object can be a handy method for constructing and returning a single row of data (or more when using SendResultsRow).

The SqlDataRecord class is new with the Microsoft.SqlServer.Server namespace, and represents a single row of data. Why a new datatype instead of just leveraging DataSet? The creators needed an object that was capable of being converted into the tabular data stream format used by SQL Server. There are two ways to return a SqlDataRecord. If only a single row of data needs to be returned, you use the Send(SqlDataRecord) method. If multiple records will be returned, then you use SendResultsStart, SendResultsRow, and SendResultsEnd (see below). In each case, you are responsible for creating and populating the values for each column in the SqlDataRecord.

Columns within a SqlDataRecord are defined using the SqlMetaData class. Each column requires the definition of an instance of a separate SqlMetaData object, with the constructor of SqlDataRecord taking an param array of these objects. Each SqlMetaData object defines the type, size, and maximum length (if appropriate) of the data for the column. The following code shows the creation of a SqlDataRecord with four columns:

 Dim rec As SqlDataRecord rec = New SqlDataRecord( _     new SqlMetaData("col1", SqlDbType.Int), _     new SqlMetaData("col2", SqlDbType.VarChar, 25), _     new SqlMetaData("col3", SqlDbType.Float), _     new SqlMetaData("col4", SqlDbType.Text, 512))

You can retrieve data from each of the columns in two ways: You can use the GetValue method, which returns the value stored in the nth column of the SqlDataRecord as an Object. Alternately, you can return the data as a particular datatype using one of the many GetXXX methods, where XXX is the type required. For example, to return the value stored in the second column above as a string, you would use GetString(1). Similarly, there are SetValue and SetXXX methods for setting the value of each column. Once you have created your SqlDataRecord and populated its values, you return it to the client by passing it to the Send method of the SqlPipe, as in the following code:

 rec.SetInt32(0, 42) rec.SetString(1, "Some string") rec.SetFloat(2, 3.14) rec.SetString(3, "Some longer string") SqlContext.Pipe.Send(rec)

The version of the Send method that takes a string is slightly different from the other two variants. Rather than return data, the intent of the Send(String) version is to return information to the calling application - the equivalent of the T-SQL print statement. You can receive this data by adding a handler to the InfoMessage event of the SqlConnection.

The final three methods of the SqlPipe used for returning multiple rows of data are used together. SendResultsStart marks the beginning of a set of rows, SendResultsRow is used to send each row, and SendResultsEnd marks the end of the set of rows.

In addition to marking the start of the block of data, SendResultsStart is used to define the structure of the returned data. This is done by using a SqlDataRecord instance. Once you have called SendResultsStart, the only valid methods of SqlPipe that you can use are SendResultsRow and SendResultsEnd. Calling any other method results in an exception. The records you send back with each call of SendResultsRow should match the structure defined in the SendResultsStart method. In fact, to conserve server resources, it is a good idea to use the same SqlDataRecord instance for all of these calls. If you create a new SqlDataRecord with each row, you are wasting memory, as each of these objects will be marked for garbage collection. The basic process for using these three methods would work as follows:

 Dim rec As New SqlDataRecord(cols) SqlContext.Pipe.SendResultsStart(rec) For I As Integer = 1 To 10     'populate the record     rec.SetInt32(0, I)     rec.SetString(1, "Row #" & I.ToString())     rec.SetFloat(2, I * Math.PI)     rec.SetString(3, "Information about row #" & I.ToString())     SqlContext.Pipe.SendResultsRow(rec)  Next  SqlContext.Pipe.SendResultsEnd()

The following code shows the complete class, containing the stored procedure for determining the nearest store with available stock:

 Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Collections.Generic Partial Public Class StoredProcedures     <Microsoft.SqlServer.Server.SqlProcedure()> _     Public Shared Sub procGetClosestStoreWithStock(ByVal street As SqlString, _         ByVal city As SqlString, _         ByVal state As SqlString, _         ByVal zip As SqlString, _         ByVal productID As SqlInt32, _         ByVal quantity As SqlInt32)         Dim loc As Location         Dim query As String = "SELECT id, Name, Street, City, " & _             "State, Zip, GeoLocation " & _             "FROM Stores INNER JOIN Stock on Stores.id = Stock.StoreId " & _             "WHERE Stock.ProductID = @productID " & _             "AND Stock.Quantity > @quantity " & _             "ORDER BY Stock.Quantity DESC"         Dim dr As SqlDataReader         Dim result As SqlDataRecord = Nothing         'get location of requested address         loc = UserDefinedFunctions.fnGetLocation(street, city, state, zip)     Using connection As New SqlConnection("context connection=true")         connection.Open()         'pipe is used to return data to the user         Dim pipe As SqlPipe = SqlContext.Pipe         'get stores with stock         Using cmd As New SqlCommand(query, connection)             With cmd.Parameters                 .Add("@productID", SqlDbType.Int).Value = productID                 .Add("@quantity", SqlDbType.Int).Value = quantity             End With              dr = cmd.ExecuteReader()              'find the closest store             Dim distance As Double             Dim smallest As Double = Double.MaxValue             Dim storeLoc As Location             Dim rowData(6) As Object             While (dr.Read)                 dr.GetSqlValues(rowData)                 storeLoc = DirectCast(rowData(6), Location)                 distance = loc.Distance(storeLoc)                 If distance < smallest Then                     result = CopyRow(rowData)                     smallest = distance                 End If             End While              pipe.Send(result)     End Using End Using     End Sub     Private Shared Function CopyRow(ByVal data() As Object) As SqlDataRecord         Dim result As SqlDataRecord         Dim cols As New List(Of SqlMetaData)         'set up columns         cols.Add(New SqlMetaData("id", SqlDbType.Int))         cols.Add(New SqlMetaData("Name", SqlDbType.NVarChar, 50))        cols.Add(New SqlMetaData("Street", SqlDbType.NVarChar, 512))         cols.Add(New SqlMetaData("City", SqlDbType.NVarChar, 50))         cols.Add(New SqlMetaData("State", SqlDbType.Char, 2))         cols.Add(New SqlMetaData("Zip", SqlDbType.VarChar, 50))         result = New SqlDataRecord(cols.ToArray())         'copy data from row to record         result.SetSqlInt32(0, DirectCast(data(0), SqlInt32))         result.SetSqlString(1, DirectCast(data(1), SqlString))         result.SetSqlString(2, DirectCast(data(2), SqlString))         result.SetSqlString(3, DirectCast(data(3), SqlString))         result.SetSqlString(4, DirectCast(data(4), SqlString))         result.SetSqlString(5, DirectCast(data(5), SqlString))         Return result     End Function End Class

There are three basic steps to the stored procedure. First, it needs to determine the location of the inputted address. Next, it needs to find stores with available stock - that is, with stock greater than the requested amount. Finally, it needs to find the store on that list that is closest to the inputted address.

Getting the location of the address is probably the easiest step, as the fnGetLocation function already exists. Rather than needing to create and use a SqlConnection, however, because the function is a shared method of the UserDefinedFunctions class, you can use it directly from your code. This shows another benefit of the way that the VB-SQL interaction was designed. The code is the same that you would have used in a system written completely in VB, but in this case it’s actually calling a SQL Server scalar function.

Obtaining the list of stores with stock is a matter of creating a SqlCommand and using it to create a SqlDataReader. Again, this is basically the same as in any other VB application. The difference here is that the code will execute within SQL Server. Therefore, the SqlConnection is defined using the connection string “context connection = true”.

The final step in the stored procedure - finding the nearest store - requires some mathematical calculations (within the Location.Distance method). While the previous two steps could have been performed easily in straight T-SQL, it is this step that would have been the most awkward to perform using that language. The code loops through each row in the list of stores with available stock. Because all of the values from each row are needed, the GetSqlValues method copies the current row to an array of Object values. Within this array is the GeoLocation column, a value that can be cast to a Location object. Once this is done, the Distance method may be used to determine the distance between the input address and the store’s address. Once the minimum distance has been determined, the Send(SqlDataRecord) method of the SqlPipe class is used to write the data to the output stream, returning it to the calling function.

The CopyRow function is used to create the SqlDataRecord to return. The first step in creating a SqlDataRecord is defining the columns of data. The constructor for the SqlDataRecord requires an array of SqlMetaData objects that define each column. The preceding code uses the List generic collection to make defining this array easier. Once the columns are defined, the data returned from the GetValues method is used to populate the columns of the new SqlDataRecord.

Exposing Web Services from SQL Server

One additional new feature of SQL Server 2005 was the addition of support for exposing Web Services directly from the server. Doing this means that there is no requirement for IIS on the server, as the requests are received and processed by SQL Server. You define what ports will be used to host the Web Service. The structure of the Web Service is defined based on the parameters and return data of the function or stored procedure you use as the source of the Web Service.

Tip 

Exposing Web Services directly from SQL Server 2005 is only supported on the Standard and above editions. SQL Server Express does not support creating Web Services in this manner.

When you are architecting a scenario and planning on exposing Web Services from SQL Server, keep in mind at least one important question: Why? As in, why do you think you need to expose this database functionality outside of the SQL Server? It’s not a trivial question. It means that you plan on hanging data off of the server, possibly for public access. On the surface, it’s a dangerous scenario not to be taken lightly. Most of the scenarios in which it makes sense to provide Web Services directly from a SQL Server involve systems entirely behind a firewall, where Web Services are used as the conduit between departments.

The following code example shows the basic syntax of the CREATE ENDPOINT command. Although both AS HTTP and AS TCP are shown, only one can occur per create endpoint command:

 CREATE ENDPOINT endPointName [ AUTHORIZATION login ] STATE = { STARTED | STOPPED | DISABLED } AS HTTP (   PATH = 'url',   AUTHENTICATION =( { BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } [ ,...n ] ),   PORTS = ( { CLEAR | SSL} [ ,... n ] )   [ SITE = {'*' | '+' | 'webSite' },]   [, CLEAR_PORT = clearPort ]   [, SSL_PORT = SSLPort ]   [, AUTH_REALM = { 'realm' | NONE } ]   [, DEFAULT_LOGON_DOMAIN = { 'domain' | NONE } ]   [, COMPRESSION = { ENABLED | DISABLED } ]   ) AS TCP (   LISTENER_PORT = listenerPort   [ , LISTENER_IP = ALL | (<4-part-ip> | <ip_address_v6> ) ]   ) FOR SOAP(   [ { WEBMETHOD [ 'namespace' .] 'method_alias'     (   NAME = 'database.owner.name'       [ , SCHEMA = { NONE | STANDARD | DEFAULT } ]       [ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ]     )   } [ ,...n ] ]   [   BATCHES = { ENABLED | DISABLED } ]   [ , WSDL = { NONE | DEFAULT | 'sp_name' } ]   [ , SESSIONS = { ENABLED | DISABLED } ]   [ , LOGIN_TYPE = { MIXED | WINDOWS } ]   [ , SESSION_TIMEOUT = timeoutInterval | NEVER ]   [ , DATABASE = { 'database_name' | DEFAULT }   [ , NAMESPACE = { 'namespace' | DEFAULT } ]   [ , SCHEMA = { NONE | STANDARD } ]   [ , CHARACTER_SET = { SQL | XML }]   [ , HEADER_LIMIT = int ]   )

Consider the following main points when creating an endpoint:

  • What stored procedure or function (or UDF) will you be exposing? This is identified in the WebMethod clause. There may be multiple Web methods exposed from a single endpoint. Each will have a separate WebMethod parameter listing. This parameter identifies the database object you will expose, and allows you to give it a new name.

  • What authentication will clients need to use? Typically, if your clients are part of the same network, then you use integrated or NTLM authentication. If clients are coming across the Internet or from non-Windows, then you may want to use Kerberos, digest, or basic authentication.

  • What network port will the service use? The basic options when creating an HTTP endpoint are CLEAR (using HTTP, typically on port 80) or SSL (using HTTPS, typically on port 443). Generally, you should use SSL when the data transmitted requires security and you are using public networks. Note that Internet Information Services (IIS) and other Web servers also use these ports. If you have both IIS and SQL Server on the same machine, alternate ports (using CLEAR_PORT or SSL_PORT) for your HTTP endpoints. When creating TCP endpoints, select a LISTENER_PORT that is unused on your server. HTTP allows for the broadest reach and number of possible clients, while TCP allows for better performance. If you are making the Web Service available over the Internet, you would generally use HTTP and TCP within the firewall, where you can control the number and type of clients.

To continue our example, you can make the procGetClosestStoreWithStock procedure available as a Web Service using the following code:

 CREATE ENDPOINT store_endpoint   STATE = STARTED AS HTTP(   PATH = '/fooStore',   AUTHENTICATION = (INTEGRATED),   PORTS = (CLEAR),   CLEAR_PORT = 8888,   SITE = 'localhost'   ) FOR SOAP(   WEBMETHOD 'GetNearestStore' (name = 'fooStore.dbo.procGetClosestStoreWithStock'),   WSDL = DEFAULT,   SCHEMA = STANDARD,   DATABASE = 'fooStore', NAMESPACE = 'http://fooStore.com/webmethods' );

Endpoints are created within the master database, as they are part of the larger SQL Server system, and not stored within each database. The endpoint defined in the preceding code creates a SOAP wrapper around the procGetClosestStoreWithStock stored procedure, making it available as GetNearestStore. Integrated security is used, which means that any users would need network credentials on the SQL Server. If this service were available over the Internet, you might use Digest or Basic instead. Because the server is also running IIS, the port for the service was moved to 8888. Once the service is created, you can create clients based on the WSDL of the service.

Accessing the Web Service

SQL Server makes some of the work easier when hosting Web Services. The WSDL for the service is automatically generated. Many SOAP tools, such as Visual Studio, enable the creation of wrapper classes by using the WSDL for the service.

The WSDL for a SQL Server Web Service may be a little daunting when you first see it, as it’s quite lengthy. This is primarily because the WSDL includes definitions for the various SQL Server datatypes as well as for the Web Services you create. Figure 14-12 shows part of the WSDL, the part created for the procGetClosestStoreWithStock procedure.

image from book
Figure 14-12

As you can see from the WSDL, two main structures are defined: GetNearestStore and GetNearestStoreResponse. The GetNearestStore document is what is sent to the Web Service. It includes definitions of each of the columns sent, along with the expected datatypes and sizes.

GetNearestStoreResponse is the return document. In the preceding sample, you can see that it is of type SqlResultStream. This type, also defined in the WSDL, is the tabular data stream returned from SQL Server. It consists of the return value from the stored procedure and any result sets of data. This will be converted to an Object array by the SOAP wrapper classes. You can then convert these data blocks to other types.

Whenever you create a Web Service, it’s helpful to create a simple test form that can be used to test the service. Add a new Windows Forms project to the solution (or create a new Project/Solution). Select the Add Web Reference command from the Solution Explorer, and select the FooStore service (see Figure 14-13).

image from book
Figure 14-13

Once you have the connection to the Web Service, you’re ready to begin laying out the fields of the test form. Most of the fields are TextBox controls, with the exception of the Product ComboBox and the DataGridView on the bottom. The following table describes the properties set on the controls:

Open table as spreadsheet

Control

Property

Value

TextBox

Name

StreetField

TextBox

Name

CityField

TextBox

Name

StateField

MaxLength

2

TextBox

Name

ZipField

MaxLength

11

ComboBox

Name

ProductList

TextBox

Name

QuantityField

Button

Name

GetNearestStoreButton

Text

&Get Nearest Store

DataGridView

Name

ResultGrid

AllowUserToAddRows

False

AllowUserToDeleteRows

False

ReadOnly

True

Organize the controls on the form in any way you find aesthetically pleasing. Figure 14-14 shows one option.

image from book
Figure 14-14

The code for the test form is as follows:

 Imports System.Data Imports System.Data.SqlClient Public Class MainForm     Private Sub GetNearestStoreButton_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles GetNearestStoreButton.Click         Using svc As New fooStore.store_endpoint             Dim result() As Object             Dim data As New DataSet             result = svc.GetNearestStore(Me.StreetField.Text, _                 Me.CityField.Text, _                 Me.StateField.Text, _                 Me.ZipField.Text, _                 CInt(Me.ProductList.SelectedValue), _                 CInt(Me.QuantityField.Text))             If result IsNot Nothing Then                 data = DirectCast(result(0), DataSet)                 Me.ResultGrid.DataSource = data.Tables(0)             End If         End Using     End Sub      Private Sub MainForm_Load(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles MyBase.Load         Dim ds As New DataSet         Using conn As New SqlConnection(My.Settings.FooStoreConnectionString)             Using da As New SqlDataAdapter("SELECT id, Name FROM PRODUCTS", conn)                 da.Fill(ds)                 With Me.ProductList                     .DataSource = ds.Tables(0)                     .ValueMember = "id"                     .DisplayMember = "Name"                 End With             End Using         End Using     End Sub End Class

The test form consists of two methods. The Load method is used to retrieve the data that populates the product drop-down. The call to the Web Service takes place in the Button click event. This method calls the Web Service wrapper, passing in the values entered on the form. Recall that the Web Service returns two result sets: the data and the return value.

Run the test application. Enter an address close to one of the stores, and select a product and quantity you know to be available. Click the Get Nearest Store button. After a brief delay, the store’s address should appear (see Figure 14-15). Try again with a larger quantity or different product so that another store is returned. Depending on the stock available at each of the store locations, the nearest store may not be all that near.

image from book
Figure 14-15




Professional VB 2005 with. NET 3. 0
Professional VB 2005 with .NET 3.0 (Programmer to Programmer)
ISBN: 0470124709
EAN: 2147483647
Year: 2004
Pages: 267

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net