如何将 SQL 查询结果映射到对象上？

Question

目前，我正在使用类似以下的代码：

    try
    {
      dr = SQL.Execute(sql);

      if(dr != null) {
         while(dr.Read()) {
           CustomObject c = new CustomObject();
           c.Key = dr[0].ToString();
           c.Value = dr[1].ToString();
           c.Meta = dr[2].ToString();
           customerInfo.CustomerList.Add(c);
         }
      }
      else
      {
          customerInfo.ErrorDetails="No records found";
      } 

有没有一种方法可以直接进行这种映射，而不是让我手动完成任务（假设列名与字段名匹配）。

然而，我的要求之一是希望通过当前使用SQL查询的方式来实现，而不是使用纯LINQ方法。首先，SQL查询已经足够复杂，涉及到复杂的JOIN操作，并经过了充分的测试，因此我暂时不想引入更多的错误。有什么建议吗？

- Legend

---

请注意，您可以使用自定义 SQL 查询与 LINQ，并且它将自动将查询结果中的字段映射到您提供的通用对象类型。请参阅DataContext.ExecuteQuery。 - mellamokb

@mellamokb：你能给我展示一下吗？或者至少指点我一些资源，或者告诉我应该搜索什么？ - Legend

请查看我添加的链接，那是你需要的具体方法。该页面上还有示例。 - mellamokb

@mellamokb：哇...请将此作为答案添加。这将帮助其他寻找类似内容的人。 - Legend

看看由Sam Saffron编写的Dapper.NET（他在Stackoverflow上工作时编写） - marc_s

Answer 1

一个简单的解决方案是为你的CustomObject创建一个构造函数，该构造函数接受一个DataRow（来自示例，如果是另一个类，请更正我）。

在你的新构造函数中，按照你在自己的示例中所做的那样操作。

public CustomObject(DataRow row)
{
    Key = row[0].ToString();
    // And so on...
}

另一种方法是引入泛型，并在您的SQL类中创建一个新函数。

例如（摘自Passing arguments to C# generic new() of templated type）：

// This function should reside in your SQL-class.
public IEnumerable<T> ExecuteObject<T>(string sql)
{
    List<T> items = new List<T>();
    var data = ExecuteDataTable(sql); // You probably need to build a ExecuteDataTable for your SQL-class.
    foreach(var row in data.Rows)
    {
        T item = (T)Activator.CreateInstance(typeof(T), row);
        items.Add(item);
    }
    return items;
}

使用示例：

public IEnumerable<CustomObject> GetCustomObjects()
{
    return SQL.ExecuteObject<CustomObject>("SELECT * FROM CustomObject");
}

我已经在LinqPad中测试过这段代码，应该可以工作。

- NoLifeKing

Answer 2

您可以通过创建一个通用方法来满足您的需求。同时，您可以将新方法作为数据表的扩展。

    public static List<T> ToList<T>(this DataTable table) where T : class, new()
{
    try
    {
        List<T> list = new List<T>();

        foreach (var row in table.AsEnumerable())
        {
            T obj = new T();

            foreach (var prop in obj.GetType().GetProperties())
            {
                try
                {
                    PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                    propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                }
                catch
                {
                    continue;
                }
            }

            list.Add(obj);
        }

        return list;
    }
    catch
    {
        return null;
    }
}

使用:

    DataTable dtCustomer = GetCustomers();
    List<CustomObject> CustomObjectList = dtCustomer.ToList<CustomObject>();

- user1553525

---

GetCustomers看起来是什么样子？ - Demodave

我很害怕一个包含try, catch {continue;}的答案竟然有赞数... - A.Rowan

@A.Rowan 公平地说，属性设置失败并不是问题所在，而是未能跟踪尝试设置属性时出现错误的事实。 - Hazel へいぜる

Answer 3

你应该研究一下MicroORMs。与常规的ORM不同，它们不提供必须使用的SDL，而是允许您使用自己的SQL查询，并仅提供从SQL结果集到C#对象和从C#对象到SQL参数的映射。

我的最爱是PetaPoco，它还提供了一个查询构建器，使用您自己的SQL，但对参数数字进行了一些巧妙的操作。

- Idan Arye

Answer 4

用户1553525的回答很好，但是如果列名与属性名不完全匹配，则不起作用。

因此，您首先需要创建一个自定义属性。然后在您要反序列化的类中使用该属性，最后将DataTable反序列化。

自定义属性

我们创建一个自定义属性，并将其应用于类内的属性。 我们创建的类具有Name属性，稍后我们将使用它来从DataTable获取正确的列。

[AttributeUsage(AttributeTargets.Property, Inherited = false)]
public class MySqlColName : Attribute
{
    private string _name = "";
    public string Name { get => _name; set => _name = value; }

    public MySqlColName(string name)
    {
        _name = name;
    }
}

需要反序列化的类

接下来，在我们将要填充的类中，我们将使用刚刚创建的属性 [MySqlColName] 声明将链接到类中属性的列名。

然而，如果属性名与数据库列名相同，则不需要在属性中指定列名，因为 .ToList<>() 函数会从属性名推断出列名。

public class EventInfo
{
    [MySqlColName("ID")]
    public int EventID { get; set; }

    //Notice there is no attribute on this property? 
    public string Name { get; set; }

    [MySqlColName("State")]
    public string State { get; set; }

    [MySqlColName("Start_Date")]
    public DateTime StartDate { get; set; }

    [MySqlColName("End_Date")]
    public DateTime EndDate { get; set; }

}

DataTable的ToList扩展方法

最后，我们通过添加一个检查来修改@user1553525的答案，以查看是否提供了我们的自定义属性。如果提供了，则将列名设置为提供的名称；否则，我们使用属性名（请参阅try块中的代码）。

public static List<T> ToList<T>(this DataTable table) where T : class, new()
{
    try
    {
        List<T> list = new List<T>();

        foreach (var row in table.AsEnumerable())
        {
            T obj = new T();

            foreach (var prop in obj.GetType().GetProperties())
            {
                try
                {
                    //Set the column name to be the name of the property
                    string ColumnName = prop.Name;

                    //Get a list of all of the attributes on the property
                    object[] attrs = prop.GetCustomAttributes(true);
                    foreach (object attr in attrs)
                    {
                        //Check if there is a custom property name
                        if (attr is MySqlColName colName)
                        {
                            //If the custom column name is specified overwrite property name
                            if (!colName.Name.IsNullOrWhiteSpace())                                        
                                ColumnName = colName.Name;
                        }
                    }

                    PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);

                    //GET THE COLUMN NAME OFF THE ATTRIBUTE OR THE NAME OF THE PROPERTY
                    propertyInfo.SetValue(obj, Convert.ChangeType(row[ColumnName], propertyInfo.PropertyType), null);
                }
                catch
                {
                    continue;
                }
            }

            list.Add(obj);
        }

        return list;
    }
    catch
    {
        return null;
    }
}//END METHOD

使用

最后，我们可以调用.ToList<>()方法并获取序列化对象的列表。

List<EventInfo> CustomObjectList;

using (DataTable dtCustomer = GetDataTable("SELECT * FROM EventIndex"))
{
    CustomObjectList = dtCustomer.ToList<EventInfo>();
}

---

副注：我使用了一些自定义方法。

public static bool IsNullOrWhiteSpace(this string x)
{
    return string.IsNullOrWhiteSpace(x);
}

public static DataTable GetDataTable(string Query)
{
    MySqlConnection connection = new MySqlConnection("<Connection_String>");
    try
    {            
        DataTable data = new DataTable();
        connection.Open();
        using (MySqlCommand command = new MySqlCommand(Query, connection))
        {
            data.Load(command.ExecuteReader());
        }
        return data;

    }
    catch (Exception ex)
    {
        // handle exception here
        Console.WriteLine(ex);
        throw ex;
    }
    finally
    {
        connection.Close();
    }            
}

- Marcello B.

---

很棒的分享。考虑到我们许多人无法控制数据库，或者有人可能在其中用下划线标记了某些内容，而我不想将其放入我的属性名称中。 - JGood

Answer 5

以下函数接受一个 SQL 字符串和一个对象，要求该对象需要为选择语句中的每个列都有一个属性。该对象必须实例化。

public object SqlToSingleObject(string sSql, object o)
{
    MySql.Data.MySqlClient.MySqlDataReader oRead;
    using (ConnectionHelper oDb = new ConnectionHelper())
    {
        oRead = oDb.Execute(sSql);
        if (oRead.Read())
        {
            for (int i = 0; i < oRead.FieldCount; i++)
            {
                System.Reflection.PropertyInfo propertyInfo = o.GetType().GetProperty(oRead.GetName(i));
                propertyInfo.SetValue(o, Convert.ChangeType(oRead[i], propertyInfo.PropertyType), null);
            }

            return o;
        }
        else
        {
            return null;
        }
    }
}

- Wize

---

那有什么意义呢？如果你要创建所有属性，为什么不直接自己构建对象呢。 - MattE

Answer 6

假设：如果你只需要用于序列化或简单的临时输出。

你可以像这样使用 ExpandoObject 和 SqlDataReader.GetSchemaTable()：

    private IEnumerable<dynamic> ReaderToAnonymmous(SqlCommand comm) {
        using (var reader = comm.ExecuteReader()) {
            var schemaTable = reader.GetSchemaTable();

            List<string> colnames = new List<string>();
            foreach (DataRow row in schemaTable.Rows) {
                colnames.Add(row["ColumnName"].ToString());
            }

            while (reader.Read()) {
                var data = new ExpandoObject() as IDictionary<string, Object>;
                foreach (string colname in colnames) {
                    var val = reader[colname];
                    data.Add(colname, Convert.IsDBNull(val) ? null : val);
                }

                yield return (ExpandoObject)data;
            }
        }
    }

虽然有发布更快的解决方案（我将此作为备选方案，用于临时的SQL/Reader结果/输出）。

- DolphinSX

---

这太棒了，正是我一直在寻找的。我正在开发一个控制台应用程序，可以从存储过程中生成Excel报告，而有了这段代码，我就不必在应用程序中映射存储过程了。谢谢。 - v1n1akabozo

Answer 7

当我搜索这个答案时，我发现您可以使用Dapper库：https://dapper-tutorial.net/knowledge-base/44980945/querying-into-a-complex-object-with-dapper。您可以像这样使用它：

        using (var connection = new SqlConnection(ConnectionString))
        {
            connection.Open();
            IList<CustomObject> result = connection.Query<CustomObject>(sql, commandType: CommandType.Text).ToList();
        }

- Queequeg

Answer 8

尽管这个问题一直存在，但我找不到一个清晰的解决方案。为了我的目的，我想出了下面的方法，在我的情况下工作得非常好。

    using System.Dynamic;

    private IEnumerable<ExpandoObject> GetQueryToList()
    {
        try
        {
            using (var conn = new SqlConnection(ConnectionString))
            using (var cmd = new SqlCommand(MyQuery, conn))
            {
                var list = new List<ExpandoObject>();
                conn.Open();
                var reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    var expandoObject = new ExpandoObject();
                    for (var i = 0; i < reader.FieldCount; i++)
                    {
                        ((IDictionary<string, object>) expandoObject).Add(
                            reader.GetName(i), reader[i]);
                    }
                    list.Add(expandoObject);
                }

                reader.Close();
                return list;
            }
        }
        catch (Exception ex)
        {
            var m = MethodBase.GetCurrentMethod();
            Console.WriteLine(ex.Message + " " + m.Name);
        }

        return null;
    }

- azpc