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