c#- Convert DataTable to List, and Count a particular column into Linq form

Question

1. How to Convert Datatable to List that can access its column.

2. Linq count in a specific column.

   DataTable dt = new DataTable(); List<> list = DataTable(); label1.Text = list.Count....where? label2.Text = list.Count....where?

Answer 1

For 1st question:

In order to convert your datatable into a List, you first have to define a class that matches the columns of the datatable.

As you have not defined any schema for your Datatable. As an example, i will take a datatable named dtUsers with 3 cols and 2 rows as :

----------------------------
|  Id  |  Fname  |  Lname  |
----------------------------
|  1   |  John   |  Wick   |
----------------------------
|  2   |  Keanu  |  Reeves |
----------------------------

Transpose that into a class as:

public class User
{
  public int Id {get:set;}
  public string Fname {get:set;}
  public string Lname {get:set;}
}

Now there are 2 ways by which you can achieve it:

Using Newtonsoft.Json(Fast)

The easiest way to convert a Datatable into a List is serializing the datatable and deserializing it into a class.

As per the above example here is how it will be done:

//Assuming that the data is already in the datatable "dtUsers" we first serialize it.
var strUsers = JsonConvert.SerializeObject(dtUsers);

//will output an searialized object of the datatable as:'[{Id:1,"Fname":"John","Lname":"Wick"},{Id:2,"Fname":"Keanu","Lname":"Reeves"}]'

//now deserialize it back into a List<User> as:
var lstUsers = JsonConvert.DeserializeObject<List<User>>(strUsers);

By Matching Property info(Slow)

You can use the given methods to convert the datatable into a List were T is for a generic approach.

Here we first make datatable in use as AsEnumerable then we iterate over each row in datatable and pass them into createObject<T>(row) function to get the class object for it and later append that to the list and return the converted object.

    public List<T> ConvertDatatableToList<T>(DataTable table) where T : class, new()
    {
        try
        {
            List<T> list = new List<T>();
            foreach (var row in table.AsEnumerable())
            {
                var obj = createObject<T>(row);
                list.Add(obj);
            }
            return list;
        }
        catch (Exception ex)
        {
            throw null;
        }
    }

    public T createObject<T>(DataRow row) where T : class, new()
    {
        T obj = new T();
        //itterating over the properties defined in the class
        foreach (var prop in obj.GetType().GetProperties())
        {
            try
            {
                //checking if the property is a generic type and if it is Nullable(?)
                if (prop.PropertyType.IsGenericType && prop.PropertyType.Name.Contains("Nullable"))
                {
                    if (!string.IsNullOrEmpty(row[prop.Name].ToString()))
                        prop.SetValue(obj, Convert.ChangeType(row[prop.Name], Nullable.GetUnderlyingType(prop.PropertyType), null));
                }
                else
                    prop.SetValue(obj, Convert.ChangeType(row[prop.Name], prop.PropertyType), null);

            }
            catch
            {
                continue;
            }
        }
        return obj;
    }

Usage:

var lstUsers = ConvertDatatableToList<User>(dtUsers);

As for the 2nd question:

To get the count you can either put a condition in the Where() and get a count of that or you can put the condition directly in the Count() itself:

For a specific column with Where():

var FnameCount = lstUsers.Where(w=>w.Fname=="John").Count(); 

For a specific column with Count() only:

var FnameCount = lstUsers.Count(w=>w.Fname=="John");