Using DotNet to Copy / Sync SQL databases

I'm creating a new system for the till in the refectory and as part of this there's going to be a locally stored database as well as one on the main database server. The idea is that the till can run disconnected from the network, but when it sees the network again it will resync the database.

It works like this:

The master database for kitchen product, users and product groups is on the network so that a back end application can edit them.

The master database for transactions is on the till.

Every x minutes the till synchronises the databases if possible. In order to do this I could either use SQL to connect the two (which proved difficult given that there could be more than one till) or use DotNet. I chose the latter.

In order to ensure that rows are copied including their key, you need the Set Identity_Insert command alongside an Insert Into command. To build these commands up automatically on rows that have to be synched, first the old row is deleted (if it exists) and then I use the following code to build the insert into command: (the strings "cols" and "vals" are the return from ColumnList and ColumnValues and it's nice and generic so will work with any dataset.

Shared Function BuildInsertQuery(ByVal TableName As String, ByVal Vals as string, ByVal Cols As String) As String
Dim Query As String = IION(TableName)
Query += "Insert into " + TableName + " (" + Cols + ") values (" + Vals + ")"
Return Query
End Function

Shared Function IION(ByVal TableName As String) As String
Return "Set Identity_Insert " + TableName + " ON" + Chr(13) + Chr(10)
End Function

Shared Function IIOFF(ByVal TableName As String) As String
Return Chr(13) + Chr(10) + "Set Identity_Insert " + TableName + " OFF"
End Function

Shared Function ColumnList(ByVal DT As DataTable) As String
' returns a list of columns in the datatable
Dim List As String = ""
For Each Col As DataColumn In DT.Columns
If List <> "" Then
List += ","
End If
List += Col.ColumnName
Next
Return List
End Function

Shared Function ColumnValues(ByVal Row As DataRow) As String
Dim Val As String = ""
For foo As Integer = 0 To Row.ItemArray.Length - 1
If foo > 0 Then
Val += ","
End If
If Row.Item(foo).GetType.ToString = "System.DateTime" Then
Dim Dt As Date = Row.Item(foo).ToString
Val += "'" + ims.UtilitiesNonSQL.ConvertDateForSQL(Dt) + "'"
Else
Val += "'" + Row.Item(foo).ToString.Replace("'", "") + "'"
End If
Next
Return Val
End Function

Seems to work!