使用 MySQL 在 C# 中填充数据表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14020038/
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
Filling a DataTable in C# using MySQL
提问by Warjekk
I'm attempting to fill a DataTable with results pulled from a MySQL database, however the DataTable, although it is initialised, doesn't populate. I wanted to use this DataTable to fill a ListView. Here's what I've got for the setting of the DataTable:
我试图用从 MySQL 数据库中提取的结果填充 DataTable，但是 DataTable 尽管已初始化，但并未填充。我想用这个 DataTable 来填充一个 ListView。这是我设置数据表的内容：
public DataTable SelectCharacters(string loginName)
{
this.Initialise();
string connection = "0.0.0.0";
string query = "SELECT * FROM characters WHERE _SteamName = '" + loginName + "'";
if (this.OpenConnection() == true)
{
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataAdapter returnVal = new MySqlDataAdapter(query,connection);
DataTable dt = new DataTable("CharacterInfo");
returnVal.Fill(dt);
this.CloseConnection();
return dt;
}
else
{
this.CloseConnection();
DataTable dt = new DataTable("CharacterInfo");
return dt;
}
}
And for the filling of the ListView, I've got:
对于 ListView 的填充，我有：
private void button1_Click(object sender, EventArgs e)
{
string searchCriteria = textBox1.Text;
dt = characterDatabase.SelectCharacters(searchCriteria);
MessageBox.Show(dt.ToString());
listView1.View = View.Details;
ListViewItem iItem;
foreach (DataRow row in dt.Rows)
{
iItem = new ListViewItem();
for (int i = 0; i < row.ItemArray.Length; i++)
{
if (i == 0)
iItem.Text = row.ItemArray[i].ToString();
else
iItem.SubItems.Add(row.ItemArray[i].ToString());
}
listView1.Items.Add(iItem);
}
}
Is there something I'm missing? The MessageBox was included so I could see if it has populated, to no luck.
有什么我想念的吗？MessageBox 已包含在内，因此我可以查看它是否已填充，但不幸的是。
Thanks for any help you can give.
谢谢你提供的所有帮助。
采纳答案by Sylca
Well, I ... can't figure out what you have done here so I'll paste you my code with which I'm filling datagridview:
好吧，我......无法弄清楚你在这里做了什么，所以我会把我用来填充 datagridview 的代码粘贴给你：
1) Connection should look something like this(if localhost is your server, else, IP adress of server machine):
1) 连接应该是这样的（如果 localhost 是你的服务器，否则是服务器机器的 IP 地址）：
string connection = @"server=localhost;uid=root;password=*******;database=*******;port=3306;charset=utf8";
2) Query is ok(it will return you something), but you shouldn't build SQL statements like that.. use parameters instead. See SQL injection.
2）查询没问题（它会返回一些东西），但你不应该构建这样的 SQL 语句......改用参数。请参阅SQL 注入。
3) Code:
3) 代码：
void SelectAllFrom(string query, DataGridView dgv)
{
_dataTable.Clear();
try
{
_conn = new MySqlConnection(connection);
_conn.Open();
_cmd = new MySqlCommand
{
Connection = _conn,
CommandText = query
};
_cmd.ExecuteNonQuery();
_da = new MySqlDataAdapter(_cmd);
_da.Fill(_dataTable);
_cb = new MySqlCommandBuilder(_da);
dgv.DataSource = _dataTable;
dgv.DataMember = _dataTable.TableName;
dgv.AutoResizeColumns();
_conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (_conn != null) _conn.Close();
}
}
So, every time I want to display some content of table in mysql database I call this method, pass query string and datagridview name to that method. and, that is it.
因此，每次我想在 mysql 数据库中显示表的某些内容时，我都会调用此方法，将查询字符串和 datagridview 名称传递给该方法。而且，就是这样。
For your sake, compare this example with your and see what can you use from both of it. Maybe, listview is not the best thing for you, just saying ...
为了您的缘故，请将此示例与您的示例进行比较，看看您可以从这两个示例中使用什么。也许，listview 对你来说不是最好的东西，只是说......
hope that this will help you a little bit.
希望这会对你有所帮助。
回答by Himanshu Dhami
Debug your application and see if your sql statement/ connection string is correct and returns some value, also verify if your application is not throwing any exception.
调试您的应用程序并查看您的 sql 语句/连接字符串是否正确并返回一些值，同时验证您的应用程序是否没有抛出任何异常。
回答by nunespascal
Your connection string is invalid.
您的连接字符串无效。
Set it as follows:
设置如下：
connection = "Server=myServer;Database=myDataBase;Uid=myUser;Pwd=myPassword;";
Refer: mysql connection strings
参考：mysql连接字符串
回答by spajce
Here the following why the codes would not work.
以下是代码不起作用的原因。
- Connection
The connection of your mySQL is invalid - Query
I guess do you want to search in thetable, try this query
- 连接
你的mySQL连接无效 - 查询
我猜你想在 中搜索table，试试这个查询
string query = "SELECT * FROM characters WHERE _SteamName LIKE '" + loginName + "%'";
string query = "SELECT * FROM characters WHERE _SteamName LIKE '" + loginName + "%'";
notice the LIKEand %this could help to list all the data.
for more details String Comparison Functions
请注意LIKE，%这可能有助于列出所有数据。
更多细节字符串比较函数
回答by Ramgy Borja
Check your connection string and instead of using
检查您的连接字符串，而不是使用
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataAdapter returnVal = new MySqlDataAdapter(query,connection);
DataTable dt = new DataTable("CharacterInfo");
returnVal.Fill(dt);
this.CloseConnection();
return dt;
you can use this one
你可以用这个
MySqlCommand cmd = new MySqlCommand(query, connection);
DataTable dt = new DataTable();
dt.load(cmd.ExecuteReader());
return dt;
