Can't get table copying code right

  • Thread starter Thread starter headware
  • Start date Start date
H

headware

I'm attempting to write a function that will overwrite the data for a
certain table in one database with the data from another table (with
the same schema) in another database. Everything seems to work
correctly up until the point at which I call the Update() function on
the data adapter. For some reason after merging the two datasets, the
destination dataset has all the rows from the source table, but
doesn't have any of the row states marked as added. They are all
marked as unchanged, so nothing gets copied over into the destination
table. What am I doing wrong here?

Thanks,
Dave

public void DoCopy(string tableName, string srcPath, string dstPath)
{
//get all data from source table
OleDbConnection srcConn = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=" + srcPath);

OleDbDataAdapter srcAdapter = new OleDbDataAdapter("SELECT * FROM "
+ tableName, srcConn);
DataSet srcDS = new DataSet();
srcAdapter.Fill(srcDS, tableName);

//clear the destination table
OleDbConnection dstConn = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=" + dstPath);
OleDbCommand cmd = new OleDbCommand("DELETE * FROM " + tableName,
dstConn);
dstConn.Open();
cmd.ExecuteNonQuery();


//copy source data into destination table
srcAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
srcAdapter.AcceptChangesDuringFill = false;

OleDbDataAdapter dstAdapter = new OleDbDataAdapter("SELECT * FROM "
+ tableName, dstConn);
dstAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet dstDS = new DataSet();
dstAdapter.Fill(dstDS, tableName);

dstDS.Merge(srcDS);

OleDbCommandBuilder cb = new OleDbCommandBuilder(dstAdapter);
dstAdapter.Update(dstDS.Tables[tableName]);

dstConn.Close();
}
 
That makes total sense because after your dstAdapter.Fill(dstDS, tableName),
dstDS.tableName is empty (you emptied the source table in the database
explicitly) and
when you do the Merge, there is no data to which the source data can be
mapped therefore the RowState indicators are left 'as is'.

A am thinking that what you appear to be trying to achieve might be better
achieved by executing an 'insert into ... select from ...' query. The Jet
engine has some extensions for selecting from a table in a different
database.
 
There are a few way to do what you want. Check out the
data points column in msdn mag, october 2k3. The code is
available here:

http://msdn.microsoft.com/msdnmag/code03.aspx

Rows pulled in from the DB are always marked as unchanged
because after the rows are read into your dataset,
acceptChanges is called automatically. You can stop it
by handling the rowUpdated event of the dataAdapter and
setting the Status property of the eventArgs to
UpdateStatus.SkipCurrentRow. Check out this link, it
explains what's going on when you fill a dataSet.

http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/cpref/html/frlrfsystemdatasqlclientsqldataadapterclassr
owupdatedtopic.asp

James
 
I appreciate everyone's help. I finally got things to work by doing
something like the following:

//*** everything up through deleting all the rows in dstTable is the
same ***/

DataTable srcTable = srcDS.Tables[0];
DataTable dstTable = srcTable.Clone();
foreach(DataRow row in srcTable.Rows)
{
DataRow newRow = dstTable.NewRow();
newRow.ItemArray = (object[])row.ItemArray.Clone();
dstTable.Rows.Add(newRow);
}

OleDbDataAdapter dstAdapter = new OleDbDataAdapter("SELECT * FROM " +
tableName, dstConn);

OleDbCommandBuilder cb = new OleDbCommandBuilder(dstAdapter);
dstAdapter.Update(dstTable);


Doing it this way kept the new row states as "Added" instead of
"Unchanged". It seems to work, unless I'm missing something. Anyway,
just thought I'd let you know what I ended up doing. Thanks again.

Dave



James Cadd said:
There are a few way to do what you want. Check out the
data points column in msdn mag, october 2k3. The code is
available here:

http://msdn.microsoft.com/msdnmag/code03.aspx

Rows pulled in from the DB are always marked as unchanged
because after the rows are read into your dataset,
acceptChanges is called automatically. You can stop it
by handling the rowUpdated event of the dataAdapter and
setting the Status property of the eventArgs to
UpdateStatus.SkipCurrentRow. Check out this link, it
explains what's going on when you fill a dataSet.

http://msdn.microsoft.com/library/default.asp?
url=/library/en-
us/cpref/html/frlrfsystemdatasqlclientsqldataadapterclassr
owupdatedtopic.asp

James

-----Original Message-----
I'm attempting to write a function that will overwrite the data for a
certain table in one database with the data from another table (with
the same schema) in another database. Everything seems to work
correctly up until the point at which I call the Update () function on
the data adapter. For some reason after merging the two datasets, the
destination dataset has all the rows from the source table, but
doesn't have any of the row states marked as added. They are all
marked as unchanged, so nothing gets copied over into the destination
table. What am I doing wrong here?

Thanks,
Dave

public void DoCopy(string tableName, string srcPath, string dstPath)
{
//get all data from source table
OleDbConnection srcConn = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=" + srcPath);

OleDbDataAdapter srcAdapter = new OleDbDataAdapter ("SELECT * FROM "
+ tableName, srcConn);
DataSet srcDS = new DataSet();
srcAdapter.Fill(srcDS, tableName);

//clear the destination table
OleDbConnection dstConn = new
OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=" + dstPath);
OleDbCommand cmd = new OleDbCommand("DELETE * FROM " + tableName,
dstConn);
dstConn.Open();
cmd.ExecuteNonQuery();


//copy source data into destination table
srcAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
srcAdapter.AcceptChangesDuringFill = false;

OleDbDataAdapter dstAdapter = new OleDbDataAdapter ("SELECT * FROM "
+ tableName, dstConn);
dstAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet dstDS = new DataSet();
dstAdapter.Fill(dstDS, tableName);

dstDS.Merge(srcDS);

OleDbCommandBuilder cb = new OleDbCommandBuilder (dstAdapter);
dstAdapter.Update(dstDS.Tables[tableName]);

dstConn.Close();
}
.
Click to expand...
Click to expand...
 
Back
Top