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

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>

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>

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