Why, why, why I hear you say would you want to use Datasets with the Entity Framework?

I’m not here to debate the whys and wherefores for doing this, just finding a simple solution to the problem.

99.9% of the time I would say don’t, but if you have a legacy system and all the code is driven by DataSet’s you have many choices.

1. Rewrite all the business logic code to support EF
2. Use an old style connection to the database
3. Use DataSet’s via EF connection

Each has their issues, the first could take some time, and would need full testing to ensure nothing had changed.

Using an old style database connection, you might as well just use the old legacy code.

What I’m going to be covering here is being able to call a Stored Procedure using the Entity Framework and return a dataset, this way the legacy code remains the same but allow you to utilise the Entity Framework for your connection.

Let’s get to work; first, we will create a Database Manager that will receive the Entity Framework DbContext

public class DatabaseManager
 {
 private readonly DbContext _context;

public DatabaseManager(DbContext context)
 {
 _context = context;
 }
 }

Then we need to call a Stored Procedure using the Entity Framework DbContext connection:

new SqlConnection(_context.Database.Connection.ConnectionString)

I won’t go over creating the command with its parameters, as this is assumed you know how this works already.  What I will mention is the SqlDataAdapter that calls the Stored Procedure and returns the DataSet

using (var adapter = new SqlDataAdapter(command))
 {
 adapter.Fill(dataSet);
 }

All you have to do now is return the dataSet and you are done.  It is that easy, here is the full code base:  DataSetEF