DataRelation(DataSet中父子表)的一个示例
System.Data.DataRelation 类,表示两个 DataTable 对象之间的父/子关系。在常见的查询中,可以利用sql2005/2008的CTE应用来进行递归查询,
这里有一个典型示例:http://www.cnblogs.com/downmoon/archive/2009/10/23/1588405.html
此外,在数据量不大的情况下,也可以用DataRelation进行主子表或父子表的关联,参看下例:
假定:有两张表请假类型LeaveType和请假表Leave
这里是一个表结构的SQL:

create table LeaveType (
PKID int identity(1,1),
TypeName nvarchar(50) null,
CurState smallint not null default 0,
constraint PK_LEAVETYPE primary key (PKID)
)
go
create table Leave (
PKID int identity(1,1),
Title nvarchar(50) null,
Reason nvarchar(254) null,
LoginID nvarchar(50) null,
LeaveTypeID int ,
DepartID int null,
EmployeeID int null,
AddTime datetime null,
BeginTime datetime null,
EndTime datetime null,
TBeginDate datetime null,
TEndDate datetime null,
Remark nvarchar(1000) null,
ModUser nvarchar(50) null,
ModTime datetime null,
CurState smallint not null default 0,
constraint PK_LEAVE primary key (PKID)
)
go
PKID int identity(1,1),
TypeName nvarchar(50) null,
CurState smallint not null default 0,
constraint PK_LEAVETYPE primary key (PKID)
)
go
create table Leave (
PKID int identity(1,1),
Title nvarchar(50) null,
Reason nvarchar(254) null,
LoginID nvarchar(50) null,
LeaveTypeID int ,
DepartID int null,
EmployeeID int null,
AddTime datetime null,
BeginTime datetime null,
EndTime datetime null,
TBeginDate datetime null,
TEndDate datetime null,
Remark nvarchar(1000) null,
ModUser nvarchar(50) null,
ModTime datetime null,
CurState smallint not null default 0,
constraint PK_LEAVE primary key (PKID)
)
go
再插入一些测试数据:

truncate table LeaveType
insert into
LeaveType
select '事假',1 union all
Select '病假',1 union all
select '婚假',1 union all
select '产假',1 union all
select '特休假',1
go
Insert into Leave
select '请假'+Convert( Nvarchar(11),dateadd(dd,-500,getdate()),120),'准备与方鸿渐结婚','孙嘉柔',3,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
union all
select '回娘家'+Convert( Nvarchar(11),dateadd(dd,-200,getdate()),120),'准备为方鸿渐生孩子','孙嘉柔',4,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
union all
select
'回娘家'+Convert( Nvarchar(11),dateadd(dd,-10,getdate()),120),'准备与方鸿渐离婚','孙嘉柔',1,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
union all
select '回娘家'+Convert( Nvarchar(11),dateadd(dd,-2,getdate()),120),'准备与方鸿渐离婚','孙嘉柔',2,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
union all
select '回娘家'+Convert( Nvarchar(11),getdate(),120),'准备与方鸿渐离婚','孙嘉柔',2,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
update Leave set Title='第'+cast(PKID as nvarchar(10))+'次'+Title
insert into
LeaveType
select '事假',1 union all
Select '病假',1 union all
select '婚假',1 union all
select '产假',1 union all
select '特休假',1
go
Insert into Leave
select '请假'+Convert( Nvarchar(11),dateadd(dd,-500,getdate()),120),'准备与方鸿渐结婚','孙嘉柔',3,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
union all
select '回娘家'+Convert( Nvarchar(11),dateadd(dd,-200,getdate()),120),'准备为方鸿渐生孩子','孙嘉柔',4,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
union all
select
'回娘家'+Convert( Nvarchar(11),dateadd(dd,-10,getdate()),120),'准备与方鸿渐离婚','孙嘉柔',1,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
union all
select '回娘家'+Convert( Nvarchar(11),dateadd(dd,-2,getdate()),120),'准备与方鸿渐离婚','孙嘉柔',2,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
union all
select '回娘家'+Convert( Nvarchar(11),getdate(),120),'准备与方鸿渐离婚','孙嘉柔',2,1,1909,getdate(),'2010-1-1','2012-1-1','2010-1-1','2012-1-1',
'这回铁了心了','孙嘉柔',getdate(),1
update Leave set Title='第'+cast(PKID as nvarchar(10))+'次'+Title
查询主要代码如下:

protected void Page_Load(object sender, EventArgs e)
{
SqlConnection objConn = default(SqlConnection);
SqlDataAdapter da = default(SqlDataAdapter);
DataSet ds = default(DataSet);
//DataRow dtrParent = default(DataRow);
//DataRow dtrChild = default(DataRow);
objConn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Testdb"]);
da = new SqlDataAdapter("SELECT * FROM LeaveType", objConn);
ds = new DataSet();
try
{
objConn.Open();
da.Fill(ds, "LeaveTypes");
da.SelectCommand = new SqlCommand("SELECT * FROM Leave", objConn);
da.Fill(ds, "Leaves");
}
catch (SqlException exc)
{
Response.Write(exc.ToString());
}
finally
{
objConn.Dispose();
}
////Create the Data Relationship
ds.Relations.Add("Type_Leave", ds.Tables["LeaveTypes"].Columns["PKID"], ds.Tables["Leaves"].Columns["LeaveTypeID"]);
////Display the Category and Child Products Within
foreach (DataRow drParent in ds.Tables["LeaveTypes"].Rows)
{
lblDisplay.Text += "<h3>" + drParent["TypeName"] + "</h3><ul>";
foreach (DataRow drChild in drParent.GetChildRows("Type_Leave"))
{
lblDisplay.Text += "<li>" + drChild["loginID"] + drChild["Title"] + drChild["Reason"] + "</li>";
}
lblDisplay.Text += "</ul>";
}
}
{
SqlConnection objConn = default(SqlConnection);
SqlDataAdapter da = default(SqlDataAdapter);
DataSet ds = default(DataSet);
//DataRow dtrParent = default(DataRow);
//DataRow dtrChild = default(DataRow);
objConn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["Testdb"]);
da = new SqlDataAdapter("SELECT * FROM LeaveType", objConn);
ds = new DataSet();
try
{
objConn.Open();
da.Fill(ds, "LeaveTypes");
da.SelectCommand = new SqlCommand("SELECT * FROM Leave", objConn);
da.Fill(ds, "Leaves");
}
catch (SqlException exc)
{
Response.Write(exc.ToString());
}
finally
{
objConn.Dispose();
}
////Create the Data Relationship
ds.Relations.Add("Type_Leave", ds.Tables["LeaveTypes"].Columns["PKID"], ds.Tables["Leaves"].Columns["LeaveTypeID"]);
////Display the Category and Child Products Within
foreach (DataRow drParent in ds.Tables["LeaveTypes"].Rows)
{
lblDisplay.Text += "<h3>" + drParent["TypeName"] + "</h3><ul>";
foreach (DataRow drChild in drParent.GetChildRows("Type_Leave"))
{
lblDisplay.Text += "<li>" + drChild["loginID"] + drChild["Title"] + drChild["Reason"] + "</li>";
}
lblDisplay.Text += "</ul>";
}
}
最终效果:
关于DataRelation 更多说明,请查阅MSDN:
http://msdn.microsoft.com/zh-cn/library/system.data.datarelation%28VS.80%29.aspx
另外,关于GridView的分组显示,可以参考;http://www.cnblogs.com/downmoon/archive/2008/08/26/1276538.html
分类:
SQL
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
· AI与.NET技术实操系列(二):开始使用ML.NET
· 单线程的Redis速度为什么快?
· 展开说说关于C#中ORM框架的用法!
2007-12-27 WebPart的Web部件页部署时发生错误--小窍门
2007-12-27 asp.net与asp共处的奥秘--IIS
2007-12-27 Webpart中用DataGrid绑定SQL数据? [downmoon原作]