从IDataReader创建对象

Question

我创建了以下内容，以便使用名为ObservableCollection of UserModule对象的UserModules创建用户对象。

我有大约。100000个用户记录，每个用户最多可以有10个模块记录，这需要几分钟才能完成。

这可能意味着改变IDataReader，接受建议。有人能提出一个更有效的方法来做这件事吗？

public void LoadUsers()
{
    clsDAL.SQLDBAccess db = new clsDAL.SQLDBAccess("USERS");
    clsDAL.SQLDBAccess db_user_modules = new clsDAL.SQLDBAccess("USERS");
    try
    {
        db.setCommandText(@"SELECT * FROM Users");

        using (var reader = db.ExecuteReader())
        {
            while (reader.Read())
            {
                var user = new User();
                MapUser(reader, user);
                _users.Add(user);

                db_user_modules.setCommandText(@"SELECT MODULE_ID, USER_MODULE_ACCESS FROM USER_MODULE_SECURITY Where USER_ID = " + user.User_ID);

                using (var reader_user_modules = db_user_modules.ExecuteReader())
                {
                    while (reader_user_modules.Read())
                    {
                        MapUserModule(reader_user_modules, user);
                    }
                }
            }
        }
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
        throw;
    }
    finally
    {
        db = null;
    }

}

MapUser

public static void MapUser(IDataRecord record, User user)
{
    try
    {
        user.User_ID = NullSafeGetter.GetValueOrDefault<int>(record, "USER_ID");
        user.Username = NullSafeGetter.GetValueOrDefault<string>(record, "USERNAME");
        user.Name = NullSafeGetter.GetValueOrDefault<string>(record, "NAME");
        user.Job_Title = NullSafeGetter.GetValueOrDefault<string>(record, "JOB_TITLE");
        user.Department = NullSafeGetter.GetValueOrDefault<string>(record, "DEPARTMENT");
        user.Company = NullSafeGetter.GetValueOrDefault<string>(record, "COMPANY");
        user.Phone_Office = NullSafeGetter.GetValueOrDefault<string>(record, "PHONE_OFFICE");
        user.Phone_Mobile = NullSafeGetter.GetValueOrDefault<string>(record, "PHONE_MOBILE");
        user.Email = NullSafeGetter.GetValueOrDefault<string>(record, "EMAIL");
        user.Password = NullSafeGetter.GetValueOrDefault<string>(record, "PASSWORD");
        user.Last_Login = NullSafeGetter.GetValueOrDefault<DateTime>(record, "LAST_LOGIN");
        user.Status = NullSafeGetter.GetValueOrDefault<int>(record, "STATUS");
        user.Session_Timeout = NullSafeGetter.GetValueOrDefault<int>(record, "SESSION_TIMEOUT");
    }
    catch (Exception ex)
    {
        MessageBox.Show("Mapping User error: " + ex.Message);
        throw;
    }
}

MapUserModule

private static void MapUserModule(IDataRecord record, User user)
{
    try
    {
        int m_id = NullSafeGetter.GetValueOrDefault<int>(record, "MODULE_ID");
        int uma = NullSafeGetter.GetValueOrDefault<int>(record, "USER_MODULE_ACCESS");

        user.UserModules.Add(new Users.UserModule(user.User_ID, m_id, uma));
    }
    catch (Exception ex)
    {
        throw new Exception("Mapping UserModule error:\n" + ex.Message);
    }
}

Answer 1

public IEnumerable<UserModule> GetUserModules()
{
    using(var db = ....)
    db.setCommandText("SELECT * FROM USERMODULES");
    using (var reader = db.ExecuteReader())
    {
        while (reader.Read())
        {
            var userId = reader[...];
            var m_id = reader[...];
            var uma = reader[...];
            yield return new UserModule (userid, m_id, uma)
        }
    }
}

public IEnumerable<User> GetUsers()
{
    var userModulesLookup = GetUserModules().ToLookup(x => x.UserId);
    using (var db = ...)
    {
        db.setCommandText("SELECT * FROM USERS");
        using (var reader = db.ExecuteReader())
        {
            while (reader.Read())
            {
                var userId = reader["userId"];
                ...blah blah blah...
                var user = return new User();
                user.Modules = new ObservableCollection<UserModule>
                                     (userModulesLookup[userId]);
                ...blah blah blah...
                yield return user;
            }
        }
    }
}

public void LoadUsers()
{
    var users = GetUsers();
    foreach(var u in users)
        _users.Add(u);
}

Answer 2

据我所知，没有比使用DataReader更快的解决方案了。

我建议您对代码进行概要分析，以查看大部分时间所占用的代码。IIRC，一次向一个可观察的集合添加大量的项是缓慢的。尝试将它们临时添加到List<>中，以尝试隔离问题。