Execute stored procedure in Entity Framework, return List<DataTable> or DataSet

Question

How do I modify the below method to return a List< DataTable> or a DataSet? I want to make it generic so it can return multiple resultsets from the database.

public static DataTable ExecuteStoredProcedure(ObjectContext db, string storedProcedureName, IEnumerable<SqlParameter> parameters)
    {
        var entityConnection = (EntityConnection) db.Connection;
        var conn = entityConnection.StoreConnection;
        var initialState = conn.State;
        var dt = new DataTable();

        try
        {
            if (initialState != ConnectionState.Open)
                conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = storedProcedureName;
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (var parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }

                using (var reader = cmd.ExecuteReader())
                {
                    dt.Load(reader);
                    reader.Close();
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (initialState != ConnectionState.Open)
                conn.Close();
        }
        return dt;
    }

Solution 1

I ended up doing it like this -

public static DataSet ExecuteStoredProcedure(ObjectContext db, string storedProcedureName, IEnumerable<SqlParameter> parameters)
    {
        var connectionString = ((EntityConnection)db.Connection).StoreConnection.ConnectionString;
        var ds = new DataSet();

        using (var conn = new SqlConnection(connectionString))
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = storedProcedureName;
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (var parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }

                using (var adapter = new SqlDataAdapter(cmd))
                {
                    adapter.Fill(ds);
                }
            }
        }

        return ds;
    }

Solution 2

reader.NextResult() should technically work. Here is the modified version:

public static DataSet ExecuteStoredProcedure(ObjectContext db, string storedProcedureName, IEnumerable<SqlParameter> parameters)
{
    var entityConnection = (EntityConnection) db.Connection;
        var conn = entityConnection.StoreConnection;
        var initialState = conn.State;

        DataSet dataSet = new DataSet();

        try
        {
            if (initialState != ConnectionState.Open)
                conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = storedProcedureName;
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (var parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }

                using (var reader = cmd.ExecuteReader())
                {
                    do
                    {
                        DataTable dt = new DataTable();
                        dt.Load(reader);
                        dataSet.Tables.Add(dt);
                    }
                    while (reader.NextResult());
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (initialState != ConnectionState.Open)
                conn.Close();
        }
        return dataSet;
}

Comments

[tempid]

@davidair: I get this error "Invalid attempt to call NextResult when reader is closed." My stored procedure is returning three resultsets.

[tempid]

@davidair: The code returned 1st and 3rd resultsets. In the first iteration, I got the 1st resultset, and in the second iteration, I got the 3rd. And it blows up at while after the second iteration.

[tempid]

I just found out that after loading a datatable, the NextResult is automatically executed behind the scenes! And in the while code, since we again specific NextResult, it skipped one more result set. More info - amitchandnz.wordpress.com/2011/09/28/…

[tempid]

From MSDN - "The Load method consumes the first result set from the loaded IDataReader, and after successful completion, sets the reader's position to the next result set, if any." How do I get around this? Thanks!

[Thomas Levesque]

@enigma, the reader will be closed only if there is no next result set. So you can just replace while (reader.NextResult()) with while (!reader.IsClosed)