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