How to get xml based dataset to update an SQL server database?

[MurdockSE]

Newbie Question (apologies ahead of time) -

I have a dataset created from an XML file (lets call it DataSet1). I
want to get that data into an existing SQL Database. How do I
efficiently add the rows from DataSet1 to DataSet2 and set them to ADD
so that an UPDATE on the DataSet2's DataAdapter will add all of those
rows to the SQL Databse?

A picture:
XML Document --> Dataset1 ---> [Here I assume I create a Dataset2
with select * from table form SQL Dbase) . How do I force Dataset2 to
include all of the rows from Dataset1, in an ADD state ; so that
calling an Update on Dataset2 will push that data to the SQL Database?

Thanks ahead of time.

Peter Robbins

 

[Guest]

<other code>
Dim dt As DataTable = ds.Tables("TableFromXML").Clone()
dt.TableName = "NewTable"
ds.Tables.Add(dt)

' The following method of populating the second dataTable is done
' so the rowstate for each row in the table will say "I am new" and the
' data adapter will pick up on it.

For Each dr In ds.Tables("TableFromXML").Rows
ds.Tables("NewTable").LoadDataRow(dr.ItemArray(), False)
Next

da.Update(ds.Tables("NewTable"))


Then....
-Create SQLdataAdapter and SQLCommand objects
--Set dataadapter to the SQL connection
--Populate the SQLCommand with the proper INSERT statement and parameter
objects

 

[MurdockSE]

I am with you up to this part -->

Then....
-Create SQLdataAdapter and SQLCommand objects
--Set dataadapter to the SQL connection
--Populate the SQLCommand with the proper INSERT statement and parameter
objects

Doesn't the code just above that tell the second dataset to Update
towards its associated Dataadapter? I don't understand why I would have
to use the INSERTs, etcetera.

Thanks for your information!! It is much appreciated.

Peter Robbin

 

[Guest]

Sorry, maybe if I rephrase a little...

After the NewTable has the data from the XMLTable in it you need a way to
get it into the SQL table. Remember, the Dataset object is "disconnected"
from a SQL server.

So, create a SQLConnection object, set the connectionstring property, and
maybe others.
Now in order to update the SQL server you can use a SQLDataAdapter object.
The SQLDataAdapter object will need some initializing before it will insert
records into the database. The InsertCommand object needs to be populated
with an instance of a SQLCommand object that knows the correct syntax of the
SQL statement that will be used to insert a record into the database.

Example....
Dim cmdInsert As SqlCommand = New SqlCommand
cmdInsert.CommandType = CommandType.Text
cmdInsert.Connection = Cn ' Previously instantiated connection

cmdInsert.CommandText = "Insert into MyTable (LastName) " & _
"values (@LastName) "

cmdInsert.Parameters.Add(New SqlParameter("@LastName", SqlDbType.VarChar,
50, Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"LastName", DataRowVersion.Current, Nothing))


da.InsertCommand = cmdInsert

da.Update(ds.Tables("NewTable")

Etc....

 

[MurdockSE]

Eureka! (lightbulb gets brighter then explodes). Thanks. That is
exactly what I was looking for. I will let you know how it turns out
and post the final code in it's entirety.

 

[MurdockSE]

Would the sqlcommand builder referred to in this quickstart guide help
any?

http://quickstart.developerfusion.co.uk/QuickStart/howto/doc/adoplus/UpdateDataFromDB.aspx

Just curious. I will use your method. I think though this sqlcommand
method may automatically create the insert, update, etcetera.

 

[Guest]

I didn't look but my first guess would be - probably so.

I'm just used to doing it manually...