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();//将运行结果输出到页面中

    }

截图如下:

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAOYAAAE4CAIAAADASxgHAAASE0lEQVR4nO2dyZXbOBBAFUKH4AzUITiEToDvOQSnwWjMTHzhvUNwCPSBW6FQAEGJolDS/5dpiZtm5qtUWAq4DACuuDz7AwDsA2XBGSgLzkBZcAbKgjNQFpxxlLJdc7lcLk2n/x769ipeAdxLStmuuVwu17aP3tNvjgRi7lN2OsG+b/DkHHwl3oZI2b69Xi5Nt0/Z0Musstv2rSyPmi6yvMwcgpfEVPba9quycxTMRDalTVmUnS4S34D5SclzURYMZVUUlDE1FWVXa9J6G15pZ9PGoiysaGWnILsjl100VUE3FWU3tDa/LeSysKCV7Zrxf/+sbF6wOYO4QVmR966NtuCaXHsM3hcrMZDKxldYTa0blZWpQd81S88BykKaPZ1cI8rBm6Ns0+ifdBXRp6cbScGSucRvw8uTUHYR4to06czg2vZD37bd6NrUM5ZjvGBfLhtk0OuLxNvw8li57GWOgUKIVQej12pWtuiJS5Rt2y5IAKbA3rXN+DVBWbDYTgz06FSBsl2jFI9PvjSdbmbpR6IsWBTlspNl48tNZTuZpM4vzCht5AgoCxsUNr+mfoRh2FLWmDHQhe2sZJTV90NZsNjdY2ANwM7KxqMKwTVBaM1kvsluNBmK6TF4V8qVFY7YA2BzlE2InsmFg9tclPVEWQhgijc4A2XBGSgLzkBZcAbKgjNQFpyBsuAMlAVnoCw4A2XBGSgLzkBZcAbKgjNQFpyBsuAMlAVnoCw4A2XBGSgLzkBZcAbKgjNQFpyBsuCM2pTVK3gNw6DW+DqQffc1PxqcTj3KjkbsUnZ+XywAnjolddAkdcWjvjqwh3qUHX1YlU0vVhuvxhwdXuTaUnangkj7fKpRNloFUYRNUxTxZhxlD1VWnjZvfwJP47HK/v3799evX5+fn19fX3/+/MmdOnlRnBiItRjLlS3YJ8/wMbrHumop9p7PA5X9/v7++PiQOmSsnUWYlc3L1bTZ1eubLq1sVrLwuNhQKr1rBA2ys3mgsr9//1YqfXx8JM5dVka8qVm+J8ruUFa07hJXhUvywyk8UNmvr684AG5dlFE2/UP8GGWXu2auItCez6lR9vPzc+uisv1qspvrBr/lt+eywSZ6+Y4yAu2ZPFDZf//+KR3yueyCWOJeL28v5Qj7w8a/ovTz5igrInf+KkYYTuaMHoMfP378/Plzo8dgQgrQ9638xT9A2XJkrpG4R2YAAx5INf2yE8aWS3LF+lJlF3R2uogsdxuRT7D3KTeUpY/rSdSurDqUUXbu9wo1ErppOae/g9umBi3iz0TD61k4UFakuemJBOqQaJpZZqadtWOn3WxD2KdQm7KPIgqfws3QeDmsBhXyLsrCy4Cy4AyUBWegLDgDZcEZKAvOQFlwRl3KqrGq3ESrU1Cdtzb04p5LVcrGQ1/BCIB5eAslVGZA2BisTY43FNwOHkRVysbS6ErBvB4FAqVnvlwu17brUbZ6KlE2TgOi2dW3+5g/ZYzU+jL1ga5tS2JQCdVU2A5idD+Yo2VPayksX5iFC6bDiCnkl3x2LEoOiLK1UEuF7SD1tP4SilqOdM3lcm2a66VpsjOvJ8f69lrQlJvSBZStikoqbAf5f9/6Kz5rfb1ovBycmmWJidmljq2Nu6ajx6AaqqmwFS5ZQVafNhsUHE75PK31Vczs/7Vprpdr23cN5Qf1UE2F7TrLP0wG8p1UWyaaxQSZVWbUeakeA/P7BedQS4XtMAyrgIkW1zBEhuR+55P1L+FqX/b1Y2C1hxKubTfepWvIC86nsgrbSdowM5UcrOyQ1y4XZXVWAidRSb/syNSnNGplB0BD2VsTg8XHdPdBQWJAp8HZ1KNssOxKon41ShVuirLL73sfvW9kzsYcg6YVjyWZPZlKlI1bValliZKVtEVRdqM/Vou7Kjt9hnAUwnIfHk0lyt7K7igL7nGuLLwfKAvOQFlwBsqCM1AWnIGy4IyXUPaWhTNZbNMrFSqbWK8zqVfZdG3FfVscsBzyE6lG2cQU6o1djDY3CItmwGxSEnlNZctuz2DZvVSj7EpyonbslZjDbdwkV65rE0TzUMHgCTdHWcLz/dSjrDldYJyaGo7tB9sciRkAyrXNWWAGWtlU3RfKPpF6KmyX4Br8cZmqYNa9F0WReGoaYvnaGvEZNypbUqgzlUmi7H3UU2GbiLLLLlzXtk+1wqwssnhnGX2jtLIpLXc4iLL3U0+F7cgcWVtj3RZpR2ZCYvIEEoOXoJoK27XFZMzC3hR0vUNGiJ09BvuVTT0gXFYMZe+jkgpb2bEvy1fbeWGhsIsgmHZdquDjo2xq1zCUPZKKKmzv79fcjrIo65/KKmwVRXVVa9fBRgEiyr4E9fTLjoTjCEbgNYZyl8baqmzfR+VaJwwlkMueQfXKbtUWbqxqIJPkm6Os+ejSKBtdyFyc+6hF2dJEdglbalQhEYRveEZ6HCL7eYr/3TD2TmpR9gx2Rlmok3dSFl4ClAVnoCw4A2XBGSgLzkBZcAbKgjNqU7Zk6e74inVmVzjYm1kUsXQtcOv5+WFXOncfSz3Krhtq7FJWCJJTdt/o2rh7WDlqLzxmETySepQdvVyVLdpoKypffHaU3fxqEH/vpRplo8KqcN8Y25BZy7woYmpieksEsyC3CLXBzc3TeaGMaipsJy33JAbawZIoK3YQ2XJcPjW1n0e0jVguiqLsIdRSYTv7MTuwvQhMvJFReWKgvhlavLi6cVvZ9JcguAnK3k0lFbbBSgXFSwxN+3XO7+WUVavGZZQd5A1KaLrlabGU2nuUvZuKKmyHYcgqq2bIxgtZZJXte/nDnUkMZGtOZsHmp4p30skoe9/SdTBRSYXtQsmqK3rH0O1waC75FgfFG1aHK9jTKdGjATdSS4WtFEMkino5uXhVoV1hK8pTZeBLrVp0bVur+WeF3uwHIsgeQ20VtkHbpm/1Lpwbyi5KbvQ8iHg3dTu0urUfdkeozoCusUK3qaz8LgYNTLiRavplJ4yVOvNRMGjcXK5t18vXOtE0VzqK6x5TnyyZaFgfKEFRoTukqV1ZdSihbKSnOEGsyCUb9+L4nNbGuWl4n/TxBcrLHo8DZcVPq506Zn9qZ61nVaaT415d+SzR75uNqvbjNkDY+6hNWYANUBacgbLgDJQFZ6AsOANlwRkoC85AWXBGbcqeU2F7L4cOum6WCUFAPco+tsJWvJkahbKHu3aV5lqC5cbDrm0/9H2XmOOAsib1KPv4Cttg/pWamxVM3Noq4tpa4Dupcfo6S0+UNalG2cdX2Or5qtII69i9e9sYa4ybnyv7X2Hj/HfkjSpsM8pGhlq3zhMFdKvUUbwtJ5ln1ronyka8U4Vt9OMfxEEjiSjcZyR6VFwMmYyacyZruYmyJu9SYSvfjcKioax6a5eyqhAhH2XXfxuaX2W8S4XtoH/s5asjlZXBX7cok8qK+2Qhmx3eqcI2blEt7xQlBhusdQ5r8F+V3RIwkcUQYw3epsI2G0kzOi8vo1WP1j+VcMvHEk3KzNXxDRA2x9tU2Oo+gEFqGR1MdXKpjjF7EQSt7HJdIjmN/IzKMJFXUE2/7MQDK2zDSm51OBpmSPWqBaFRKxsvRRM2sQruGj196lPA2pnalVWH7qiwXV/HqeQwDEFuklhoIwrp9niHtQjNmPD0RoeW9b0z+4Npe404UPaYCttbmZ6eHMqIW2DxCdZI7HzXIKYaLbXU+Nz7UpuyABugLDgDZcEZKAvOQFlwBsqCM1AWnIGy4AyUBWegLDgDZcEZKAvOQFlwBsqCM1AWnIGy4AyUBWegLDgDZcEZKAvOQFlwBsqCM1AWnHGcssnVUACOxFK2aPXBSE61EksbrxXIGj1wABllU4aNa53IVaPa6+XadmGU1UuvE4HhGO5X1lp6tem6rplPixQHuIN7E4Nxqai2WddpVXsRkRPAsRyVGFzGnQvmVVSl2Ru3A9jFEcqK/QHW1TEnceXCfVgLB3CvsuJFFySv6x4GZLFwJId0cpXs80qEhWMoGEqI94QT6MTVvhZv4TDuVTY8pyAiA9yHVrZs360oaqYGCxhEgKMhyoIzDlVW7q+pBm/xFg7iIGVVn4HY2WhJIPoeZeEAjlF2sTPXrUB3ARwBU7zBGSgLzkBZcAbKgjNQFpyBsuAMlAVnoCw4A2XBGSgLzkBZcAbKgjNQFpyBsuAMlAVnoCw4A2XBGSgLzkBZcAbKgjNQFpyBsuCMfcrK5QpYSgOegqFsZulNuRTBvJRG8SpeaneF4F4bS3dtrwbKIglvQ0mULVkuRjD7lbLI8HNrbQ61JHjhIXhJzldWR+WmSwZRvesNysIzlF13TZj3YzRirHoLZWFhXy5blD7mlRX7fEx/znqKLRp07kAuCwtnR1mxbucUZNt5VxChLOvOQZLTE4M1YMqf/Wvbi5949mOENE/oMUhs0jQ3y/RlRlIgBNdvw8tTj7LzZfoxiW3GUruPwaujlS1te2Xi2m3Krg9WN0VZCHhylO27Ltqi2ezvQlmYeKqyXXORO4wH/QfqdigLE09UdjknzGK1figLAYduCLrJOuh1CcYTQsPDB9JjAAFpZUMnDu3W75pwhzDj7utgAlEWApjiDc5AWXAGyoIzUBacgbLgDJQFZ6AsOANlwRkoC85AWXAGyoIzUBacgbLgDJQFZ6AsOANlwRkoC85AWXAGyoIzUBacgbLgDJQFZ6BsAdNqoit9e6UE/VlUpGwkxkzfXq11FMq2wtHbiMyvE1enFsZD2WqoR9m+78Wi9MERW1njFhsnxscDGeXh4q2hjl+YBPLUo+zI6NCGMNe23+XUbFQcx+U7aeGJsjVRi7KhBPGrxaX5xfjPcLvHrr1emla6PG4asmwdEmmsMoX0JjrrtwRVn8xjlf379++vX78+Pz+/vr7+/PmTObPv5TKdGWWHzJsb73XN9Nf63qrsVuCcjy8LjMKTeKCy39/fHx8fMrLlrR2GxYfcr76xLWMuygbnrqvORX/pFUSjzxUkEMTa5/FAZX///q1s+/j4KLt0DGldFPjW8JiUOlRWGBheq/+KnyDeiAVF2+fxQGW/vr5io7JXLD/Okzj6x1r/KPftVe4ZumgorjGSATPIyo8Q9hrYgXo5jLdnc2qU/fz8zF6xKLmIIyWNs00VajeVXe4pkgFjK6dlVdsoQzC6jsXee3AKD1T2379/StmNXDZoFYmOqcWh5ELiKvSaiYE4lGlxhVJu9qTh6vmc0WPw48ePnz9/bra9VoNCb1J7Lxg+TV1RVpQNrglDucSIo1ZKsaxDDqdTS7+sSmR1xrhD2WgThfW6KVKvXbpm30AoYxDdVQcDnV3PoB5lRQYQ9jutEc7+JQ4DaRhkxcG4o8oeGpZX69Pi5hfWnk41yq4R8Nr26VbNLG5nZ5lNo6LkpKxKAqLkIRWT4w6v4O5Y+wSqUfZ2ZJRdckxjQ/J9qA605N1UsgwP5wWUhfcCZcEZKAvOQFlwBsqCM1AWnFGdsjXWqFBIUxO1KfvYSf9U2L4AdSkrVYjmCoQjVDvKFTMFiVTY+qMqZVMrGRwV1KiwfQUqUnbNCazZKfcZQoXt61BLha0qyCoMapnkwfjJpsL2JaimwlbaZ82MLWsXRZeZMwepsPVMZRW2toOHKEuF7YtQVYVtqjzlCGWpsH0V6qmwVS2koubXjlyWCttXoZoK27BqJj52fy5Lhe1rUFGFbZpsa948mKpwocLWPxX1yy5EOeRGB1TkZ6pxRIXtK1CTsup3OzyQj2p6FCvtq3gUFbYuqUZZFfTyDatr2242vIJGWEuF7ctQjbK1QYVtraAsOANlwRkoC85AWXAGyoIzUBacgbIFUEhTE7Uqe4sU+Uky6gwqbL1SlbIFZa1ZURJ74Ab3p8LWO9Upm3NSzyQoxJjVkrojFbb141rZkuCW2seZCluvVFNhOww3JAZU2L4h1VTYDsO+KBtfWd72osLWM1VV2N7S/JoC7TgdMRf+qLB9EaqqsN0RZRe7VRNfJQJm7kuFrWvqqbC9g8WtSakl8pqzsamw9U01FbYFTakZVRV2CbuxjLPU2VTYuqbqCtvccFa82WKQKyR2aabC1j819csWjQ7YqowCW/uJGsktFbauqUnZiO1JA3MmILZXjC014i4Vtn5xq2zgqsyDzStUEkCFrWNqUba6aShU2NZKLcoCFIKy4AyUBWegLDgDZcEZ/wE5ll06RcehyAAAAABJRU5ErkJggg==" alt="" />

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

     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[];

         //将DictTypeID赋给列数组

         PrimaryKeyColumns[] = table.Columns["DictTypeID"];

         table.PrimaryKey = PrimaryKeyColumns;//设置DictTypeID字段为主键

         ds = new DataSet();//创建数据集对象

         ds.Tables.Add(table);//将上面创建的数据表对象添加表数据集中

         //为数据表添加数据

         for (int i = ; i< ; 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 = ; i <= ; i ++)

         {

             row = table.NewRow();

             //row["DictItemID"] = i;//因为该列是自增列所以不用赋值

             row["DictItemName"] = "DictItemName " + i;

             row["DictTypeID"] = ;

             table.Rows.Add(row);

         }

         for(int i = ; i <= ; i ++)

         {

             row = table.NewRow();

             //row["DictItemID"] = i + 5;

             row["DictItemName"] = "DictItemName " + (i+);

             row["DictTypeID"] = ;

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

     }

巴特西

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

最新文章

热门文章