Learning never exhausts the mind
Home >  Coding > C# ASP.Net MVC > Connecting to MySql Databases with C# / ASP.Net

Published 27th November 2007 by

ADO.Net works best when connecting to Microsoft SQL Server sources, but it can access data from other sources such as MySql. In this tutorial, I will show you how to connect a sample ASP.Net page to MySql data source and utilise the provided data controls.

ADO Connection to MySQL

MySql connections can be established using the free software MySql Connector Net. Once installed this product provides the .Net Framework classes to interact with a MySql Server.

You can use the standard ADO.Net data components; however you will have to override the default functions such as Sort. The code provided is designed for an ASP page, but it can easily be converted to Windows Forms. The code will need to be changed to connect to your database and tables. The grid view will need changing to match columns of the table, as will the sort expressions.

ASP.Net Webpage (.aspx)

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>MySql Test</title>
    <form id="form1" runat="server">
        <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" Style="position: relative" AllowSorting="True" GridLines="Vertical" CaptionAlign="Left" AutoGenerateColumns="False" OnSorting="GridView1_Sorting" EmptyDataText="<strong>There are no records to show.</strong>">
          <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
          <RowStyle ForeColor="Black" BackColor="#EEEEEE" />
          <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
          <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
          <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
          <AlternatingRowStyle BackColor="Gainsboro" />
                    <asp:Button ID="btnDelete" CommandName="Delete" runat="server" Text="Delete" CssClass="okbutton" OnClientClick="return confirm('Are you sure you wish to delete this item?');" />
            <asp:boundfield datafield="productID" readonly="True" headertext="ID" SortExpression="productID"/>
            <asp:boundfield datafield="productName" readonly="True" headertext="Style" SortExpression="productName"/>
            <asp:hyperlinkfield datatextfield="URL" datatextformatstring="view" datanavigateurlfields="URL" datanavigateurlformatstring="{0}" headertext="URL" target="_blank" />

Code Behind (.cs)

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
    private MySqlConnection conn;
    private DataTable data;
    private MySqlDataAdapter da;
    private MySqlCommandBuilder cb;
    private string SortString = "";
    private string SortOrder;
    private string SortExpression;
    protected void Page_Load(object sender, EventArgs e)
        SortOrder = (string)Session["SortOrder"];
        SortExpression = (string)Session["SortExpression"];

        if (SortOrder == null)
            SortOrder = "ASC";

        if (SortExpression == null)
            SortExpression = "productID";
        if (!IsPostBack)
            QueryServer("SELECT * FROM database.table ORDER BY " + SortExpression + " " + SortOrder);

    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
        SortString = e.SortExpression;

        QueryServer("SELECT * FROM database.table ORDER BY " + SortString + " " + SortOrder);

        if (SortOrder == "ASC")
          SortOrder = "DESC";
          SortOrder = "ASC";

        Session["SortOrder"] = SortOrder;
        Session["SortExpression"] = e.SortExpression;

    protected void QueryServer(string cmd)

        if (cmd == "")
            cmd = "SELECT * FROM database.table";

        string connStr = String.Format("server={0};user id={1}; password={2}; database=mysql; pooling=false", "localhost", "username", "password");
            conn = new MySqlConnection(connStr);

            MySqlDataReader reader = null;

                da = new MySqlDataAdapter(cmd, conn);
                data = new DataTable();

                GridView1.DataSource = data;
                GridView1.AllowSorting = true;
            catch (Exception ex)
                throw new Exception("Failed to populate database list: " + ex.Message);
                if (reader != null) reader.Close();

        catch (MySqlException ex)
            throw new Exception("Error connecting to the server: " + ex.Message);

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
        int index = e.RowIndex;
        string id = GridView1.Rows[index].Cells[1].Text;

        string command = "DELETE FROM database.table WHERE logID = " + id + " LIMIT 1;";
        command += "SELECT * FROM database.table ORDER BY " + SortExpression + " " + SortOrder;


Leave a Reply

Fields marked with * are mandatory.

We respect your privacy, and will not make your email public. Hashed email address may be checked against Gravatar service to retrieve avatars. This site uses Akismet to reduce spam. Learn how your comment data is processed.