Dataset to Dataset synchronization

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This should be easier than I am finding it (maybe)

I have 2 databases (one external, and one internal) they have identical
tables, fields, etc. Completely identical. We cannot access the external
SQL server directly, so we built web services to return the datasets of the
tables we need to get data from. All we need to do is download the dataset
of tables and put that data into the internal tables.

I have tried using the merge and update functions, but they are not working.

I have tried the following:
Dim daSQL As New SqlClient.SqlDataAdapter(strSQLLocalSelect,
con)
Dim cb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(daSQL)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
dtLocal = New DataTable(dtCurrent.TableName)

daSQL.Fill(dsLocal, dtCurrent.TableName)
dtLocal = dtCurrent.Copy
daSQL.Update(dsLocal.Tables(dtLocal.TableName))

That does not work due to the fact that the rowstate is also copied, and all
of the rows have a rowstate of unchanged.

I have also tried:

Dim daSQL As New SqlClient.SqlDataAdapter(strSQLLocalSelect,
con)
Dim cb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(daSQL)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
dtLocal = New DataTable(dtCurrent.TableName)

Dim rw As DataRow
For Each rw In dtCurrent.Rows
dtLocal.Rows.Add(rw)
Next
daSQL.Fill(dsLocal, dtCurrent.TableName)
daSQL.Update(dsLocal.Tables(dtLocal.TableName))

This does not work because the rw row belongs to another table. Any
suggestions on anything else to try? There is a requirement to make sure
that when the data structure changes that the synchronization job does not
change. The data structures will have other code in place to make sure that
they are always identical.

Please help!! :)
 
Thanks. I got that to work really nice. The only problem I have now is with
IDENTITY fields. I really don't want to remove them from the tables, but I
can't seem to get around them.

Here is what I have:

cmd.CommandText = "SET IDENTITY_INSERT " &
dtCurrent.TableName & " ON"
cmd.ExecuteNonQuery()

' Update the copied data to the local machine
daSQL.Update(dsReceive.Tables(dtCurrent.TableName))

cmd.CommandText = "SET IDENTITY_INSERT " &
dtCurrent.TableName & " OFF"
cmd.ExecuteNonQuery()

When I get to the daSQL.Update() line, I get the error:
Explicit value must be specified for identity column in table 'MyNames' when
IDENTITY_INSERT is set to ON

Any idea what I can do to fix this?

--
Thanks in advance,
Brad Simon


W.G. Ryan - MVP said:
Fill it with AcceptChangesDuringFill set to false
http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic4053.aspx

http://www.knowdotnet.com/articles/datasetmerge.html

Brad Simon said:
This should be easier than I am finding it (maybe)

I have 2 databases (one external, and one internal) they have identical
tables, fields, etc. Completely identical. We cannot access the external
SQL server directly, so we built web services to return the datasets of
the
tables we need to get data from. All we need to do is download the
dataset
of tables and put that data into the internal tables.

I have tried using the merge and update functions, but they are not
working.

I have tried the following:
Dim daSQL As New
SqlClient.SqlDataAdapter(strSQLLocalSelect,
con)
Dim cb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(daSQL)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
dtLocal = New DataTable(dtCurrent.TableName)

daSQL.Fill(dsLocal, dtCurrent.TableName)
dtLocal = dtCurrent.Copy
daSQL.Update(dsLocal.Tables(dtLocal.TableName))

That does not work due to the fact that the rowstate is also copied, and
all
of the rows have a rowstate of unchanged.

I have also tried:

Dim daSQL As New
SqlClient.SqlDataAdapter(strSQLLocalSelect,
con)
Dim cb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(daSQL)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
dtLocal = New DataTable(dtCurrent.TableName)

Dim rw As DataRow
For Each rw In dtCurrent.Rows
dtLocal.Rows.Add(rw)
Next
daSQL.Fill(dsLocal, dtCurrent.TableName)
daSQL.Update(dsLocal.Tables(dtLocal.TableName))

This does not work because the rw row belongs to another table. Any
suggestions on anything else to try? There is a requirement to make sure
that when the data structure changes that the synchronization job does not
change. The data structures will have other code in place to make sure
that
they are always identical.

Please help!! :)
Click to expand...
Click to expand...
 
Back
Top