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  lt  5000000  i          DataRow dr   dt NewRow        dr  id     i ToString        dr  name      name    i ToString        dt Rows Add dr        dt AcceptChanges       List lt Class1 gt  clslist   new List lt Class1 gt      for  int i   0  i  lt  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   lt br gt     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 lt Class1 gt    So that I can return objects in my service stack and go ahead

User · Answer

You may want to have a look at the code here. Although it doesn't answer your question directly you could adapt the generic class types that are used to map between data classes and business objects.

Also by using generic you run the conversion process as quickly as possible.

User · Answer

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     amp  DateDiff DateInterval Second  x  Now   amp   -   amp  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 -    amp  i   address -    amp  i  DateAdd DateInterval Second  i  Now       Next      Debug WriteLine  Datatable created     amp  DateDiff DateInterval Second  x  Now   amp   -   amp  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     amp  DateDiff DateInterval Second  x  Now   amp   -   amp  Now       Debug WriteLine  Complated    End Sub

User · Answer

Initialize DataTable   DataTable dt   new DataTable     dt Columns Add  id   typeof String     dt Columns Add  name   typeof String     for  int i   0  i  lt  5  i          string index   i ToString        dt Rows Add new object     index   name    index         Query itself   IList lt Class1 gt  items   dt AsEnumerable   Select row   gt       new Class1                       id   row Field lt string gt   id                name   row Field lt string gt   name              ToList

User · Answer

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     quot SELECT   jobID   AS ConfigNo   MainSettings  AS ParamName  VarNm AS ParamFieldName  1 AS ParamSetId  Val1 AS ParamValue FROM StrSVar WHERE NmSp     sAppName  Params  jobID   quot              Dim dtParameters As DataTable   DBLib GetDatabaseData sSql               Dim paramListObject As New List Of ParameterListModel                 If  Not dtParameters Is Nothing And dtParameters Rows Count  gt  0  Then                 Dim json   Newtonsoft Json JsonConvert SerializeObject dtParameters  ToString                    paramListObject   Newtonsoft Json JsonConvert DeserializeObject Of List Of ParameterListModel   json              End If

User · Answer

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 lt T gt  CreateListFromTable lt T gt  DataTable tbl  where T   new                            define return list             List lt T gt  lst   new List lt T gt                     go through each row             foreach  DataRow r in tbl Rows                                   add to the list                 lst Add CreateItemFromRow lt T gt  r                                  return the list             return lst                        function that creates an object from the given data row         public static T CreateItemFromRow lt T gt  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 lt T gt  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  amp  amp  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 lt YourViewModel gt  model   new List lt YourViewModel gt          if  ds    null                  Pass datatable from dataset to our DAL Method          model   DAL CreateListFromTable lt YourViewModel gt  ds Tables 0                                     Till the date  for many of my applications  I found this as the best structure to get data

[c#] How to convert DataTable to class Object?

Examples related to c#

Examples related to asp.net

Examples related to .net

Examples related to linq

Examples related to datatable