DataSet中表的关系及约束

时间:2022-02-11 16:22:27

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);
        }
        //----------------------------------------------------------------------------------------------------------------------------
    }
}