using System;
using System.Data;
using System.Data.SqlClient;
class NestedXml
{
public static void Main()
{
SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");
SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
mySqlCommand.CommandText =
"SELECT TOP 2 EmployeeID, CompanyName " +
"FROM Employee " +
"ORDER BY EmployeeID;" +
"SELECT DeptID, EmployeeID, City " +
"FROM Dept " +
"WHERE EmployeeID IN (" +
" SELECT TOP 2 EmployeeID " +
" FROM Employee " +
" ORDER BY EmployeeID " +
")";
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 customersDeptDataRel =
new DataRelation(
"EmployeeDept",
customersDT.Columns["EmployeeID"],
ordersDT.Columns["EmployeeID"]
);
myDataSet.Relations.Add(
customersDeptDataRel
);
myDataSet.WriteXml("nonNestedXmlFile.xml");
myDataSet.Relations["EmployeeDept"].Nested = true;
myDataSet.WriteXml("nestedXmlFile.xml");
}
}