Returning datatable using entity framework
ado.net-entity-data-model, c#, entity-framework
Solution
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;
}
Problem
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. } ```