How to work with joined tables in ADO.NET

  • Thread starter Thread starter Flare
  • Start date Start date
F

Flare

Hi.

I have (simplified) 3 tables. (See bottom). This is not my real tables but
they show my point. I want to represent data from all tree tables and i
wanna do this by joining them. The joined data will be represented in a
datagrid, where editing,inserting, deleting will be aviable.Update is ok for
now.

How do i do this the right ADO.net way? I just cant figure out an elegant
way to perform the update. Eg. i can make a view wich fetch the data, and
put it in the grid....But how do i update the right tables on an elegant
way? Eg. I edit a country tag, and want to peform an update.

Ideally i have my database, a webservive from where i get a dataset, and
where i (maybe sent som kind of a diffgram) wich enable the webservice to
perform the updates. All this i can figure out if it wasnt for the joins.

Anyone know how to work with joined tables in ADO.net or have some links? I
have used HOURS to read "all" the MSDN articles about datasets, dataadpters,
autogen commands, but nothing seems to solve my problem ....an "easy" way.

It cant be right that i have to wirte a stored-proc where i sent (see
bottom) all me variables and make it update each and every table manually?

Looking forward to some inputs.

Regards Anders

======Tables========
TablePerson
-------------
Id
Country_FKId
City_FKId

TableCountry
 
Select the records from the three tables respectively via three different
queries. Each query should be represented in a different datatable and each
datatable should be in one dataset. You can use the datarelation object to
relate the tables. Then, you can make whatever changes to each of your
datatables and then submit them with three updates. Since you used three
queries to pull data from each of the DB tables, you can use a
CommandBUilder, SQL DataAdapter or write your own update logic, but
essentially, all you need to do is provide the update logic and call the
DataAdapter.Update for each of the respective tables. You may want to use a
Transaction, but that's another story. I know you have visited MSDN but
this is the code snippet I took from there. The approach is pretty
straighforward. If I understand your diagram correctly, then Table Person
is the child of the three, so you'd reference each of the other two tables
columns as the parent. You can have multiple relations on a table, so you
can just substitute your table names in the below snippet, and then add
another relation using the same logic. I'm not sure exactly what's giving
you the problem, so I'm speaking in generalized terms but if you look
through this and still have trouble, let me know and I can address it
directly. HTH,

Bill

private void CreateRelation() {
// Get the DataColumn objects from two DataTable objects in a DataSet.
DataColumn parentCol;
DataColumn childCol;
// Code to get the DataSet not shown here.
parentCol = DataSet1.Tables["Customers"].Columns["CustID"];
childCol = DataSet1.Tables["Orders"].Columns["CustID"];
// Create DataRelation.
DataRelation relCustOrder;
relCustOrder = new DataRelation("CustomersOrders", parentCol, childCol);
// Add the relation to the DataSet.
DataSet1.Relations.Add(relCustOrder);
}
 
Select the records from the three tables respectively via three different
queries. Each query should be represented in a different datatable and each
datatable should be in one dataset. You can use the datarelation object to
relate the tables. Then, you can make whatever changes to each of your
datatables and then submit them with three updates. Since you used three
queries to pull data from each of the DB tables, you can use a
CommandBUilder, SQL DataAdapter or write your own update logic, but
essentially, all you need to do is provide the update logic and call the
DataAdapter.Update for each of the respective tables. You may want to use a
Transaction, but that's another story. I know you have visited MSDN but
this is the code snippet I took from there. The approach is pretty
straighforward. If I understand your diagram correctly, then Table Person
is the child of the three, so you'd reference each of the other two tables
columns as the parent. You can have multiple relations on a table, so you
can just substitute your table names in the below snippet, and then add
another relation using the same logic. I'm not sure exactly what's giving
you the problem, so I'm speaking in generalized terms but if you look
through this and still have trouble, let me know and I can address it
directly. HTH,

Bill

private void CreateRelation() {
// Get the DataColumn objects from two DataTable objects in a DataSet.
DataColumn parentCol;
DataColumn childCol;
// Code to get the DataSet not shown here.
parentCol = DataSet1.Tables["Customers"].Columns["CustID"];
childCol = DataSet1.Tables["Orders"].Columns["CustID"];
// Create DataRelation.
DataRelation relCustOrder;
relCustOrder = new DataRelation("CustomersOrders", parentCol, childCol);
// Add the relation to the DataSet.
DataSet1.Relations.Add(relCustOrder);
Click to expand...

Thx for your time William.

I see that approach would solve my problem. My "Problem" was that i wanted
to use join on the server. But I realize now thats the wrong way to handle
the problem. Antoher thing was that i wanted to fetch my data with a view so
when i changed my datamodel (likely) i would still could use the view from
my program. But since i cant edit back trough the joined view, im not going
anywere with that approach either. (right? using mssql2k)

So im left to work directly down in my tables.

Any comments one that?

Reagards
Anders, DK
 
Joining the tables on the server has two main drawbacks...1 - that it's
going to be slower b/c you'll bring back a ton of redundant data 2- Updating
is a nightmare. Everything I've read on the subject is pretty stern about
avoiding it and opting for DataRelations instead. I've used DataRelations a
ton and after the stumbling a little on the first attempt, I've found them
easy to use and definely like them more than joins --- even if joins were as
easy to use b/c I can pull my tables over and redefine relations on the fly.
I think for this one, go with the DataRelation approach.

HTH,

Bill
Flare said:
Select the records from the three tables respectively via three different
queries. Each query should be represented in a different datatable and each
datatable should be in one dataset. You can use the datarelation object to
relate the tables. Then, you can make whatever changes to each of your
datatables and then submit them with three updates. Since you used three
queries to pull data from each of the DB tables, you can use a
CommandBUilder, SQL DataAdapter or write your own update logic, but
essentially, all you need to do is provide the update logic and call the
DataAdapter.Update for each of the respective tables. You may want to
Click to expand...
use
a
Transaction, but that's another story. I know you have visited MSDN but
this is the code snippet I took from there. The approach is pretty
straighforward. If I understand your diagram correctly, then Table Person
is the child of the three, so you'd reference each of the other two tables
columns as the parent. You can have multiple relations on a table, so you
can just substitute your table names in the below snippet, and then add
another relation using the same logic. I'm not sure exactly what's giving
you the problem, so I'm speaking in generalized terms but if you look
through this and still have trouble, let me know and I can address it
directly. HTH,

Bill

private void CreateRelation() {
// Get the DataColumn objects from two DataTable objects in a DataSet.
DataColumn parentCol;
DataColumn childCol;
// Code to get the DataSet not shown here.
parentCol = DataSet1.Tables["Customers"].Columns["CustID"];
childCol = DataSet1.Tables["Orders"].Columns["CustID"];
// Create DataRelation.
DataRelation relCustOrder;
relCustOrder = new DataRelation("CustomersOrders", parentCol, childCol);
// Add the relation to the DataSet.
DataSet1.Relations.Add(relCustOrder);
Click to expand...

Thx for your time William.

I see that approach would solve my problem. My "Problem" was that i wanted
to use join on the server. But I realize now thats the wrong way to handle
the problem. Antoher thing was that i wanted to fetch my data with a view so
when i changed my datamodel (likely) i would still could use the view from
my program. But since i cant edit back trough the joined view, im not going
anywere with that approach either. (right? using mssql2k)

So im left to work directly down in my tables.

Any comments one that?

Reagards
Anders, DK
Click to expand...
 
Joining the tables on the server has two main drawbacks...1 - that it's
going to be slower b/c you'll bring back a ton of redundant data 2- Updating
is a nightmare. Everything I've read on the subject is pretty stern about
avoiding it and opting for DataRelations instead. I've used DataRelations a
ton and after the stumbling a little on the first attempt, I've found them
easy to use and definely like them more than joins --- even if joins were as
easy to use b/c I can pull my tables over and redefine relations on the fly.
I think for this one, go with the DataRelation approach.
Click to expand...

Thx alot william for contructive answeres. I will go for the datarelation
model. I allready have good results on the simple tables.

Anders
 
Back
Top