Returning a DataTable using Entity Framework ExecuteStoreQuery
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;
}
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.
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;
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
