ado.net 学习 09 [DataView的,基于关系来过滤数据行]
1. view1.RowFilter = "Max(Child(CatProds).UnitPrice)>50";
DataView支持一些复杂的过滤表达式,其中包括基于关系来过滤行。例如可以显示“Categories(种类)” 的从表“Products”中全部行,其中UnitPrice最大值超过50的Categories记录。
2. GridView1.DataSource与GridView1.DataBind(),一个都不能少。先指定数据源,再绑定。之前调试过程,没加GridView1.DataBind(),页面数据总是出不来。
GridView1.DataSource = view1;
GridView1.DataBind();
1 protected void Page_Load(object sender, EventArgs e) 2 { 3 //没调通。Web中的数据出不来。 4 // Create the Connection, DataAdapter, and DataSet. 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 CategoryID,UnitPrice FROM Products"; 10 11 SqlDataAdapter da = new SqlDataAdapter(sqlCat, con); 12 DataSet ds = new DataSet(); 13 14 15 con.Open(); 16 17 // Fill the DataSet with the Categories table. 18 da.Fill(ds, "Categories"); 19 20 // Change the command text and retrieve the Products table. 21 // You could also use another DataAdapter object for this task. 22 da.SelectCommand.CommandText = sqlProd; 23 da.Fill(ds, "Products"); 24 25 26 // Define the relationship between Categories and Products. 27 DataRelation relat = new DataRelation("CatProds", 28 ds.Tables["Categories"].Columns["CategoryID"], 29 ds.Tables["Products"].Columns["CategoryID"]); 30 // Add the relationship to the DataSet. 31 ds.Relations.Add(relat); 32 33 DataView view1 = new DataView(ds.Tables["Categories"]); 34 view1.RowFilter = "Max(Child(CatProds).UnitPrice)>50"; 35 36 GridView1.DataSource = view1; 37 GridView1.DataBind(); 38 }
前台代码:
1 <div> 2 <asp:GridView ID="GridView1" runat="server"> 3 </asp:GridView> 4 </div>
运行结果:
对比数据库中SQL查询结果: