SqlDataReader problems

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

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

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
}