DataSet values to store in List object

Question

I have a DataSet, that needs to be passed into a list object. Name of columns in DataSet are same as properties of list object.

Example:

Data Set columns

PickupCity
PickupState
PickupCountry

List Object Properties

ContactPerson
PickupCity
PickupState
PickupCountry
PhoneNumber

I do not want to assign / define each column one by one, like:

PickupCity = dr["PickupCity"].ToString(),

I am looking for something like below that automatically assign available data with same column / property name:

List<PickupClass> pickup = DataSet

Answer 1

This can be done with reflection. For each member name find the the dataset column with the same name. Your reflection code will need to determine the data type, and call the proper conversion routine.

Here is a sample

https://exceptionnotfound.net/mapping-datatables-and-datarows-to-objects-in-csharp-and-net-using-reflection/

You might be better off using an ORM library like dapper or entitity framework to begin with.

Answer 2

Try this

static void Main(string[] args)
{
    DataTable dt = new();
    dt.Columns.Add("List", typeof(List<string>));

    List<string> names = null;

    names = new List<string>(new[] { "John", "Mary" });
    dt.Rows.Add(names);

    names = new List<string>(new[] { "Bob", "Carol", "Ted", "Alice" });
    dt.Rows.Add(names);

    names = new List<string>(new[] { "Harry", "Sally" });
    dt.Rows.Add(names);

    foreach (DataRow row in dt.Rows)
    {
        var items = row.Field<List<string>>("List");
        foreach (var item in items)
        {
            Debug.WriteLine(item);
        }

        Debug.WriteLine("");
    }

}

Answer 3

Also check a handmade example that is based on serialisation:

public class MyObject
{
    public string ContactPerson { get; set; }
    public string PickupCity { get; set; }
    public string PickupState { get; set; }
    public string PickupCountry { get; set; }
    public string PhoneNumber { get; set; }
}

. . .

var dt = new DataTable( "MyObject" );
dt.Columns.Add( "PickupCity" );
dt.Columns.Add( "PickupState" );
dt.Columns.Add( "PickupCountry" );

dt.Rows.Add( "City1", "State1", "Country1" );
dt.Rows.Add( "City2", "State2", "Country2" );

List<MyObject> result;

using( var ms = new MemoryStream( ) )
{
    dt.WriteXml( ms, XmlWriteMode.IgnoreSchema );
    ms.Position = 0;

    XmlSerializer s = new XmlSerializer( typeof( List<MyObject> ), new XmlRootAttribute( "DocumentElement" ) );

    result = (List<MyObject>)s.Deserialize( ms );
}

Maybe it can be adapted for your case.

Answer 4

T item = GetItem<T>(row);

this line got error that

"Severity Code Description Project File Line Suppression State

Error CS0103 The name 'GetItem' does not exist in the current context [xxxlinno]. Active

"

Answer 5

Hi @Zeeshan Dar ,

I do not want to assign / define each column one by one, like:

 PickupCity = dr["PickupCity"].ToString(),  

I am looking for something like below that automatically assign available data with same column / property name:

 List<PickupClass> pickup = DataSet  

You can try to use the following method to get the list of objects from the Dataset.

1. using LINQ statement for example:

       //create a datatable   
       DataTable dt = new DataTable("Pickup");  
       dt.Columns.Add("PickupId", typeof(Int32));  
       dt.Columns.Add("PickupCity", typeof(string));  
       dt.Columns.Add("PickupState", typeof(string));  
       dt.Columns.Add("PickupCountry", typeof(string));  
       //Data    
       dt.Rows.Add(1, "Abbeville", "Alabama", "usa");  
       dt.Rows.Add(2, "Adamsville", "Alabama", "usa");  
       dt.Rows.Add(3, "Akutan", "Alaska", "usa");  
       dt.Rows.Add(4, "Aleutians East Borough", "Alaska", "usa");  
   
       DataSet ds = new DataSet();  
       ds.Tables.Add(dt);  
   
       List<PickupViewModel> result = (ds.Tables[0].AsEnumerable()  
           .Select(datarow => new PickupViewModel()  
           {  
               PickupCity = datarow.Field<string>("PickupCity"),  
               PickupState = datarow.Field<string>("PickupState"),  
               PickupCountry = datarow.Field<string>("PickupCountry"),  
           })).ToList();  
   

   The PickupViewModel class:

   public class PickupViewModel  
   {  
       public string ContactPerson { get; set; }  
       public string PickupCity { get; set; }  
       public string PickupState { get; set; }  
       public string PickupCountry { get; set; }  
       public string PhoneNumber { get; set; }  
   }  
   

   The result is like this:
   2. Convert DataTable to List using a Generic Method private static List<T> ConvertDataTable<T>(DataTable dt)
      {
      List<T> data = new List<T>();
      foreach (DataRow row in dt.Rows)
      {
      T item = GetItem<T>(row);
      data.Add(item);
      }
      return data;
      }
      //required using System.Reflection;
      private static T GetItem<T>(DataRow dr)
      {
      Type temp = typeof(T);
      T obj = Activator.CreateInstance<T>();

      foreach (DataColumn column in dr.Table.Columns)  
      {  
          foreach (PropertyInfo pro in temp.GetProperties())  
          {  
              if (pro.Name == column.ColumnName)  
                  pro.SetValue(obj, dr[column.ColumnName], null);  
              else  
                  continue;  
          }  
      }  
      return obj;  
      

      }
   Then call the above method to convert the table to list:

   List<PickupViewModel> result2 = ConvertDataTable<PickupViewModel>(ds.Tables[0]);  
   

   The result is like this:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion