using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.Entity;
using System.Dynamic;
using System.Linq;
namespace ConsoleApp1
{
public class Program
{
static void Main(string[] args)
{
FactureEntities fe = new FactureEntities();
List<dynamic> results = SQLHelpers.DynamicListFromSql(fe, "select a.nom as a_nom, a.prix as a_prix, c.nom as c_nom from article a inner join categorie c on a.categorieid = c.id where a.id > @xxx", new Dictionary<string, object> { { "xxx", 0 } }).ToList();
foreach (var r in results)
{
Console.WriteLine($"{r.a_nom} - {r.a_prix} - {r.c_nom}");
}
Console.ReadLine();
}
}
public static class SQLHelpers
{
public static IEnumerable<dynamic> DynamicListFromSql(this DbContext db, string Sql, Dictionary<string, object> Params = null)
{
using (var cmd = db.Database.GetDbConnection().CreateCommand())
{
cmd.CommandTimeout = 300;
cmd.CommandText = Sql;
if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); }
if (Params != null)
{
foreach (KeyValuePair<string, object> p in Params)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = p.Key;
dbParameter.Value = p.Value;
cmd.Parameters.Add(dbParameter);
}
}
using (var dataReader = cmd.ExecuteReader())
{
List<string> listNameFields = null;
while (dataReader.Read())
{
listNameFields = new List<string>();
var row = new ExpandoObject() as IDictionary<string, object>;
for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
{
int i = 1;
string fieldName = dataReader.GetName(fieldCount);
//pour éviter l'erreur de plusieurs noms de champs avec le même nom (duplicate key field)
if (listNameFields.Any(l => l == dataReader.GetName(fieldCount)))
{
fieldName += i++;
}
listNameFields.Add(fieldName);
row.Add(fieldName, dataReader[fieldCount]);
}
yield return row;
}
}
}
}
}
}
