Update Multiple Tables in Database using DataRelations

  • Thread starter Thread starter russzee
  • Start date Start date
R

russzee

Hello,

I'm working with NorthWind

My Customers Form uses Untyped Dataset and Bound Controls
(BindingManagerBase). However, deleting a customer and updating the
database is
throwing an error because of Referential Integrity issues.

I'm trying to use DataRelations to resolve this issue.

- I've filled my Dataset with Customers, Orders and Order Details Table

- I then created two DataRelations as below :

' Creating the DataRelation between Customers and Orders Tables

Dim CustomersOrders As New DataRelation("CustomersOrders", _
dsCustomers.Tables("Customers").Columns("CustomerID"), _
dsCustomers.Tables("Orders").Columns("CustomerID"))

dsCustomers.Relations.Add(CustomersOrders)

' Creating the DataRelation between Orders and Order Details Tables

Dim OrderRelation As New DataRelation("OrderRelation", _
dsCustomers.Tables("Orders").Columns("OrderID"), _
dsCustomers.Tables("Order Details").Columns("OrderID"))

dsCustomers.Relations.Add(OrderRelation)

Here's my question :

How do I update my database so that when a Customer is deleted , the
corresponding Information in the Orders and Order Details Tables are
first deleted in the dataset and then on clicking the "Update Database"
button the changes in all 3 tables are reflected to the database.

Currently, I'm using the sqlcommandbuilder object seperately for each
dataadapter and using its Fill Method seprately to Update the database.
I know I'm missing something since this doesnt seem to work.


Thank You,
russzee
 
Personally I am not a big fan of the SqlCommandBuilder as you can often build
more efficient SQL queries yourself. But whether you use SQL you created
yourself, stored procedures or the SqlCOmmandBuilder the solution is to call
the SqlDataAdapter.Update method for each of the DataTables. For only
deletes, it goes like this:

delete the Order Details
delete the Orders
delete the Customers

You'll need a separate DataAdapter for each DataTable. If you are inserting
and updating too, you'll want to do it like this to avoid RI issues:

insert customer
update customers
insert orders
update orders
insert order details
update order details
delete the Order Details
delete the Orders
delete the Customers

You can pull out the changed, inserted or deleted rows from the DataSet's
DataTables using the GetChanges method or the Selet method ... just pass it
the parameter for the type of changes you want to retrieve.

oDa.Update(oDs.Tables["OrderDetails"].Select("", "",
DataViewRowState.ModifiedCurrent));

Hope this helps. If not, I wrote an article on this topic at MSDN Magzine
that you can refer to. It has full source code, too. You can find it here:
http://msdn.microsoft.com/msdnmag/issues/04/05/DataPoints/


-- John Papa
http://codebetter.com/blogs/john.papa
 
Thanks for your reply. I'm doing just what you mentioned but my code
still doesnt work. Do you see anything wrong with this. I'm trying to
enfore Constraints in my Dataset so that when a Customer is deleted in
my Customer DataTable , it deletes the related orders in the Orders
DataTable and Order Details DataTable.

'Creating SQL QueryString
strSQLCustomers = "Select * from Customers"
strSQLOrders = "Select * from Orders"
strSQLOrderDetails = "Select * from [Order Details]"

' Use Data Adapter object
daCustomers = New SqlDataAdapter(strSQLCustomers, pSQLConn)
daOrders = New SqlDataAdapter(strSQLOrders, pSQLConn)
daOrderDetails = New SqlDataAdapter(strSQLOrderDetails,
pSQLConn)

' Fill the dataset with data using data adapter's Fill method
daCustomers.Fill(dsCustomers, "Customers")
daOrders.Fill(dsCustomers, "Orders")
daOrderDetails.Fill(dsCustomers, "Order" & "" & "Details")

' Creating the DataRelation between Customers and Orders Tables
Dim pCol1 As DataColumn
Dim cCol1 As DataColumn
Dim myFKC1 As ForeignKeyConstraint

pCol1 = dsCustomers.Tables("Customers").Columns("CustomerID")
cCol1 = dsCustomers.Tables("Orders").Columns("CustomerID")

Dim CustomersOrders As New DataRelation("CustomersOrders",
pCol1, cCol1)

dsCustomers.Relations.Add(CustomersOrders)

myFKC1 = New ForeignKeyConstraint("CustomersOrders1", pCol1,
cCol1)
myFKC1.DeleteRule = Rule.Cascade
myFKC1.UpdateRule = Rule.Cascade

'dsCustomers.Tables("Orders").Constraints.Add(myFKC1)

' Creating the DataRelation between Orders and Order Details
Tables
Dim pCol2 As DataColumn
Dim cCol2 As DataColumn
Dim myFKC2 As ForeignKeyConstraint

pCol2 = dsCustomers.Tables("Orders").Columns("OrderID")
cCol2 = dsCustomers.Tables("Order" & "" &
"Details").Columns("OrderID")

Dim OrderRelation As New DataRelation("OrderRelation", pCol2,
cCol2)

dsCustomers.Relations.Add(OrderRelation)
myFKC2 = New ForeignKeyConstraint("OrderRelation1", pCol2,
cCol2)
myFKC2.DeleteRule = Rule.Cascade
myFKC2.UpdateRule = Rule.Cascade

'dsCustomers.Tables("Order Details").Constraints.Add(myFKC2)

'dsCustomers.EnforceConstraints = True

' Create command builder. This line automatically generates the
'insert,update,delete commands,
' so you don't have to provide or create your own.

pSQLCommCust = New SqlCommandBuilder(daCustomers)
pSQLCommOrd = New SqlCommandBuilder(daOrders)
pSQLCommOrd = New SqlCommandBuilder(daOrderDetails)

Try

daCustomers.Update(dsCustomers, "Order" & "" & "Details")
daCustomers.Update(dsCustomers, "Orders")
daCustomers.Update(dsCustomers, "Customers")

MessageBox.Show("Update Successful ! Thank You", "Success")

Catch eSql As SqlException
MessageBox.Show(eSql.Message, "Sql error")
End Try


Thanks
Russel

John said:
Personally I am not a big fan of the SqlCommandBuilder as you can often build
more efficient SQL queries yourself. But whether you use SQL you created
yourself, stored procedures or the SqlCOmmandBuilder the solution is to call
the SqlDataAdapter.Update method for each of the DataTables. For only
Click to expand...
deletes, it goes like this:

delete the Order Details
delete the Orders
delete the Customers

You'll need a separate DataAdapter for each DataTable. If you are inserting
and updating too, you'll want to do it like this to avoid RI issues:

insert customer
update customers
insert orders
update orders
insert order details
update order details
delete the Order Details
delete the Orders
delete the Customers

You can pull out the changed, inserted or deleted rows from the DataSet's
DataTables using the GetChanges method or the Selet method ... just pass it
the parameter for the type of changes you want to retrieve.

oDa.Update(oDs.Tables["OrderDetails"].Select("", "",
DataViewRowState.ModifiedCurrent));

Hope this helps. If not, I wrote an article on this topic at MSDN Magzine
that you can refer to. It has full source code, too. You can find it here:
http://msdn.microsoft.com/msdnmag/issues/04/05/DataPoints/


-- John Papa
http://codebetter.com/blogs/john.papa



russzee said:
Hello,

I'm working with NorthWind

My Customers Form uses Untyped Dataset and Bound Controls
(BindingManagerBase). However, deleting a customer and updating the
database is
throwing an error because of Referential Integrity issues.

I'm trying to use DataRelations to resolve this issue.

- I've filled my Dataset with Customers, Orders and Order Details Table

- I then created two DataRelations as below :

' Creating the DataRelation between Customers and Orders Tables

Dim CustomersOrders As New DataRelation("CustomersOrders", _
dsCustomers.Tables("Customers").Columns("CustomerID"), _
dsCustomers.Tables("Orders").Columns("CustomerID"))

dsCustomers.Relations.Add(CustomersOrders)

' Creating the DataRelation between Orders and Order Details Tables

Dim OrderRelation As New DataRelation("OrderRelation", _
dsCustomers.Tables("Orders").Columns("OrderID"), _
dsCustomers.Tables("Order Details").Columns("OrderID"))

dsCustomers.Relations.Add(OrderRelation)

Here's my question :

How do I update my database so that when a Customer is deleted , the
corresponding Information in the Orders and Order Details Tables are
first deleted in the dataset and then on clicking the "Update Database"
button the changes in all 3 tables are reflected to the database.

Currently, I'm using the sqlcommandbuilder object seperately for each
dataadapter and using its Fill Method seprately to Update the database.
I know I'm missing something since this doesnt seem to work.


Thank You,
russzee
Click to expand...
Click to expand...
 
To help me out here, are you isnerting, updating and delting rows? Or just
deleting? Also, what is the exact error message you re getting?

The way the code is written you are calling the DA.Update method on all of
the rows in each of the 3 DataTables. That will cause an issue if you are
inserting, updating and deleting rows because you should remove the rows from
the database from child to parent and add and update from parent to child
(see my last post).

-- John Papa
http://codebetter.com/blogs/john.papa


russzee said:
Thanks for your reply. I'm doing just what you mentioned but my code
still doesnt work. Do you see anything wrong with this. I'm trying to
enfore Constraints in my Dataset so that when a Customer is deleted in
my Customer DataTable , it deletes the related orders in the Orders
DataTable and Order Details DataTable.

'Creating SQL QueryString
strSQLCustomers = "Select * from Customers"
strSQLOrders = "Select * from Orders"
strSQLOrderDetails = "Select * from [Order Details]"

' Use Data Adapter object
daCustomers = New SqlDataAdapter(strSQLCustomers, pSQLConn)
daOrders = New SqlDataAdapter(strSQLOrders, pSQLConn)
daOrderDetails = New SqlDataAdapter(strSQLOrderDetails,
pSQLConn)

' Fill the dataset with data using data adapter's Fill method
daCustomers.Fill(dsCustomers, "Customers")
daOrders.Fill(dsCustomers, "Orders")
daOrderDetails.Fill(dsCustomers, "Order" & "" & "Details")

' Creating the DataRelation between Customers and Orders Tables
Dim pCol1 As DataColumn
Dim cCol1 As DataColumn
Dim myFKC1 As ForeignKeyConstraint

pCol1 = dsCustomers.Tables("Customers").Columns("CustomerID")
cCol1 = dsCustomers.Tables("Orders").Columns("CustomerID")

Dim CustomersOrders As New DataRelation("CustomersOrders",
pCol1, cCol1)

dsCustomers.Relations.Add(CustomersOrders)

myFKC1 = New ForeignKeyConstraint("CustomersOrders1", pCol1,
cCol1)
myFKC1.DeleteRule = Rule.Cascade
myFKC1.UpdateRule = Rule.Cascade

'dsCustomers.Tables("Orders").Constraints.Add(myFKC1)

' Creating the DataRelation between Orders and Order Details
Tables
Dim pCol2 As DataColumn
Dim cCol2 As DataColumn
Dim myFKC2 As ForeignKeyConstraint

pCol2 = dsCustomers.Tables("Orders").Columns("OrderID")
cCol2 = dsCustomers.Tables("Order" & "" &
"Details").Columns("OrderID")

Dim OrderRelation As New DataRelation("OrderRelation", pCol2,
cCol2)

dsCustomers.Relations.Add(OrderRelation)
myFKC2 = New ForeignKeyConstraint("OrderRelation1", pCol2,
cCol2)
myFKC2.DeleteRule = Rule.Cascade
myFKC2.UpdateRule = Rule.Cascade

'dsCustomers.Tables("Order Details").Constraints.Add(myFKC2)

'dsCustomers.EnforceConstraints = True

' Create command builder. This line automatically generates the
'insert,update,delete commands,
' so you don't have to provide or create your own.

pSQLCommCust = New SqlCommandBuilder(daCustomers)
pSQLCommOrd = New SqlCommandBuilder(daOrders)
pSQLCommOrd = New SqlCommandBuilder(daOrderDetails)

Try

daCustomers.Update(dsCustomers, "Order" & "" & "Details")
daCustomers.Update(dsCustomers, "Orders")
daCustomers.Update(dsCustomers, "Customers")

MessageBox.Show("Update Successful ! Thank You", "Success")

Catch eSql As SqlException
MessageBox.Show(eSql.Message, "Sql error")
End Try


Thanks
Russel

John said:
Personally I am not a big fan of the SqlCommandBuilder as you can often build
more efficient SQL queries yourself. But whether you use SQL you created
yourself, stored procedures or the SqlCOmmandBuilder the solution is to call
the SqlDataAdapter.Update method for each of the DataTables. For only
Click to expand...
deletes, it goes like this:

delete the Order Details
delete the Orders
delete the Customers

You'll need a separate DataAdapter for each DataTable. If you are inserting
and updating too, you'll want to do it like this to avoid RI issues:

insert customer
update customers
insert orders
update orders
insert order details
update order details
delete the Order Details
delete the Orders
delete the Customers

You can pull out the changed, inserted or deleted rows from the DataSet's
DataTables using the GetChanges method or the Selet method ... just pass it
the parameter for the type of changes you want to retrieve.

oDa.Update(oDs.Tables["OrderDetails"].Select("", "",
DataViewRowState.ModifiedCurrent));

Hope this helps. If not, I wrote an article on this topic at MSDN Magzine
that you can refer to. It has full source code, too. You can find it here:
http://msdn.microsoft.com/msdnmag/issues/04/05/DataPoints/


-- John Papa
http://codebetter.com/blogs/john.papa



russzee said:
Hello,

I'm working with NorthWind

My Customers Form uses Untyped Dataset and Bound Controls
(BindingManagerBase). However, deleting a customer and updating the
database is
throwing an error because of Referential Integrity issues.

I'm trying to use DataRelations to resolve this issue.

- I've filled my Dataset with Customers, Orders and Order Details Table

- I then created two DataRelations as below :

' Creating the DataRelation between Customers and Orders Tables

Dim CustomersOrders As New DataRelation("CustomersOrders", _
dsCustomers.Tables("Customers").Columns("CustomerID"), _
dsCustomers.Tables("Orders").Columns("CustomerID"))

dsCustomers.Relations.Add(CustomersOrders)

' Creating the DataRelation between Orders and Order Details Tables

Dim OrderRelation As New DataRelation("OrderRelation", _
dsCustomers.Tables("Orders").Columns("OrderID"), _
dsCustomers.Tables("Order Details").Columns("OrderID"))

dsCustomers.Relations.Add(OrderRelation)

Here's my question :

How do I update my database so that when a Customer is deleted , the
corresponding Information in the Orders and Order Details Tables are
first deleted in the dataset and then on clicking the "Update Database"
button the changes in all 3 tables are reflected to the database.

Currently, I'm using the sqlcommandbuilder object seperately for each
dataadapter and using its Fill Method seprately to Update the database.
I know I'm missing something since this doesnt seem to work.


Thank You,
russzee
Click to expand...
Click to expand...
Click to expand...
 
I am only Deleting a row from the Customers Table in the Dataset
using pointer = Me.BindingContext(dsCustomers, "Customers") and then
when the "Delete" button is clicked i use
pointer.RemoveAt(pointer.Position) . This removes that Customer from
the Customers DataTable.

Then, I am then trying to delete all Orders and Order Details for that
CustomerID. In order to to that I figured I could have 2 data relations
(Customer --> Orders and Orders --> Order Details) with Foreign Key
contraints enabled and ForeignKeyConstraint.DeleteRule = Rule.Cascade
for both relations. In that way , when I delete any customer from the
Customer DataTable , the related rows in the Orders and Order Details
DataTables will automatically get deleted.

Once the above is accomplished , I can simply use

daCustomers.Update(dsCustomers­, "Order Details")
daCustomers.Update(dsCustomers­, "Orders")
daCustomers.Update(dsCustomers­, "Customers")

Am I on the right track. Is there an easier way ?

to include these deleted changes to the database and bypass the
Referential Integrity Issues.

Am i on the right track ?
 
This is what I get when I run the above code:

System Error :
Missing the DataColumn 'CustomerID' in the DataTable 'OrderDetails' for
the SourceColumn 'Customer ID'

Thanks once again
 
russzee said:
I am only Deleting a row from the Customers Table in the Dataset
using pointer = Me.BindingContext(dsCustomers, "Customers") and then
when the "Delete" button is clicked i use
pointer.RemoveAt(pointer.Position) . This removes that Customer from
the Customers DataTable.
Click to expand...

Actually it ONLY removes the record from the DataTable - it does NOT
flag it as Deleted, so the DataAdapter will not issue a Delete command.
You need to use DataRow.Delete() to do a real delete.

brian
 
Looks like you may be removing a column or a row. (like one of these calls)
oDs.Tables[0].Rows.RemoveAt(i);
oDs.Tables[0].Columns.RemoveAt(i);

I think you want to be calling the Delete method instead.

oDs.Tables[0].Rows[0].Delete();

The exception you are getting is not a RI exception, it is telling you that
it expects a column called CustomerID in the Order Details table ... which
isn't right since it should not have a CusotmerID. Perghaps you could send
the entire code snippet so I can see everything that is occurring? SOunds
like you've got something else going on in there too.

-- John Papa
http://codebetter.com/blogs/john.papa
 
Back
Top