Returning datatable using entity framework

Question

I am using entity framework. There is one particular situation in my application where I have to use a stored procedure. Since there are a lot of SQL statements written in the SP, I don't want to re-write it in my C# code. I only need to get the result back in the form of a datatable. I have written a little bit of code but I am stuck at one point. Can someone complete the code below?

using (dbContext.Database.Connection)
{
dbContext.Database.Connection.Open();
DbCommand cmdItems= dbContext.Database.Connection.CreateCommand();
cmdItems.CommandText = "GetAvailableItems";
cmdItems.CommandType = CommandType.StoredProcedure;
cmdItems.Parameters.Add(new SqlParameter("jobCardId", 100525));
//Need to write code below to populate a DataTable.
}

Answer 1

Thanks a lot guys. I solved it. Here is the solution:

using (var context = new DataBaseContext())
{
    var dt = new DataTable();
    var conn = context.Database.Connection;
    var connectionState = conn.State;
    try
    {
        if (connectionState != ConnectionState.Open) conn.Open();
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = "GetAvailableItems";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("jobCardId", 100525));
            using (var reader = cmd.ExecuteReader())
            {
                dt.Load(reader);
            }
        }
    }
    catch (Exception ex)
    {
        // error handling
        throw;
    }
    finally
    {
        if (connectionState != ConnectionState.Closed) conn.Close();
    }
    return dt;
}

Answer 2

This example will return a datatable object selecting data from EntityFramework.

I believe this is the best solution to the goal. However the problem with this solution is that each record is enumerated. You might want to filter the list first then run this from the list to avoid that.

DataTable dt = new DataTable();
(from rec in database.Table.AsEnumerable()
                     select new
                     {
                         id = rec.id,
                         name = rec.Name
                         //etc
                     }).Aggregate(table, (dt, r) =>
                     {
                         dt.Rows.Add(r.id, r.Name);
                         return dt;
                     });

Answer 3

This solution is simple, very fast and easy to use.

Create a DbContext extension:

using System.Data;
using System.Data.Common;
using System.Data.Entity;
..
..
public static class DbContextExtensions
{
    public static DataTable DataTable(this DbContext context, string sqlQuery)
    {
        DbProviderFactory dbFactory = DbProviderFactories.GetFactory(context.Database.Connection);

        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = context.Database.Connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sqlQuery;
            using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;

                DataTable dt = new DataTable();
                adapter.Fill(dt);

                return dt;
            }
        }
    }
}

Examples:

using (MyDbContext db = new MyDbContext())
{
    string query = db.Students.Where(o => o.Age > 20).ToString();

    DataTable dataTable = db.DataTable(query);

    ..

    DataTable dt = db.DataTable(
                         (  from o in db.Studets
                            where o.Age > 20
                            select o
                         ).ToString()
                    );
}

Answer 4

Just improving the previous solution, now including generic parameters (not SQL Server specific) and mutiple resultsets support:

    DataSet GetDataSet(string sql, CommandType commandType, Dictionary<string, Object> parameters)
    {
        // creates resulting dataset
        var result = new DataSet();

        // creates a data access context (DbContext descendant)
        using (var context = new MyDbContext())
        {
            // creates a Command 
            var cmd = context.Database.Connection.CreateCommand();
            cmd.CommandType = commandType;
            cmd.CommandText = sql;

            // adds all parameters
            foreach (var pr in parameters)
            {
                var p = cmd.CreateParameter();
                p.ParameterName = pr.Key;
                p.Value = pr.Value;
                cmd.Parameters.Add(p);
            }

            try
            {
                // executes
                context.Database.Connection.Open();
                var reader = cmd.ExecuteReader();

                // loop through all resultsets (considering that it's possible to have more than one)
                do
                {
                    // loads the DataTable (schema will be fetch automatically)
                    var tb = new DataTable();
                    tb.Load(reader);
                    result.Tables.Add(tb);

                } while (!reader.IsClosed);
            }
            finally
            {
                // closes the connection
                context.Database.Connection.Close();
            }
        }

        // returns the DataSet
        return result;
    }

Answer 5

I added the following method into my DataContext class:

public async Task<DataTable> ExecReturnQuery(string query)
    {
        using (var command = this.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = query;

            this.Database.OpenConnection();

            using (var result = await command.ExecuteReaderAsync())
            {
                var table = new DataTable();
                table.Load(result);

                // returning DataTable (instead of DbDataReader), cause can't use DbDataReader after CloseConnection().
                this.Database.CloseConnection();

                return table;
            }
        }
    }

Then I call it from any class (where I'm injecting DataContext class) like so:

DataTable myTableRecords = await _dataContext.ExecReturnQuery("SELECT * FROM MyTable");

Answer 6

I just mixed the answers. this code run a dynamic query and convert result to dictionary list.

public List < Dictionary < string,object >> DataTableToDictionaryList(DataTable table) {
    List < Dictionary < string,
    object >> parentRow = new List < Dictionary < string,
    object >> ();
    Dictionary < string,
    object > childRow;
    foreach(DataRow row in table.Rows) {
        childRow = new Dictionary < string,
        object > ();
        foreach(DataColumn col in table.Columns) {
            childRow.Add(col.ColumnName, row[col]);
        }
        parentRow.Add(childRow);
    }
    return (parentRow);
}

List < Dictionary < string,object >> RunDynamicQuery(string sql, Dictionary < string, Object > parameters = null, int resultSet = 0, CommandType commandType = CommandType.Text) {
    // creates resulting dataset
    var resultDataSet = new DataSet();

    // creates a data access context (DbContext descendant)
    using(var context = new DataDbContext()) {
        // creates a Command 
        var conn = context.Database.Connection;
        var cmd = conn.CreateCommand();
        cmd.CommandType = commandType;
        cmd.CommandText = sql;

        if (parameters != null) {
            // adds all parameters
            foreach(var pr in parameters) {
                var p = cmd.CreateParameter();
                p.ParameterName = pr.Key;
                p.Value = pr.Value;
                cmd.Parameters.Add(p);
            }
        }

        try {
            // executes
            if (conn.State != ConnectionState.Open) {
                conn.Open();
            }

            var reader = cmd.ExecuteReader();

            // loop through all resultsets (considering that it's possible to have more than one)
            int currentResultSet = -1;
            do {
                currentResultSet++;
                //skip lower resultsets
                if (resultSet > currentResultSet) {
                    continue;
                }

                // loads the DataTable (schema will be fetch automatically)
                var tb = new DataTable();
                tb.Load(reader);
                resultDataSet.Tables.Add(tb);
                //make sure to get only one result set
                break;
            } while (! reader . IsClosed );

        }
        finally {
            // closes the connection
            context.Database.Connection.Close();
        }
    }

    return DataTableToDictionaryList(resultDataSet.Tables[0]);
}