从 IDataReader 创建对象

Question

>我创建了以下内容来创建具有称为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;
    }
}

地图用户

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;
    }
}

地图用户模块

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，一次向可观察集合添加大量项目很慢。 尝试将它们添加到列表中<>暂时尝试隔离问题。