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

dataset, datatable, entity-framework, sqldatareader

Solution

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

Problem

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; } ```

Original source

Related problems