SqlDataReader vs DataSet

Question

I am attempting to get the information of user whenever user logged in to the website, it success when I used a DataSet, but if I want to use the SqlDataReader, the error says: Invalid attempt to read when reader is closed. I have search why is it like that and I have found an article says that

SqlDataReader requires connection remains open in order to get the data from the server, while DataSet does not need requires connection remains open.

My question is: I want to know how can I use SqlDataReader as well? So that I don't have to depends on DataSet all the times when I want to get the data from the database.

My problem is occurs when I am trying to change the structure of reading the data function using SqlDataReader, so that it can be re-usable anytime.

Here is the code:

DatabaseManager class:

public SqlDataReader GetInformationDataReader(string procName, SqlParameter[] parameters)
        {
            SqlDataReader reader = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(procName, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    if (parameters != null)
                    {
                        foreach(SqlParameter parameter in parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                    reader = cmd.ExecuteReader();
                }
            }
            return reader;
        }

Web Manager class:

public ModelContexts.InformationContext GetInformation(string username)
        {
            SqlDataReader reader = null;
            ModelContexts.InformationContext context = new ModelContexts.InformationContext();
            SqlParameter[] parameters =
            {
                new SqlParameter("@Username", SqlDbType.NVarChar, 50)
            };
            parameters[0].Value = username;
            try
            {
                reader = DatabaseManager.Instance.GetInformationDataReader("GetInformation", parameters);
                while(reader.Read())
                {
                    context.FirstName = reader["FirstName"].ToString();
                    context.LastName = reader["LastName"].ToString();
                    context.Email = reader["Email"].ToString();
                }
            }
            catch(Exception ex)
            {
                throw new ArgumentException(ex.Message);
            }
            return context;
        }

Controller:

public ActionResult MainMenu(ModelContexts.InformationContext context, string firstName, string lastName, string username, string email)
        {
            context = WebManager.Instance.GetInformation(User.Identity.Name);
            firstName = context.FirstName;
            lastName = context.LastName;
            username = User.Identity.Name;
            email = context.Email;
            return View(context);
        }

Model contains string return value with getter and setter (FirstName, LastName and Email).

View contains the html label and encode for FirstName, LastName and Email from the Model.

Appreciate your answer.

Thanks.

Answer 1

Here is an approach you can use to keep the code pretty clean that allows you to read from the SqlDataReader while the connection is still open. It takes advantage of passing delegates. Hopefully the code is understandable. You can adjust it to fit your specific needs, but hopefully it illustrates another option at your disposal.

public void GetInformationDataReader(string procName, SqlParameter[] parameters, Action<SqlDataReader> processRow)
{
    SqlDataReader reader = null;
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand cmd = new SqlCommand(procName, conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach(SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
            using (SqlDataReader dataReader = cmd.ExecuteReader())
            {
                while (dataReader.Read())
                {
                    // call delegate here.
                    processRow(dataReader);
                }
            }
        }
    }
    return reader;
}

public ModelContexts.InformationContext GetInformation(string username)
{
    SqlDataReader reader = null;
    ModelContexts.InformationContext context = new ModelContexts.InformationContext();
    SqlParameter[] parameters =
    {
        new SqlParameter("@Username", SqlDbType.NVarChar, 50)
    };
    parameters[0].Value = username;
    try
    {
        // Instead of returning a reader, pass in a delegate that will perform the work
        // on the data reader at the right time, and while the connection is still open.
        DatabaseManager.Instance.GetInformationDataReader(
            "GetInformation",
            parameters,
            reader => {
                context.FirstName = reader["FirstName"].ToString();
                context.LastName = reader["LastName"].ToString();
                context.Email = reader["Email"].ToString();
            });
    }
    catch(Exception ex)
    {
        throw new ArgumentException(ex.Message);
    }
    return context;
}

---

Brief explanation:

You'll notice that the overall structure of the code is very similar to what you already have. The only changes are:

• Instead of returning a SqlDataReader, the GetInformationDataReader() method accepts an Action<SqlDataReader> delegate.
• Within the GetInformationDataReader() method, the delegate is invoked at the correct time, while the connection is still open.
• The call to GetInformationDataReader() is modified to pass in a block of code as a delegate.

This sort of pattern can be useful for exactly these cases. It makes the code reusable, it keeps it pretty clean and separate, and it doesn't prevent you from benefiting from the using construct to avoid resource/connection leaks.

Answer 2

You have wrapped your SqlConnection object in a using clause, therefore at the end of it SqlConnect.Dispose is called, closing the connection. Whatever caller is consuming your SqlDataReader no longer has the open connection, therefore you're getting your error.

while DataSet does not need requires connection remains open.

That is not entirely correct. DataSet is just an object that is typically filled when called by SqlDataAdapter (the Fill() method of that class). The SqlDataAdapter handles the opening and closing of the SqlConnection, which is most likely why that comment states that. But it's a different class that handles that, not the DataSet itself. Think of the DataSet as just the object that holds the result set of the SqlCommand.

To answer your comment...

So, shouldn't I use using keyword for this matter? In all of the Sql keyword?

I wouldn't take that approach either. You could have a connection leak bug quite easily with that model, and running out of pooled connections could be a not-so-fun thing to troubleshoot.

Typically it's best to consume your data and then close/dispose your connection. There's a saying, "open late, close early". That's typically how you'd want to approach this. I wouldn't try to pass a SqlDataReader object between class methods for this very issue that you're dealing with. The workaround (leaving the connection open) is very error prone.

Another though process, going back to something we mentioned, don't use the SqlDataReader. You have no benefit to cyclically loop through reading each row. Depending on your result set, just fill a DataSet (or usually more appropriate, a DataTable) and return either that Data[Set | Table] or, even better, an object that is more representative of the data it pertains to.