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

Indigo

why do you need it to be in one line ? is the current multi lined form just as easy to read? – Elsy

@JensKloster whats the harm in knowing if it could be written in one line :) – Indigo

Answer 1

Try something like this:

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

Kiakiah

this is what i tried --- var empList = ds.Tables[0].AsEnumerable().Select(dataRow => new Employee { Name = dataRow.Field<string>("Name"), Age = dataRow.Field<int>("Age") }).ToList(); says "Specified cast is not valid" – Indigo

If you are getting the error "Specified cast is not valid". Try put ToList right after AsEnumerable. Solution from: #22160195 – Footman

var ProTLData = ds.Tables[0].AsEnumerable().ToList().Select(s => new IssueViewModel { ProjectId = s.Field<long>("ProjectId"), RoleId = s.Field<long>("RoleId"), UserId = s.Field<long>("UserId"), UserName = s.Field<string>("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 – Emanuelemanuela

Answer 2

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

Astera

love this, great answer and also a nice example of generics. – Flub

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 – Canty

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? – Borgeson

yes @StevenVolckaert – Astera

Answer 3

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

Hube

Answer 4

Use the code below:

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

Phocis

Best way to do it. – Tomchay

Answer 5

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

Boarer

Just a small comment but string is already nullable, no use to use string? – Kiakiah

I cant find the ToList() method , it does not come up – Kin

@Kin use - using System.Linq; – Coimbra

Answer 6

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

Oakley

+1. This gets me closer to the answer of my own XML serialization/deserialization question. – Feuilleton

Answer 7

                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;

Shabbygenteel

Answer 8

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

Ias

Answer 9

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;
    }
}

Sebastien

where is the declaration for row variable from the if being used in the ToList method? – Vendetta

You forgot the foreach loop after line List<T> result = new List<T>(); – Chainsmoke

this doesnt build as is. row is not instantiated in your ToList method – July

Answer 10

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;
    }

Paediatrics

Answer 11

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

Midweek

Please add some explanation to the mentioned source code. – Doran