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


P

Pesko S

Hi,

I know you are probably 'bizzy' as h*ll but I wonder if you just could
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. Should I use
OnRowUpdate and if so, how?

Any guidens would be highly appreciated.

Regards,
Pesko
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top