How to connect to MySQL database from ASP.NET
MySQL database is a low-cost alternative to MS SQL Server database and can be
used easily in web applications, including ASP.NET projects. In this article
I’ll explain the necessary steps to connect to MySQL from ASP.NET. We will
connect to our MySQL database using ODBC .NET data provider and DSN-less
connection.
-
Make sure you have installed the .Net Framework on your server
-
Download the ODBC .Net data provider and install it on your server. You can
find the download here:
http://www.microsoft.com/downloads/release.asp?ReleaseID=35715
-
Make sure that Microsoft.data.odbc.dll file is located in the following folder
on your server C:\Program Files\Microsoft.NET\Odbc.Net\.
-
Install Microsoft Data Access Components (MDAC) 2.6 or later. MDAC 2.8 is
recommended. You can download it here: http://msdn.microsoft.com/data/
-
Install MySQL Server.
-
install MySQL ODBC Driver-MyODBC 3.51. You can download it here:
http://www.mysql.com/downloads/api-myodbc-3.51.html
Here is the actual code connection to the MySQL database and retrieving and
displaying all the records from Table1 table in your database.
<%@ 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 sConString As String = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;
DATABASE=Your_Mysql_DB; UID=mysql_username; PASSWORD=mysql_password; OPTION=3"
Dim oConnection as ODBCConnection = new ODBCConnection(sConString)
Dim sSQL as String = "SELECT * FROM Table1"
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>
After you have tested your solution on your development workstation, you need
to find a web hosting company offering ASP.NET hosting and supporting MySQL
database, in order to deploy your web application and make it available on
Internet.