CODEWITHSUNDEEP
c#sql-serverentity-frameworkreflection
Alireza Noori
Alireza Noori

Reputation: 15275

Generate a class based on table schema

I want to dynamically generate a class based on the results from a query that user submits. For instance, if the user enters Select name, age from tbl, the result is a name column which is string and age which is an int. The resulting class should be:

public class Test
{
   public string Name { get; set; }
   public int Age { get; set; }
}

Is there an efficient way to do this via EntityFramework or features in C# or I have to use maybe reflection to create a new type and instantiate it.

PS: My purpose is to run this query on the database and show the results in a Grid to the user and run some filter/sort/etc. on it.

Upvotes: 1

Views: 1157

Answers (1)

ashin
ashin

Reputation: 2613

You could use TypeBuilder to create a new type and execute the query against database using EF's SqlQuery() as mentioned here.

OR

A cleaner method would be to use dynamic objects to bind the grid. Extend EF to return a collection of dynamic objects as suggested by ChristineBoersen here. The code was written before EF went to RTM. Here's a version that works:

public static class EFExtensions
{
    public static IEnumerable<dynamic> CollectionFromSql(this DbContext dbContext, string Sql, Dictionary<string, object> Parameters)
    {
        using (var cmd = dbContext.Database.Connection.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;
                }
            }
        }
    }

    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 could invoke the above method as follows:

var results = context.CollectionFromSql("Select Name, Age from tbl", new Dictionary<string, object>()).ToList();
// Bind results to grid

Upvotes: 2

Related Questions