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

Question

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?

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

View more solutions

Comments

[mellamokb]

Note that you can use LINQ with custom SQL queries, and it will automatically map the fields in the query result to the generic object type you provide. See DataContext.ExecuteQuery

[Legend]

@mellamokb: Can you show me how? Or at least point me to some resource or maybe what I should search for?

[mellamokb]

Take a look at the link I added, which is the specific method you would need. There are also examples on that page.

[Legend]

@mellamokb: Wow... please add this as an answer. It will help others looking for something similar.

[marc_s]

Look at Dapper.NET written by Sam Saffron (while he worked at/on Stackoverflow)

[v1n1akabozo]

this is awesome, exactly what i've been looking for. I'm console application that generates reports in excel from stored procedures, and with this piece of code i don't have to map the procedures within the application. Thank you.

[MattE]

Whats the point of that? If you are going to create all the poperties, why not just build the object yourself.

[Demodave]

what does GetCustomers look like?

[A.Rowan]

I am frightened that an answer with try, catch {continue;} has upvotes...

[Taco タコス]

@A.Rowan It's only fair to say that a property failing to set isn't the problem though, it's the failure to track the fact that something went wrong when we tried to set one.

[JGood]

Great share. Considering many of us don't have control over the database or someone may have labeled something in there with underscores that I don't want to put into my property names.