022. ASP.NET为DataSet中数据集添加关系及动态创建主子表和添加主子表关系

    protected void Page_Load(object sender, EventArgs e)
    {
        string connectionString = "server=.;database=TYW;uid=sa;pwd=123.456;";// ConfigurationSettings.AppSettings["strCon"];
        SqlConnection mycon = new SqlConnection(connectionString);//创建数据库连接
        string sqlCategory = "select  * from Category";//查询商品分类表中信息
        string sqlPhoto = "select  * from productinfo";//查询商品表中信息
        SqlDataAdapter da = new SqlDataAdapter(sqlCategory, mycon);//创建数据适配器
        DataSet ds = new DataSet();//创建数据集
        try
        {
            //显式地打开数据库连接
            if (mycon.State.Equals(ConnectionState.Closed))
            {
                mycon.Open();
            }
            da.Fill(ds, "Category");//填充商品分类表
            da.SelectCommand.CommandText = sqlPhoto;
            da.Fill(ds, "productinfo");//填充商品信息表
        }
        finally
        {
            mycon.Close();//显式地关闭数据库连接
        }
        //创建DataRelation对象，关联表间关系, DataRelation表示两个DataTable 对象之间的父/子关系。
        //   relationName:DataRelation 的名称。如果为 null 或空字符串 ("")，则当创建的对象添加到 System.Data.DataRelationCollection
        //     时，将指定一个默认名称。
        //   parentColumn:
        //     关系中的父级对应的列.
        //   childColumn:
        //     关系中的子级对应的列.
        DataRelation relat = new DataRelation("Category", ds.Tables["Category"].Columns["cateID"], ds.Tables["productinfo"].Columns["cateID"]);
        ds.Relations.Add(relat);//添加表间关系
        StringBuilder builder = new StringBuilder("");
        foreach (DataRow row in ds.Tables["Category"].Rows)
        {
            builder.Append("<b>");
            builder.Append(row["cateName"].ToString());
            builder.Append("</b><ul>");
            DataRow[] childRows = row.GetChildRows(relat);
            foreach (DataRow childRow in childRows)
            {
                builder.Append("<li>");
                builder.Append(childRow["productName"].ToString());
                builder.Append("</li>");
            }
            builder.Append("</ul>");
        }
        lbText.Text += builder.ToString();//将运行结果输出到页面中
    }

截图如下:

动态添加主子表, 且动态创建主子表的关系:

    protected void Page_Load(object sender, EventArgs e)
    {
        MakeMasterTable();  //创建主表
        MakeDetailTable();  //创建子表
        MakeDataRelation(); //建立主子表关联
    }

    private DataSet ds;

    private void MakeMasterTable()
    {
        //创建数据表对象
        DataTable table = new DataTable("MasterTable");
        DataColumn column;
        DataRow row;
        //创建数据列对象
        column = new DataColumn();
        //设置数据列的数据类型
        column.DataType = System.Type.GetType("System.Int32");
        column.ColumnName = "DictTypeID";//指定数据列名
        column.ReadOnly = true;          //设置数据列为只读列
        column.Unique = true;            //设置数据列中的值不允许重复
        table.Columns.Add(column);

        column = new DataColumn();
        column.DataType = System.Type.GetType("System.String");
        column.ColumnName = "DictTypeName";
        column.AutoIncrement = false;    //设置数据列中的值不自动递增
        column.Caption = "DictTypeName"; //设置数据列标题
        column.ReadOnly = false;
        column.Unique = false;
        table.Columns.Add(column);
        //创建列数组
        DataColumn[] PrimaryKeyColumns = new DataColumn[1];
        //将DictTypeID赋给列数组
        PrimaryKeyColumns[0] = table.Columns["DictTypeID"];
        table.PrimaryKey = PrimaryKeyColumns;//设置DictTypeID字段为主键

        ds = new DataSet();//创建数据集对象
        ds.Tables.Add(table);//将上面创建的数据表对象添加表数据集中
        //为数据表添加数据
        for (int i = 0; i< 2; i++)
        {
            row = table.NewRow();
            row["DictTypeID"] = i;
            row["DictTypeName"] = "DictTypeName " + i;
            table.Rows.Add(row);
        }
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }

    private void MakeDetailTable()
    {
        DataTable table = new DataTable("DetailTable");
        DataColumn column;
        DataRow row;

        column = new DataColumn();
        column.DataType= System.Type.GetType("System.Int32");
        column.ColumnName = "DictItemID";
        column.AutoIncrement = true;//设置该数据列是自增列
        column.Caption = "DictItemID";
        column.ReadOnly = true;
        column.Unique = true;

        table.Columns.Add(column);

        column = new DataColumn();
        column.DataType= System.Type.GetType("System.String");
        column.ColumnName = "DictItemName";
        column.AutoIncrement = false;
        column.Caption = "DictItemName";
        column.ReadOnly = false;
        column.Unique = false;
        table.Columns.Add(column);

        column = new DataColumn();
        column.DataType= System.Type.GetType("System.Int32");
        column.ColumnName = "DictTypeID";
        column.AutoIncrement = false;
        column.Caption = "DictTypeID";
        column.ReadOnly = false;
        column.Unique = false;
        table.Columns.Add(column);

        ds.Tables.Add(table);

        for(int i = 0; i <= 4; i ++)
        {
            row = table.NewRow();
            //row["DictItemID"] = i;//因为该列是自增列所以不用赋值
            row["DictItemName"] = "DictItemName " + i;
            row["DictTypeID"] = 0;
            table.Rows.Add(row);
        }
        for(int i = 0; i <= 4; i ++)
        {
            row = table.NewRow();
            //row["DictItemID"] = i + 5;
            row["DictItemName"] = "DictItemName " + (i+5);
            row["DictTypeID"] = 1;
            table.Rows.Add(row);
        }
    }

    private void MakeDataRelation()
    {
        DataColumn masterColumn =
            ds.Tables["MasterTable"].Columns["DictTypeID"];
        DataColumn detailColumn =
            ds.Tables["DetailTable"].Columns["DictTypeID"];
        //使用DataRelation对象建立主子表的关联关系
        DataRelation relation = new 
            DataRelation("MasterDetail", masterColumn, detailColumn);
        ds.Tables["DetailTable"].ParentRelations.Add(relation);
    }