لطفا منتظر باشید ...
Practical Example
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.
PETRAS Web Service
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 functionTimeSheet.XSD, containing the XSD file for the XML passed to the StoreTimeSheet functionPETRAS.asmx, containing the code for the Web serviceThe 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.
Listing 23-14. Example XML Output from the GetStaticData Function
<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>
Listing 23-15. Example XML Passed to the StoreTimeSheet Function
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
<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>
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.
Listing 23-16. The GetStaticText 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:
<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
Listing 23-17 shows the code for the StoreTimeSheet function, with the error handling removed for clarity.
Dim bhRow As PETRASTimeSheet.BillableHoursRow
For Each bhRow In dsTimeSheet.BillableHours.Rows
Listing 23-17. The StoreTimeSheet 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).
<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
PETRAS Timesheet
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).Chapter 23
Listing 23-18. Populating the Consultant List from GetStaticData
We also use the MSXML library to create our XML in the PostTimeEntriesToWebService procedure, as shown in Listing 23-19.
''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
Listing 23-19. Building the XML to Submit to the Web Service
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
PETRAS Reporting
The PETRAS reporting application has not changed for this chapter, because it is still retrieving its data from the same database as before.
