ASP .NET - Configure SQLDataSource to use MySQL .NET Connector

Question

I am using VS 2008.

Looking around on the forums/stack hasn't provided a clear answer on how to use MySQL with a ASP .NET site.

How do I configure a SQLDataSource to use MySQL by using the MySQL Connector NET provider?

I would prefer not to use the ODBC driver - which I can get working. The connector has been added as a reference to the project and appears in the web.config as:

<add assembly="MySql.Data, Version=5.2.2.0, Culture=neutral, PublicKeyToken=C5687FC88969C44D"/>

And I also attempted to manually create a section under :

<add name="MYSQL" connectionString="Server=localhost;Database=data;Uid=root;Pwd=1234;" providerName="MySql.Data" />

The MySQL Connector version that I have is 5.2.2.0

Answer 1

In web.conf file, you add following lines:

<connectionStrings>
    <add name="connMysql"
         connectionString ="Server=localhost;Database=transcode;Uid=root;"
         providerName="MySql.Data.MySqlClient"/>
  </connectionStrings>

in .aspx file you can add following lines:

<asp:SqlDataSource ID="Datacmd" runat="server" ConnectionString="<%$ConnectionStrings:connMysql %>"
            ProviderName="<%$ ConnectionStrings:connMysql.providerName%>" SelectCommand="SELECT command.id,server.ip,command.name,command.cmd,command.input_ip,command.output_ip,command.pid,command.status FROM server,command WHERE command.server_id=server.id "
        </asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" DataSourceID="Datacmd"
            AutoGenerateColumns="False" ForeColor="#003300">
            <Columns>
.............................
             </Columns>
        </asp:GridView>

Answer 2

The only way I could get the design time binding to work was to use the ODBC driver. the .Net driver simply would not work. You have to install the MySQL ODBC driver, restart visual studio, then add connection string to web.config.

In Web.Config:

<connectionStrings>
<add name="ConnectionStringODBC" connectionString="Driver={MySQL ODBC 5.3 ANSI Driver};server=localhost;database=myproviewer;uid=myproviewer;password=mypassword" providerName="System.Data.Odbc" />
</connectionStrings>

In ASPX:

<dx:ASPxGridView ID="gridTagGroups" runat="server" DataMember="DefaultView" Theme="Office2010Blue" Width="95%" Font-Size="8pt" AutoGenerateColumns="False" DataSourceID="SqlDataSourceTagGroup">
    <Settings ShowHeaderFilterButton="true" ShowFilterRow="true" ShowFilterRowMenu="true" ShowGroupPanel="true" />
    <SettingsPager PageSize="20" Position="TopAndBottom" AlwaysShowPager="True">
        <PageSizeItemSettings Caption="Items Per Page:" Items="10, 15, 20, 25, 50" Visible="True" Position="Right">
        </PageSizeItemSettings>
    </SettingsPager>
    <SettingsDataSecurity AllowDelete="False" AllowEdit="False" AllowInsert="False" />
    <Columns>
        <dx:GridViewDataTextColumn FieldName="TagCatId" VisibleIndex="0">
            <EditFormSettings Visible="False" />
        </dx:GridViewDataTextColumn>
        <dx:GridViewDataTextColumn FieldName="TagCatName" VisibleIndex="1">
        </dx:GridViewDataTextColumn>
        <dx:GridViewDataTextColumn FieldName="OrderIndex" VisibleIndex="2">
        </dx:GridViewDataTextColumn>
        <dx:GridViewDataTextColumn FieldName="HiddenFromSearch" VisibleIndex="3">
        </dx:GridViewDataTextColumn>
        <dx:GridViewDataTextColumn FieldName="MediaType" VisibleIndex="4">
        </dx:GridViewDataTextColumn>
    </Columns>
    <SettingsBehavior AllowFocusedRow="True" AllowSelectSingleRowOnly="True" />
</dx:ASPxGridView>
<asp:SqlDataSource ID="SqlDataSourceTagGroup" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringODBC %>" ProviderName="<%$ ConnectionStrings:ConnectionStringODBC.ProviderName %>" 
    SelectCommand="SELECT * FROM tagcat ORDER BY OrderIndex" 
    DeleteCommand="DELETE FROM tagcat WHERE TagCatId = ?" 
    InsertCommand="INSERT INTO tagcat (TagCatId, TagCatName, OrderIndex, HiddenFromSearch, MediaType) VALUES (?, ?, ?, ?, ?)" 
    UpdateCommand="UPDATE tagcat SET TagCatName = ?, OrderIndex = ?, HiddenFromSearch = ?, MediaType = ? WHERE TagCatId = ?">
    <DeleteParameters>
        <asp:Parameter Name="TagCatId" Type="Int32" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="TagCatId" Type="Int32" />
        <asp:Parameter Name="TagCatName" Type="String" />
        <asp:Parameter Name="OrderIndex" Type="Int32" />
        <asp:Parameter Name="HiddenFromSearch" Type="Object" />
        <asp:Parameter Name="MediaType" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="TagCatName" Type="String" />
        <asp:Parameter Name="OrderIndex" Type="Int32" />
        <asp:Parameter Name="HiddenFromSearch" Type="Object" />
        <asp:Parameter Name="MediaType" Type="String" />
        <asp:Parameter Name="TagCatId" Type="Int32" />
    </UpdateParameters>
</asp:SqlDataSource>

Answer 3

The C# version of creating a basic MySQL connection is:

<%@ Page Language="C#" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="C#" runat="server">

private void Page_Load(Object sender ,EventArgs e)
{   
    MySqlConnection myConnection = new MySqlConnection();
    MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();
    DataSet myDataSet = new DataSet();
    string strSQL;
    int iRecordCount;

    myConnection = new MySqlConnection("server=localhost; user id=root; password=ii33uuqwerty; database=wlc_data; pooling=false;");

    strSQL = "SELECT * FROM troutetracking LIMIT 100;";

    myDataAdapter = new MySqlDataAdapter(strSQL, myConnection);
    myDataSet = new DataSet();
    myDataAdapter.Fill(myDataSet, "mytable");

    MySQLDataGrid.DataSource = myDataSet;
    MySQLDataGrid.DataBind();

}

Simple MySQL Database Query