ASP.NET Tutorial/ADO.net Database/OleCommand

Материал из .Net Framework эксперт
Перейти к: навигация, поиск

OleCommand and OleParameter

   <source lang="csharp">

<%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.OleDb" %> <script runat="server">

   void Page_Load(object sender, EventArgs e) {
       if (!Page.IsPostBack)
       {
           OleDbConnection myConnection = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="+ Server.MapPath("EmployeeDatabase.mdb;"));
   
           try{
               string CommandTextPublisher = "SELECT ID, FirstName FROM Employee";
               OleDbCommand myCommandPublishers = new OleDbCommand(CommandTextPublisher, myConnection);
   
               myConnection.Open();
   
               DropDownList1.DataSource = myCommandPublishers.ExecuteReader();
               DropDownList1.DataTextField = "FirstName";
               DropDownList1.DataValueField = "ID";
               DropDownList1.DataBind();
               DropDownList1.Items.Insert(0, new ListItem("-- All Publishers --", "0"));
           }
           catch (Exception ex){
               throw(ex);
           }
           finally{
               myConnection.Close();
           }
       }
   }
   
   void ApplyFilter_Click(Object sender, EventArgs e) {
       OleDbConnection myConnection = new OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="+ Server.MapPath("EmployeeDatabase.mdb;"));
   
       try{
           string CommandTextBooks = "SELECT Book.BookTitle, Publisher.PublisherName FROM Book INNER JOIN Publisher on Book.BookPublisherID = Publisher.PublisherID WHERE ?=0 OR Book.BookPublisherID=? ORDER BY Book.BookTitle";
           OleDbCommand myCommandBooks = new OleDbCommand(CommandTextBooks, myConnection);
   
           OleDbParameter myParameter1 = new OleDbParameter();
           myParameter1.ParameterName = "@pub1";
           myParameter1.OleDbType = OleDbType.Integer;
           myParameter1.Value = DropDownList1.SelectedValue;
           myCommandBooks.Parameters.Add (myParameter1);
           OleDbParameter myParameter2 = new OleDbParameter();
           myParameter2.ParameterName = "@pub2";
           myParameter2.OleDbType = OleDbType.Integer;
           myParameter2.Value = DropDownList1.SelectedValue;
           myCommandBooks.Parameters.Add (myParameter2);
   
           myConnection.Open();
   
           DataGrid1.DataSource = myCommandBooks.ExecuteReader();
           DataGrid1.DataBind();
       }
       catch (Exception ex){
           throw(ex);
       }
       finally{
           myConnection.Close();
       }
   }

</script> <html> <head> </head> <body>

   <form runat="server">
       
           Select a Publisher: 
           <asp:DropDownList id="DropDownList1" runat="server"></asp:DropDownList>
             
           <asp:Button id="Button1" onclick="ApplyFilter_Click" runat="server" Text="Show Titles"></asp:Button>
       
       <asp:datagrid id="DataGrid1" runat="server" EnableViewState="False">
           <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle>
           <ItemStyle backcolor="#DEDFDE"></ItemStyle>
       </asp:datagrid>
   </form>

</body> </html></source>