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.
}
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;
}
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;
});
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()
);
}
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;
}