将用户从Access读取到列表中只返回一个用户

Question

我正在用VS 2010 SP1开发C#中的WPF应用程序。

我在这里和网上的其他地方看了很多例子，我的代码似乎是正确的，但当我试图将Access DB中的行读取到类中，然后读取到ObservableCollection中时（我也只尝试了一个List），只有一行最终会出现在集合中。然后，我尝试在ListBox中显示用户名列表，当然，集合中只显示一个用户。

我已经被这个问题困扰了一段时间了。如有任何帮助，我们将不胜感激。我希望我只是忽略了一些微不足道的事情。

用户类别：

public class User
{
    public User() { }
    public int UserID { get; set; }
    public string UserName { get; set; }
    public string UserTitle { get; set; }
    public string UserArea { get; set; }
}

代码：

// Setting up DB stuff.
string s_ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data " + 
    "Source=|DataDirectory|\CloseoutApp.accdb";
OleDbConnection AccessConn = new OleDbConnection(s_ConnString);
string s_Query = "SELECT UserID, UserName, UserTitle, UserArea FROM Users " +
    "ORDER BY UserID;";
OleDbCommand AccessCmd = new OleDbCommand(s_Query, AccessConn);
OleDbDataReader rdr;
AccessConn.Open();
rdr = AccessCmd.ExecuteReader();
// Collection of Users.
ObservableCollection<User> userList = new ObservableCollection<User>();
try
        {
            // Read each user from DB into a User instance, then add that instance to the userList.
            while(rdr.Read())
            {
                User newUser = new User();
                newUser.UserID = rdr.GetInt32(0);
                newUser.UserName = rdr.GetString(1);
                newUser.UserTitle = rdr.GetString(2);
                newUser.UserArea = rdr.GetString(3);
                userList.Add(newUser);
            }
        }
catch(Exception e)
        {
            //Update Statusbar
        }
// Close the DB connection.
rdr.Close();
AccessConn.Close();
// Add users to the ListBox.
foreach(User u in userList)
    {
        lb_Users.Items.Add(u.UserName);  
    } 

Answer 1

当我以类似的方式处理数据库连接时，也发生了类似的事情。

试试下面的代码：

// Setting up DB stuff.
        string s_ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data " +
            "Source=|DataDirectory|\CloseoutApp.accdb";
        string s_Query = "SELECT UserID, UserName, UserTitle, UserArea FROM Users " +
            "ORDER BY UserID;";
        ObservableCollection<User> userList = new ObservableCollection<User>();
        using (OleDbConnection AccessConn = new OleDbConnection(s_ConnString))
        {
            using (OleDbCommand AccessCmd = AccessConn.CreateCommand())
            {
                AccessCmd.CommandText = s_Query;
                try
                {
                    AccessConn.Open();
                    OleDbDataReader rdr = AccessCmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        User newUser = new User();
                        newUser.UserID = rdr.GetInt32(0);
                        newUser.UserName = rdr.GetValue(1).ToString();
                        newUser.UserTitle = rdr.GetValue(2).ToString();
                        newUser.UserArea = rdr.GetValue(3).ToString();
                        userList.Add(newUser);
                    }
                    rdr.Close();
                }
                catch(Exception ex)
                {
                    //do something with ex
                }
            }
        }
        // Add users to the ListBox.
        foreach (User u in userList)
        {
            lb_Users.Items.Add(u.UserName);
        }