This document describes the procedure for data synchronization from MS Access to Sql Server. First of all the table should exist in both Access and SQL Server and its structure should be same. The details of the MS Access database such as table names and its corresponding number of columns are kept in a XML file.

<?xml
version
="1.0"
encoding="utf-8"
?>

<Table
>

<Item
totalcolumns
="15"
tablename
="Customer"
/>

<Item
totalcolumns
="3"
tablename
="Equipment"
/>

<Item
totalcolumns
="35"
tablename
="Vehicle"
/>

</Table
>

 

The above example shows details of Customer, Equipment and Vehicle tables and its column count15,3 and 35.

The data synchronizer reads the table name from the XML file and retrieves data for the corresponding table from the MS Access database insert them into the Sql server.

1  Reading a XML file

This section describes how to read the configuration from the XML file.

Below is the code for reading the contents from a XML file.

 

Dim doc As New
XmlDocument

//Loading XML file through XML file path

doc.Load(ConfigurationSettings.AppSettings(WebConstants.XML_FILEPATH).ToString)

Dim mainNode As
XmlNode = doc.SelectSingleNode("Table")

Dim val As
Integer
= mainNode.ChildNodes.Count

Dim i As
Integer

For i = 0 Toval – 1

Dim
totalColumn AsInteger
= mainNode.ChildNodes(i).Attributes.Item(0).Value()

Dim
tableName AsString
= mainNode.ChildNodes(i).Attributes.Item(1).Value()

synchronize(tableName, totalColumn,

(ConfigurationSettings.AppSettings(WebConstants.SOURCE_DBTYPE).ToString),

(ConfigurationSettings.AppSettings(WebConstants.SOURCE_CONNECTION).ToString),

(ConfigurationSettings.AppSettings(WebConstants.TARGET_DBTYPE).ToString),

(ConfigurationSettings.AppSettings(WebConstants.TARGET_CONNECTION).ToString))

Next

2.  Data Synchronization Steps

Steps for performing Data Synchronization is as follows:

1) Delete the contents of the table from the target database
before inserting the datas from the source database using the statement below.

 

TRUNCATE TABLE  tableName

 

2) Retrieve the data from the source database using the  querry below.

 

SELECT * FROM tableName

 

3) Insert the retrieved source data into the target database. 

 

Below is the Sample Code of data synchronization:

 

Public
Class DataSynchronizationADO

Inherits DataAccessObject

Public
Sub synchronize(ByRef tableName
As
String
, ByRef totalColumn As Integer , ByRef sourceDBType As
    String
, ByRef sourceConnString As String
, ByRef targetDBType As String
, ByRef targetConnStr As String)

Try
   Dim
dataList As New ArrayList

deleteFromTarget(tableName, targetDBType, targetConnStr)
dataList = selectFromSource(tableName, totalColumn, sourceDBType, sourceConnString)
insertIntoTarget(tableName, targetDBType, targetConnStr, dataList)

Catch ex
As Exception

Throw ex

End Try


End Sub

‘Selecting datas from the source table

Public Function selectFromSource(ByRef tableName
As String, ByRef totalColumn
As Integer
, ByRef sourceDBType
As String, ByRef sourceConnString
As String)
As ArrayList

Return MyBase.Find(“SELECT * FROM ” + tableName.ToString
+ “”, totalColumn, sourceDBType, sourceConnString)

End Function

‘Deleting the datas from target table

Public Sub deleteFromTarget(ByRef tableName
As String
, ByRef targetDBType
As String, ByRef targetConnStr
As String)

Dim deleteStr As String =
Nothing

Dim num As Integer =
Nothing

deleteStr = “TRUNCATE TABLE ” + tableName.ToString + “”
num = MyBase.ExecuteNonQuery(deleteStr, targetDBType, targetConnStr)

End Sub

‘ Inserting datas into the target

Public Sub insertIntoTarget(ByRef tableName
As String, ByRef targetDBType
As String, ByRef targetConnStr
As String, ByRef dataList
As ArrayList)

Dim num As Integer =
Nothing

Dim count As Integer = 0

Dim dataEnumerator As IEnumerator

Try

count = dataList.Count

If count Then
dataEnumerator = dataList.GetEnumerator

While dataEnumerator.MoveNext

Dim insertStr As String =
Nothing

Dim length As Integer =
Nothing

Dim i As Integer =
Nothing

Dim array As Array =
CType(dataEnumerator.Current(), Array)

length = array.Length
insertStr = “INSERT INTO ” + tableName.ToString + ” VALUES (”

For i = 0 To length – 2

Dim queryStr As
String = Nothing

If i > 0 Then

queryStr += “,”

End If

If array(i).GetType Is
GetType(String) Then

If Not array(i).Equals(String.Empty) Then

array(i) = array(i).Replace(“‘”, “””)

End If

queryStr += “‘” + array(i).ToString + “‘”

ElseIf array(i).GetType Is GetType(Integer)
Then

queryStr += “‘” + CInt(array(i)).ToString + “‘”

ElseIf array(i).GetType Is GetType(Date)
Then

queryStr += “‘” + CDate(array(i)).ToString + “‘”

ElseIf array(i).GetType Is GetType(Double)
Then

queryStr += “‘” + CDbl(array(i)).ToString + “‘”

ElseIf array(i).GetType Is GetType(Boolean)
Then

queryStr += “‘” + array(i).ToString + “‘”

ElseIf IsDBNull(array(i)) Then
queryStr += “‘” + “0” + “‘”

End If

insertStr += queryStr

Next

insertStr += “);”

num = MyBase.ExecuteNonQuery(insertStr, targetDBType, targetConnStr)

End While

End If

Catch ex As Exception

Throw ex

End Try

End Sub


End Class

Note: Care should be taken while performing data synchronization such that the data type of the columns are same in both source and target databases.