SqlConnection thisConnection = new SqlConnection(@"Server = ./sqlexpress;Integrated Security = true;" + "Database = northwind");
SqlDataAdapter thisAdapter = new SqlDataAdapter("SELECT CustomerID,CompanyName FROM Customers", thisConnection);//此处可以省略 但是为了整体看上去层次明显 保留此Apapter
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(thisAdapter);
DataSet thisDataSet = new DataSet();
SqlDataAdapter CustAdapter = new SqlDataAdapter("SELECT * FROM Customers", thisConnection);
SqlDataAdapter OrderAdapter = new SqlDataAdapter("SELECT * FROM Orders", thisConnection);
CustAdapter.Fill(thisDataSet,"Customers");
OrderAdapter.Fill(thisDataSet,"Orders");
DataRelation thisRelation = thisDataSet.Relations.Add("CustOrder",
thisDataSet.Tables["Customers"].Columns["CustomerID"],
thisDataSet.Tables["Orders"].Columns["CustomerID"]);//
foreach (DataRow custRow in thisDataSet.Tables["Customers"].Rows)
{
Console.WriteLine(custRow["CustomerID"] +"/t"+ custRow["CompanyName"]);
foreach (DataRow orderRow in custRow.GetChildRows(thisRelation))
{
Console.WriteLine(orderRow["OrderID"]);
}
}
thisConnection.Close();
上述例子是两表之间的访问,如果现在有四张表1 2 3 4,应该写成
DataRelation thisRelation = thisDataSet.Relations.Add("CustOrder1",
thisDataSet.Tables["1"].Columns["CustomerID"],
thisDataSet.Tables["2"].Columns["CustomerID"]);//
DataRelation thisRelation = thisDataSet.Relations.Add("CustOrder2",
thisDataSet.Tables["2"].Columns["CustomerID"],
thisDataSet.Tables["3"].Columns["CustomerID"]);//
DataRelation thisRelation = thisDataSet.Relations.Add("CustOrder3",
thisDataSet.Tables["3"].Columns["CustomerID"],
thisDataSet.Tables["4"].Columns["CustomerID"]);//