Convert your datatable into generic poco object in c# using linq, ado and reflections.

Follow @harshit_parshii
The most common problem that we face these days is to create a common class and method that can be used across all the projects and codes.

So today I will be sharing my code where you can see how to make and create a generic function without using entity framework for ado. net.

The scenario is like you have an old software that uses stored procedure to return set of entities as a data-table, you do not want to re-write the back-end code as you are creating a web API in c# which needs to be delivered asap.

You need to map these data tables to models as you might be using MV* pattern.

So here we will be doing one to one mapping of model to data- table, and in similar fashion insert or update can also be done.

So basically we are converting a data-table to list of strongly typed object model to do CRUD operations.

So we have following things before hand.

A helper class is referenced as the database(dbFactory) which executes ado. net commands and returns data whether it's a nonquery or a dataset/data tables.

Below is the code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66

/// <summary> /// Call the Get to fetch Data /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="U"></typeparam> /// <param name="model"></param> /// <param name="param"></param> /// <param name="procName"></param> /// <returns></returns> public static List<T> Get<T, U>(T model, U param, string procName) where T : new() { Type type = param.GetType(); Type modelType = param.GetType(); var propertiesParam = typeof(U).GetProperties(); prm = new SqlParameter[propertiesParam.Length]; for (int i = 0; i < propertiesParam.Length; i++) { prm[i] = new SqlParameter(propertiesParam[i].Name.ToString(), propertiesParam[i].GetValue(param)); } database = new DbFactory(); return ToList(new T(), database.returnDataTable(strConn, procName, prm, 1)); } /// <summary> /// Convert DataTable to Strongly typed objects /// </summary> /// <typeparam name="T"></typeparam> /// <param name="Model"></param> /// <param name="dataTable"></param> /// <returns></returns> private static List<T> ToList<T>(T Model, DataTable dataTable) where T : new() { var dataList = new List<T>(); const BindingFlags flags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.NonPublic; var objFieldNames = (from PropertyInfo aProp in typeof(T).GetProperties(flags) select new { Name = aProp.Name, Type = Nullable.GetUnderlyingType(aProp.PropertyType) ?? aProp.PropertyType }).ToList(); var dataTblFieldNames = (from DataColumn aHeader in dataTable.Columns select new { Name = aHeader.ColumnName, Type = aHeader.DataType }).ToList(); var commonFields = objFieldNames.Intersect(dataTblFieldNames).ToList(); foreach (DataRow dataRow in dataTable.AsEnumerable().ToList()) { var aTSource = new T(); foreach (var aField in commonFields) { PropertyInfo propertyInfos = aTSource.GetType().GetProperty(aField.Name); var value = (dataRow[aField.Name] == DBNull.Value) ? null : dataRow[aField.Name]; //if database field is nullable propertyInfos.SetValue(aTSource, value, null); } dataList.Add(aTSource); } return dataList; }

So in the above code as you can see these are generics methods in which we need to pass a model, sql proc name and parameters, we will recieve the model with values in it. The catch is the model properties name should be same as table column headers, my next target will be to have attributes functionality over model properties so we can have custom names of entity like in MVC.

Anyway ToList function above will convert your sql datatable to model entity using reflections capabilities of c#.

Well just copy paste the above code and enjoy.