Currently at work I am working on an application that does a database difference on particular tables in two databases. The application needs to allow a user to decide if they want to move over records to one database or update them if they already exist. This is very similar to SQL Delta, SQL Compare or the tools in Visual Studio Database Edition. We are writing this custom because we have business logic that needs to occur when moving the data.
While trying to find the easiest way to accomplish this I ran across a lot of resources on the net. There was an easy way, however, I think the posts are old because they are missing some key points. I spent a while trying to figure this out. In the first method you simply populate two datasets with data. I am grabbing data here from the same table in two different databases. I have already ran a check to make sure the schemas are the same between the two databases. After I do this I create a third dataset. I simply merge the destination dataset into the merged one. You then HAVE to make sure you AcceptChanges here because you don’t want these changes to be included in your diff. Finally you merge the source database into the merged dataset. Finally this method returns a dataset that contains the changes. The GetChanges method takes in one parameter which allows you to specify if you want modified rows, new rows or deleted rows. You will get all differences if you don’t specify that parameter. Now, for the fun part. The part that all the blogs and examples left out. You MUST do a few things to your DataAdapter for this to work. I spent a lot of time figuring this out this afternoon. First, you must make sure that you set the AcceptChangesDuringFill property to false. If you do not do this, it basically accepts the changes as soon as you do a merge. You will end up with what appears as no changes if you do not do this. The second thing you must do is call the FillSchema method and also set the MissingSchemaAction property. If you do not do this the tables in your dataset will not contain the schema of the table. Most importantly, the primary key. To be able to do a difference on the tables a primary key must be specified. By calling FillSchema the same primary key that is set in your database will be set in your dataset.
After getting past the DataAdapter issues I was pleasantly surprised with how few lines of code it takes to get the difference between a table in two databases.
My code plugin doesn’t handle long lines of code well. For a better format just hover over the code and click the markup button on the dialog that appears.
Public Shared Function GetChanges(ByVal sourceConnectionString As String, ByVal destinationConnectionString As String, ByVal tableName As String, ByVal rowState As DataRowState) As DataSet
Dim sourceDS As DataSet = GetDataset(sourceConnectionString, String.Format(My.Resources.SQLQueries.GetAllDataForTable, tableName), CommandType.Text, "ds")
Dim destDS As DataSet = GetDataset(destinationConnectionString, String.Format(My.Resources.SQLQueries.GetAllDataForTable, tableName), CommandType.Text, "ds")
Dim mergeDS As New DataSet()
Dim mergedDS As New DataSet
mergedDS.Merge(destDS, False)
mergedDS.AcceptChanges()
mergedDS.Merge(sourceDS, True, MissingSchemaAction.AddWithKey)
Return mergedDS.GetChanges(rowState)
End Function
Public Shared Function GetDataset(ByVal connectionString As String, ByVal command As String, ByVal commandType As CommandType, ByVal dsName As String) As DataSet
Dim sqlConn As New SqlConnection(connectionString)
sqlConn.Open()
Dim sqlCommand As New SqlCommand(command, sqlConn)
sqlCommand.CommandType = commandType
Dim sqlAdapter As New SqlDataAdapter(sqlCommand)
Dim ds As New DataSet(dsName)
sqlAdapter.AcceptChangesDuringFill = False
sqlAdapter.FillSchema(ds, SchemaType.Source)
sqlAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
sqlAdapter.Fill(ds)
sqlAdapter.Dispose()
sqlCommand.Dispose()
sqlConn.Close()
Return ds
End Function
