How to connect to a MySql database in ASP.NET web application?

Question

Let's say I already have a correctly formed connection string named 'myConn' in my Web.config. The database is on a different server and is MySQL. How do I connect to this database in my ASP.NET web app?

What I tried:
I tried installing the 'ADO.NET C# DbContext Generator With WCF Support' but this blew up in my face with the error message 'The assembly reference "System.Data.Entity" could not be added to the project. This wizar will continue to run, but the resulting project may not build properly.'

Notes:

Using Visual Studio 2010 (Version 10.0.40219.1 SP1Rel)
Using .NET Framework 2.0

are you at all familiar with how to make db connections in asp.net? like sqlconnection, linqdatasource, etc.? — mellamokb, Jul 30 '12 at 14:07
possibly dumb question, but have downloaded the MySql ado.net client? — Simon Halsey, Jul 30 '12 at 14:15
@Simon Halsey I downloaded/installed the above mentioned template and it gave me errors. Not sure whether this is the 'client' to which you refer though. — eric, Jul 30 '12 at 14:59
@DanielA.White right. Good question. I'm using .NET Frameworkd 2.0. I'll add it to my question description. — eric, Jul 30 '12 at 15:00
You may want to try the .net data provider at http://dev.mysql.com/downloads/connector/net/ — Simon Halsey, Jul 31 '12 at 12:28

score 4 · Accepted Answer · edited May 23 '17 at 11:48

Assuming ODBC (not the only option), it should just be a matter of:

OdbcConnection conn = new OdbcConnection(connectionString);
conn.Open();

edit: oh, and in case it was getting it from the web.config you were wondering about (probably not, but who knows), that part would be:

string connectionString = System.Configuration.ConfigurationManager.AppSettings["myConn"].ToString(); //ToString here is optional unless you're doing some weirdness in the web.config

Per your request, here's the basics behind obdc data operations:

The following is for a query-type statement:

string command = "SELECT Something FROM SomeTable WHERE SomethingElse = '%" + "@Parameter1" + "%' AND SomethingElseStill LIKE '%" + @Parameter2 + "%' ";
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
    OdbcCommand command = new OdbcCommand(command, conn);
    command.Parameters.Add("@Parameter1", OdbcType.VarChar, 255);
    command.Parameters["@Parameter1"].Value = "SomeString"
    command.Parameters.Add("@Parameter2", OdbcType.Int);
    int SomeInteger = 1;
    command.Parameters["@Parameter2"].Value = SomeInteger;
    OdbcDataAdapter adapter = new OdbcDataAdapter(command,con);
    DataSet Data = new DataSet();
    adapter.Fill(Data);
}

That will take data from your database and shove it into a DataTable object, using the OdbcDataAdapter object. This is not the only way to do it, but it is certainly the most basic. Look at this great answer for a method of converting the result into a list of whatever object you want, using a little bit of reflection. You can also use something like LINQ to map the data to custom classes, but I'll leave that bit to you.

Anyway, here's the same, but with a non-query:

string command = "INSERT INTO SomeTable (column1, column2, column3) VALUES '%" + "@Parameter1" + "%', '%" + "@Parameter2" + "%', '%" + "@Parameter3" + "%' ";
using (OdbcConnection connection = new OdbcConnection(connectionString))
{
    OdbcCommand command = new OdbcCommand(command, conn);
    command.Parameters.Add("@Parameter1", OdbcType.VarChar, 255);
    command.Parameters["@Parameter1"].Value = "SomeString"
    command.Parameters.Add("@Parameter2", OdbcType.Int);
    int SomeInteger = 1;
    command.Parameters["@Parameter2"].Value = SomeInteger;
    command.Parameters.Add("@Parameter3", OdbcType.VarChar, 255);
    command.Parameters["@Parameter3"].Value = "SomeOtherStringOrSomething";
    command.ExecuteNonQuery();
}

Make sure to sanity-check my quotes and stuff, as I wrote it in the StackOverflow editor, not any kind of IDE, so unfortunately I dont get syntax highlighting :P

@EricTurner cool, let me know how it goes. If you'd like, I'll add an edit showing you how to do basic operations using the odbc "pattern" — Phillip Schmidt, Jul 30 '12 at 14:36
thank you for going the extra mile on this. Really appreciate it. Hats off to you, sir m(_ _)m — eric, Jul 30 '12 at 15:07
@EricTurner no problem! you may want to look over it again, as I've edited it about a thousand times in the last few minutes to add little bits of info I thought'd be useful. The stackoverflow answer I linked is a big one. — Phillip Schmidt, Jul 30 '12 at 15:09
Understood. I'll need some more time though as I won't be able to try/confirm the solution until later in the day. — eric, Jul 30 '12 at 15:51

score 0 · Answer 2 · answered Jul 30 '12 at 14:08

0

If it's a different server but it's the same database, with the same credentials, all you need to do is point the connection string to the IP address of the new server and it should work.

answered Jul 30 '12 at 14:08

Icarus

63,293
14
100
115

I think I'm okay with the connection string part (it's formed correctly as far as I can tell) but what I am struggling with is what to do next. – eric Jul 30 '12 at 14:29

score 0 · Answer 3 · answered Jul 30 '12 at 14:27

Check For Refernece to : System.Data.DataSetExtensions

Code

 using System.Data.Odbc;

 private const string ConnStr = "Driver={MySQL ODBC 3.51    Driver};
 Server=localhost;Database=test;uid=root;pwd=;option=3";

and even you can put connectionstring in web.config..

score 0 · Answer 4 · answered Jul 30 '12 at 15:23

0

Since you are using .NET 2.0, you can't use entity framework or System.Data.Entity.

answered Jul 30 '12 at 15:23

Daniel A. White

187,200
47
362
445

How to connect to a MySql database in ASP.NET web application?

4 Answers4

Code