I create dynamically table when website is active with this code:
public void CreateTable(string tableName, string field1, double field2, ...)
{
string Raw = string.Format("CREATE TABLE [{0}] ([ID] UNIQUEIDENTIFIER NOT NULL, [{1}] NVARCHAR (MAX) NOT NULL, [{2}] FLOAT NOT NULL, ..., , CONSTRAINT[PK_{0}] PRIMARY KEY CLUSTERED([ID] ASC);", tableName, field1, field2);
_context.Database.ExecuteSqlRaw(Raw);
_context.SaveChanges();
}
Now I want run query SQL select something like this::
private readonly ApplicationDbContext _context;
public HomeController(ApplicationDbContext context)
{
_context = context;
}
public void List(string tableName)
{
string Raw = string.Format("SELECT * FROM [{0}];", tableName);
var table = _context.Database.ExecuteSqlRaw(Raw);
//--- Do something with table here
}
NOTE: I try using reflection like this:
var table = _context.GetType().GetProperty(tableName).GetValue(_context) as IQueryable;
But not working and return NULL.
I'm still a newbie on C#, really appreciate if you can show me example based on this code.
you can do it with a Command with to get dynamic objects.
first create a function to get the datarow.
public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
{
using (var cmd = dbContext.Database.GetDbConnection().CreateCommand())
{
cmd.CommandText = Sql;
if (cmd.Connection.State != ConnectionState.Open)
cmd.Connection.Open();
foreach (KeyValuePair<string, object> param in Parameters)
{
DbParameter dbParameter = cmd.CreateParameter();
dbParameter.ParameterName = param.Key;
dbParameter.Value = param.Value;
cmd.Parameters.Add(dbParameter);
}
//var retObject = new List<dynamic>();
using (var dataReader = cmd.ExecuteReader())
{
while (dataReader.Read())
{
var dataRow = GetDataRow(dataReader);
yield return dataRow ;
}
}
}
}
Then add the data to the dynamic objects.
private static dynamic GetDataRow(DbDataReader dataReader)
{
var dataRow = new ExpandoObject() as IDictionary<string, object>;
for (var fieldCount = 0; fieldCount < dataReader.FieldCount; fieldCount++)
dataRow.Add(dataReader.GetName(fieldCount), dataReader[fieldCount]);
return dataRow;
}
you can use like this:
List<dynamic> MyList = MyDbContext.CollectionFromSql("SELECT * FROM @tableName",
new Dictionary<string, object> { { "@tableName", tableName } }).ToList();