如何使用 C# 连接到 Mysql？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14801948/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to connect to Mysql using C#?
提问by
I'm just a beginner in C#. I'm using XAMPP server for MySQL database and Visual C# 2010. Then I have created a database named "testdb" in phpMyAdmin and a table named "login". I have inserted my username and password in the table. I'm doing a simple WinForm login where I made two text boxes for username and password and a button. I have my codes done and there's no compiler error. But I had troubled in one line. It says "Unable to connect to any of the specified MySQL hosts". I added MySql.Data to my references. I want to fetch the data in the database table when I'm going to log in. Then authorize the user or if not matched, it will prompt an error message.
我只是 C# 的初学者。我将 XAMPP 服务器用于 MySQL 数据库和 Visual C# 2010。然后我在 phpMyAdmin 中创建了一个名为“testdb”的数据库和一个名为“login”的表。我已经在表中插入了我的用户名和密码。我正在做一个简单的 WinForm 登录，其中我为用户名和密码制作了两个文本框和一个按钮。我已经完成了我的代码并且没有编译器错误。但我在一行中遇到了麻烦。它说“无法连接到任何指定的 MySQL 主机”。我将 MySql.Data 添加到我的引用中。想登陆的时候去取数据库表中的数据，然后给用户授权，或者不匹配就会提示错误信息。
Here is my code:
这是我的代码：
using MySql.Data.MySqlClient;
public bool Login(string username, string password)
{
MySqlConnection con = new MySqlConnection("host=localhost;username…");
MySqlCommand cmd = new MySqlCommand("SELECT * FROM login WHERE username='" +
username + "' AND password='" + password + "';");
cmd.Connection = con;
con.Open(); // This is the line producing the error.
MySqlDataReader reader = cmd.ExecuteReader();
if (reader.Read() != false)
{
if (reader.IsDBNull(0) == true)
{
cmd.Connection.Close();
reader.Dispose();
cmd.Dispose();
return false;
}
else
{
cmd.Connection.Close();
reader.Dispose();
cmd.Dispose();
return true;
}
}
else
{
return false;
}
}
*I hope for your your feedback. :)
*希望得到您的反馈。:)
回答by Mt. Schneiders
Try modifying your ConnectionString accordingly to the Standard MySQL ConnectionString:
尝试根据标准 MySQL ConnectionString 修改您的 ConnectionString：
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
Source: MySQL ConnectionStrings
You can also take a look at the following link, that shows how to connect to a MySQL database using C#:
您还可以查看以下链接，其中显示了如何使用 C# 连接到 MySQL 数据库：
回答by Daniel Brückner
Your immediate problem is probably either an incorrect connection string or the database server is not available. The connection string should be something like this
您的直接问题可能是连接字符串不正确或数据库服务器不可用。连接字符串应该是这样的
Server=localhost;Database=testdb;Uid=<username>;Pwd=<password>;
with <username>and <password>replaced with your actual values.
使用<username>并<password>替换为您的实际值。
Besides that your code has several issues and you should definitely look into them if this is intended to become production code and probably even if this is just a toy project to learn something. The list is in particular order and may not be comprehensive.
除此之外，您的代码有几个问题，如果这打算成为生产代码，并且可能即使这只是一个学习东西的玩具项目，您也绝对应该调查它们。该列表按特定顺序排列，可能并不全面。
- Do not hard code your connection string. Instead move it to a configuration file.
- Do not include plain text passwords in configuration files or source code. There are various solutions like windows authentication, certificatesor passwords protectedby the Windows Data Protection API.
- Do not just dispose
IDisposableinstances by callingIDisposable.Dispose(). Instead use theusingstatement to release resources even in the case of exceptions. - Do not build SQL statements using string manipulation techniques. Instead use
SqlParameterto prevent SQL injection attacks. - Do not store plain text passwords in a database. Instead at least store salted hashes of the passwords and use a slow hash function, not MD5 or a member of the SHA family.
- You can use
IDbCommand.ExecuteScalarto retrieve a scalar result and avoid using a data reader. - Comparing a boolean value with
trueorfalseis redundant and just adds noise to your code. Instead ofif (reader.IsDBNull(0) == true)you can just useif (reader.IsDBNull(0)). The same holds forif (reader.Read() != false)what is equivalent toif (reader.Read() == true)and therefore alsoif (reader.Read()). - Using an O/R mapper like the Entity Frameworkis usually preferred over interacting with the database on the level of SQL commands.
- 不要硬编码您的连接字符串。而是将其移动到配置文件。
- 不要在配置文件或源代码中包含纯文本密码。有多种解决方案，例如Windows 身份验证、受Windows 数据保护 API保护的证书或密码。
- 不要只是
IDisposable通过调用来处理实例IDisposable.Dispose()。即使在出现异常的情况下，也可以使用该using语句来释放资源。 - 不要使用字符串操作技术构建 SQL 语句。而是用于
SqlParameter防止 SQL 注入攻击。 - 不要将纯文本密码存储在数据库中。相反，至少存储密码的加盐哈希并使用慢速哈希函数，而不是 MD5 或 SHA 系列的成员。
- 您可以使用
IDbCommand.ExecuteScalar检索标量结果并避免使用数据读取器。 - 将布尔值与
trueor进行比较false是多余的，只会给您的代码增加噪音。而不是if (reader.IsDBNull(0) == true)你可以使用if (reader.IsDBNull(0)). 这同样适用于if (reader.Read() != false)等价于if (reader.Read() == true)并且因此也if (reader.Read())。 - 使用像实体框架这样的 O/R 映射器通常比在 SQL 命令级别上与数据库交互更受欢迎。
回答by Ramgy Borja
Make it simple and sql injection free, and also don't forget to add MySql.Webin your references since your using XAMPP
让它变得简单和 sql 注入免费，并且不要忘记 在您的引用中添加MySql.Web，因为您使用XAMPP
public bool Login(string username, string password)
{
DataTable dt = new DataTable();
string config = "server=....";
using (var con = new MySqlConnection { ConnectionString = config })
{
using (var command = new MySqlCommand { Connection = con })
{
con.Open();
command.CommandText = @"SELECT * FROM login WHERE username=@username AND password=@password";
command.Parameters.AddWithValue("@username", username);
command.Parameters.AddWithValue("@password", password);
dt.Load(command.ExecuteReader());
if (dt.Rows.Count > 0)
return true;
else
return false;
} // Close and Dispose command
} // Close and Dispose connection
}
