ASP.NET Tutorial/ADO.net Database/MySQL
Содержание
- 1 Execuate insert command against MySQL database
- 2 Execuate select statement against MySQL database with OdbcCommand
- 3 Execute Delete command against MySQL database with OdbcCommand (C#)
- 4 Execute update command against MySQL database with OdbcCommand
- 5 Read scalar data from MySQL database by using OdbcCommand
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>