Building the Web Services
The first task is to build the Web Services that your mobile application will use. You'll be creating three Web Services with two methods each that access the sample Northwind Traders database. While you would typically not build Web Services for this purpose, they serve a useful purpose here to show you another method in which Web Services can be integrated into different types of applications.
To get started, create a new Web Service project in your choice of languages. For this project, we'll be using Visual Basic .NET, as has been done throughout these projects.
Adding the Database Class
The first task is to add the Database class to get access to SQL Server without having to write a lot of extra data access code. This class makes it easy to connect to and retrieve data from the selected database.
This class should be added to the Web Service project, because that is where you'll be accessing the database. In other cases, this class would belong in a shared assembly so it could be used among multiple projects. This class's code was covered in Chapter 12, “Business to Business XML Web Services,” but Listing 14.1 provides it here for reference.
Listing 14.1. Database.vb
Imports System.Data.SqlClient
Imports System.Configuration
Public Class Database
Private m_cnDB As SqlConnection
'
' This constructor reads the application configuration
' file (Web.config for web applications) for a string
' called ConnectionString. If it's not there, an exception
' is thrown. Otherwise, the connection is made.
'
Public Sub New()
Dim strConn As String
strConn = ConfigurationSettings.AppSettings("ConnectionString")
If strConn = "" Then
Throw New Exception("Connection string not found " _
& "in application configuration file.")
Else
m_cnDB = New SqlConnection(strConn)
m_cnDB.Open()
End If
End Sub
'
' This constructor accepts a connection string as input
' and makes a connection to that SQL Server.
'
Public Sub New(ByVal ConnectionString As String)
m_cnDB = New SqlConnection(ConnectionString)
m_cnDB.Open()
End Sub
'
' In case there are other objects that need the live
' connection, make it available through a read-only
' property.
'
Public ReadOnly Property Connection() As SqlConnection
Get
Return m_cnDB
End Get
End Property
'
' Run a query that returns records in the form
' of a SqlDataReader.
'
Public Function GetDataReader(ByVal SQL As String, _
Optional ByVal blnSkipRead As Boolean = False) As SqlDataReader
Dim cmdQuery As New SqlCommand()
Dim dr As SqlDataReader
cmdQuery.Connection = m_cnDB
cmdQuery.CommandText = SQL
cmdQuery.CommandType = CommandType.Text
dr = cmdQuery.ExecuteReader
If Not blnSkipRead Then dr.Read()
Return dr
End Function
'
' Run a query that returns records in the form
' of a DataSet.
'
Public Function GetDataSet(ByVal SQL As String) As DataSet
Dim da As New SqlDataAdapter(SQL, m_cnDB)
Dim ds As New DataSet("Results")
da.Fill(ds)
Return ds
End Function
'
' Close the database connection.
'
Public Sub Close()
m_cnDB.Close()
End Sub
End Class
|
The class has two constructors—one that takes no arguments, and one that takes a connection string. The one that doesn't take arguments reads the ConnectionString variable from the web.config file located in the web application's directory. The ConnectionString is located in the AppSettings section, as shown in the following:
... rest of Web.config file ...
</system.web>
<appSettings>
<add key="ConnectionString"
value="server=(local);database=Northwind;UID=sa;PWD=;" />
</appSettings>
</configuration>
Creating the Products Service
Because the point of this chapter is to show how to integrate the Web Services with various platforms, these web methods are straightforward and similar to ones you've built before. The code you need to add to your Web Service file is highlighted in bold in Listing 14.2.
Listing 14.2. Products.asmx
Imports System.Web.Services
<WebService(Namespace:="http://www.nwind.ncs/")> _
Public Class NorthwindProducts
Inherits System.Web.Services.WebService
Private db As Database
#Region " Web Services Designer Generated Code "
Public Sub New()
MyBase.New()
'This call is required by the Web Services Designer.
InitializeComponent()
'Add your own initialization code after the InitializeComponent() call
db = New Database()
End Sub
'Required by the Web Services Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Web Services Designer
'It can be modified using the Web Services Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
components = New System.ComponentModel.Container()
End Sub
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
'CODEGEN: This procedure is required by the Web Services Designer
'Do not modify it using the code editor.
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
#End Region
<WebMethod(Description:="Returns all products from database, sorted by name.")> _
Public Function GetList() As DataSet
Return db.GetDataSet("SELECT ProductID, ProductName FROM Products " & "ORDER BY
 ProductName")
End Function
<WebMethod(Description:="Returns product details for selected product.")> _
Public Function GetDetails(ByVal ProductID As Integer) As DataSet
Return db.GetDataSet("SELECT * FROM Products WHERE ProductID = " & ProductID.ToString())
End Function
End Class
|
After instantiating the Database object in the New constructor method, it connects to the database specified in the web.config file for the application. When that is done, the Web Service has a live database connection. Depending on the latency between calls, you may want to open the connection and have a timeout value so that it is shut down when it is not in use.
The first method returns a simple list. Based on the way the list will be displayed in the mobile device, only the product name will be visible, so only that field along with the primary key is required. This data is returned as a DataSet so that it can be bound to the mobile data control you'll learn about.
The second method returns the details for a particular product ID. The form will be showing some of the fields from the table, but not all. If you want, you can modify this query to only return the requested fields.
After you build this application, you can test this Web Service by exercising both methods. The first method will return a list of all the products, from which you can choose a product ID to feed into the second method. When you're sure that the Web Service is working fine, you can move on to the second part of this project.
Creating the Customers Service
The next service you need to build is used to retrieve customers and customer details. This service also has two methods—one to retrieve the list and one to retrieve the details of a particular record. One difference between this service and the last is the fact that the customer ID is a string instead of a number. The code is shown in Listing 14.3, with the code you need to write shown in boldface.
Listing 14.3. Customers.asmx
Imports System.Web.Services
<WebService(Namespace:="http://www.nwind.ncs/")> _
Public Class NorthwindCustomers
Inherits System.Web.Services.WebService
Private db As Database
#Region " Web Services Designer Generated Code "
Public Sub New()
MyBase.New()
'This call is required by the Web Services Designer.
InitializeComponent()
'Add your own initialization code after the InitializeComponent() call
db = New Database()
End Sub
'Required by the Web Services Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Web Services Designer
'It can be modified using the Web Services Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()>
Private Sub InitializeComponent()
components = New System.ComponentModel.Container()
End Sub
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
'CODEGEN: This procedure is required by the Web Services Designer
'Do not modify it using the code editor.
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
#End Region
<WebMethod(Description:="Returns all customers, sorted by name.")> _
Public Function GetList() As DataSet
Return db.GetDataSet("SELECT CompanyName, 'customers.aspx?id='" & " + CustomerID As
CustomerLink FROM Customers ORDER BY CompanyName")
End Function
<WebMethod(Description:="Returns details for selected customer.")> _
Public Function GetDetails(ByVal CustomerID As String) As DataSet
Return db.GetDataSet("SELECT * FROM Customers WHERE CustomerID = '" & CustomerID
.ToUpper() & "'")
End Function
End Class
|
After initializing the service, the two methods are ready for business. The GetList method returns a DataSet with the customer name and a link to the customer that will be used later. The GetDetails method returns the details for a customer, given the customer ID. In this case, we change the customer ID to an uppercase string, because that's how it is in the database. If you want to force case sensitivity, simply remove the ToUpper() call in the GetDetails method.
Creating the Orders Service
The third Web Service you'll be creating works with the Orders
table to provide information about the orders placed by a particular customer. The code for the service is shown in Listing 14.4.
Listing 14.4. Orders.asmx
Imports System.Web.Services
<WebService(Namespace:="http://www.nwind.ncs/")> _
Public Class NorthwindOrders
Inherits System.Web.Services.WebService
Private db As Database
#Region " Web Services Designer Generated Code "
Public Sub New()
MyBase.New()
'This call is required by the Web Services Designer.
InitializeComponent()
'Add your own initialization code after the InitializeComponent() call
db = New Database()
End Sub
'Required by the Web Services Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Web Services Designer
'It can be modified using the Web Services Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()>
Private Sub InitializeComponent()
components = New System.ComponentModel.Container()
End Sub
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
'CODEGEN: This procedure is required by the Web Services Designer
'Do not modify it using the code editor.
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
#End Region
<WebMethod(Description:="Returns all orders from database by customer ID.")>
Public Function GetList(ByVal CustomerID As String) As DataSet
Return db.GetDataSet("SELECT CONVERT(varchar, OrderDate, 101) " & " As
OrderDateConverted, " _
& "'orders.aspx?oid=' + convert(varchar, OrderID) As OrderLink " & "FROM Orders WHERE
CustomerID = '" _
& CustomerID.ToUpper & "' ORDER BY OrderDate ASC")
End Function
<WebMethod(Description:="Returns details for selected order.")> _
Public Function GetDetails(ByVal OrderID As Integer) As DataSet
Return db.GetDataSet("SELECT CONVERT(varchar, OrderDate, 101) " & "As
OrderDateConverted, "
& "CONVERT(varchar, ShippedDate, 101) As ShippedDateConverted " _
& "FROM Orders WHERE OrderID = " & OrderID.ToString())
End Function
End Class
|
As in the Customers service, the customer ID is uppercased to allow any lowercase requests to be handled without generating errors. In this case, the GetList requires a customer ID to be entered to select the orders, which are returned with the order ID and the date. The order ID is actually embedded into a URL so that when it is put into the mobile device's List control, it is clickable and will pass the correct data to the orders page.
After the order ID has been obtained, the customer ID is not necessary for the next request, which is to retrieve the details about the order. In this case, the method retrieves the other fields from the Orders table, not the Order Details table. If you wanted the line items in the order, it's a simple next step to add to this application. The SQL query actually converts the dates being retrieved to the following format:
MM/DD/YYYY
This could be done in the Web page, as well, but it's just as easy to do it right in the query.
After you get all the Web Service code entered, be sure to build and test each service and each method. It's easier to find the bugs here before getting into the next portion of the chapter. With the Web Services created and tested, it's time to build the mobile application to wrap these services with a user interface.
