Synchronize Your Databases with .NET Web Services (Part II) : Page 4
When data gets created in many locations, you often need to create a process that collects and copies this data to multiple sites. In this article, you'll see how to use Web services to automatically synchronize remote databases in a decentralized way, by letting each machine query the others until they all contain the same data.
by Alex Homer
Mar 15, 2004
Page 4 of 10
Building the SQL Statement
As the code in the GetLogTables method iterates through the list of tables, it uses a separate private function to create the SQL statement that extracts the required set of rows from the database. The GetSQLStatement function takes three parameters (the host IP address, the name of the table currently being processed, and the date that the last synchronization took place). Note that the method uses a GregorianCalendar instance to obtain the week number from the DateTime value passed as the last synchronization date. This is required because some of the tables contain a year and week number instead of a datetime column:
Private Function GetSQLStatement(sHostIP As String, _
sTableName As String, _
dLastUpdate As Date) As String
Dim oCal As New System.Globalization.GregorianCalendar
Dim iYear As Integer = dLastUpdate.Year
Dim iWeek As Integer = oCal.GetWeekOfYear(dLastUpdate,
Dim sSQL As String = "SELECT * FROM " & sTableName & _
" WHERE "
If sTableName.ToLower() = "pagesbyweeksummary" _
Or sTableName.ToLower() = "trafficbyhoursummary" Then
sSQL &= "DATEDIFF(day, '" &
dLastUpdate.ToString("yyyy-MM-dd") & _
"', TSumDate) > 0 AND TSiteIP = '" & sHostIP & "'"
sSQL &= "((TYearNumber = " & iYear.ToString() _
& " AND TWeekNumber > " & iWeek.ToString() _
& ") OR (TYearNumber > " & iYear.ToString() _
& ")) AND HostIP = '" & sHostIP _
& "' ORDER BY TYearNumber, TWeekNumber"
The GetSQLStatement method calls the GetWeekOfYear method, which takes three parameters: the date to work from, a CalendarWeekRule value (which indicates how the first week of a new year should be determined), and a DayOfWeek value (which determines what day is the first each week). Passing the value Nothing (null in C#) for these two parameters uses the defaults determined by the culture and internationalization settings of the machine, which should match the values used by ASP.NET and the database server software. For more details check out the GregorianCalendar class in the System.Globalization namespace section of the .NET SDK Reference section.
Returning Error Details
Another private function in the Web service class creates a DataSet containing error details, if any process within the service throws an exception. This routine is identical to the one shown in Part I of this article series. You'll see how a client application uses this "error" DataSet later in this article:
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 5 shows the test page that ASP.NET generates automatically for the GetLogTables method when you select it in the main service description page (which was shown in Figure 4).