C#从数据库中的表填充一个组合框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14113931/
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
C# Populate a ComboBox from table in database
提问by Amna Ahmed
I have a combobox which is called combobox1, which I want to populate it with idas value and Nameas display name. I searched and read some tutorial and found this code to use in Form load event, but it doesn't populate the list. I see an empty dropdown. Any ideas of where I am wrong?
我有一个名为 的组合框combobox1，我想用id值和Name显示名称填充它。我搜索并阅读了一些教程，发现此代码可用于表单加载事件，但它没有填充列表。我看到一个空的下拉列表。关于我错在哪里的任何想法？
In my database class I have this function.
在我的数据库类中，我有这个功能。
public static void FillDropDownList(string Query, System.Windows.Forms.ComboBox DropDownName)
{
SqlDataReader dr;
SqlConnection myConnection = new SqlConnection(CONNECTION_STRING);
try
{
myConnection.Open();
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
// Check whether the Drop Down has existing items. If YES, empty it.
if (DropDownName.Items.Count > 0)
DropDownName.Items.Clear();
SqlCommand cmd = new SqlCommand(Query, myConnection);
dr = cmd.ExecuteReader();
while (dr.Read())
DropDownName.Items.Add(dr[0].ToString());
Console.Write(DropDownName.Items.Add(dr[0].ToString()));
dr.Close();
}
In my form i call it as
在我的形式中，我称之为
private void sales_record_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(DBUtils.CONNECTION_STRING);
DBUtils.FillDropDownList("select id,Name from Farms", comboBox1);
}
采纳答案by Neolisk
My advice - use .NET built-in functionality as much as possible, and don't handle data binding manually (which is what you are trying to do in your code):
我的建议 - 尽可能多地使用 .NET 内置功能，并且不要手动处理数据绑定（这是您尝试在代码中执行的操作）：
- Use ExecuteQuery to pull a DataTable from database.
- Set DropDownName.DataSource = yourDataTable.
- Set DropDownName.ValueMember = "id".
- Set DropDownName.DisplayMember = "Name".
- 使用 ExecuteQuery 从数据库中提取一个 DataTable。
- 设置 DropDownName.DataSource = yourDataTable。
- 设置 DropDownName.ValueMember = "id"。
- 设置 DropDownName.DisplayMember = "Name"。
So your code would look similar to this:
所以你的代码看起来类似于：
public static void FillDropDownList(string Query, System.Windows.Forms.ComboBox DropDownName)
{
DataTable dt;
using (var cn = new SqlConnection(CONNECTION_STRING))
{
cn.Open();
try
{
SqlCommand cmd = new SqlCommand(Query, cn);
dt = cmd.ExecuteQuery();
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
return;
}
}
DropDownName.DataSource = dt;
DropDownName.ValueMember = "id";
DropDownName.DisplayMember = "Name";
}
Notice how I changed exception type to SqlException, so we are only looking for database errors. Everything else will blow up. I don't remember any situation when myConnection.Open();would throw an exception, so your try block is not very useful. Notice in my try clause - it has ExecuteQueryinside it, which is likely to fail.
请注意我如何将异常类型更改为SqlException，因此我们只查找数据库错误。其他一切都会爆炸。我不记得什么时候myConnection.Open();会抛出异常，所以你的 try 块不是很有用。注意我的 try 子句 - 它在ExecuteQuery里面，很可能会失败。
EDIT:There is no need to close connection in the finallyblock when using the usingconstruct. So it can be removed - and your code becomes more compact as a result.
编辑：有没有必要在紧密结合finally块使用时using结构。所以它可以被删除 - 结果你的代码变得更加紧凑。
回答by XIVSolutions
This should do what you are seeking. The issue I see, from a design standpoint, is that anyone coding a query for this will need to be aware that the first two columns returned need to reflect the ID and the display item, respectively. Other than that, the actual column names don;t matter, because the ValueMember and DisplayMember properties (the string name of each respective column in the DataSource) are obtained by ordinal reference to Column[0] and Column[1].
这应该做你正在寻找的。我看到的问题是，从设计的角度来看，任何为此编写查询代码的人都需要知道返回的前两列需要分别反映 ID 和显示项目。除此之外，实际的列名并不重要，因为 ValueMember 和 DisplayMember 属性（DataSource 中每个相应列的字符串名称）是通过对 Column[0] 和 Column[1] 的顺序引用获得的。
Note that I have wrapped each DataAccess object (the SQLConnection instance and the SQLCommand Instance in using blocks. This is a recommended practice for data access objects, which tend to consume non-managed resources and need to be disposed. The using block handles disposal of each object for you. Note that each using block contains its own scope.
请注意，我已经将每个 DataAccess 对象（SQLConnection 实例和 SQLCommand 实例都包装在 using 块中。这是对数据访问对象的推荐做法，这些对象往往会消耗非托管资源并需要处理。 using 块处理每个对象为您。请注意，每个 using 块都包含自己的作用域。
Hope that helps!
希望有帮助！
UPDATE: @Neolisk posted his answer while I was composing mine. While they are not duplicates, they cover a lot of the same ground. Between his answer and this, you should have what you need!
更新：@Neolisk 在我创作时发布了他的答案。虽然它们不是重复的，但它们涵盖了很多相同的领域。在他的回答和这个之间，你应该有你需要的！
public void FillDropDownList(string Query, ComboBox DropDownName)
{
// If you use a DataTable (or any object which implmenets IEnumerable)
// you can bind the results of your query directly as the
// datasource for the ComboBox.
DataTable dt = new DataTable();
// Where possible, use the using block for data access. The
// using block handles disposal of resources and connection
// cleanup for you:
using (var cn = new SqlConnection(CONNECTION_STRING))
{
using(var cmd = new SqlCommand(Query, cn))
{
cn.Open();
try
{
dt.Load(cmd.ExecuteReader());
}
catch (SqlException e)
{
// Do some logging or something.
MessageBox.Show("There was an error accessing your data. DETAIL: " + e.ToString());
}
}
}
DropDownName.DataSource = dt;
DropDownName.ValueMember = dt.Columns[0].ColumnName;
DropDownName.DisplayMember = dt.Columns[1].ColumnName;
}
回答by Amna Ahmed
I was able to make it work using @Neolisk 's code. Only made some minor changes to code which are as follows.
我能够使用 @Neolisk 的代码使其工作。仅对代码进行了一些小的更改，如下所示。
public static void FillDropDownList(string Query, System.Windows.Forms.ComboBox DropDownName)
{
using (var cn = new SqlConnection(CONNECTION_STRING))
{
cn.Open();
DataTable dt = new DataTable();
try
{
SqlCommand cmd = new SqlCommand(Query, cn);
SqlDataReader myReader = cmd.ExecuteReader();
dt.Load(myReader);
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
return;
}
DropDownName.DataSource = dt;
DropDownName.ValueMember = "id";
DropDownName.DisplayMember = "Name";
}
}
