ASP.NET Tutorial/ADO.net Database/MySQL

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

Execuate insert command against MySQL database

   <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) {
       string ConnectionString = ConfigurationSettings.AppSettings["MySQLConnectString"];
       OdbcConnection myConnection = new OdbcConnection(ConnectionString);
   
       try{
           string CommandText = "INSERT Publisher (PublisherName, PublisherCity, PublisherContact_Email, PublisherWebsite) VALUES ("New Publisher", "a", "a@a.ru", "http://www.a.ru")";
           OdbcCommand myCommand = new OdbcCommand(CommandText, myConnection);
   
           myConnection.Open();
   
           lblRecords.Text = Convert.ToString(myCommand.ExecuteNonQuery());
       } catch (Exception ex){
           throw(ex);
       } finally{
           myConnection.Close();
       }
   }

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

   <form runat="server">
       Records affected: <asp:Label id="lblRecords" runat="server"></asp:Label>
   </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>


Execuate select statement against MySQL database with OdbcCommand

   <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) {
       string ConnectionString = ConfigurationSettings.AppSettings["MySQLConnectString"];
       string CommandText = "select PublisherID, PublisherName, PublisherCity, PublisherWebsite FROM Publisher ORDER BY PublisherID";
   
       OdbcConnection myConnection = new OdbcConnection(ConnectionString);
       OdbcCommand myCommand = new OdbcCommand(CommandText, myConnection);
   
       myConnection.Open();
   
       DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
       DataGrid1.DataBind();
   }

</script> <html> <head> </head> <body style="FONT-FAMILY: arial">

   <form runat="server">
       <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> 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>


Execute Delete command against MySQL database with OdbcCommand (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) {
       string ConnectionString = ConfigurationSettings.AppSettings["MySQLConnectString"];
       OdbcConnection myConnection = new OdbcConnection(ConnectionString);
   
       try{
           string CommandText = "DELETE FROM Publisher WHERE PublisherID = 6";
           OdbcCommand myCommand = new OdbcCommand(CommandText, myConnection);
   
           myConnection.Open();
   
           lblRecords.Text = Convert.ToString(myCommand.ExecuteNonQuery());
       } catch (Exception ex){
           throw(ex);
       } finally{
           myConnection.Close();
       }
   }

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

   <form runat="server">
       Records affected: <asp:Label id="lblRecords" runat="server"></asp:Label>
       
   </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>


Execute update command against MySQL database with OdbcCommand

   <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) {
       string ConnectionString = ConfigurationSettings.AppSettings["MySQLConnectString"];
       OdbcConnection myConnection = new OdbcConnection(ConnectionString);
   
       try{
           string CommandText = "UPDATE Publisher Set PublisherName="Old Publisher", PublisherCity = "Manchester" WHERE PublisherID = 6";
           OdbcCommand myCommand = new OdbcCommand(CommandText, myConnection);
   
           myConnection.Open();
   
           lblRecords.Text = Convert.ToString(myCommand.ExecuteNonQuery());
       } catch (Exception ex){
           throw(ex);
       } finally{
           myConnection.Close();
       }
   }

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

   <form runat="server">Records affected: <asp:Label id="lblRecords" runat="server"></asp:Label>
   </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>


Read scalar data from MySQL database by using OdbcCommand

   <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) {
       string ConnectionString = ConfigurationSettings.AppSettings["MySQLConnectString"];
       string CommandText = "select PublisherID, PublisherName, PublisherCity, PublisherWebsite FROM Publisher ORDER BY PublisherID";
   
       OdbcConnection myConnection = new OdbcConnection(ConnectionString);
       OdbcCommand myCommand = new OdbcCommand(CommandText, myConnection);
   
       myConnection.Open();
   
       string CommandTextCount = "SELECT COUNT(*) FROM Publisher";
       OdbcCommand myCommandCount = new OdbcCommand(CommandTextCount, myConnection);
   
       lblTotal.Text = Convert.ToString(myCommandCount.ExecuteScalar());
   
       DataGrid1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
       DataGrid1.DataBind();
   }

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

       Publishers in the database: <asp:Label id="lblTotal" runat="server"></asp:Label>
   
   <form runat="server">
       <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> 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>