CODEWITHSUNDEEP
c#asp.netmysql
eric
eric

Reputation: 4951

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

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:

Upvotes: 1

Views: 8300

Answers (4)

Daniel A. White
Daniel A. White

Reputation: 191048

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

Upvotes: 0

Phillip Schmidt
Phillip Schmidt

Reputation: 8818

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

Upvotes: 4

user1102001
user1102001

Reputation: 707

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..

Upvotes: 0

Icarus
Icarus

Reputation: 63970

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.

Upvotes: 0

Related Questions