ASP.NET Tutorial/ADO.net Database/MySQL

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

Execuate insert command against MySQL database

<%@ 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>


Execuate select statement against MySQL database with OdbcCommand

<%@ 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>


Execute Delete command against MySQL database with OdbcCommand (C#)

<%@ 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>
        <!-- Insert content here -->
    </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>


Execute update command against MySQL database with OdbcCommand

<%@ 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>


Read scalar data from MySQL database by using OdbcCommand

<%@ 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>
Источник — «http://nfex.ru/index.php?title=ASP.NET_Tutorial/ADO.net_Database/MySQL&oldid=2682»