Best way to update table from dataset?

[olichap]

I have a dataset whose structure is built using an xml schema and whose data is populated through the parsing of a text file. I do not have an OleDBDataAdapter or OleDbCommand associated with the dataset since I'm not selecting records from the database into the data object. The dataset is not "bound" to anything.

The documentation I have on updating the dataset info to my SQL Server database seems to make the process a bit more involved than necessary (I say this, but since this represents my first use of the dataset, what do I know?).

Anyway, it seems to require the use of a Command object for each update statement (I have about 14 tables in my dataset that will update). Then the doc mentions the need to assign each field in my dataset.table to the command's Parameters array. All this seems a bit un-necessary to me.

Can you loop through as with ADO (do while not .eof) and utilized an "execute" method that executes an SQL string to insert the current row? Are there other ways to accomplish the same task of inserting records into the table?

I have the dataset tables and rows all set. Now I need to update to the SQL server database FROM the dataset. I could use the DataAdapter's InsertCommand property but then I'd have to set all the Command.Parameters with every field in my dataset (I have 14 or so tables).

In ADO 25/27 we could utilize the Execute method and an sql string to insert values from the current recordset.row into the table. I'd like figure out how to do this through .NET. My dataset is not populated through a SELECT statement but through code and the parsing of a text file, so is not bound to the database at all.

I figure I might be able to make use of the Command.ExecuteNonQuery method somehow but still have the problem of iterating through the records.

What is the NET equivalent of ADO25's Do While Not .EOF?

What is the best way to iterate through the Dataset.Tables.Rows?
When inserting data into the DataTable I have to find the current row by doing something like this:
iint = dsEDI.Tables("Header").Rows.Count - 1
dsEDI.Tables("Header").Rows(iint)!ContactName = arSeg(2)

I guess I could iterate through the dataset the same way to do my insert (a loop based on iint and the Rows.Count) but have to think NET has a better way have handling the basic requirement of moving through records.

Any ideas would be greatly appreciated

Thanks,

O.

[Thelonius]

You can loop through the dataset and submit your own Command Object.

-- Side topic. If you are using SQL Server, use the SQL specific objects.

For best performance I would use a stored procedure to do all the updating. However, you can loop through the dataset tables and execute an update.

somthing like:

Dim i As Integer
Dim the_id As String
Dim scmd As SqlCommand

For i = 1 To ds.Tables.Item("blah").Rows.Count
the_id = ds.Tables("blah").Rows.Item(i).Item("idColumn")

scmd = New SqlCommand("UPDATE table SET field='" & sometext & "' where id=" & the_id, CONN_STRING)
scmd.ExecuteNonQuery()

Next


This *should* work but you may have to play with the rows collection to get the values you need.

[olichap]

Thanks Thelonius,

Yeah, that was more or less how I planned on iterating. I was hoping .NET had a better way built in (something akin to the MoveNext capabilities in ADO2.5). I could have sworn I stumbled across something like it in .NET a while back (when just playing around with Beta) but don't know where (or if) it exists in SR1.

Any idea why this doesn't work:
Dim row1 As Data.DataRow

For Each row1 In dsEDI.Tables("ediProvider").Rows

Next

returns this error:
An unhandled exception of type 'System.InvalidCastException' occurred in testapp.exe

Additional information: Specified cast is not valid.

[Thelonius]

You can use the Update method of the Dataset object.

I was able to loop through rows collection with no problem.

Dim ds As New DataSet()

Dim sda As New SqlDataAdapter("SELECT * FROM tUsers", CONN_STRING)

sda.Fill(ds)

Dim dr As DataRow

For Each dr In ds.Tables(0).Rows
ListBox1.Items.Add(dr.Item(0))
Next

[olichap]

Just got my For Each loop to work. I must have had the ( ) at the end of my Dim ... as DataRow statement.

For Each row1 In dsEDI.Tables("ediProvider").Rows
'can now use Row1!FieldName to return values
Next

Still have to decide how I want to handle the insert but will probably use the ExecuteNonQuery instead of building the Command.Parameters collections.