using System;
using System.Data;
using System.Data.SqlClient;
class NestedXml {
public static void Main() {
SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 2 CustomerID, CompanyName " +
"FROM Customers " +
"ORDER BY CustomerID;" +
"SELECT OrderID, CustomerID, ShipCountry " +
"FROM Orders " +
"WHERE CustomerID IN (" +
" SELECT TOP 2 CustomerID " +
" FROM Customers " +
" ORDER BY CustomerID " +
")";
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
mySqlDataAdapter.SelectCommand = mySqlCommand;
DataSet myDataSet = new DataSet();
mySqlConnection.Open();
int numberOfRows = mySqlDataAdapter.Fill(myDataSet);
Console.WriteLine("numberOfRows = " + numberOfRows);
mySqlConnection.Close();
DataTable customersDT = myDataSet.Tables["Table"];
DataTable ordersDT = myDataSet.Tables["Table1"];
DataRelation customersOrdersDataRel =
new DataRelation(
"CustomersOrders",
customersDT.Columns["CustomerID"],
ordersDT.Columns["CustomerID"]
);
myDataSet.Relations.Add(
customersOrdersDataRel
);
myDataSet.WriteXml("nonNestedXmlFile.xml");
myDataSet.Relations["CustomersOrders"].Nested = true;
myDataSet.WriteXml("nestedXmlFile.xml");
}
}
|