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
AsString
, 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.