Library code snippets
How to access a MySQL database with .NET
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.
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>
Related articles
Related discussion
-
VB.NET Type 'SqlDatabaseException' not defined
by Mulish Mehdi (1 replies)
-
An Introduction to VB.NET and Database Programming
by yen (12 replies)
-
How can I execute server-side function using asp.net Ontextchanged orJavascript onchange?
by konikula (6 replies)
-
how to make smtp in vb.net
by konikula (1 replies)
-
Send images through Shell(" Net Send") in VB.NET
by Th0mas (2 replies)
Related podcasts
-
ADO.NET "Astoria" Data Services with Shawn Wildermuth
Scott chats with Shawn Wildermuth, "the ADO Guy," about ADO.NET Data Services, aka "Project Astoria." It's REST for SQL Server. Should you care? What's REST? How does this relate to WCF or ASP.NET?
Related jobs
-
Microsoft .Net Architect
in AMSTERDAM (€50K-€90K per annum)
Events coming up
-
Jun
16
Code Generation 2009
Cambridge, United Kingdom
A developer event with a practical focus on helping people get to grips with code generation tools and technologies.
maybe this is the wrong place for this specific question, but how do you walk through the data? In Visual Basic 6, using databases was easy. Create a record set, then; recordsetName!FieldName
I can't find any easy way to do this in C#. Any ideas?
Can u just tell how do u connect to MaxDB using the languages that u r using...Moreover if you come accross, give some links that highlight on the connectivity of asp.net and MaxDB.
Intranet Application-E Commerce-web Solutions
Smart way to be on NET
Could you please just guide me to write the connection string for asp.net and MaxDB. I have installed MaxDb 7.5 and its ODBC 7.5 and SQL Studio.. But Have no Idea of how to connect to the database from asp.net...
School Administration at your Finger Tips.
Dear SergioCossa
I am new to the MaxDB database and trying to connect from my asp.Net application and Don't have any Idea for the same....Can you just guide me to get a start. And I searched complete web for a solution but could not get a solution - I would like to know whether u r using provider or DSN for establishing connection...If so how and just show by code to have the same...
thanks
Santhosh
India
Very goog article!
Now... I am trying to use the MaxDB version of that database, since it supports stored procedures.
I have been able to connect and to execute SQL command, but when trying to use a SP it happens an error.
Example:
...
odbcComm.CommandText = "call playercount";
...
ERROR [42000] [MySQL MaxDB][SQLOD32 DLL][MaxDB] Syntax error or access violation;-3005 POS(1) Invalid SQL statement ERROR [42000] [MySQL MaxDB][SQLOD32 DLL][MaxDB] Syntax error or access violation;-3005 POS(1) Invalid SQL statement
Have you some experience with this version?
Best Regards!
Sergio Cossa
Argentina
This thread is for discussions of How to access a MySQL database with .NET.