The examples used to explain XML and Web services were obviously very simple so we could focus on the technology and how to use it, particularly with the explanation of Web services. In the practical example for this chapter, we create a rather more complex Web service to act as an interface between our PETRAS timesheet add-in and the central database used to store the static information of consultants, clients and projects and submitted timesheet data. By using the Web service, our consultants can now access the time sheet information over the Internet, enabling them to submit their timesheets from anywhere in the world.
The data sent between the Web service and the add-in will be done using XML, which makes it extremely easy to connect to the database and enables us to validate the data structure at each end of the communication. Specifically, the following data will be passed between the Web service and the add-in:
At startup, the add-in will retrieve some XML containing the static lists of consultants, clients, projects and activities, by calling the Web service''s GetStaticData function.
When the user clicks the Post Timesheet Data button, the add-in will generate some XML to contain the timesheet data and send it to the Web service, which will store it in the central database.
The new PETRAS Web service has been written using Visual Basic.NET, connecting to the same Access database we introduced in Chapter 13 Programming with Databases, but this time using ADO.NET. Visual Studio.NET creates numerous files for a Web service. The interesting files are:
StaticData.XSD, containing the XSD file for the XML returned by the GetStaticData function
TimeSheet.XSD, containing the XSD file for the XML passed to the StoreTimeSheet function
PETRAS.asmx, containing the code for the Web service
The Web service provides the following two functions:
GetStaticData, which returns an XML data set containing all the lists of consultants, activities, clients and projects. An example of the XML returned is shown in Listing 23-14. Notice that the Project elements for a client are nested inside the Client element.
StoreTimeSheet, which is passed an XML data set containing the timesheet data entered into the Excel sheet, writes the data to the BillableHours table of the Access database and returns a confirmation message that includes the number of rows inserted. An example of the XML passed is shown in Listing 23-15.
<StaticData xmlns="http://www.oaltd.co.uk/PETRASWeb/StaticData"> <Consultant> <ID>1</ID> <Name>Rob Bovey</Name> </Consultant> <Consultant> <ID>2</ID> <Name>Stephen Bullen</Name> </Consultant> <Activity> <ID>1</ID> <Name>General Programming</Name> </Activity> <Activity> <ID>2</ID> <Name>Phone Conference</Name> </Activity> <Client> <ID>1</ID> <Name>Big Auto Corp.</Name> <Project> <ID>1</ID> <Name>BAC 1</Name> </Project> <Project> <ID>2</ID> <Name>BAC 2</Name> </Project> </Client> </StaticData>
<TimeSheet xmlns="http://www.oaltd.co.uk/PETRASWeb/TimeSheet"> <Consultant> <ID>2</ID> <Name>Stephen Bullen</Name> </Consultant> <WeekEnding>2004-07-04</WeekEnding> <BillableHours> <DateWorked>2004-07-01</DateWorked> <ProjectID>2</ProjectID> <ActivityID>1</ActivityID> <Hours>6.75</Hours> </BillableHours> <BillableHours> <DateWorked>2004-07-02</DateWorked> <ProjectID>2</ProjectID> <ActivityID>1</ActivityID> <Hours>7.5</Hours> </BillableHours> </TimeSheet>
All of the data connectivity for the Web service is set up using Visual Studio''s wizards, resulting in the following objects that can be seen on the Web service''s "Design" page:
conPETRASDbConnection An OleDbConnection used to define the connection to the Access database.
daConsultants, daActivities, daClients and daProjects OleDbDataAdapters, used to retrieve the list of consultants, activities, clients and projects from the database.
cmDeleteTime An OleDbCommand to delete timesheet records from the database. When a timesheet is submitted, any previous records for the same consultant and period are deleted.
cmInsertTime An OleDbCommand to insert timesheet records into the database.
When we include an XSD file in a Visual Studio.NET project, we have the option of automatically creating a DataSet from the schema. After we''ve done that, we can map the elements in our schema to fields in a DataAdapter, in much the same way that Excel 2003 enables us to map elements to worksheet cells. In our Web service, each section of the schema is mapped to its own DataAdapter. (For example, the <ID> and <Name> in the <Consultant> elements in the XSD are mapped to the ConsultantID and Name fields in the daConsultants DataAdapter.) Having mapped everything in our StaticData schema to the DataAdapters, we can retrieve the XML for all our static lists by telling each of the DataAdapters to fill their part of the schema, then reading the XML from the data set, as shown in the code for the GetStaticData function in Listing 23-16.
<WebMethod(Description:="Provides all the static data " & _ for the PETRAS Time Sheet")> _ Public Function GetStaticData() As String ''Declare an instance of our StaticData data set, ''which was generated by .NET from the XSD Dim dsStatic As New StaticData ''Set the connection string of our connection object Me.conPETRASDbConnection.ConnectionString = _ "Provider="Microsoft.Jet.OLEDB.4.0";Data Source="" & _ msDATABASE & "";User ID=Admin;Password=;" ''Clear the data set dsStatic.Clear() ''Fill each section of the data set daConsultants.Fill(dsStatic) daActivities.Fill(dsStatic) daClients.Fill(dsStatic) daProjects.Fill(dsStatic) ''Return the resulting XML Return dsStatic.GetXML End Function
The DataSet created from our XSD is a strongly typed object that enables us to treat our data as if it were a full object modelso each of our XML complexType elements become objects, our repeating elements become collections and our simple element types become properties. We are then able to use the names of our data types directly in our code, such as iterating through all the <BillableHours> elements of the timesheet table:
Dim bhRow As PETRASTimeSheet.BillableHoursRow For Each bhRow In dsTimeSheet.BillableHours.Rows
Listing 23-17 shows the code for the StoreTimeSheet function, with the error handling removed for clarity.
<WebMethod(Description:="Writes time sheet data to the " & _
"central database")> _
Public Function StoreTimeSheet(ByVal sTimesheet As String) _
As String
Dim dsTimeSheet As New PETRASTimeSheet
Dim iConsultant As Integer
Dim dtWeekEnd As Date
Dim bhRow As PETRASTimeSheet.BillableHoursRow
''Read the text into the data set and validate it
dsTimeSheet.ReadXML(New System.IO.StringReader(sTimesheet))
''Get the consultant ID and week ending
iConsultant = dsTimeSheet.Consultant(0).ID
dtWeekEnd = dsTimeSheet.TimeSheet(0).WeekEnding
''Open the database connection
conPETRASDbConnection.ConnectionString = _
"Provider="Microsoft.Jet.OLEDB.4.0";Data Source="" _
& msDATABASE & "";User ID=Admin;Password=;"
conPETRASDbConnection.Open()
''Clear any existing data for this consultant and week
With cmDeleteTime
.Parameters("prmConsultantID").Value = iConsultant
.Parameters("prmWeekStart").Value = dtWeekEnd.AddDays(-6)
.Parameters("prmWeekEnd").Value = dtWeekEnd
.ExecuteNonQuery()
End With
''Loop through the billable hours, adding them to the table
With cmInsertTime
.Parameters("prmConsultantID").Value = iConsultant
''We can treat our data like objects!
For Each bhRow In dsTimeSheet.BillableHours.Rows
.Parameters("prmDateWorked").Value = bhRow.DateWorked
.Parameters("prmProjectID").Value = bhRow.ProjectID
.Parameters("prmActivityID").Value = bhRow.ActivityID
.Parameters("prmHours").Value = bhRow.Hours
.ExecuteNonQuery()
Next
End With
''Close the connection when we''re done
conPETRASDbConnection.Close()
''Return an OK message, with the number of rows inserted
Return "OK:" & dsTimeSheet.BillableHours.Rows.Count & _
" row(s) inserted for " & dsTimeSheet.Consultant(0).Name
End Function
Note that the Web service we have created for this book is for demonstration purposes only and should not be used in a production environment. We have not included any security checks in our connectivity, nor any data validation checks (other than that provided by the XML schema), so anyone who can connect to the Web service could insert records into our timesheet database (assuming they can work out the XML schema we''re using).
The PETRAS timesheet add-in has been changed for this chapter to receive data from and send data to the new Web service, instead of connecting directly to the central database across the network. To maintain compatibility with Excel 2000 onward, we do not use Excel 2003''s XML handling features, instead using the MSXML object library directly to do the validating and parsing of the XML data we receive from the Web service. Similarly, we construct the XML containing our timesheet information using VBA. The communication between the add-in and the Web service is done using classes generated using the Web Services Toolkit.
Because we''re no longer connecting directly to the database, we no longer need the Browse for Database feature, which has been replaced by a simple input box to provide the URL of the PETRAS Web service (in case we have to deploy it to a different server).
Module
Procedure
Change
wksCommandBars
Renamed menu items to refer to the Web service instead of the database.
MEntryPoints
PostTimeEntriesToWebService
Renamed to refer to Web service. Modified to create XML string instead of UDT and submit to Web service.
MEntryPoints
SpecifyWebServiceLocation
Renamed to refer to Web service. Modified to use an input box to specify the Web service URL instead of a folder.
MBrowseForFolder
Removed module as it is no longer required.
CPetrasWeb (new class)
Class created by the Web Service Toolkit to handle the connection to the PETRAS Web service.
MOpenClose
Remove calls to create and destroy the database connection.
MDataAccess
Modified to communicate with the Web service (via the CPetrasWeb class) instead of the database, importing the XML using VBA.
MDataAccess
bLoadInitialData
Rewritten to retrieve the data from the XML obtained from the Web service and populate the static data worksheet.
The most interesting changes to the PETRAS timesheet add-in are in the MDataAccess.bLoadInitialData and MEntryPoints.PostTimeEntries ToWebService procedures. Part of the bLoadInitialData routine is shown in Listing 23-18, showing the VBA to extract the Consultant data from the XML and populate the static data sheet. Using the MSXML library to parse the XML enables us to navigate through our data using syntax very similar to navigating an object library:
''An object to parse the XML from GetStaticData Dim xmlParser As MSXML2.DOMDocument40 ''Objects use to navigate around the XML Dim xeParent As MSXML2.IXMLDOMElement ''Create an instance of the Web service connection Set clsPetrasWeb = New CPetrasWeb ''Initialise the URL clsPetrasWeb.WebServiceURL = GetSetting(gsREG_APP, _ gsREG_SECTION, gsREG_KEY, clsPetrasWeb.WebServiceURL) ''Connect to the Web service clsPetrasWeb.Connect ''Get the XML representing the static lists sXML = clsPetrasWeb.GetStaticData ''Load the XML into the MSXML parser Set xmlParser = New MSXML2.DOMDocument30 xmlParser.LoadXml sXML ''Use XPath expressions to find our elements xmlParser.SetProperty "SelectionLanguage", "XPath" ''Specify the default namespace to look for, giving it ''the alias ''sd'' to use in our element names xmlParser.SetProperty "SelectionNamespaces", _ "xmlns:sd="http://www.oaltd.co.uk/PETRASWeb/StaticData"" '' Load each of the program data lists. '' Consultants With wksProgData.Range(gsRNG_CONSULT_TOP) ''Remove any existing consultants .CurrentRegion.Offset(1, 0).ClearContents lItem = 1 ''Loop through all the Consultant elements in the XML ''Equivalent to: For Each oConsultant in Consultants For Each xeParent In xmlParser.selectNodes( _ "sd:StaticData/sd:Consultant") lItem = lItem + 1 ''Store the consultant name and ID ''Equivalent to: Cell.Value = oConsultant.Name .Cells(lItem, 1).Value = xeParent.selectSingleNode( _ "sd:Name").nodeTypedValue .Cells(lItem, 2).Value = CLng(xeParent.selectSingleNode( _ "sd:ID").nodeTypedValue) Next End With
We also use the MSXML library to create our XML in the PostTimeEntriesToWebService procedure, as shown in Listing 23-19.
Public Sub PostTimeEntriesToWebService()
Dim rngCell As Range
Dim rngTable As Range
Dim domXML As MSXML2.DOMDocument
Set rngTable = wksSheet.Range(gsRNG_BILLABLE_HOURS)
''Create a new XML document
Set domXML = New MSXML2.DOMDocument
''Create the root element <TimeSheet>
Set domXML.documentElement = _
NewElement(domXML, "TimeSheet")
With domXML.documentElement
''Add the <Consultant> element
With .appendChild(NewElement(domXML, "Consultant"))
''Add the Consultant''s ID and Name elements and values
.appendChild(NewElement(domXML, "ID")) _
.nodeTypedValue = rngTable.Cells(1, 1).Value
.appendChild(NewElement(domXML, "Name")) _
.nodeTypedValue = wksSheet.Range("inpEmployee").Value
End With
''Add the WeekEnding element and value
.appendChild(NewElement(domXML, "WeekEnding")) _
.nodeTypedValue = Format( _
wksSheet.Range("inpWeekEnding").Value, "yyyy-mm-dd")
'' Loop each entry in the time sheet and add it to the XML
For Each rngCell In rngTable
''Add a <BillableHours> element
With .appendChild(NewElement(domXML, "BillableHours"))
''Add the elements for a BillableHours record
.appendChild(NewElement(domXML, "DateWorked")) _
.nodeTypedValue = Format(_
rngCell.Offset(0, 1).Value, "yyyy-mm-dd")
.appendChild(NewElement(domXML, "ProjectID")) _
.nodeTypedValue = rngCell.Offset(0, 2).Value
.appendChild(NewElement(domXML, "ActivityID")) _
.nodeTypedValue = rngCell.Offset(0, 3).Value
.appendChild(NewElement(domXML, "Hours")) _
.nodeTypedValue = _
Trim$(Str$(rngCell.Offset(0, 4).Value))
End With
Next rngCell
End With
''Submit the XML to the Web service
bSubmitXML domXML.XML
''etc.
End Sub
'' Create a new element with our namespace
Private Function NewElement( _
ByRef domXML As MSXML2.DOMDocument, _
ByVal sElementName As String) As IXMLDOMNode
Const sNS As String = _
"http://www.oaltd.co.uk/PETRASWeb/TimeSheet"
Set NewElement = domXML.createNode(NODE_ELEMENT, _
sElementName, sNS)
End Function
The PETRAS reporting application has not changed for this chapter, because it is still retrieving its data from the same database as before.