How to convert DataTable to class Object?

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

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

Solution 3:

Was looking at this and realized: it's from one type of object to another; basicaclly we're trying to do proper reflection.

There are proper ways to construct the relationship between different fields but give the class definition is done, it can be easily done by Newtonsoft.Json

Process: DataSet/DataTable (Serialize) ==> Json (Deserialize) ==> Target Object List In this example as the OP, simply do:

string serializeddt = JsonConvert.SerializeObject(dt, Formatting.Indented);

Now the DataTable is serialized into a plain string. Then do this:

List<Class1> clslist = JsonConvert.DeserializeObject<List<Class1>>(serialized, new JsonSerializerSettings { NullValueHandling = NullValueHandling.Ignore });

Now you should have the List with all the DataTable rows as individual objects.

Solution 4:

It is Vb.Net version:

Public Class Test
Public Property id As Integer
Public Property name As String
Public Property address As String
Public Property createdDate As Date

End Class

  Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim x As Date = Now

    Debug.WriteLine("Begin: " & DateDiff(DateInterval.Second, x, Now) & "-" & Now)

    Dim dt As New DataTable
    dt.Columns.Add("id")
    dt.Columns.Add("name")
    dt.Columns.Add("address")
    dt.Columns.Add("createdDate")

    For i As Integer = 0 To 100000
        dt.Rows.Add(i, "name - " & i, "address - " & i, DateAdd(DateInterval.Second, i, Now))
    Next

    Debug.WriteLine("Datatable created: " & DateDiff(DateInterval.Second, x, Now) & "-" & Now)


    Dim items As IList(Of Test) = dt.AsEnumerable().[Select](Function(row) New _
            Test With {
                        .id = row.Field(Of String)("id"),
                        .name = row.Field(Of String)("name"),
                        .address = row.Field(Of String)("address"),
                        .createdDate = row.Field(Of String)("createdDate")
                       }).ToList()

    Debug.WriteLine("List created: " & DateDiff(DateInterval.Second, x, Now) & "-" & Now)

    Debug.WriteLine("Complated")

End Sub