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
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