I'm trying to make a function to extract the values of a specific record in a database through a raw query, and i want to return an object of the type of the entity that is requested.
<(Edited: some briefing to better understanding the purpose) I'm making a kind of an Entity Framework Addon function(s) to be able to request and save any object of a respective model class on a MySqlite db created by code first approach. Here is the request but the save is basically the same. Just one function for save and one for read, for all entities. This will also save me trouble of creating a form for every model as there will be only on form that handles all models on front end. This is a WebAssembly project with a visual database management system for the Admin>
I'm looking for an elegant solution for that tiny piece of code under the commented line, ortherwise i'll have to use a switch with all types and respective tryParse on the value. If anyone knows i appreciate and hope this code would be usefull for someone.
See commented line
public async Task<object> GetClassData(string entityName, int id=0)
{
var classes = AppDomain.CurrentDomain.GetAssemblies()
.SelectMany(t => t.GetTypes())
.Where(t => t.IsClass && t.Namespace == "MecanicoAppSqlite.Shared.Models");
Type genericClassType = classes.FirstOrDefault(x => x.Name.ToLower() == entityName.ToLower());
PropertyInfo[] newClassProperties = genericClassType.GetProperties();
var tempClass = System.Reflection.Assembly.GetAssembly(genericClassType).CreateInstance(genericClassType.ToString());
string query = $"SELECT * from {entityName} WHERE Id LIKE {id}";
try
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
await context.Database.OpenConnectionAsync();
using (var reader = await command.ExecuteReaderAsync())
{
reader.Read();
int columnCount= reader.FieldCount;
for (int h = 0; h < columnCount; h )
{
string colName = reader.GetName(h);
PropertyInfo pinfo = newClassProperties.FirstOrDefault(x => x.Name.ToLower() == colName.ToLower());
Type tp = pinfo.GetType();
//This tp above is a Int32 or string or whatever. Related problem is in next line
var x = reader.IsDBNull(h) ? null : reader.GetFieldValueAsync<tp>(h).Result;
pinfo.SetValue(tempClass, x);
}
}
}
return StatusCode(200, tempClass); // Get all users
}
catch (Exception e)
{
return StatusCode(500, e);
}
}
CodePudding user response：
You should really use something like Dapper or Entity framework. But answering your question, you can use MakeGenericMethod for that:
public async Task<object> GetClassData(string entityName, int id = 0)
{
var classes = AppDomain.CurrentDomain.GetAssemblies()
.SelectMany(t => t.GetTypes())
.Where(t => t.IsClass && t.Namespace == "MecanicoAppSqlite.Shared.Models");
Type genericClassType = classes.FirstOrDefault(x => x.Name.ToLower() == entityName.ToLower());
PropertyInfo[] newClassProperties = genericClassType.GetProperties();
var tempClass = System.Reflection.Assembly.GetAssembly(genericClassType).CreateInstance(genericClassType.ToString());
string query = $"SELECT * from {entityName} WHERE Id LIKE {id}";
try
{
using (var command = context.Database.GetDbConnection().CreateCommand())
{
command.CommandText = query;
command.CommandType = CommandType.Text;
await context.Database.OpenConnectionAsync();
// Here we get the MethodInfo for the method we want to call
MethodInfo method = typeof(DbDataReaderExtensions).GetMethod("GetFieldValue");
using (var reader = await command.ExecuteReaderAsync())
{
reader.Read();
int columnCount = reader.FieldCount;
for (int h = 0; h < columnCount; h )
{
string colName = reader.GetName(h);
PropertyInfo pinfo = newClassProperties.FirstOrDefault(x => x.Name.ToLower() == colName.ToLower());
// pinfo.GetType() will give you typeof(PropertyInfo) not the type of the property
Type tp = pinfo.PropertyType;
var genericMethod = method.MakeGenericMethod(new[] { tp });
var x = reader.IsDBNull(h) ? null : genericMethod.Invoke(reader, new object[] {h});
pinfo.SetValue(tempClass, x);
}
}
}
return StatusCode(200, tempClass); // Get all users
}
catch (Exception e)
{
return StatusCode(500, e);
}
}
CodePudding user response：
I'll use a switch with all the datatypes that i use in this application for now
using (var reader = await command.ExecuteReaderAsync())
{
reader.Read();
var tempClass = System.Reflection.Assembly.GetAssembly(genericClassType).CreateInstance(genericClassType.ToString());
int numeroDeColunas = reader.FieldCount;
for(int h=0;h<numeroDeColunas;h )
{
string colName = reader.GetName(h);
PropertyInfo property = newClassProperties.FirstOrDefault(x => x.Name.ToLower() == colName.ToLower());
Type propType = property.GetType();
var x = reader.IsDBNull(h) ? "" : reader.GetFieldValueAsync<string>(h).Result;
switch (propType.Name)
{
case "Int32":
int iv;
if (!Int32.TryParse(x, out iv)) throw new ApplicationException("Unable to parse at ValuesController>GetAll");
property.SetValue(tempClass, iv);
break;
case "String":
property.SetValue(tempClass, x);
break;
case "DateTime":
DateTime dv;
if (!DateTime.TryParse(x, out dv)) throw new ApplicationException("Unable to parse at ValuesController>GetAll");
property.SetValue(tempClass, dv);
break;
case "TimeSpan":
TimeSpan tv;
if (!TimeSpan.TryParse(x, out tv)) throw new ApplicationException("Unable to parse at ValuesController>GetAll");
property.SetValue(tempClass, tv);
break;
default:
Console.WriteLine("Subject is C#");
break;
}
}
}