Simple question: Updating a datasource

[Peder Y]

This might be a simple question, but I havent found a solution anywhere:

I do some stuff, and fill a DataSet from a database.

After the tables have been processed (rows inserted/deleted/updated) I
want to write the DataSet back to the database.

My DataTable shall replace the table in the database.

Consider the original DataAdapter disposed.


My current workaround is to create a new DataAdapter and new DataSet,
fill the new DataSet as I did from the start, replace ALL table rows
with the rows from the first table, and finally call the Update() method
to write back the changes.

This strikes me as not the proper way to do things. I don't see the
point in retrieving all rows from the database if all I want to do is
overwrite the table. Can't I create a new DataAdapter and simply tell it
to update the target table with my DataTable?

Secondly, how do I insert a single row into the target Table? Is the
proper way to write an SQL command and put it in an OleDbCommand object?

Thanks!

- Peder -

 

[recoil]

Peder, in response to your question about "do i need to retrieve all
the rows", I believe there are at least 2 and maybe 3 ways to do it.
You should be able to build everything by hand. If you want to build it
by hand then a dataconnectionis not required to retrieve the columns
because you will add them manually via code.
If you want the columns to be added automatically you need to select at
least one row. There might be a way to read the schema from a table and
use this for a dataset. I have not seen a method of doing this so I
can't comment on whehter itis possible.

What I DO do is i do a =
bwcLogic.NewSproc("SecretOperation_PerformedOperations_ListNone"); This
is a stored procedure that basically queries the table but retrieves no
rows. Once again, this may not be the best solution, but sometimes in
the absence of more real-world practical documentation/samples we make
do with what makes sense to us.

In response to your Question about updating. This is what I do .

OleDA.InsertCommand =
bwcLogic.NewSproc("Dental_PerformedOperations_Insert",
new SqlParameter("@SSN", SqlDbType.VarChar, 50, "SSN"),
new SqlParameter("@Code", SqlDbType.VarChar, 5, "Code"),
new SqlParameter("@Note", SqlDbType.VarChar, 255, "Note"),
new SqlParameter("@Times", SqlDbType.Int, 4, "Times")
// Here I am using a Stored Procedure, for increased performance and
security reasons. The 4th most parameter maps the column name to the
parameter. For each row that is inserted it will execute this command
mapping as I have specified. This is why it is possible to build the
table manually with code adding the columns yourself. You just need to
make sure the data type and sizes are the appropriate data type.
// code goes here to insert all your rows
then simply go
OleDA.Update(TmpDS, TableName);
// close connections and cleanup.

Please note, that the actual order is not significant however.


Hope this helps to answer some of your questions.

 

[Peder Y]

Well, I fell back on writing an OleDbCommand that did the trick. I will
however use your idea of simply retrieving a single row to get hold of
the dataset upon insertion and deletion. At this point the performance
isn't an issue since the tables are small. At this point...

- Peder -