从C＃中的查询填充类的最简单方法

问题描述：

我想从查询中填充类。
这是一个示例类：

I want to populate a class from a query. this is an example class:

private class TVChannelObject
{
    public string number { get; set; }
    public string title { get; set; }
    public string favoriteChannel { get; set; }
    public string description { get; set; }
    public string packageid { get; set; }
    public string format { get; set; }

}

如何从数据库查询中填充此类？只要表列名称与类属性相同，是否可以自动执行此操作？

How can I fill this class from an database query? Is there any way to do this automatically as far as table column names are identical to class attributes?

答

ORM是最好的方法。但是，您可以使用反射来实现此功能：

As others have suggested, an ORM is the best way to go. You could, however, implement this functionality using reflection:

using System.Reflection;

void Main()
{
    var connectionString = "...";
    var records = new Query(connectionString).SqlQuery<TVChannel>("select top 10 * from TVChannel");
}

private class TVChannel
{
    public string number { get; set; }
    public string title { get; set; }
    public string favoriteChannel { get; set; }
    public string description { get; set; }
    public string packageid { get; set; }
    public string format { get; set; }
}

public class Query
{
    private readonly string _connectionString;

    public Query(string connectionString)
    {
        _connectionString = connectionString;
    }

    public List<T> SqlQuery<T>(string query)
    {
        var result = new List<T>();

        using (var connection = new SqlConnection(_connectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = query;
                using (var reader = command.ExecuteReader())
                {
                    var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToArray();
                    var properties = typeof(T).GetProperties();

                    while (reader.Read())
                    {
                        var data = new object[reader.FieldCount];
                        reader.GetValues(data);

                        var instance = (T)Activator.CreateInstance(typeof(T));

                        for (var i = 0; i < data.Length; ++i)
                        {
                            if (data[i] == DBNull.Value)
                            {
                                data[i] = null;
                            }

                            var property = properties.SingleOrDefault(x => x.Name.Equals(columns[i], StringComparison.InvariantCultureIgnoreCase));

                            if (property != null)
                            {
                                property.SetValue(instance, Convert.ChangeType(data[i], property.PropertyType));
                            }
                        }
                        result.Add(instance);
                    }
                }
            }
        }
        return result;
    }
}

从C＃中的查询填充类的最简单方法

相关推荐