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

Solution 1

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

Solution 2

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

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

Example (Took code from 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.

Solution 3

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.

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

Solution 4

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

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.

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 Name that we will use later to get the correct column from our 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.

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.

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

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).

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

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

Solution 5

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

You can use ExpandoObject and SqlDataReader.GetSchemaTable() like this:

    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).

Share:
Admin
Author by

Admin

Updated on October 28, 2021

Comments

  • Admin
    Admin 9 months

Related