How to access a MySQL database with .NET

An often overlooked combination of scripting technologies is ASP.NET with MySQL (instead of Access [slow] or SQL Server [expensive]). MySQL is a fast and popular database and you can find numerous ISPs which offer it with ASP.NET (e.g. www.discountasp.net).

If you are installing it on your own server, you need to download MySQL and install it, then run c:\mysql\bin\winmysqladmin.exe. You also need to download the ODBC driver (choose Windows Downloads Driver Installer) and install it. Then use this code to access your MySQL database.

<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.Odbc" %>
<%@ Page Language="C#" %>
<script runat="server">
public void Page_Load(Object sender, EventArgs e) {
    DataTable dtRecords = GetDataTable("SELECT * FROM newone");
    foreach(DataRow dr in dtRecords.Rows) {
        Response.Write(dr["FirstName"].ToString() + " " + dr["LastName"].ToString() + "<br/>");
    }
}

private static string GetConnection() {
    return "DRIVER={MySQL ODBC 3.51 Driver};Server=localhost;Database=testdatabase";
}
public static DataTable GetDataTable(string sql) {
    DataTable rt = new DataTable();
    DataSet ds = new DataSet();
    OdbcDataAdapter da = new OdbcDataAdapter();
    OdbcConnection con = new OdbcConnection(GetConnection());
    OdbcCommand cmd = new OdbcCommand(sql, con);
    da.SelectCommand = cmd;
    da.Fill(ds);
    try {
        rt = ds.Tables[0];
    }
    catch {
        rt = null;
    }
    return rt;
}

</script>

You might also like...

Comments

Edward Tanguay Edward Tanguay updates his personal web site tanguay.info weekly with code, links, quotes and thoughts on web development. Sign up for the free newsletter.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The most exciting phrase to hear in science, the one that heralds new discoveries, is not 'Eureka!' but 'That's funny...'” - Isaac Asimov