ASP.NET Tutorial/ADO.net Database/OdbcCommand — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 18:30, 26 мая 2010
Create OdbcCommand and execute it (C#)
<source lang="csharp">
<%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.Odbc" %> <script runat="server">
void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { string ConnectionString = ConfigurationSettings.AppSettings["MySQLConnectString"]; OdbcConnection myConnection = new OdbcConnection(ConnectionString); try{ string CommandTextPublisher = "SELECT PublisherID, PublisherName FROM Publisher"; OdbcCommand myCommandPublishers = new OdbcCommand(CommandTextPublisher, myConnection); myConnection.Open(); DropDownList1.DataSource = myCommandPublishers.ExecuteReader(); DropDownList1.DataTextField = "PublisherName"; DropDownList1.DataValueField = "PublisherID"; 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) { string ConnectionString = ConfigurationSettings.AppSettings["MySQLConnectString"]; OdbcConnection myConnection = new OdbcConnection(ConnectionString); try{ string CommandTextBooks = "SELECT Book.BookTitle, Publisher.PublisherName FROM Book INNER JOIN Publisher on Book.BookPublisherID = Publisher.PublisherID"; string filterValue = DropDownList1.SelectedValue; if (filterValue != "0") CommandTextBooks += " WHERE Book.BookPublisherID = " + filterValue; CommandTextBooks+=" ORDER BY Book.BookTitle"; OdbcCommand myCommandBooks = new OdbcCommand(CommandTextBooks, myConnection); 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" CellSpacing="1" GridLines="None" CellPadding="3" BackColor="White" ForeColor="Black" EnableViewState="False"> <HeaderStyle font-bold="True" forecolor="white" backcolor="#4A3C8C"></HeaderStyle> <ItemStyle backcolor="#DEDFDE"></ItemStyle> </asp:datagrid> </form>
</body> </html> File: Web.config <configuration>
<appSettings> <add key="MySQLConnectString" value="driver={MySQL ODBC 3.51 Driver};server=localhost;database=Books;uid=YourID;pwd=letmein;" /> </appSettings>
</configuration></source>
OdbcCommand and OdbcParameter
<source lang="csharp">
<%@ Page Language="C#" %> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.Odbc" %> <script runat="server">
void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { OdbcConnection myConnection = new OdbcConnection(@"driver={MySQL ODBC 3.51 Driver};server=localhost;database=books;uid=user;pwd=letmein;"); try{ string CommandTextPublisher = "SELECT PublisherID, PublisherName FROM Publisher"; OdbcCommand myCommandPublishers = new OdbcCommand(CommandTextPublisher, myConnection); myConnection.Open(); DropDownList1.DataSource = myCommandPublishers.ExecuteReader(); DropDownList1.DataTextField = "PublisherName"; DropDownList1.DataValueField = "PublisherID"; 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) { OdbcConnection myConnection = new OdbcConnection(@"driver={MySQL ODBC 3.51 Driver};server=localhost;database=books;uid=user;pwd=letmein;"); 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"; OdbcCommand myCommandBooks = new OdbcCommand(CommandTextBooks, myConnection); OdbcParameter myParameter1 = new OdbcParameter(); myParameter1.ParameterName = "@pub1"; myParameter1.OdbcType = OdbcType.Int; myParameter1.Value = DropDownList1.SelectedValue; myCommandBooks.Parameters.Add (myParameter1); OdbcParameter myParameter2 = new OdbcParameter(); myParameter2.ParameterName = "@pub2"; myParameter2.OdbcType = OdbcType.Int; 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>