如何从数据库中动态设置值

My search queries will always return one row and put it into a datatable.

我的搜索查询将始终返回一行并将其放入数据表中。

Is there a way that I can check if the column name and the property name matches, then set the value?

有没有办法可以检查列名和属性名是否匹配,然后设置值?

For example:

    SELECT * FROM mytable WHERE ID = 10;

Query returns column names: "ID, ScriptName, Type, Flags, VerifiedBuild" and their values.

查询返回列名:“ID,ScriptName,Type,Flags,VerifiedBuild”及其值。

Then run a foreach statement checking if the column name and property name match and set the property values below.

然后运行foreach语句,检查列名和属性名是否匹配,并设置下面的属性值。

    public int ID { get; set; }
    public int ScriptName { get; set; }
    public int Type { get; set; }
    public int Flags { get; set; }
    public int VerifiedBuild { get; set; }

3 个解决方案

#1

You can do it very easly using EntityLite, at its core, EntityLite can materialize entities from data readers. This funtionallity is publically exposed.

您可以使用EntityLite轻松地完成它,EntityLite的核心是EntityLite可以实现数据读取器中的实体。这种功能性公开暴露。

Imagine you have a method like the following;

想象一下,你有一个像下面这样的方法;

public IDataReader GetDataReaderFromQuery()
{
    //TODO: implement this method
    throw new NotImplementedException();
}

And one POCO entity:

还有一个POCO实体:

public class MyEntity 
{
    // properties go here
}

you can use FisrtOrDefault, ToEnumberable and ToList extension methods on the datareader to get POCO entities:

您可以在datareader上使用FisrtOrDefault,ToEnumberable和ToList扩展方法来获取POCO实体:

using inercya.EntityLite;
using inercya.EntityLite.Extensions;

//......

MyEntity SomeMethod()
{
    using (reader = GetDataReaderFromQuery())
    {

         return reader.FirstOrDefault<MyEntity>();
    }
}

EntityLite will build a method at runtime using DynamicMethod that materializes an entity of that type from that specific datareader. The method is cached, so if you need to materialize more entities of that type from datareaders with the same schema, the method is reused. It is almost as fast as de best handwritten code.

EntityLite将在运行时使用DynamicMethod构建一个方法,该方法从该特定的datareader实现该类型的实体。该方法被缓存,因此如果您需要从具有相同模式的数据引用器实现该类型的更多实体,则该方法将被重用。它几乎和最好的手写代码一样快。

#2

With the help of FastDynamic (a tiny library that helps you to efficiently create objects and to access properties of compile-time-unkonwn types), you could write the following data reader extension methods:

借助FastDynamic(一个可以帮助您高效创建对象并访问编译时unkonwn类型属性的小型库),您可以编写以下数据读取器扩展方法:

using FastDynamic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sample
{
    public static class DataReaderExtensions
    {
        private static void SetPropertiesFromDataReader(IDataReader reader, object entity)
        {
            if (reader == null || entity == null) throw new ArgumentNullException();
            Type entityType = entity.GetType();
            var setters = entityType.GetSetters();
            for (int fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
            {
                var fieldName = reader.GetName(fieldIndex);
                Setter setter = null;
                if (!string.IsNullOrEmpty(fieldName) && setters.TryGetValue(fieldName, out setter))
                {
                    if (!reader.IsDBNull(fieldIndex))
                    {
                        setter(entity, reader.GetValue(i));
                    }
                }
            }
        }

        public static IEnumerable<T> ToEnumberable<T>(this IDataReader reader) where T:class, new()
        {
            Type entityType = typeof(T);
            Func<object> activator = entityType.GetActivator();
            while (reader.Read())
            {
                var entity = activator();
                SetPropertiesFromDataReader(reader, entity);
                yield return (T)entity;
            }
        }

        public T FirstOrDefault<T>(this IDataReader reader) where T : class, new()
        {
            return reader.ToEnumberable<T>().FirstOrDefault();
        }

        public List<T> ToList<T>(this IDataReader reader) where T : class, new()
        {
            return reader.ToEnumberable<T>().ToList();
        }

    }
}

#3

Yes. You need to use reflection. By using reflection you can get parameter names in an array and you can compare names in a for loop.

是。你需要使用反射。通过使用反射,您可以在数组中获取参数名称,并且可以比较for循环中的名称。

ParameterInfo

#1