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
' 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
--
Brad
"Software is like melted pudding..."
> 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
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....
--
Brad
"Software is like melted pudding..."
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.
I'm just used to doing it manually...
--
Brad
"Software is like melted pudding..."