Return datatable using entity structure

Question

I use the framework entity. There is one specific situation in my application when I have to use a stored procedure. Since SP has many SQL statements, I don’t want to rewrite them in my C # code. I need to return the result only in the form of data. I wrote some code, but I was stuck at some point. Can someone fill in 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 returns a datatable that selects data from EntityFramework .

I believe that this is the best solution to this goal. However, the problem with this solution is that each record is listed. First you can filter the list first and then run it from the list to avoid this.

 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 the 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 general parameters (not specific to SQL Server) and support for several results:

  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 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; }