DataRelation Using WHERE Clause in sql

[Guest]

I am trying to create a DataSet with DataRelations. The problem is I don't
want all the rows in the related tables.

A sql statement of what I'm looking for is:
SELECT ParentTable.*,ChildTable.* FROM ParentTable
INNER JOIN ChildTable ON ParentTable.inner_package_size =
ChildTable.inner_package_size
WHERE ParentTable.Key = 5

Trying to do the ado.net way with seperate querys and DataSet:


DataSet ds = new DataSet();
string sql = "SELECT Key,inner_package_size FROM ParentTable WHERE Key=5";
System.Data.SqlClient.SqlDataAdapter da = new
System.Data.SqlClient.SqlDataAdapter(sql, conn);
da.Fill(ds,"ParentTable");
sql = "Select inner_package_size,cost FROM ChildTable";
da = new System.Data.SqlClient.SqlDataAdapter(sql, conn);
da.Fill(ds,"ChildTable");

DataRelation rel =
new DataRelation("Relation1",
ds.Tables["ParentTable"].Columns["inner_package_size"],
ds.Tables["ChildTable"].Columns["inner_package_size"]);
ds.Relations.Add(rel);

This is a more simple version of the talbes & fields involved.

I only want the ChildTable rows that link to the ParentTable. I can't put
the field on the Where clause for the child table because I don't know what
the inner_package_size field value will be.
If I query all for the child table I get exceptions when adding the relation
about not all the values have corresponding parent values.

 

[David Browne]

I am trying to create a DataSet with DataRelations. The problem is I don't
want all the rows in the related tables.

A sql statement of what I'm looking for is:
SELECT ParentTable.*,ChildTable.* FROM ParentTable
INNER JOIN ChildTable ON ParentTable.inner_package_size =
ChildTable.inner_package_size
WHERE ParentTable.Key = 5

Trying to do the ado.net way with seperate querys and DataSet:


DataSet ds = new DataSet();
string sql = "SELECT Key,inner_package_size FROM ParentTable WHERE Key=5";
System.Data.SqlClient.SqlDataAdapter da = new
System.Data.SqlClient.SqlDataAdapter(sql, conn);
da.Fill(ds,"ParentTable");
sql = "Select inner_package_size,cost FROM ChildTable";
da = new System.Data.SqlClient.SqlDataAdapter(sql, conn);
da.Fill(ds,"ChildTable");

DataRelation rel =
new DataRelation("Relation1",
ds.Tables["ParentTable"].Columns["inner_package_size"],
ds.Tables["ChildTable"].Columns["inner_package_size"]);
ds.Relations.Add(rel);

This is a more simple version of the talbes & fields involved.

You'll have to use a query for ChildTable using a subquery, like:

select * from ChildTable
where inner_package_size in
(select inner_package_size
from ParentTable
where Key = @key)

David