How to convert DataTable to class Object?

Question

I have already developed an application which returns DataTable everywhere.

Now my client wants to convert (use some part using service stack), so I need to return DTO (objects) in my application.

I don't want to change my existing stored procedures or even not want to use LINQ as much as possible (I am not too much aware with LINQ).

For small functionality, I can use Linq no issue.

My question is: how can I change my DataTable to objects of that class?

The sample code is below:

string s = DateTime.Now.ToString(); DataTable dt = new DataTable();  dt.Columns.Add("id"); dt.Columns.Add("name");  for (int i = 0; i < 5000000; i++) {     DataRow dr = dt.NewRow();     dr["id"] = i.ToString();     dr["name"] = "name" + i.ToString();     dt.Rows.Add(dr);      dt.AcceptChanges(); }  List<Class1> clslist = new List<Class1>();  for (int i = 0; i < dt.Rows.Count; i++) {     Class1 cls = new Class1();     cls.id = dt.Rows[i]["id"].ToString();     cls.name = dt.Rows[i]["name"].ToString();     clslist.Add(cls); }  Response.Write(s); Response.Write("<br>"); Response.Write(DateTime.Now.ToString()); 

I know, the above method is time-consuming, and I am trying to find an alternate solution.

Is there any alternative way (I guess, LINQ to DataTable) by which it directly converts the rows of tables to List<Class1>?

So that I can return objects in my service stack and go ahead.

Answer 1

Initialize DataTable:

DataTable dt = new DataTable();  dt.Columns.Add("id", typeof(String));  dt.Columns.Add("name", typeof(String));  for (int i = 0; i < 5; i++) {     string index = i.ToString();     dt.Rows.Add(new object[] { index, "name" + index }); } 

Query itself:

IList<Class1> items = dt.AsEnumerable().Select(row =>      new Class1         {             id = row.Field<string>("id"),             name = row.Field<string>("name")         }).ToList(); 

Answer 2

Amit, I have used one way to achieve this with less coding and more efficient way.

but it uses Linq.

I posted it here because maybe the answer helps other SO.

Below DAL code converts datatable object to List of YourViewModel and it's easy to understand.

public static class DAL {         public static string connectionString = ConfigurationManager.ConnectionStrings["YourWebConfigConnection"].ConnectionString;          // function that creates a list of an object from the given data table         public static List<T> CreateListFromTable<T>(DataTable tbl) where T : new()         {             // define return list             List<T> lst = new List<T>();              // go through each row             foreach (DataRow r in tbl.Rows)             {                 // add to the list                 lst.Add(CreateItemFromRow<T>(r));             }              // return the list             return lst;         }          // function that creates an object from the given data row         public static T CreateItemFromRow<T>(DataRow row) where T : new()         {             // create a new object             T item = new T();              // set the item             SetItemFromRow(item, row);              // return              return item;         }          public static void SetItemFromRow<T>(T item, DataRow row) where T : new()         {             // go through each column             foreach (DataColumn c in row.Table.Columns)             {                 // find the property for the column                 PropertyInfo p = item.GetType().GetProperty(c.ColumnName);                  // if exists, set the value                 if (p != null && row[c] != DBNull.Value)                 {                     p.SetValue(item, row[c], null);                 }             }         }          //call stored procedure to get data.         public static DataSet GetRecordWithExtendedTimeOut(string SPName, params SqlParameter[] SqlPrms)         {             DataSet ds = new DataSet();             SqlCommand cmd = new SqlCommand();             SqlDataAdapter da = new SqlDataAdapter();             SqlConnection con = new SqlConnection(connectionString);              try             {                 cmd = new SqlCommand(SPName, con);                 cmd.Parameters.AddRange(SqlPrms);                 cmd.CommandTimeout = 240;                 cmd.CommandType = CommandType.StoredProcedure;                 da.SelectCommand = cmd;                 da.Fill(ds);             }             catch (Exception ex)             {                return ex;             }              return ds;         } } 

Now, The way to pass and call method is below.

    DataSet ds = DAL.GetRecordWithExtendedTimeOut("ProcedureName");      List<YourViewModel> model = new List<YourViewModel>();      if (ds != null)     {         //Pass datatable from dataset to our DAL Method.         model = DAL.CreateListFromTable<YourViewModel>(ds.Tables[0]);                     }       

Till the date, for many of my applications, I found this as the best structure to get data.