Saving a dataset to a different database

[Darren Sim]

Hi there,

I am fairly new to webservices and C#and currently have a problem with
updating data to a remote sql database from data entered into a local
MS access database.

The dataset I am using comprises of several tables. Once the
webservice has the dataset some operations are performed so that the
data can be included in the SQL database


i.e. if a company already exists on the SQL database then the company
id on the dataset is updated, and so on.
As a result I cannot (I think) use the dataAdapter to post any modified
records or new additions to the SQL database.
As an added complication we cannot gaurantee which fields will be in
each of the tables (although the access and sql databases have the same
structure), this depends on the particular client. Which means that
hardcoding the insert and update into the webservice is not an option.


So far I have had no problems in building and transferring the dataset, same
goes for updating the details held in the dataset ready for
updating/inserting to the SQL database.

I have tried to manually set the state of a row so that I could use the
dataAdapter, i.e.

DR.RowState = DataRowState.Modified;

But this appears to be a read only attribute of DataRow that cannot be
altered manually.

Any ideas?


TIA

Darren

 

[Darren Sim]

would it be sensible/possible to update the data in all tables of the
dataset as required, deleting any of the datarows that will not be
added/updated and saving the details to an error log. Then creating a copy
of the dataset - deleting any rows from the SQL database that will be
updated, finally using the copy of the dataset to insert the rows?

i.e.
DataSet bufferDataSet = new DataSet();
SqlConnection sourceConnection = new SqlConnection(myConnectionString);
SqlConnection destinationConnection = new
SqlConnection(myConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter("Select * from sometable",
sourceConnection);
adapter.AcceptChangesDuringFill = false;
adapter.SelectCommand.Connection.Open();
adapter.Fill(bufferDataSet, "SomeTable");
adapter.SelectCommand.Connection.Close();
adapter.UpdateCommand = new SqlCommand("Insert into ....",
destinationConnection);
adapter.UpdateCommand.Connection.Open();
adapter.Update(bufferDataSet, "SomeTable");
adapter.UpdateCommand.Connection.Close();

 

[Guest]

Since you have a SQL server involved, you really might want to look into
usind DTS packages (2000) or Integration Services (2005) to do this type of
work.

I know there's a bit of ramp up time involved with adding a new technology
to your solution. However, you're going to end up with a much more stable
solution in the long run. Also, the next time you have a requirement that
involves moving data from one environment to the other, it will only take
around 30 minutes to do the entire thing.