ado.net 学习 03(SqlDataReader,GridView.DataSource,内连接查询显示)
1. 调用配置文件中的连接字符串;
2. 查询语句,在程序中的应用。查询输入框,通过在SQL脚本中间入插TextBox1.text实现;
3. 创建连接对象:SqlConnection con= new SqlConnection(connectionString);
4. 创建SqlCommand
5. 执行查询语句,并将结果放在reader中; [C#:SqlDataReader reader = cmd.ExecuteReader();]
6. 将Gridview绑定到数据
后台代码:
1 protected void Button1_Click(object sender, EventArgs e) 2 { 3 //调用配置文件中的连接字符串 4 string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString; 5 //查询语句,查询输入框对应 语句中的 "+TextBox1.Text +" 6 string sql = "SELECT o.CustomerID ,o.OrderID,COUNT(od.OrderID),sum(od.UnitPrice*od.Quantity)"+ 7 "FROM dbo.Orders o INNER JOIN dbo.[Order Details] od "+ 8 "ON o.OrderID =od.OrderID WHERE o.CustomerID='" +TextBox1.Text + 9 "' GROUP BY o.OrderID,o.CustomerID"; 10 SqlConnection con= new SqlConnection(connectionString); 11 SqlCommand cmd = new SqlCommand(sql,con); 12 try 13 { 14 con.Open(); 15 SqlDataReader reader = cmd.ExecuteReader(); 16 GridView1.DataSource = reader; 17 GridView1.DataBind(); 18 reader.Close(); 19 } 20 catch (SqlException exc) 21 { 22 Label1.Text += string.Format("错误:", exc.Message); 23 } 24 finally 25 { 26 con.Close(); 27 }
前台代码:
1 <div> 2 <asp:Label ID="Label1" runat="server" Text="Enter Customer ID:"></asp:Label><br /> 3 <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <asp:Button ID="Button1" 4 runat="server" Text="Button" onclick="Button1_Click" /><br /> 5 <asp:GridView ID="GridView1" runat="server"> 6 </asp:GridView> 7 </div>
运行结果:
2013-05-22 22:00:55