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?

Answer 1

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.

Answer 2

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