Convert DataSet to List

Question

Here is my c# code

Employee objEmp = new Employee();
List<Employee> empList = new List<Employee>();
foreach (DataRow dr in ds.Tables[0].Rows)
{
    empList.Add(new Employee { Name = Convert.ToString(dr["Name"]), Age = Convert.ToInt32(dr["Age"]) });
}

It uses a loop to create a List from a dataset.Is there any direct method or shorter method or one line code to convert dataset to list

why do you need it to be in *one line* ? is the current multi lined form just as easy to read? — Jens Kloster, Jun 14 '13 at 11:20
@JensKloster whats the harm in knowing if it could be written in one line :) — iJade, Jun 17 '13 at 04:23

score 116 · Accepted Answer · edited Sep 23 '19 at 13:35

116

Try something like this:

var empList = ds.Tables[0].AsEnumerable()
    .Select(dataRow => new Employee
    {
        Name = dataRow.Field<string>("Name")
    }).ToList();

edited Sep 23 '19 at 13:35

Julio Borges

661
14
29

answered Jun 14 '13 at 11:18

Carra

17,808
7
62
75

2

this is what i tried --- var empList = ds.Tables[0].AsEnumerable().Select(dataRow => new Employee { Name = dataRow.Field("Name"), Age = dataRow.Field("Age") }).ToList(); says "Specified cast is not valid" – iJade Jun 14 '13 at 11:33
If you are getting the error "Specified cast is not valid". Try put ToList right after AsEnumerable. Solution from: https://stackoverflow.com/questions/22159695/specified-cast-is-not-valid-linq-query – Burak Altin Aug 31 '18 at 03:00
var ProTLData = ds.Tables[0].AsEnumerable().ToList().Select(s => new IssueViewModel { ProjectId = s.Field("ProjectId"), RoleId = s.Field("RoleId"), UserId = s.Field("UserId"), UserName = s.Field("UserName") }); custData.IssueList = ProTLData.ToList(); this is not generating the specified cast error but when i assign in custData.IssueList then it generate the same error – Aug 03 '19 at 12:16

Nitin Sawant · Answer 2 · 2022-01-13T11:41:43.253

Here's extension method to convert DataTable to object list:

    public static class Extensions
    {
        public static List<T> ToList<T>(this DataTable table) where T : new()
        {
            IList<PropertyInfo> properties = typeof(T).GetProperties().ToList();
            List<T> result = new List<T>();

            foreach (var row in table.Rows)
            {
                var item = CreateItemFromRow<T>((DataRow)row, properties);
                result.Add(item);
            }

            return result;
        }

        private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new()
        {
            T item = new T();
            foreach (var property in properties)
            {
                if (property.PropertyType == typeof(System.DayOfWeek))
                {
                    DayOfWeek day = (DayOfWeek)Enum.Parse(typeof(DayOfWeek), row[property.Name].ToString());
                    property.SetValue(item,day,null);
                }
                else
                {
                    if(row[property.Name] == DBNull.Value)
                        property.SetValue(item, null, null);
                    else
                    {
                        if (Nullable.GetUnderlyingType(property.PropertyType) != null)
                        {
                            //nullable
                            object convertedValue = null;
                            try
                            {
                                convertedValue = System.Convert.ChangeType(row[property.Name], Nullable.GetUnderlyingType(property.PropertyType));
                            }
                            catch (Exception ex)
                            {
                            }
                            property.SetValue(item, convertedValue, null);
                        }
                        else
                            property.SetValue(item, row[property.Name], null);
                    }
                }
            }
            return item;
        }
    }

usage:

List<Employee> lst = ds.Tables[0].ToList<Employee>();

@itay.b CODE EXPLAINED: We first read all the property names from the class T using reflection
then we iterate through all the rows in datatable and create new object of T,
then we set the properties of the newly created object using reflection.

The property values are picked from the row's matching column cell.

PS: class property name and table column names must be same

love this, great answer and also a nice example of generics. — Simon, Apr 08 '17 at 10:47
I can see you explained how to convert system defined enum type, can you please explain how can i convert enum type defined in code like Gender? Thanks — sairfan, May 16 '17 at 22:17
May I ask why in `CreateItemFromRow`, you first check whether the property's type equals `typeof(System.DayOfWeek)`? Would calling `property.SetValue(item, row[property.Name], null)` cause an exception? — Steven Volckaert, Apr 16 '20 at 09:32

score 16 · Answer 3 · answered Jun 14 '13 at 11:19

16

var myData = ds.Tables[0].AsEnumerable().Select(r => new Employee {
    Name = r.Field<string>("Name"),
    Age = r.Field<int>("Age")
});
var list = myData.ToList(); // For if you really need a List and not IEnumerable

answered Jun 14 '13 at 11:19

Fabian Bigler

10,403
6
47
70

score 4 · Answer 4 · edited Feb 27 '17 at 10:47

4

Use the code below:

using Newtonsoft.Json;
string JSONString = string.Empty;
JSONString = JsonConvert.SerializeObject(ds.Tables[0]);

edited Feb 27 '17 at 10:47

Jeru Luke

20,118
13
80
87

answered Feb 27 '17 at 10:17

Ajsatis

41
2

Best way to do it. – Badhon Jain Oct 22 '19 at 05:54

score 2 · Answer 5 · answered Jun 14 '13 at 11:21

2

Try this....modify the code as per your needs.

      List<Employee> target = dt.AsEnumerable()
      .Select(row => new Employee
      {
        Name = row.Field<string?>(0).GetValueOrDefault(),
        Age= row.Field<int>(1)
      }).ToList();

answered Jun 14 '13 at 11:21

Qasim Javaid Khan

650
4
10

2

Just a small comment but string is already nullable, no use to use string? – Carra Jun 14 '13 at 11:25
I cant find the ToList() method , it does not come up – NewTech Dec 23 '16 at 16:22
@NewTech use - using System.Linq; – PAVITRA Jun 12 '19 at 09:36

score 2 · Answer 6 · answered Mar 31 '15 at 15:51

                DataSet ds = new DataSet();
                ds = obj.getXmlData();// get the multiple table in dataset.

                Employee objEmp = new Employee ();// create the object of class Employee 
                List<Employee > empList = new List<Employee >();
                int table = Convert.ToInt32(ds.Tables.Count);// count the number of table in dataset
                for (int i = 1; i < table; i++)// set the table value in list one by one
                {
                    foreach (DataRow dr in ds.Tables[i].Rows)
                    {
                        empList.Add(new Employee { Title1 = Convert.ToString(dr["Title"]), Hosting1 = Convert.ToString(dr["Hosting"]), Startdate1 = Convert.ToString(dr["Startdate"]), ExpDate1 = Convert.ToString(dr["ExpDate"]) });
                    }
                }
                dataGridView1.DataSource = empList;

enter image description here

score 2 · Answer 7 · edited Mar 01 '17 at 08:14

Add a new class named as "Helper" and change the property of the class to "public static"

public static class Helper
{
    public static List<T> DataTableToList<T>(this DataTable table) where T : class, new()
    {
        try
        {
            List<T> list = new List<T>();

            foreach (var row in table.AsEnumerable())
            {
                T obj = new T();

                foreach (var prop in obj.GetType().GetProperties())
                {
                    try
                    {
                        PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);
                        propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);
                    }
                    catch
                    {
                        continue;
                    }
                }

                list.Add(obj);
            }

            return list;
        }
        catch
        {
            return null;
        }
    }
}

and access this class in your code behind as like below

 DataTable dtt = dsCallList.Tables[0];
 List<CallAssignment> lstCallAssignement = dtt.DataTableToList<CallAssignment>();

score 1 · Answer 8 · answered Mar 05 '14 at 06:27

Fill the dataset with data from, say a stored proc command

DbDataAdapter adapter = DbProviderFactories.GetFactory(cmd.Connection).CreateDataAdapter();
adapter.SelectCommand = cmd;
DataSet ds = new DataSet();
adapter.Fill(ds);

Get The Schema,

string s = ds.GetXmlSchema();

save it to a file say: datasetSchema.xsd. Generate the C# classes for the Schema: (at the VS Command Prompt)

xsd datasetSchema.xsd /c

Now, when you need to convert the DataSet data to classes you can deserialize (the default name given to the generated root class is NewDataSet):

public static T Create<T>(string xml)
{
    XmlSerializer serializer = new XmlSerializer(typeof(T));
    using (StringReader reader = new StringReader(xml))
    {
        T t = (T)serializer.Deserialize(reader);

        reader.Close();
        return t;
    }
}

var xml = ds.GetXml();
var dataSetObjects = Create<NewDataSet>(xml);

+1. This gets me closer to the answer of [my own XML serialization/deserialization question](http://stackoverflow.com/questions/28652889/serializing-objects-into-xml-repository). — Nick Alexeev, Feb 22 '15 at 02:43

Jacob White · Answer 9 · 2018-06-20T17:55:07.607

I couldn't get Nitin Sawant's answer to work, but I was able to modify his code to work for me. Essentially I needed to use GetRuntimeFields instead of GetProperties. Here's what I ended up with:

public static class Extensions
{
    public static List<T> ToList<T>(this DataTable table) where T : new()
    {
        IList<FieldInfo> fields = typeof(T).GetRuntimeFields().ToList();
        List<T> result = new List<T>();
        if (row.Table.Columns.Contains(field.Name))
        {
            foreach (var row in table.Rows)
            {
                var item = CreateItemFromRow<T>((DataRow)row, fields);
                result.Add(item);
            }
        }

        return result;
    }

    private static T CreateItemFromRow<T>(DataRow row, IList<FieldInfo> fields) where T : new()
    {
        T item = new T();

        foreach (var field in fields)
        {
            if (row[field.Name] == DBNull.Value)
                field.SetValue(item, null);
            else
                field.SetValue(item, row[field.Name]);
        }
        return item;
    }
}

where is the declaration for row variable from the if being used in the ToList method? — wintoch, Nov 13 '18 at 07:42
You forgot the `foreach` loop after line `List result = new List();` — Burak Dobur, Mar 27 '19 at 12:16
this doesnt build as is. row is not instantiated in your ToList method — Terrance Jackson, Jul 12 '22 at 18:06

score -1 · Answer 10 · answered Jun 14 '13 at 11:19

Try the above which will run with any list type.

  public DataTable ListToDataTable<T>(IList<T> data)
    {
        PropertyDescriptorCollection props =
            TypeDescriptor.GetProperties(typeof(T));
        DataTable table = new DataTable();
        for (int i = 0; i < props.Count; i++)
        {
            PropertyDescriptor prop = props[i];
            table.Columns.Add(prop.Name, prop.PropertyType);
        }
        object[] values = new object[props.Count];
        foreach (T item in data)
        {
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = props[i].GetValue(item);
            }
            table.Rows.Add(values);
        }
        return table;
    }

score -1 · Answer 11 · edited Jan 02 '18 at 14:04

 List<GSTEntity.gst_jobwork_to_mfgmaster> ListToGetJwToMfData = new List<GSTEntity.gst_jobwork_to_mfgmaster>();
            DataSet getJwtMF = new DataSet();
            getJwtMF = objgst_jobwork_to_mfgmaster_BLL.GetDataJobWorkToMfg(AssesseeId, PremiseId,  Fyear,  MonthId, out webex);
            if(getJwtMF.Tables["gst_jobwork_to_mfgmaster"] != null)
            {

                ListToGetJwToMfData = (from master in getJwtMF.Tables["gst_jobwork_to_mfgmaster"].AsEnumerable() select new GSTEntity.gst_jobwork_to_mfgmaster { Partygstin = master.Field<string>("Partygstin"), Partystate =
                                       master.Field<string>("Partystate"), NatureOfTransaction = master.Field<string>("NatureOfTransaction"), ChallanNo = master.Field<string>("ChallanNo"), ChallanDate=master.Field<int>("ChallanDate"),  OtherJW_ChallanNo=master.Field<string>("OtherJW_ChallanNo"),   OtherJW_ChallanDate = master.Field<int>("OtherJW_ChallanDate"),
                    OtherJW_GSTIN=master.Field<string>("OtherJW_GSTIN"),
                    OtherJW_State = master.Field<string>("OtherJW_State"),
                    InvoiceNo = master.Field<string>("InvoiceNo"),
                    InvoiceDate=master.Field<int>("InvoiceDate"),
                    Description =master.Field<string>("Description"),
                    UQC= master.Field<string>("UQC"),
                    qty=master.Field<decimal>("qty"),
                    TaxValue=master.Field<decimal>("TaxValue"),
                    Id=master.Field<int>("Id")                        

                }).ToList();

Please add some explanation to the mentioned source code. – Dominique Jan 02 '18 at 13:27 — Dominique, Jan 02 '18 at 13:27

Convert DataSet to List

11 Answers11

Linked

Related