SqlDataReader problems

Question

I have a problem when storing data into a SqlDataReader. When i assing the sdr variable, at the point where connection.close(), the sdr variable becomes empty. Why?

   string strConnection = ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString;

   SqlDataReader sdr = null;

    using (SqlConnection connection = new SqlConnection(strConnection))
    {
        connection.Open();
        using (SqlCommand cmd = new SqlCommand("GetProducts", connection))
        {


            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            sdr = cmd.ExecuteReader();


        }
        connection.Close();
    }

    return (sdr);

BFree · Accepted Answer · 2011-02-15 22:17:14Z

3

You need to iterate over the reader in a loop while calling Read:

while(sdr.Read())
{
   var someValue = sdr["SomeValue"]; //Where SomeValue is the column name you're expecting from the DB
}

All that ExecuteReader does, is it returns a DataReader object to you, however it's on you to go through each row returned and pull the values out of it. If you want to get it all in one shot, you can use a SqlDataAdapter:

var adapter = new SqlDataAdapter(command);
var table = new DataTable();
adapter.Fill(table);

answered Feb 15, 2011 at 22:17

BFree

104k21 gold badges164 silver badges205 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user532104 Over a year ago

Is it not possible to return the value back as a SqlDataReader?

BFree Over a year ago

@user: You can do that, however you need to keep the connection open. Once you close the connection, the Reader has no way to keep reading from the DB...

Joel Coehoorn · Accepted Answer · 2011-02-15 22:36:39Z

You need an open and active connection while iterating over the DataReader. If you close it before returning the data reader, it won't work. What I usually do to get around this is turn the DataReader into an IEnumerable, like so:

public IEnumerable<IDataRecord> GetProductsFromDB()
{
    string strConnection = ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString;

    using (SqlConnection connection = new SqlConnection(strConnection))
    using (SqlCommand cmd = new SqlCommand("GetProducts", connection))
    {
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        connection.Open();
        using (var sdr = cmd.ExecuteReader())
        {
            while (sdr.Read())
            {
                yield return sdr;
            }
        }    
    }
}

Notice that I also changed the order around a bit: wait as long as possible to open the connection, and put the connection and command creation next to each to avoid so much nesting.

This pattern opens up a whole new world for the way you write your data access code, because now suddenly your raw sql queries and stored procedure calls works with the linq-to-objects operators. You can do some cool like this:

foreach (var product in GetProductsFromDB()
           .Select(i => CreateProductFromDataRow(i))
           .Where(p => p.IsOnSale()) )
{
    // do something with products that are on sale
}

Collectives™ on Stack Overflow

SqlDataReader problems

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related