Trying to load a DataSet, but it is always null. Regular sqlReader.GetValue() is not

Question

I don't use DataSets much. Usually find myself using an ORM or just a basic sqlReader.Read() followed by some GetValues(). I'm working on some legacy code that has DataSets all over the place, and while fixing a bug was trying to DRY some of it up.

However, I can't seem to actually get the data loaded into a non-typed DataSet.

public static DataSet ExecuteStoredProcedure(string storedProcedure, DBEnum db, IEnumerable<SqlParameter> parameters)
{
    DataSet result = new DataSet();
    using (SqlConnection connection = SqlHelper.GetSqlConnection(db))
    {
        SqlCommand command = connection.CreateCommand();
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = storedProcedure;

        if (parameters != null)
            foreach (SqlParameter parameter in parameters)
                command.Parameters.Add(parameter);

        connection.Open();
        DataTable table = new DataTable();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            table.Load(reader);
        }
        result = table.DataSet; // table.DataSet is always empty!
   }
   return result;
}

I assumed table.Load(reader) does all the necessary reader.Read() calls ... but I went ahead and tried it both with and without reader.Read() before the table.Load(), to no avail.

I know that the stored procedure being called is actually returning data. If I do something like this, I see the data just fine:

using(SqlDataReader reader = command.ExecuteReader())
{
    reader.Read();
    object test = reader.GetValue(0); // returns the expected value
}

Seems like I'm missing something simple here, but I've been scratching my head over this one for a while now.

This is in .NET 3.5.

Answer 1

Hi there.

If you can, I would suggest using a SqlDataAdapter to populate the DataTable

using(SqlDataAdapter sqlDA = new SqlDataAdapter(command))
{
    sqlDA.Fill(table);
}

Cheers. Jas.

Answer 2

You logic shows the DataTable being loaded with data from the reader but DataTable is never added to a dataset.

Answer 3

I believe the dataset should be created first. In fact, you could use DataSet.Load instead of the DataTable.Load. The DataSet.Load should create data tables, but it won't work the other way around.

Answer 4

A DataSet contains DataTables not the other way round. So if you want to create a DataSet to return then you'll probably want to create a new dataset and then add your DataTable into it before returning it. If a DataTable is not in a DataSet then the reference to its parent dataset will always be null.

That having been said I do also recommend Jason Evans' suggestion of using teh SqlDataAdapter.

Answer 5

Think of a dataset as a collection of tables and optionally information about the relationships between them.

In your example code you are creating an independent Table that does not belong to a DataSet. To pragmatically create a table that is part of a dataset you could do the following:

DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add();
//or
ds.Tables.Add(MyAlreadyCreatedTable);

Jason Evans above is also correct, populating DataTables and DataSets is much simpler using SqlDataAdaptors as he demonstrated.

Finally, the Method as you have it written is meant to return a DataSet. But it only captures a single result set from the stored procedure it is calling. It's possible that a procedure could return any number of separate results.

All you should need to do is change the following:

    DataTable table = new DataTable();
    using (SqlDataReader reader = command.ExecuteReader())
    {
        table.Load(reader);
    }

to

//you can skip creating a new DataTable object
using (SqlDataAdapter da = new SqlDataAdapter(command))
{
    da.Fill(result); // the result set you created at the top
}