Connecting to MySQL server via C#

Question

This is my code that is meant to connect to a MySQL server and fetch some info:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;

namespace test_MLDropCopy
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = "server=xxxxx;uid=xxxxx;pwd=xxxxx";

            SqlConnection mySqlConnection = new SqlConnection(connectionString);

            string selectString = "select Symbol from today_positions.all_rttp where ServiceName like 'ML%' and ID = 137800";

            SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

            mySqlConnection.Open(); 

            mySqlCommand.CommandText = selectString;

            SqlDataReader myReader = null;

            myReader = mySqlCommand.ExecuteReader();

            string Symbol = myReader["Symbol"].ToString();

            Console.WriteLine(Symbol);

            mySqlConnection.Close();


        }
    }
}

However, this is the error I'm getting:

Unhandled Exception: System.Data.SqlClient.SqlException: A network-related or in
stance-specific error occurred while establishing a connection to SQL Server. Th
e server was not found or was not accessible. Verify that the instance name is c
orrect and that SQL Server is configured to allow remote connections. (provider:
 Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I have double checked the login credentials I am using. Is there a problem in my code, or is the problem from the database end (offline, inadequate permissions etc.) ?

Answer 1

You're trying to connect to Microsoft SQL Server.

Use the Connector/Net documentation instead. E.g. from the intro tutorial

string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
MySqlConnection conn = new MySqlConnection(connStr);

Note that since both MySQL and SQL Server implement the ADO.NET interfaces, you can put IDbConnection as the type of conn (the same principle applies for other variables).

Answer 2

You are trying to connect to a MySQL server using the Microsoft SQL Server driver(which is only for connecting to MS SQL Server).

Download and install the MySQL Connector from here , the docs here should get you started

Answer 3

You can't connect with the SqlClient, because it is for SQL Server only. You need to install the MySQL connector or connect via ODBC.

Look here for an example for MySQL connector: http://bitdaddys.com/MySQL-ConnectorNet.html

Answer 4

The SqlConnection class is not the base class for a DB connection; its purpose is to connect to MS SQL Server DBs. What you want is a basic OleDbConnection or OdbcConnection; I would use OleDb if your MySql instance supports it.

Answer 5

I had to do this to import data from SQL Server to MySql. The relevant code for MySql connection (as root) and data handling is:

using MySql.Data.MySqlClient;
using MySql.Data.Types;

// snip 

    string sourceString = "Server=127.0.0.1;Uid=root;Pwd=password;Database=dbname;";
    string[] tables = { "Table1", "Table2"};

    using (MySqlConnection source = new MySqlConnection(sourceString))
    {
        source.Open();
        foreach (string table in tables)
        {
            using (MySqlCommand readCommand = new MySqlCommand("select * from " + table, source))
           {
               using (MySqlDataReader reader = readCommand.ExecuteReader())
               {
                    while (reader.Read())
                    {
                         // do work here
                    }
               }
           }
       }
   }