Browse DevX
Sign up for e-mail newsletters from DevX


Synchronize Your Databases with .NET Web Services (Part II)  : Page 8

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

The Synchronization Code
The ProcessAllSynchOperations method's task is to implement the process shown in the schematic in Figure 8. Before it gets called, a couple of other routines (not shown here) collect the values from the configuration file. These routines split the list of Web service URLs (the Web sites from which to collect new rows) into an array named gSourceURLArray; split the list of "key" values— (the matching IP addresses of these sites)— into an array named gSiteIPArray; and split the list of table names into an array named gTableArray.

Code involved only in writing status information to the Form window and the log file has been removed from this section.

Iterating Through the Web Service URLs
The ProcessAllSynchOperations method iterates through the gSourceURLArray array of Web service URLs using an Integer index to extract the matching key value from the gSiteIPArray. A separate function reads the file containing the last successful update date for that site. The URL, key, and date information are then used in the call to each site's Web service. If all goes well you get back a DataSet containing all the new rows from all the tables for that site; otherwise you get a DataSet containing only a single table named Errors, or Nothing if the remote Web service fails completely. The method checks for both these conditions. Notice how it extracts the error message if the service returns an "error" DataSet. The method throws an exception that is caught in the main routine within the application, which displays and logs the message and then halts execution.

Sub ProcessAllSynchOperations() ' iterate through all the Web service URLs For iLoop = 0 To gSourceURLArray.Length - 1 ' get values for this source site sSourceURL = gSourceURLArray(iLoop).Trim() sWebSiteIP = gSiteIPArray(iLoop).Trim() dLastUpdate = GetLastDateLoaded(sWebSiteIP) ' call routine to fetch the DataSet from ' the remote Web service Dim oDS As DataSet = FetchTables(sSourceURL, sWebSiteIP, gTableNames, dLastUpdate) ' see if there were any errors with the Web service If oDS Is Nothing Then Throw New Exception( _ "No DataSet returned from Web service") End If If oDS.Tables(0).TableName = "Errors" Then Throw New Exception( "Error reported by Web service: " & _ oDS.Tables(0).Rows(0)(0)) End If ...

Iterating Through the List of Tables
At this point, you know that the service returned a DataSet containing any new rows, so you begin to insert the rows into the local database. You need to create a connection to the local database, open it, and start a transaction over this connection, iterating through the list of tables held in gTableArray. If no table in the DataSet matches the current item in the gTableArray list, (there were no rows added on the source machine), the application simply logs and displays a message. However, if the table does exist, it checks to see if there are any new rows in it using the Count property of the Rows collection:

... Dim oConn As SqlConnection Dim oTrans As SqlTransaction Try ' create Connection, open it and start a transaction oConn = New SqlConnection(gConnStr) oConn.Open() oTrans = oConn.BeginTransaction() ' iterate through all the tables in the list Dim sTableName As String For Each sTableName In gTableArray ' check if table is in DataSet -- ' might not be if there were no new rows If oDS.Tables(sTableName) Is Nothing Then WriteStatus(">> WARNING: no rows received " & _ "for table '" & sTableName & "'") Else ' see how many rows are in this table Dim iRows As Integer = oDS.Tables(sTableName).Rows.Count If iRows > 0 Then Try ...

Inserting New Rows into the Database
To insert the rows automatically, you must attach a DataAdapter to the DataSet, and call its Update method. However, you also need to ensure that this happens within the control of the currently pending transaction, which involves some extra code. Normally, you create a DataAdapter by specifying the connection and a SQL statement that selects an appropriate set of rows:

Dim oDA As New SqlDataAdapter("sql-statement", connection-instance)

However, calling that constructor only sets the SelectCommand property of the DataAdapter. To create a suitable InsertCommand, you can add a CommandBuilder:

Dim oCB As New SqlCommandBuilder(oDA)

Then, when you call the DataAdapter's Update method, the CommandBuilder generates a suitable parameterized SQL INSERT statement, and assigns it to the CommandText property of the Command object associated with the DataAdapter's InsertCommand property. It also populates the Parameters collection of that Command with the appropriate set of Parameter instances. If you choose not to use a CommandBuilder, you'll have to do all this work manually.

However, there's a problem; the Command objects that the CommandBuilder creates are not added to the pending transaction, and trying to assign the transaction to them afterwards causes an error. To get around this, you have to specify the current pending transaction when you create a new Command object. Subsequently, you can pass that Command to the constructor of the DataAdapter where it will be used as the SelectCommand.

Next, you can use the GetInsertCommand method of a new CommandBuilder to obtain a Command object, assign the pending transaction to it, and then assign it to the InsertCommand of the DataAdapter. Finally, you're ready to call the Update method to push the rows into the database:

... ' create a new Command for the SelectCommand ' within the current transaction Dim oSCmd As New SqlCommand( _ "SELECT * FROM " & sTableName, oConn, oTrans) ' create DataAdapter using that Command Dim oDA As New SqlDataAdapter(oSCmd) ' create a new CommandBuilder from the DataAdapter Dim oCB As New SqlCommandBuilder(oDA) ' get the InsertCommand Command instance ' from the CommandBuilder Dim oICmd As SqlCommand = oCB.GetInsertCommand() ' attach the current transaction to the InsertCommand oICmd.Transaction = oTrans ' specify this as the InsertCommand of the DataAdapter oDA.InsertCommand = oICmd ' update the database table Dim iCount As Integer = oDA.Update(oDS, sTableName) ...

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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