C# 如何将 sql 查询的结果映射到对象上？

How can I map the results of a sql query onto objects?

提问by Legend

Currently, I am using something like this:

目前，我正在使用这样的东西：

    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";
      } 

Instead of me doing the assigments manually, is there a way to do this mapping directly (assume that the column names match with the field names).

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

One requirement, however is that I want to do this by my current approach of using sql queries and not by using pure LINQ based approaches. For one, the SQL queries are big enough, involve complex JOINs and have been tested thoroughly so I don't want to introduce more bugs at the moment. Any suggestions?

然而，一个要求是我想通过我当前使用 sql 查询的方法而不是使用纯基于 LINQ 的方法来做到这一点。一方面，SQL 查询足够大，涉及复杂的 JOIN 并且已经过彻底测试，所以我现在不想引入更多错误。有什么建议？

回答by NoLifeKing

One simple solution would be to make a constructor for your CustomObjectthat takes a DataRow(from the example, so if it's another class, please correct me).

一个简单的解决办法是让一个构造你CustomObject是需要DataRow（从例如，所以如果它是另一个类，请指正）。

And in your new constructor, do as you do in your own example.

在你的新构造函数中，按照你自己的例子做。

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

One other way would be to introduce generics, and make a new function in your SQL-class

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

Example (Took code from Passing arguments to C# generic new() of templated type):

示例（从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;
}

Example usage:

用法示例：

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

I have tested this code in LinqPad, it should work.

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

回答by user1553525

You can achieve by creating a generic method for your requirement. Also you can make your new method as the extension for the data table.

您可以通过为您的要求创建通用方法来实现。您也可以将新方法作为数据表的扩展。

    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;
    }
}

}

}

Usage:

用法：

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

回答by Idan Arye

You should look into MicroORMs. Unlike regular ORMs, that provide an SDL you must use, MicroORMs allow you to use your own SQL queries and only provide the mapping from SQL result sets to C# objects and from C# objects to SQL parameters.

您应该研究 MicroORM。与提供必须使用的 SDL 的常规 ORM 不同，MicroORM 允许您使用自己的 SQL 查询，并且仅提供从 SQL 结果集到 C# 对象以及从 C# 对象到 SQL 参数的映射。

My favorite is PetaPoco, which also provides a query builder that uses your own SQL but does some neat manipulation of parameter numbers.

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

回答by Wize

The following function accepts a SQL string and an object, it requires the object to have a property for each column in the select statement. The object must be instantiated.

下面的函数接受一个 SQL 字符串和一个对象，它要求对象在 select 语句中的每一列都有一个属性。对象必须被实例化。

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;
        }
    }
}

回答by DolphinSX

Assumption:if you need objects only for serialization or simple ad-hoc output.

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

You can use ExpandoObjectand SqlDataReader.GetSchemaTable()like this:

您可以使用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;
            }
        }
    }

Although there are posted faster solutions (i posted this as alternative lazy approach for ad-hoc SQL/Reader results/outputs).

尽管发布了更快的解决方案（我将其发布为临时 SQL/Reader 结果/输出的替代懒惰方法）。

回答by Queequeg

When searching for this answer I found that you can use Dapper library: https://dapper-tutorial.net/knowledge-base/44980945/querying-into-a-complex-object-with-dapper

在搜索此答案时，我发现您可以使用 Dapper 库：https: //dapper-tutorial.net/knowledge-base/44980945/querying-into-a-complex-object-with-dapper

You can use something like this:

你可以使用这样的东西：

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

回答by Suit Boy Apps

@user1553525's answer is great, however, if your column names do not match up exactly with your property names it does not work.

@user1553525的回答很好，但是，如果您的列名与您的属性名称不完全匹配，则它不起作用。

So first you would want to create a custom attribute. Then use the attribute in your class that you are trying to deserialize, finally, you want to deserialize the DataTable.

所以首先你要创建一个自定义属性。然后使用您尝试反序列化的类中的属性，最后，您想要反序列化 DataTable。

Custom Attribute

自定义属性

We create a custom attribute that will be applied to the properties inside of our class. We create the class to have the property Namethat we will use later to get the correct column from our 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;
    }
}

Class to deserialize

要反序列化的类

Next, in the class that we are going to populate, we are going to declare the column names that will link to the properties in the class using the attribute [MySqlColName]that we just created.

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

However, if the property name is the same as the database column we do not need to specify the column name in an attribute because the .ToList<>()function will assume the name of the column from the properties name.

但是，如果属性名称与数据库列相同，我们不需要在属性中指定列名称，因为该.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 Extension Method

DataTable ToList 扩展方法

Finally, we modify @user1553525's answer by adding in a check to see if our custom attribute has been provided. If it is then we set the name of the column to the name provided, otherwise, we use the property name (see code inside of the try block).

最后，我们修改@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

Usage

用法

Finally, we can call the .ToList<>()method and get a list of serialized objects

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

List<EventInfo> CustomObjectList;

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

---

---

Side Note:I have a few custom methods that I used

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

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();
    }            
}