Using Entity Framework ExecuteStoreQuery to return a DataTable

code-first ef-code-first entity-framework stored-procedures

Question

I am working with a system that has many stored procedures that need to be displayed. Creating entities for each of my objects is not practical.

Is it possible and how would I return a DataTable using ExecuteStoreQuery ?

public ObjectResult<DataTable> MethodName(string fileSetName) {
using (var dataContext = new DataContext(_connectionString))
{
var returnDataTable = ((IObjectContextAdapter)dataContext).ObjectContext.ExecuteStoreQuery<DataTable>("SP_NAME","SP_PARAM");
return returnDataTable;
}
1
16
11/5/2013 7:07:28 PM

Accepted Answer

No, I don't think that'll work - Entity Framework is geared towards returning entities and isn't meant to return DataTable objects.

If you need DataTable objects, use straight ADO.NET instead.

11
1/3/2011 5:45:59 PM

Popular Answer

Yes it's possible, but it should be used for just dynamic result-set or raw SQL.

public DataTable ExecuteStoreQuery(string commandText, params Object[] parameters)
{
    DataTable retVal = new DataTable();
    retVal = context.ExecuteStoreQuery<DataTable>(commandText, parameters).FirstOrDefault();
    return retVal;
}

Edit: It's better to use classical ADO.NET to get the data model rather than using Entity Framework because most probably you cannot use DataTable even if you can run the method: context.ExecuteStoreQuery<DataTable>(commandText, parameters).FirstOrDefault();

ADO.NET Example:

public DataSet GetResultReport(int questionId)
{
    DataSet retVal = new DataSet();
    EntityConnection entityConn = (EntityConnection)context.Connection;
    SqlConnection sqlConn = (SqlConnection)entityConn.StoreConnection;
    SqlCommand cmdReport = new SqlCommand([YourSpName], sqlConn);
    SqlDataAdapter daReport = new SqlDataAdapter(cmdReport);
    using (cmdReport)
    {
        SqlParameter questionIdPrm = new SqlParameter("QuestionId", questionId);
        cmdReport.CommandType = CommandType.StoredProcedure;
        cmdReport.Parameters.Add(questionIdPrm);
        daReport.Fill(retVal);
    }
    return retVal;
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow