How to Connect to MySQL Database with ADO.Net and C#Connecting to MySQL with ADO.Net objects using "MySQL Connector Net" to provide native database functionality within .Net applications.
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.
Connecting to MySQL via ADO.Net
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 the columns of the table, as will the sort expressions.
ASP.Net Webpage (.aspx)
xml
<%@ 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>
</head>
<body>
<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" />
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Button ID="btnDelete" CommandName="Delete" runat="server" Text="Delete" CssClass="okbutton" OnClientClick="return confirm('Are you sure you wish to delete this item?');" />
</ItemTemplate>
</asp:TemplateField>
<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" />
</Columns>
</asp:GridView>
</form>
</body>
</html> Code Behind (.cs) for Connecting to MySQL Database
C#
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";
else
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");
try
{
conn = new MySqlConnection(connStr);
conn.Open();
MySqlDataReader reader = null;
try
{
da = new MySqlDataAdapter(cmd, conn);
data = new DataTable();
da.Fill(data);
GridView1.DataSource = data;
GridView1.AllowSorting = true;
GridView1.DataBind();
}
catch (Exception ex)
{
throw new Exception("Failed to populate database list: " + ex.Message);
}
finally
{
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;
QueryServer(command);
}
} 
