CODEWITHSUNDEEP
c#.netmysqldatabasevisual-studio-2010
dsp_099
dsp_099

Reputation: 6121

Problems connecting to a local MySQL database using C# and .NET 2.0

Newb questions incoming.

I'm running MySQL locally and I am having a really hard time connecting to it with C#.

Here's the code I'm using, much of the stuff I've tried out is commented out:

using System;
using System.Collections.Generic;
//using System.Data.Common.DbConnection;
using System.Threading;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Threading;
namespace mvpde
{
    class Program
    {
        class DataReader_SQL
        {
            /// <summary>
            /// The main entry point for the application.
            /// </summary>
            [STAThread]
            static void Main(string[] args)
            {
                //
                // TODO: Add code to start application here
                //
                try
                {

                    OleDbConnection con = new OleDbConnection(@"Provider=sqloledb;Data Source=127.0.0.1:3306;database=database_name;User id=id;Password=password;");
                    con.Open();

                    //Thread.Sleep(9999);

                    //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

                    //builder.DataSource = "localhost";
                    //builder.UserID = "hello";
                    //builder.Password = "password";
                    //builder.InitialCatalog = "db_123";

                    //SqlConnection thisConnection = new SqlConnection(builder.ToString());
                    //thisConnection.Open();
                    //SqlCommand thisCommand = thisConnection.CreateCommand();
                    //thisCommand.CommandText = "SELECT CustomerID, CompanyName FROM Customers";
                    //SqlDataReader thisReader = thisCommand.ExecuteReader();
                    //while (thisReader.Read())
                    //{
                    //    Console.WriteLine("\t{0}\t{1}", thisReader["CustomerID"], thisReader["CompanyName"]);
                    //}
                    //thisReader.Close();
                    //thisConnection.Close();
                }
                catch (SqlException e)
                {
                    Console.WriteLine(e.Message);
                    Thread.Sleep(9999999);
                }

            }
        }
    }
}

I've copied it nearly verbatim and tried to make it work from this site.

I have installed the MySQL on Windows 7 - I have created the user and the database I'm trying to access and I can do all this just fine using MySQL's own MySQL Workbench 5.2 CE software. I seem to have no clue what I'm doing using C# to connect to it.

All I want is to connect to it and send it a simple query like "USE DATABASE somedb" and then "SELECT TABLE sometable".

Errors I'm getting: At first (when I used WAMP, before installing the MySQL package from mysql website) I would be able to connect once and see a login error; after that, it's as if the connection wasn't closed properly and I'd be unable to get even that far) Now that I'm trying to connect to a MySQL instance that is created by MySQL's own software (or by running mysqld.exe) all I ever get is timeout problems\server not found\inaccessible.

Questions:

I imagined connecting to a locally-running MySQL server should be a breeze, but I've no idea what I'm doing.

Thank you.

Upvotes: 0

Views: 3468

Answers (3)

Steve
Steve

Reputation: 216243

You should use the MySql/Connector for DotNet.
Download it at this address then change your connection string accordingly.
For example "Server=127.0.0.1;Database=database_name;Uid=id;Pwd=password;Port=3306"
For other options, specific for MySQL, look at www.connectionstrings.com

Upvotes: 1

Darren
Darren

Reputation: 70718

You can't use a SQL Connection object. You need a MySQL Connection object which is available in this library.http://www.connectionstrings.com/mysql

Download the MySQL Connection Library and use one of their connection strings provided on the site. 

using MySql.Data.MySqlClient;

MySqlConnection myConnection = new MySqlConnection;();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
//execute queries, etc
myConnection.Close();

Then use the connection object to initalise the connection string and connect to MySQL

Upvotes: 1

smnbss
smnbss

Reputation: 1041

have you had a look at http://www.connectionstrings.com/mysql ?

Upvotes: 1

Related Questions