Problems connecting to local MySQL server

Question

I have problems connecting to a local MySQL database.

I made sure the server is running and i can connect to the server using the user and password from the connection string and create databases with the terminal.

I'm using visual studio community on a mac and MySQL 8.0.16.

Example: Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace ReadSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            DataAccess dataAccess = new DataAccess();
            dataAccess.GetTask();
        }
    }
}

DataAccess.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;

namespace ReadSQL
{
    public class DataAccess
    {
        public List<Task> GetTask()
        {
            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("workloadDB")))
            {
                var output = connection.Query<Task>($"select * from task where id=1").ToList(); //exception thrown here
                Console.WriteLine(output);           
            }
        }
     }
}

Helper.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ReadSQL
{

        public static class Helper
        {
            public static string CnnVal(string name)
            {
                return ConfigurationManager.ConnectionStrings[name].ConnectionString;
            }
        } 
}

Task.cs

using System;
namespace ReadSQL
{
    public class Task
    {
        int id { get; set; }
        string name { get; set; }
    }
}

App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <connectionStrings>
        <add name="workloadDB" connectionString="Server=localhost;Database=workload;Uid=root;Pwd=adminPasswort;" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

I expect the variable output to contain the first row of my table, but instead i get the error message:

System.Data.SqlClient.SqlException A network-related or instance-specific error occurred while establishing a connection to sql server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections.

I don't know why i cannot establish the connection, because the MySQL Server is definitely running and the connection string should be correct (also tried: trusted_connection=true;). Thanks in Advance!

Answer 1

You cannot use the SqlConnection class to connect to MySQL. Instead, install MySqlConnector from NuGet, then use:

using (IDbConnection connection = new MySqlConnection(Helper.CnnVal("workloadDB")))
{
    var output = connection.Query<Task>($"select * from task where id=1").ToList();          
}

Your connection string looks like it should be compatible with MySqlConnection, but if you're using any other options you should double-check them against the list of valid connection options.