using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ConsoleApplication1.DataSetRelationsConstraints
{
/// <summary>
/// DataSet中表的数据关系及约束
/// </summary>
public class DataSetRelationsConstraintsBO
{
DataSet ds = new DataSet("Relationships");
//----------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 构造函数
/// </summary>
public DataSetRelationsConstraintsBO()
{
ds.Tables.Add(CreateBuildingTable());
ds.Tables.Add(CreateRoomTable());
Console.WriteLine("构造数据成功：");
ShowDataSet();
}
//----------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 测试
/// </summary>
public static void TestDataSetRelationsConstraints()
{
DataSetRelationsConstraintsBO dsr = new DataSetRelationsConstraintsBO();
//测试一：关系
//Console.WriteLine("-----------------------------显示关系及数据");
//dsr.SetRelations();
//dsr.ShowConstraints();
//dsr.ShowRelationsTableData();
//dsr.ShowDataSet();
//Console.WriteLine("-----------------------------删除父表记录");
//dsr.DeleteBuildingRow();
//dsr.ShowRelationsTableData();
//dsr.ShowDataSet();
//Console.WriteLine("-----------------------------删除子表记录");
//dsr.DeleteRoomRow();
//dsr.ShowRelationsTableData();
//dsr.ShowDataSet();
//Console.WriteLine("-----------------------------更新父表主键");
//dsr.UpdateBuildingID(10);
//dsr.ShowRelationsTableData();
//dsr.ShowDataSet();
//测试二：主键约束
//dsr.SetPrimaryKeyConstraints();
//dsr.ShowConstraints();
//测试三：外键约束
//Console.WriteLine("-----------------------------显示约束及数据");
//dsr.SetForeignKeyConstraints();
//dsr.ShowConstraints();
//dsr.ShowDataSet();
//Console.WriteLine("-----------------------------删除父表记录，引发级联删除");
//dsr.DeleteBuildingRow();
//dsr.ShowDataSet();
//Console.WriteLine("-----------------------------删除子表记录");
//dsr.DeleteRoomRow();
//dsr.ShowDataSet();
//Console.WriteLine("-----------------------------更新父表主键，引发级联更新");
//dsr.UpdateBuildingID(10);
//dsr.ShowDataSet();
dsr.SetRelations();
dsr.Export();
}
//----------------------------------------------------------------------------------------------------------------------------
public void Export()
{
ds.WriteXml("data.xml");
ds.WriteXmlSchema("schema.xml");
}
//----------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 设置表之间的关系
/// </summary>
public void SetRelations()
{
//建立DataSet中表的关系
//说明一：主表的主键必须具有唯一性
//说明二：子表的所有记录的外建都必须为主表的主键，不能在主表中不存在；
//说明三：自动将Building中的BuildingID设置约束
//说明四：自动设置级联删除、级联更新约束
ds.Relations.Add("Rooms", ds.Tables["Building"].Columns["BuildingID"], ds.Tables["Room"].Columns["BuildingID"]);
}
/// <summary>
/// 显示关系表数据
/// </summary>
public void ShowRelationsTableData()
{
//遍历父表中的所有记录,并且显示出父表中每个记录对应子表中的所有记录;
Console.WriteLine("父表Building记录，并循环显示每个父表对应子表中的关系记录：");
foreach (DataRow theBuilding in ds.Tables["Building"].Rows)
{
//获取当前行所对应子表的所有记录行
DataRow[] children = theBuilding.GetChildRows("Rooms");
int roomCount = children.Length;
Console.WriteLine("Building{0} contains {1} room{2}",
theBuilding["Name"],
roomCount,
roomCount > 1 ? "s" : "");
foreach (DataRow theRoom in children)
{
Console.WriteLine(" Room:{0}", theRoom["Name"]);
}
}
//遍历子表中的所有记录,并且显示出子表中对应的父表中的记录信息
Console.WriteLine("子表Room记录：");
foreach (DataRow theRoom in ds.Tables["Room"].Rows)
{
DataRow parents = theRoom.GetParentRow("Rooms");
Console.WriteLine("Room {0} is contained in building {1}", theRoom["Name"], parents["Name"]);
}
}
//----------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 设置约束（主键码唯一约束）
/// </summary>
public void SetPrimaryKeyConstraints()
{
DataColumn[] buildingPK = new DataColumn[1];
buildingPK[0] = ds.Tables["Building"].Columns["BuildingID"];
ds.Tables["Building"].Constraints.Add(new UniqueConstraint("PK_Building", buildingPK));
ds.Tables["Building"].PrimaryKey = buildingPK;
DataColumn[] roomPK = new DataColumn[2];
roomPK[0] = ds.Tables["Room"].Columns["RoomID"];
roomPK[1] = ds.Tables["Room"].Columns["Name"];
ds.Tables["Room"].Constraints.Add(new UniqueConstraint("PK_Room", roomPK));
ds.Tables["Room"].PrimaryKey = roomPK;
}
/// <summary>
/// 设置约束（外键约束）
/// </summary>
public void SetForeignKeyConstraints()
{
//设置表Building的主键约束,Key为BuildingID
DataColumn[] buildingPK = new DataColumn[1];
buildingPK[0] = ds.Tables["Building"].Columns["BuildingID"];
ds.Tables["Building"].Constraints.Add(new UniqueConstraint("PK_Building", buildingPK));
ds.Tables["Building"].PrimaryKey = buildingPK;
//设置表Room的主键约束，Key为RoomID
DataColumn[] roomPK = new DataColumn[1];
roomPK[0] = ds.Tables["Room"].Columns["RoomID"];
ds.Tables["Room"].Constraints.Add(new UniqueConstraint("PK_Room", roomPK));
ds.Tables["Room"].PrimaryKey = roomPK;
//设置外键
//说明：设置外键后可以级联操作数据，但无法通过关系去访问数据（如：子表查找父表记录，父表查找子表记录）
DataColumn parent = ds.Tables["Building"].Columns["BuildingID"];
DataColumn child = ds.Tables["Room"].Columns["BuildingID"];
ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Product_CategoryID", parent, child);
fk.DeleteRule = Rule.Cascade; //设置级联删除
fk.UpdateRule = Rule.Cascade; //设置级联更新
ds.Tables["Room"].Constraints.Add(fk);
/*更新和删除约束
* Rule.Cascade：如果更新了父键，就应把亲的键值复制到所有子记录上。如果删除了父记录，也将删除子记录，这是默认选项。
* Rule.None：不执行任何操作，这个选项会留下数据表中的孤立行。
* Rule.SetDefault：如果定义了一个子记录，那么每个受影响的子记录都把外键码列设置为其默认值。
* Rule.SetNull：父记录删除或父记录更新主键时，所有的子行都把外键列设置为DBNull;
*/
}
/// <summary>
/// 显示约束
/// </summary>
public void ShowConstraints()
{
Console.WriteLine("表Building的约束：");
foreach (Constraint item in ds.Tables["Building"].Constraints)
{
Console.WriteLine(" Building ConstraintName：{0};", item.ConstraintName);
}
Console.WriteLine("表Room的约束：");
foreach (Constraint item in ds.Tables["Room"].Constraints)
{
Console.WriteLine(" Room ConstraintName：{0};", item.ConstraintName);
}
}
//----------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 删除Building行
/// </summary>
public void DeleteBuildingRow()
{
ds.Tables["Building"].Rows[0].Delete();
}
/// <summary>
/// 删除Room行
/// </summary>
public void DeleteRoomRow()
{
ds.Tables["Room"].Rows[0].Delete();
}
/// <summary>
/// 更新BuildingID
/// </summary>
/// <param name="buildingID">buildingID</param>
public void UpdateBuildingID(int buildingID)
{
ds.Tables["Building"].Rows[0][0] = buildingID;
}
//----------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 显示DataSet数据
/// </summary>
public void ShowDataSet()
{
Console.WriteLine("表Building的数据：");
foreach (DataRow item in ds.Tables["Building"].Rows)
{
Console.WriteLine("BuildingID={0}; Name={1}; ", item[0], item[1]);
}
Console.WriteLine("表Room的数据：");
foreach (DataRow item in ds.Tables["Room"].Rows)
{
Console.WriteLine("RoomID={0}; Name={1}; BuildingID={2}; ", item[0], item[1], item[2]);
}
}
//----------------------------------------------------------------------------------------------------------------------------
/// <summary>
/// 生成Building表
/// </summary>
/// <returns>DataTable</returns>
private DataTable CreateBuildingTable()
{
DataTable dt = new DataTable("Building");
dt.Columns.Add("BuildingID", typeof(int));
dt.Columns.Add("Name", typeof(string));
BuildingTableAddRow(1, "Building1", dt);
BuildingTableAddRow(2, "Building2", dt);
BuildingTableAddRow(3, "Building3", dt);
BuildingTableAddRow(4, "Building4", dt);
BuildingTableAddRow(5, "Building5", dt);
BuildingTableAddRow(6, "Building6", dt);
return (dt);
}
private void BuildingTableAddRow(int buildingID, string name, DataTable dt)
{
DataRow dr;
dr = dt.NewRow();
dr["BuildingID"] = buildingID;
dr["Name"] = name;
dt.Rows.Add(dr);
}
/// <summary>
/// 生成Room表
/// </summary>
/// <returns>DataTable</returns>
private DataTable CreateRoomTable()
{
DataTable dt = new DataTable("Room");
dt.Columns.Add("RoomID", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("BuildingID", typeof(int));
RoomTableAddRow(1, "Room1", 1, dt);
RoomTableAddRow(2, "Room2", 1, dt);
RoomTableAddRow(3, "Room3", 1, dt);
RoomTableAddRow(4, "Room4", 1, dt);
RoomTableAddRow(5, "Room5", 2, dt);
RoomTableAddRow(6, "Room6", 2, dt);
RoomTableAddRow(7, "Room7", 2, dt);
RoomTableAddRow(8, "Room8", 3, dt);
RoomTableAddRow(9, "Room9", 3, dt);
return (dt);
}
private void RoomTableAddRow(int roomID, string name, int buildingID, DataTable dt)
{
DataRow dr;
dr = dt.NewRow();
dr["RoomID"] = roomID;
dr["BuildingID"] = buildingID;
dr["Name"] = name;
dt.Rows.Add(dr);
}
//----------------------------------------------------------------------------------------------------------------------------
}
}