Update a dataset

  • Thread starter Thread starter Kyle Rowe
  • Start date Start date
K

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
 
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
 
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
 
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
 
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
 
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.
 
But be aware that the CommandBuilder doesn't always create accurate commands
(especially if you have joins).
 
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.
 
Thanks Italo, that really helped me!


Italo Silveira said:
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
Click to expand...
 
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.
 
Back
Top