By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,900 Members | 1,364 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,900 IT Pros & Developers. It's quick & easy.

Insert/Update tables in a database with relational data from an XML file

P: n/a
Hi,

Could anybody just point me in a direction where I can find information
on how the heck I can update a database with relational data from an XML
file.

I use stored procedures to insert the data. And I am NOT storing the xml
in the database. I want to map the xml elements to the database columns.

I am about to start peeling my skin off in frustration about this. There
are loads of info how to update using SetParentRow with ONE parent row
and ONE child row like this:

Dim ds As DataSet = New DataSet
Dim conn As SqlConnection = New SqlConnection("DataSource... ")

conn.Open()

Dim da1 As SqlDataAdapter = New SqlDataAdapter(New SqlCommand("SELECT *
FROM ParentTable", conn))

da1.InsertCommand = New SqlCommand("AddParentData", conn)

Dim cmd As SqlCommand = da1.InsertCommand

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@ParentID", SqlDbType.Int))
cmd.Parameters("@ParentID ").Direction = ParameterDirection.Output
cmd.Parameters("@ParentID").SourceColumn = "ParentID"

cmd.Parameters.Add(New SqlParameter("@ParentText", SqlDbType.NVarChar,
50, "ParentText"))

da1.FillSchema(ds, SchemaType.Source)

Dim pTable As DataTable = ds.Tables("Table")
pTable.TableName = "ParentTable"

’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ’’’’’’’’’’’’’’’’’’’’’

Dim da2 As SqlDataAdapter = New SqlDataAdapter(New SqlCommand("SELECT *
FROM ChildTable", conn))

da2.InsertCommand = New SqlCommand("AddChildData", conn)

cmd = da2.InsertCommand

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add(New SqlParameter("@ParentID", SqlDbType.Int))
cmd.Parameters("@ParentID ").SourceColumn = "ParentID"

cmd.Parameters.Add(New SqlParameter("@ChildText", SqlDbType.VarChar, 50,
"ChildText"))

da2.FillSchema(ds, SchemaType.Source)

Dim cTable As DataTable = ds.Tables("Table")
cTable.TableName = "ChildTable"
ds.Relations.Add(New DataRelation("ParentChild", ds.Tables("ParentTable
").Columns("ParentID"), ¬_
ds.Tables("ChildTable ").Columns("ParentID")))
Dim dr1 As DataRow = ds.Tables ("ParentTable").NewRow()

dr1 ("ParentTest") = "TESTING_"
ds.Tables("ParentTable").Rows.Add(dr1)

Dim dr2 As DataRow = ds.Tables("ChildTable").NewRow()

dr2("ChildTest") = "TESTING_

dr2.SetParentRow(dr1)

ds.Tables("ChildTable").Rows.Add(dr2)

da1.Update(ds, "ParentTable")
da2.Update(ds, "ChildTable")

It works like a charm BUT it's not dynamic and it gets a bit confusing
when I want to load an xml document with, say, 128 parent rows(with a
bunch of elements) and each parent row(also with a bunch of elements)
contain between 1 to 500 child rows. I’ve been trying different
solutions for a couple of days now and nothing works.

Any guidens would be highly appreciated.

Regards,

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Hi,

DON'T BOTHER TO ANSWER THIS MESSAGE...I HAVE SOLVED THE PROBLEM...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.