使用ConfigurationManager获取的连接字符串初始化MySqlConnection报错求助

Hey there! I see you're having trouble getting MySqlConnection to work with your connection string, even though the same approach worked for SqlConnection. Let's break down what's going wrong and fix it step by step.

Why This Happens #

The main differences between SqlConnection (for SQL Server) and MySqlConnection are:

• MySQL requires a separate .NET driver package (it's not included in the default .NET framework like SqlConnection)
• MySQL connection strings use slightly different key names and format compared to SQL Server

Step 1: Install the Correct MySQL .NET Driver #

First, make sure you have the right NuGet package installed. The two most common options are:

• MySqlConnector: The modern, actively maintained driver (recommended)
• MySql.Data: The older, official Oracle driver

To install via NuGet Package Manager Console:

# For MySqlConnector
Install-Package MySqlConnector

# Or for MySql.Data
Install-Package MySql.Data

You can also install it via the NuGet UI in Visual Studio by searching for the package name.

Step 2: Fix Your Connection String in App.config/Web.config #

MySQL connection strings use different keys than SQL Server. Update your connectionStrings section to match MySQL's format:

<connectionStrings>
  <add name="connstr" 
       connectionString="Server=your_mysql_server;Database=your_database_name;Uid=your_username;Pwd=your_password;" 
       providerName="MySqlConnector" /> <!-- Use "MySql.Data.MySqlClient" if you installed MySql.Data -->
</connectionStrings>

Key notes for the connection string:

• Use Server instead of Data Source (though MySQL does support Data Source for compatibility)
• Use Uid for username (instead of User ID) and Pwd for password (instead of Password)
• Double-check that your server address, database name, username, and password are all correct

Step 3: Update Your Code #

First, add the correct using statement at the top of your file:

// If using MySqlConnector
using MySqlConnector;

// If using MySql.Data
using MySql.Data.MySqlClient;

Then, rewrite your Select method to use using statements (this automatically handles closing connections, even if an error occurs):

using System.Data;
using System.Windows.Forms;
using System.Configuration;
using MySqlConnector; // Adjust based on your installed package

public DataTable Select()
{
    DataTable dt = new DataTable();
    string myconnstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

    // Using statements auto-dispose connections/commands/adapters
    using (MySqlConnection conn = new MySqlConnection(myconnstr))
    {
        try
        {
            string sql = "SELECT * FROM Member";
            using (MySqlCommand cmd = new MySqlCommand(sql, conn))
            {
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                {
                    conn.Open();
                    adapter.Fill(dt);
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error loading data: {ex.Message}");
        }
        // No need for finally block - using closes the connection automatically
    }
    return dt;
}

Additional Troubleshooting Tips #

• Verify that your MySQL server is running and accessible from your application
• Check if your firewall allows traffic on MySQL's default port (3306)
• If you're using a remote server, ensure the MySQL user has permission to connect from your application's IP address

内容的提问来源于stack exchange，提问作者Hamza Khan