Update a dataset

[Kyle Rowe]

Hi,

I think this post was more appropriate here...

How do I Update a dataset into a DB if the orginal SqlDataAdapter I used to
fill the dataset is gone?


Thanks

 

[Italo Silveira]

You don't need the original SqlAdapter. You can recreate it at anytime; you
can do this by using a SqlCommandBuilder, or manually write the Insert,
Delete, and Update commands.

Good Luck,
Italo

 

[Kyle Rowe]

Hi,

Thanks for responding.

So if I filled my DataSet with this expression "SELECT * FROM foo"

then, when I recreate my new SqlAdapter I have to execute the above
expression again? It seems like a lot of work just to update one DataSet...

I have create a new SqlAdapter, and a new SqlCommand and set all the
properties of SqlCommand, create a new SqlCommandBuilder, then Update and
AcceptChanges()

I don't want to manually write the Insert, Delete and Update commands...

Is this the only way to do this?


Thanks

 

[Italo Silveira]

You're not executing the T-SQL when you recreate the SqlAdapter (The T-SQL
is only executed when you call Fill). There is no performance loss (besides
recreating the SqlAdapter) by doing this. SqlCommandBuilder is smart enough
to create the insert, delete, and update commands without executing the
T-SQL command.



Italo

 

[Italo Silveira]

Example:

// Connect to the server
SqlConnection myConn = new SqlConnection("integrated security=SSPI;data
source=HServer;persist security info=False;initial catalog=Northwind");
myConn.Open();

// Get the data
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Categories",
myConn);

DataSet ds = new DataSet();
myDataAdapter.Fill(ds, "Table1");

// Release the SqlAdapter
myDataAdapter.Dispose();
myDataAdapter = null;

// Create a new SqlAdapter to update the data
SqlDataAdapter myDataAdapter2 = new SqlDataAdapter();
myDataAdapter2.SelectCommand = new SqlCommand("SELECT * FROM Categories",
myConn);
SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter2);

myDataAdapter2.Update(ds, "Table1");

// Cleanup
myConn.Close();



Italo

 

[Scott M.]

You must have a command object that is configured properly to do updates,
period. You could create a command for Inserts, one for Updates and one for
Deletes and configure each or you could create a DataAdapter and supply its
SelectCommand with a Select statement that the commandBuilder will then look
at to create the Insert, Update and Delete command's.

 

[Scott M.]

But be aware that the CommandBuilder doesn't always create accurate commands
(especially if you have joins).

 

[Kyle Rowe]

I just learned that myself. I've been experiencing some Concurrency
exceptions regarding the DeleteCommand. I went ahead and created my own
DeleteCommand and those exceptions have disappeared!! but I did still leave
the CommandBuilder there for adding rows.

 

[Kyle Rowe]

Thanks Italo, that really helped me!

Example:

// Connect to the server
SqlConnection myConn = new SqlConnection("integrated security=SSPI;data
source=HServer;persist security info=False;initial catalog=Northwind");
myConn.Open();

// Get the data
SqlDataAdapter myDataAdapter = new SqlDataAdapter();
myDataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Categories",
myConn);

DataSet ds = new DataSet();
myDataAdapter.Fill(ds, "Table1");

// Release the SqlAdapter
myDataAdapter.Dispose();
myDataAdapter = null;

// Create a new SqlAdapter to update the data
SqlDataAdapter myDataAdapter2 = new SqlDataAdapter();
myDataAdapter2.SelectCommand = new SqlCommand("SELECT * FROM Categories",
myConn);
SqlCommandBuilder cb = new SqlCommandBuilder(myDataAdapter2);

myDataAdapter2.Update(ds, "Table1");

// Cleanup
myConn.Close();



Italo

 

[Scott M.]

The best way to avoid concurrency issues is to have a primary key that is
time dependent (at least in part anyway) and to add a WHERE clause to your
delete statements that check the dataset record to be deleted against the
database record with the same primary key value.