Library code snippets
How to access a MySQL database with .NET
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>
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.
Related articles
Related discussion
-
High-Performance .NET Application Development & Architecture
by Manjot Bawa (0 replies)
-
How to Change Default exe Icon in C#.net Windows Application
by sonali.terse (2 replies)
-
hey developers out there
by pitsophera (0 replies)
-
how to save email into database in xml format to sql format and how to trigger back in html
by shahid123 (0 replies)
-
Hi this is Sajjan Hooda
by sajjanh (0 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?
Events coming up
-
Mar
15
DevWeek 2010
London, United Kingdom
DevWeek is Europe’s leading independent conference for software developers, database professionals and IT architects, and features expert speakers on a wide range of topics, including .NET 4.0, Silverlight 3, WCF 4, Visual Studio 2010, REST, Windows Workflow 4, Thread Synchronization, ASP.NET 4.0, SQL Server 2008 R2, LINQ, Unit Testing, CLR & C# 4.0, .NET Patterns, WPF 4, F#, Windows Azure, ADO.NET, Entity Framework, Debugging, T-SQL Tips & Tricks, and more.
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.
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
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.