any dataset experts ?

  • Thread starter Thread starter suzy
  • Start date Start date
S

suzy

I have 2 tables in my database (orders, customers). Not all customers have
orders, but all orders have customers.

When I run the following code, the XML that is returned shows a list of all
orders, and the corresponding customers nested within each order (which is
kind of what i want). BUT, it is also returning customers that don't have
an order.

how can i structure my code so only all orders are returned with
corresponding customers, rather than all orders and all customers.

thanks.

DataSet oDataSet = new DataSet("CustomerOrder");
SqlCommand oCmd = new SqlCommand("select * from orders");
oCmd.Connection = oConn;

SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd);
oAdapter.Fill (oDataSet, "Order");

oCmd.CommandText = "select * from customers";
oAdapter.SelectCommand = oCmd;
oAdapter.Fill (oDataSet, "Customer");

DataRelation oRelation = oDataSet.Relations.Add ("OrderCustomer",
oDataSet.Tables["Order"].Columns["CustomerId"],
oDataSet.Tables["Customer"].Columns["CustomerId"],
false);

oRelation.Nested = true;

return oDataSet.GetXml();
 
A simple way would be to restructure the SQL queries.
Assuming you want two seperate tables change the first
query to return values based on a join of the customers
and orders. The join will eliminate any customers that do
not have orders leaving you with a table of customers with
orders and a table of all the orders.

Likewise you could use the IN with in the first SQL select

SELECT * FROM customers WHERE CustomerID IN (SELECT
CustomerID FROM orders)

Again the results should be the same without the
generation of a join directly in the SQL
 
If I perform an inner join in my 1st query and get rid of the 2nd query
completely, then it works but the XML is not nested.

Both orders and customers are on the same hierachical level in the XML. Any
ideas how I can get the Customer nested within the order?


Perley said:
A simple way would be to restructure the SQL queries.
Assuming you want two seperate tables change the first
query to return values based on a join of the customers
and orders. The join will eliminate any customers that do
not have orders leaving you with a table of customers with
orders and a table of all the orders.

Likewise you could use the IN with in the first SQL select

SELECT * FROM customers WHERE CustomerID IN (SELECT
CustomerID FROM orders)

Again the results should be the same without the
generation of a join directly in the SQL

-----Original Message-----
I have 2 tables in my database (orders, customers). Not all customers have
orders, but all orders have customers.

When I run the following code, the XML that is returned shows a list of all
orders, and the corresponding customers nested within each order (which is
kind of what i want). BUT, it is also returning customers that don't have
an order.

how can i structure my code so only all orders are returned with
corresponding customers, rather than all orders and all customers.

thanks.

DataSet oDataSet = new DataSet("CustomerOrder");
SqlCommand oCmd = new SqlCommand("select * from orders");
oCmd.Connection = oConn;

SqlDataAdapter oAdapter = new SqlDataAdapter(oCmd);
oAdapter.Fill (oDataSet, "Order");

oCmd.CommandText = "select * from customers";
oAdapter.SelectCommand = oCmd;
oAdapter.Fill (oDataSet, "Customer");

DataRelation oRelation = oDataSet.Relations.Add ("OrderCustomer",
oDataSet.Tables["Order"].Columns["CustomerId"],
oDataSet.Tables["Customer"].Columns["CustomerId"],
false);

oRelation.Nested = true;

return oDataSet.GetXml();



.
Click to expand...
Click to expand...
 
Suzy,

oCmd.CommandText = "select * from customers";

Air code

oCmd.CommandText = "select * from customers where CustomerID in (Select
CustomerID From Orders)";
 
Without using a query to make sure that the dataset only contains customers
with orders (which would presumably be contrary to why you have all the
customers loaded in the first place) the only way you are going to be able
to do this is walk through each row in the parent table (customer) and write
out it's XML only if the parent row's GetChildren() method returns you
something useful.

Not particularly elegant, but not too tricky to achieve either. Good luck

--
Peter Wright
Author of ADO.NET Novice To Pro, from Apress Inc.


_____________________________
 
Back
Top