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

MySQl and ASP.NET tutorial

maybe this will help?

Answer 2

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