System.Data.DataRelation 类,表示两个 DataTable 对象之间的父/子关系。在常见的查询中,可以利用sql2005/2008的CTE应用来进行递归查询,
这里有一个典型示例:http://www.cnblogs.com/downmoon/archive/2009/10/23/1588405.html
此外,在数据量不大的情况下,也可以用DataRelation进行主子表或父子表的关联,参看下例:
假定:有两张表请假类型LeaveType和请假表Leave
这里是一个表结构的SQL:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
再插入一些测试数据:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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
查询主要代码如下:
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
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