为dataset的多个表定义表间关系,并输出主从表:
// 创建dataset
string constr = "server=(local);uid=sa;pwd=123456; database=信息中心科技资料管理数据库";
SqlConnection con = new SqlConnection(constr);
string com1 = "select * from 资料信息表";
string com2 = "select * from 资料借阅表";
SqlDataAdapter ada1 = new SqlDataAdapter(com1,con);
SqlDataAdapter ada2 = new SqlDataAdapter(com2,con);
DataSet ds = new DataSet();
ada1.Fill(ds, "资料信息表");
ada2.Fill(ds, "资料借阅表");
//定义表间关系
ds.Relations.Add("关系名", ds.Tables["主表"].Columns["主表主键"], ds.Tables["从表"].Columns["从表外键"]);
//由主表找出从有并输出
foreach (DataRow perrow in ds.Tables["资料信息表"].Rows)
{
DataRow[] chilrow =perrow.GetChildRows("rel"); //"rel"关系名
int roomCount = chilrow.Length;
Console.WriteLine("档号 {0} 行数 {1} 借阅信息:{2}", perrow["档号"], roomCount,roomCount > 1 ? "--" : "");
foreach (DataRow theRoom in chilrow)
Console.WriteLine("借阅人: {0}", theRoom["借阅人"]);
}
//由从表找到主表并输出
foreach (DataRow theChild in ds.Tables["资料借阅表"].Rows)
{
DataRow[] parents = theChild.GetParentRows("rel");
foreach (DataRow thePar in parents)
Console.WriteLine("借阅人: {0} 被借阅的档号: {1}",
theChild["借阅人"],
thePar["档号"]);
}