Browse DevX
Sign up for e-mail newsletters from DevX


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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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, Nothing, Nothing) 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 & "'" Else sSQL &= "((TYearNumber = " & iYear.ToString() _ & " AND TWeekNumber > " & iWeek.ToString() _ & ") OR (TYearNumber > " & iYear.ToString() _ & ")) AND HostIP = '" & sHostIP _ & "' ORDER BY TYearNumber, TWeekNumber" End If Return sSQL End Function

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) As DataSet Try Dim oDT As New DataTable("Errors") oDT.Columns.Add("Message", System.Type.GetType("System.String")) Dim oDR As DataRow = oDT.NewRow() oDR("Message") = sMessage oDT.Rows.Add(oDR) Dim oDS As New DataSet("Error") oDS.Tables.Add(oDT) Return oDS Catch Return Nothing End Try End Function

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).

Thanks for your registration, follow us on our social networks to keep up-to-date