How to create a DataSet with two tables having a 'JOIN' in ADO.NET?
-
I want to create a DataSet that has two tables which are connected by a LEFT JOIN. All MSDN examples tell that if two tables are connected by a parent-child relation, I can add the two tables to DataSet object, create a DataRelation object by passing (parent, child) as parameters, and then add the DataRelation to the DataSet. But no example tells how to relate the tables if they are connected by a JOIN. I don't think we can create DataRelation for JOINs. If we can, how? And how to differentiate between JOIN, LEFT JOIN, FULL JOIN etc. while creating DataRelation? Any help is appreciated.
It's better to know some of the questions than all of the answers.
Pravin. -
I want to create a DataSet that has two tables which are connected by a LEFT JOIN. All MSDN examples tell that if two tables are connected by a parent-child relation, I can add the two tables to DataSet object, create a DataRelation object by passing (parent, child) as parameters, and then add the DataRelation to the DataSet. But no example tells how to relate the tables if they are connected by a JOIN. I don't think we can create DataRelation for JOINs. If we can, how? And how to differentiate between JOIN, LEFT JOIN, FULL JOIN etc. while creating DataRelation? Any help is appreciated.
It's better to know some of the questions than all of the answers.
Pravin.Try the Database forum?
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
-
I want to create a DataSet that has two tables which are connected by a LEFT JOIN. All MSDN examples tell that if two tables are connected by a parent-child relation, I can add the two tables to DataSet object, create a DataRelation object by passing (parent, child) as parameters, and then add the DataRelation to the DataSet. But no example tells how to relate the tables if they are connected by a JOIN. I don't think we can create DataRelation for JOINs. If we can, how? And how to differentiate between JOIN, LEFT JOIN, FULL JOIN etc. while creating DataRelation? Any help is appreciated.
It's better to know some of the questions than all of the answers.
Pravin.I agree this is more of a db question, but you're answer may not lie in thinking of the dataset as two tables. The underlying query will contain a join, but the dataset will still only contain a single result set table.
-
I want to create a DataSet that has two tables which are connected by a LEFT JOIN. All MSDN examples tell that if two tables are connected by a parent-child relation, I can add the two tables to DataSet object, create a DataRelation object by passing (parent, child) as parameters, and then add the DataRelation to the DataSet. But no example tells how to relate the tables if they are connected by a JOIN. I don't think we can create DataRelation for JOINs. If we can, how? And how to differentiate between JOIN, LEFT JOIN, FULL JOIN etc. while creating DataRelation? Any help is appreciated.
It's better to know some of the questions than all of the answers.
Pravin.A JOIN produces one table, not two.
-
Try the Database forum?
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
I thought about that before posting, but this one is more of an ADO.NET question than a database question, so thought I'll be in good company here. :) The DB forum doesn't have many 'programming' questions, but if I don't get any answer here, probably I'll try my luck there as well.
It's better to know some of the questions than all of the answers.
Pravin. -
I want to create a DataSet that has two tables which are connected by a LEFT JOIN. All MSDN examples tell that if two tables are connected by a parent-child relation, I can add the two tables to DataSet object, create a DataRelation object by passing (parent, child) as parameters, and then add the DataRelation to the DataSet. But no example tells how to relate the tables if they are connected by a JOIN. I don't think we can create DataRelation for JOINs. If we can, how? And how to differentiate between JOIN, LEFT JOIN, FULL JOIN etc. while creating DataRelation? Any help is appreciated.
It's better to know some of the questions than all of the answers.
Pravin.Ignore the people who say this is a database question. They probably have never worked with a DataRelation object and don't realize that ADO.Net allows you to essentially create an entire relational database in a DataSet. The parent child relationships are determined by the cardinality of the two tables. If for every record in table A there can be many records in table B, then A is the parent and B is the child and vise versa. In the case of a left join, the table that may have nulls is always the child. In the case of a many-to-many relationship you should create two DataRelations, one in each direction.
-
Ignore the people who say this is a database question. They probably have never worked with a DataRelation object and don't realize that ADO.Net allows you to essentially create an entire relational database in a DataSet. The parent child relationships are determined by the cardinality of the two tables. If for every record in table A there can be many records in table B, then A is the parent and B is the child and vise versa. In the case of a left join, the table that may have nulls is always the child. In the case of a many-to-many relationship you should create two DataRelations, one in each direction.
And how does that answer the question?
-
Try the Database forum?
Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together.
DataSet ds = new DataSet(); DataTable dt1 = new DataTable(); dt1.Columns.Add("PK", typeof(int)); DataTable dt2 = new DataTable(); dt2.Columns.Add("FK", typeof(int)); DataRelation Join = new DataRelation("Join", dt1.Columns[0], dt2.Columns[0]); ds.Relations.Add(Join);
-
DataSet ds = new DataSet(); DataTable dt1 = new DataTable(); dt1.Columns.Add("PK", typeof(int)); DataTable dt2 = new DataTable(); dt2.Columns.Add("FK", typeof(int)); DataRelation Join = new DataRelation("Join", dt1.Columns[0], dt2.Columns[0]); ds.Relations.Add(Join);
Shouldn't you have replied against the OP rather than somebody questioning where the post should go?
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Forgive your enemies - it messes with their heads
-
DataSet ds = new DataSet(); DataTable dt1 = new DataTable(); dt1.Columns.Add("PK", typeof(int)); DataTable dt2 = new DataTable(); dt2.Columns.Add("FK", typeof(int)); DataRelation Join = new DataRelation("Join", dt1.Columns[0], dt2.Columns[0]); ds.Relations.Add(Join);
This is exactly what all the MSDN examples show. My questions are: 1. This code is for the case where we have a query like "SELECT something FROM dt1, dt2 WHERE dt1.PK=dt2.FK". Will this also hold good for a query like "SELECT something FROM dt1 JOIN dt2 on (dt1.PK=dt2.FK)"? 2. How does the statement
DataRelation Join = new DataRelation("Join", dt1.Columns[0], dt2.Columns[0]);change if in case of a JOIN, I have a FULL JOIN, or a LEFT JOIN, or an OUTER JOIN etc.?
It's better to know some of the questions than all of the answers.
Pravin.
