Connect to MySQL database from ASP.NET

Connecting from ASP.NET to MySQL database is relatively easy. MySQL is a database server which is very popular and frequently used for web application backend. Most people with Windows background will wonder why you would use MySQL instead of MS SQL Server when using ASP.NET. The answer is that MySQL can do the same job as MS SQL Server for small to medium traffic website, but just for fraction of the cost or even for free.

I've explained below how to connect to MySQL from ASP.NET. We will connect to our MySQL database from ASP.NET using ODBC .NET data provider and DSN-less connection. You need to do the following before attempting to connect from ASP.NET to MySQL database:

1. Make sure you have installed the .Net Framework on your server

2. Download the ODBC .Net data provider and install it on your development machine or your server. You can download it here

3. Install MySQL ODBC Driver-MyODBC 3.51. You can download it here

The ASP.NET code below connects to a MySQL database and displays all the records from Users table.

<%@ Page CompilerOptions='/R:"C:\Program Files\Microsoft.NET\ Odbc.Net\Microsoft.data.odbc.dll"'%>

<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="Microsoft.Data.Odbc" %>

< HTML >

< HEAD >

< SCRIPT Language="VBScript" Runat="server" >

Sub Page_Load(Source as object, e as EventArgs)

Dim sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; DATABASE=; UID=;PASSWORD=< Put your MySQL password here >; OPTION=3"

Dim oConnection as ODBCConnection = new ODBCConnection(sConnection)

Dim sSQL as String = "SELECT FirstName, LastName FROM Users"

Dim oDataAdapter as ODBCDataAdapter = New ODBCDataAdapter(sSQL, oConnection)

Dim oDataSet as DataSet = new DataSet()

oDataAdapter.Fill(oDataSet)

oDataGrid.DataSource = oDataSet

oDataGrid.DataBind()

End Sub

< /SCRIPT >

< /HEAD >

< BODY >

< ASP:DataGrid ID="oDataGrid" Runat="server" />

< /BODY >

< /HTML >

As I told you connecting between ASP.NET and MySQL was easy. Both ASP.NET and MySQL are great technologies and they are free to use, which make them appealing to a broad software and web development audience.