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();//将运行结果输出到页面中 }
截图如下:
动态添加主子表, 且动态创建主子表的关系:
1 protected void Page_Load(object sender, EventArgs e) 2 { 3 MakeMasterTable(); //创建主表 4 MakeDetailTable(); //创建子表 5 MakeDataRelation(); //建立主子表关联 6 } 7 8 private DataSet ds; 9 10 private void MakeMasterTable() 11 { 12 //创建数据表对象 13 DataTable table = new DataTable("MasterTable"); 14 DataColumn column; 15 DataRow row; 16 //创建数据列对象 17 column = new DataColumn(); 18 //设置数据列的数据类型 19 column.DataType = System.Type.GetType("System.Int32"); 20 column.ColumnName = "DictTypeID";//指定数据列名 21 column.ReadOnly = true; //设置数据列为只读列 22 column.Unique = true; //设置数据列中的值不允许重复 23 table.Columns.Add(column); 24 25 column = new DataColumn(); 26 column.DataType = System.Type.GetType("System.String"); 27 column.ColumnName = "DictTypeName"; 28 column.AutoIncrement = false; //设置数据列中的值不自动递增 29 column.Caption = "DictTypeName"; //设置数据列标题 30 column.ReadOnly = false; 31 column.Unique = false; 32 table.Columns.Add(column); 33 //创建列数组 34 DataColumn[] PrimaryKeyColumns = new DataColumn[1]; 35 //将DictTypeID赋给列数组 36 PrimaryKeyColumns[0] = table.Columns["DictTypeID"]; 37 table.PrimaryKey = PrimaryKeyColumns;//设置DictTypeID字段为主键 38 39 ds = new DataSet();//创建数据集对象 40 ds.Tables.Add(table);//将上面创建的数据表对象添加表数据集中 41 //为数据表添加数据 42 for (int i = 0; i< 2; i++) 43 { 44 row = table.NewRow(); 45 row["DictTypeID"] = i; 46 row["DictTypeName"] = "DictTypeName " + i; 47 table.Rows.Add(row); 48 } 49 GridView1.DataSource = ds; 50 GridView1.DataBind(); 51 } 52 53 private void MakeDetailTable() 54 { 55 DataTable table = new DataTable("DetailTable"); 56 DataColumn column; 57 DataRow row; 58 59 column = new DataColumn(); 60 column.DataType= System.Type.GetType("System.Int32"); 61 column.ColumnName = "DictItemID"; 62 column.AutoIncrement = true;//设置该数据列是自增列 63 column.Caption = "DictItemID"; 64 column.ReadOnly = true; 65 column.Unique = true; 66 67 table.Columns.Add(column); 68 69 column = new DataColumn(); 70 column.DataType= System.Type.GetType("System.String"); 71 column.ColumnName = "DictItemName"; 72 column.AutoIncrement = false; 73 column.Caption = "DictItemName"; 74 column.ReadOnly = false; 75 column.Unique = false; 76 table.Columns.Add(column); 77 78 column = new DataColumn(); 79 column.DataType= System.Type.GetType("System.Int32"); 80 column.ColumnName = "DictTypeID"; 81 column.AutoIncrement = false; 82 column.Caption = "DictTypeID"; 83 column.ReadOnly = false; 84 column.Unique = false; 85 table.Columns.Add(column); 86 87 ds.Tables.Add(table); 88 89 for(int i = 0; i <= 4; i ++) 90 { 91 row = table.NewRow(); 92 //row["DictItemID"] = i;//因为该列是自增列所以不用赋值 93 row["DictItemName"] = "DictItemName " + i; 94 row["DictTypeID"] = 0; 95 table.Rows.Add(row); 96 } 97 for(int i = 0; i <= 4; i ++) 98 { 99 row = table.NewRow(); 100 //row["DictItemID"] = i + 5; 101 row["DictItemName"] = "DictItemName " + (i+5); 102 row["DictTypeID"] = 1; 103 table.Rows.Add(row); 104 } 105 } 106 107 private void MakeDataRelation() 108 { 109 DataColumn masterColumn = 110 ds.Tables["MasterTable"].Columns["DictTypeID"]; 111 DataColumn detailColumn = 112 ds.Tables["DetailTable"].Columns["DictTypeID"]; 113 //使用DataRelation对象建立主子表的关联关系 114 DataRelation relation = new 115 DataRelation("MasterDetail", masterColumn, detailColumn); 116 ds.Tables["DetailTable"].ParentRelations.Add(relation); 117 }