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.

Frog

possible duplicate of Convert DataTable to List<T> – Dropkick

just want to add that what you have done above to populate a class from DataTable is not slow, in fact its as fast as it gets given we have DataTable to work with, linq or anything else wont make it faster it just may give you a simpler code which wont ask you to write a loop but implement itself. – Cardioid

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

Marlomarlon

For my case, i just need to change IList to List<class1>, so it returns me a collection of class, In your case just it is Interface, no difference. Thanks very much – Frog

Good one but what if have a class having same names of fields as in DB but i have more than 100 fields, any routine that automatically pick fields from class and fill it? – Oeildeboeuf

This may help Adeem. c-sharpcorner.com/blogs/converting-datatable-to-model-list2 – Rhett

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.

Landloper

This doesn't work. dotnetfiddle.net/x5dtGA It should also be noticed that reflection is very slow and should not be used for performance-critical code. – Embroider

@Embroider it will work if you set data type for your data table's column. regarding performance issue, it's dynamic mapping, and as I have used reflection, it will be little slow but not that much that you can't even use for map. – Landloper

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

Eyelet

Great answer. If it doesn't work somehow, copy your serializeddt json string to json2csharp.com to create the correct classes. – Hollerman

Answer 4

As of 2023, a much simpler and ver less code

public class ClassName
{
   public int Id { get; set; }
   public String FirstName { get; set; }
   public String LastName { get; set; }
}

For a class object List

DataTable dt = new DataTable();

var _objectList = JArray.FromObject(dt).ToObject<List<Venues>>();

And For a class object

DataTable dt = new DataTable();

var _object = JArray.FromObject(dt)[].ToObject<Venues>();

Glyptograph

Answer 5

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

Leotie

Answer 6

Is it very expensive to do this by json convert? But at least you have a 2 line solution and its generic. It does not matter eather if your datatable contains more or less fields than the object class:

Dim sSql = $"SELECT '{jobID}' AS ConfigNo, 'MainSettings' AS ParamName, VarNm AS ParamFieldName, 1 AS ParamSetId, Val1 AS ParamValue FROM StrSVar WHERE NmSp = '{sAppName} Params {jobID}'"
            Dim dtParameters As DataTable = DBLib.GetDatabaseData(sSql)

            Dim paramListObject As New List(Of ParameterListModel)()

            If (Not dtParameters Is Nothing And dtParameters.Rows.Count > 0) Then
                Dim json = Newtonsoft.Json.JsonConvert.SerializeObject(dtParameters).ToString()

                paramListObject = Newtonsoft.Json.JsonConvert.DeserializeObject(Of List(Of ParameterListModel))(json)
            End If

Caricaria