数据读取器已打开

发布于 2024-12-27 23:32:06 字数 1002 浏览 0 评论 0原文

我遇到错误，提示我的数据读取器已打开。

我的代码如下所示

public static Users GetByID(int ID, SqlConnection connection)
    {
        SqlCommand command = new SqlCommand("Select Name, Email, LastLogin, FK_Role_ID from Users where ID=@id");
        command.Connection = connection;

        command.Parameters.Add(new SqlParameter("id", ID));

        SqlDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
            Users user = new Users();
            user.ID = ID;
            user.Name = reader.GetString(0);
            user.Email = reader.GetString(1);
            user.LastLogin = reader.GetString(2);
            user.role = Role.GetRoleByID(reader.GetInt32(3), connection);
            reader.Close();
            return user;
        }
        else
        {
            reader.Close();
            return null;
        }
    }

Role.GetRoleByID 中出现错误，表示 datareader 命令已打开。这是真的，但是我如何使用读者提供的信息调用 Role.GetRoleByID 。

我用 C# 和 ASP.NET 编写代码

原文

I got a problem with an error that say that my datareader is already open.

My code looks like this

public static Users GetByID(int ID, SqlConnection connection)
    {
        SqlCommand command = new SqlCommand("Select Name, Email, LastLogin, FK_Role_ID from Users where ID=@id");
        command.Connection = connection;

        command.Parameters.Add(new SqlParameter("id", ID));

        SqlDataReader reader = command.ExecuteReader();
        if (reader.Read())
        {
            Users user = new Users();
            user.ID = ID;
            user.Name = reader.GetString(0);
            user.Email = reader.GetString(1);
            user.LastLogin = reader.GetString(2);
            user.role = Role.GetRoleByID(reader.GetInt32(3), connection);
            reader.Close();
            return user;
        }
        else
        {
            reader.Close();
            return null;
        }
    }

The error occours in the Role.GetRoleByID saying that the datareader command is alreader open. Which is true, but how do I call Role.GetRoleByID with the information from my reader.

I code in c# and ASP.NET

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

强者自强 2025-01-03 23:32:06

看起来您的 Role.GetRoleByID 将尝试重用该连接。

选项：

从 GetByID 内的 SqlDataReader 获取所需的数据，关闭该读取器，然后然后调用 Role.GetRoleByID （所以你一次只有一个活跃的读者）
启用多个活动结果集（MARS） - 我不能说我有任何这方面的经验
让每个方法使用单独的连接来减少方法之间的依赖关系。请注意，连接池将使打开/关闭变得相当便宜。

如果我是你，我会选择第一个选择——或者可能是最后一个。我还会使用 using 语句自动关闭阅读器：

private const string GetUserByIdSql =
    "Select Name, Email, LastLogin, FK_Role_ID from Users where ID=@id";

public static Users GetByID(int ID, SqlConnection connection)
{
    var sql = ;
    Users user;
    int roleId;
    using (var command = new SqlCommand(GetUserByIdSql, connection))
    {
        command.Parameters.Add(new SqlParameter("id", ID));
        using (var reader = command.ExecuteReader())
        {
            if (!reader.Read())
            {
                return null;
            }
            user = new Users
            {
                Name = reader.GetString(0),
                Email = reader.GetString(1),
                LastLogin = reader.GetString(2),
            };
            // Remember this so we can call GetRoleByID after closing the reader
            roleID = reader.GetInt32(3);
        }
    }
    user.Role = Role.GetRoleByID(roleID, connection);
    return user;
}

作为第四个选项 - 为什么不在现有查询中执行 GetRoleByID 所需的联接？这意味着您只需要访问一次数据库。

It looks like your Role.GetRoleByID will try to reuse the connection.

Options:

Fetch the data you need from the SqlDataReader within GetByID, close that reader, and then call Role.GetRoleByID (so you've only got one active reader at a time)
Enable Multiple Active ResultSets (MARS) - I can't say I have any experience of that
Make each method use a separate connection to reduce the dependencies between the methods. Note that the connection pool will make the opening/closing reasonably cheap.

I'd go with the first option if I were you - or possibly the last. I'd also use a using statement to close the reader automatically:

private const string GetUserByIdSql =
    "Select Name, Email, LastLogin, FK_Role_ID from Users where ID=@id";

public static Users GetByID(int ID, SqlConnection connection)
{
    var sql = ;
    Users user;
    int roleId;
    using (var command = new SqlCommand(GetUserByIdSql, connection))
    {
        command.Parameters.Add(new SqlParameter("id", ID));
        using (var reader = command.ExecuteReader())
        {
            if (!reader.Read())
            {
                return null;
            }
            user = new Users
            {
                Name = reader.GetString(0),
                Email = reader.GetString(1),
                LastLogin = reader.GetString(2),
            };
            // Remember this so we can call GetRoleByID after closing the reader
            roleID = reader.GetInt32(3);
        }
    }
    user.Role = Role.GetRoleByID(roleID, connection);
    return user;
}

As a fourth option - why not just perform the join required by GetRoleByID in your existing query? That would mean you'd only need one trip to the database.

回复收藏 0 原文