ado.net 学习 05 DataSet [填充DataSet，使用多个表和关系]

填充DataSet: 

 1. DataSet ds = new DataSet();

     da.Fill(ds,"Employees");

 1 protected void Page_Load(object sender, EventArgs e)
 2     {
 3         string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
 4         SqlConnection con = new SqlConnection(connectionString);
 5 
 6         string sql = "select * from Employees";
 7 
 8         SqlDataAdapter da = new SqlDataAdapter(sql,con);
 9 
10         DataSet ds = new DataSet();
11         da.Fill(ds,"Employees");
12 
13 
14         StringBuilder htmlStr = new StringBuilder("");
15         foreach (DataRow dr in ds.Tables["Employees"].Rows)
16         {
17             htmlStr.Append("<li>");
18             htmlStr.Append(dr["TitleofCourtesy"].ToString());
19             htmlStr.Append("<b>");
20             htmlStr.Append(dr["LastName"].ToString());
21             htmlStr.Append("</b>,");
22             htmlStr.Append("</li>");
23         }
24         HtmlContent.Text=htmlStr.ToString();
25     }

运行结果：

 

 

 

使用多个表和关系：

1. da.Fill(ds,"Categories");

2. da.SelectCommand.CommandText=sqlProd;

    da.Fill(ds,"Products");

3. DataRelation relat= new ("CatProds",

　　　　　　　　　　　　　　ds.Tables["Categories"].Columns["CategoryID"],

                                       ds.Tables["Products"].Columns["CategoryID"]);

    ds.Relations.Add(relat);

 

 1 public partial class DataSetRelationships : System.Web.UI.Page
 2 {
 3     protected void Page_Load(object sender, EventArgs e)
 4     {
 5         string connectionString =WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
 6         SqlConnection con = new SqlConnection(connectionString);
 7 
 8         string sqlCat = "select categoryID,CategoryName from categories";
 9         string sqlProd = "select ProductName,CategoryID from products";
10 
11         SqlDataAdapter da= new SqlDataAdapter(sqlCat,con);
12         DataSet ds=new DataSet();
13 
14         try
15         {
16             con.Open();
17 
18             //用Categories填充DataSet。
19             da.Fill(ds,"Categories");
20 
21             //改变命令文本并获取Products表。
22             da.SelectCommand.CommandText = sqlProd;
23             da.Fill(ds,"Products");
24         }
25         finally
26         {
27             con.Close();
28         }
29         //定义Categories与Products之间的关系；
30         DataRelation relat = new DataRelation("CatProds",ds.Tables["Categories"].Columns["CategoryID"],
31             ds.Tables["Products"].Columns["CategoryID"]);
32 
33         //将关系添加到DataSet
34         ds.Relations.Add(relat);
35 
36         StringBuilder htmlStr = new StringBuilder("");
37 
38         foreach (DataRow row in ds.Tables["Categories"].Rows)
39         {
40             htmlStr.Append("<b>");
41             htmlStr.Append(row["CategoryName"].ToString());
42             htmlStr.Append("</b><ul>");
43 
44             DataRow[] childRows = row.GetChildRows(relat);
45 
46             foreach(DataRow childRow in childRows)
47             {
48                 htmlStr.Append("<li>");
49                 htmlStr.Append(childRow["ProductName"].ToString());
50                 htmlStr.Append("</li>");
51             }
52 
53             htmlStr.Append("</ul>");
54         }
55 
56         HtmlContent.Text = htmlStr.ToString();
57     }
58 }

 

    <div>
        <asp:Literal ID="HtmlContent" runat="server"></asp:Literal>
    </div>

运行结果：

 

 

posted @ 2013-05-28 22:09  罗汉果  阅读(213)  评论(0)  编辑  收藏  举报