Synchronize Your Databases with .NET Web Services (Part I) : Page 5
The ever-increasing use of XML is an exciting development in Web site design and construction, and provides new ways for site authors to expose information to visitors. In this two-part article, you'll see how to create a data-driven Web service and explore three different ways to consume it. Then, in part II, you'll see how to use such services to synchronize the content in distributed databases automatically.
by Alex Homer
Mar 10, 2004
Page 5 of 8
The Sample Web Service
The sample PublicIISLogs Web service (see the sample code) exposes four Web methods, including one named TrafficSummary that returns data on the total traffic for our site over the past 52 weeks. Here's the code for that method:
<WebMethod(Description:="Traffic summary for the previous 52 weeks")> _
Public Function TrafficSummary() As DataSet
Dim sTableName As String = "WeekSummary"
Dim dFromDate As Date = DateTime.Now.AddMonths(-12)
Dim sSQL As String = GetSQLStatement( _
Return GetDataSet(sSQL, sTableName)
Catch oErr As Exception
You can see that it uses three other routines within the class file: GetSQLStatement, GetDataSet, and GetErrorDataSet. These are all declared as Private and aren't available as Web service methods because they don't have the WebMethod attribute. The GetSQLStatement method builds the rather complex SQL statement required to extract rows from the IISLogs database, using the table name and starting date values passed into this routine as parameters:
Private Function GetSQLStatement(sTableName As String, _
dFromDate As Date) As String
Dim oCal As New System.Globalization.GregorianCalendar
Dim iYear As Integer = dFromDate.Year
Dim iWeek As Integer = oCal.GetWeekOfYear( _
dFromDate, Nothing, Nothing)
sSQL = "SELECT Year = MAX(TYearNumber), Week = _
MAX(TWeekNumber), " _
& "Hits = SUM(HitCount), KBytes = SUM(KBytes), " _
& "Sessions = SUM(Sessions) FROM " & sTableName _
& " WHERE ((TYearNumber = " & iYear.ToString() _
& " AND TWeekNumber > " & iWeek.ToString() _
& ") OR (TYearNumber > " & iYear.ToString() _
& ")) GROUP BY TYearNumber, TWeekNumber " _
& "ORDER BY TYearNumber, TWeekNumber"
The method returns the completed SQL statement. The GetDataSet method uses this statement and the table name to populate a DataSet with the required results. The source database connection string comes from a key value named IISLogs within the web.config file:
Private Function GetDataSet(sSQL As String, _
sTableName As String) As DataSet
Dim sConnect As String = _
Dim oDS As New DataSet()
Dim oConn As New SqlConnection(sConnect)
Dim oDA As New SqlDataAdapter(sSQL, oConn)
The method returns the populated DataSet object.
Managing Error Situations and Returning Status Information
One interesting problem that arises regularly when dealing with a Web service involves managing errors. It's easy enough to trap an exception in the ASP.NET code, and get details of the error that caused the exception to be raised. But how do you communicate error information to the client is a useful way?
If you're passing back a simple string, such as the current time (as described at the start of this article), you could just trap the error and send back a string such as: "Sorry, an error occurred", or even something more detailed like: "The remote time server failed to respond". However, if the client is expecting a non-string data type then you can't send back a string.
For example, if the Web service usually returns an integer value, you might be able to use a specific value (perhaps -1 or 999) to represent an error. This pre-supposes that the client and server can agree beforehand on what return values are valid, what values are treated as errors, and what error each value represents.
But in this example, the clients expect a DataSet, and so your choices are to send back a DataSet or the value Nothing (null in C#), to indicate a failure, but that's not particularly expressive or useful. Instead, the sample code creates a DataSet containing a single table named Errors, which has a single column named Message. When an error occurs, this column contains the text error message returned to the client. The code calls the GetErrorDataSet function whenever any error occurs while executing the TrafficSummary method:
Private Function GetErrorDataSet(sMessage As String) _
Dim oDT As New DataTable("Errors")
Dim oDR As DataRow = oDT.NewRow()
oDR("Message") = sMessage
Dim oDS As New DataSet("Error")
Figure 4 shows an example of the XML returned when an error occurs.