Tuesday 12 August 2014

how to use linq query on datatable in c#

hi friends once i got the task to query and filter datatable with linq where i got exception lik
"Invalid cast exception".
Finally when i got the solution i thought to share it with all.

1)i have created my DataTable with some dummy values:

public DataSet DummyDataTable()
            {

                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                dt.Columns.Add("Id", typeof(int));
                dt.Columns.Add("Name", typeof(string));
                dt.Columns.Add("Phone", typeof(string));
                DataRow dr = dt.NewRow();
                dr["Id"] = 1;
                dr["Name"] = "shoeb";
                dr["Phone"] = "9773217179";
                dt.Rows.Add(dr);
                dr = dt.NewRow();
                dr["Id"] = 2;
                dr["Name"] = "abdul";
                dr["Phone"] = "9821838577";
                dt.Rows.Add(dr);
                ds.Tables.Add(dt);
                return ds;

            }

2)here is my linq query to sort DataTable 


public void DataSetLinq()
            {
                DataSet ds = DummyDataTable();
                DataTable dt = new DataTable();
                dt = ds.Tables[0];
                var x = from m
                        in dt.AsEnumerable()
                        where m.Field<int>("Id") == 1
                        select new
                        {
                            Id = m.Field<int>("Id"),
                            Name = m.Field<string>("Name"),
                            Phone = m.Field<string>("Phone")
                        };
                foreach (var item in x)
                {
                    Console.WriteLine("{0} {1} {2}", item.Id, item.Name, item.Phone);
                }
            }

OUTPUT:



The point is to use your DataTable as Enumerable, where you access strongly-typed columns. 

IMPORTANT: be aware that using .Field<Datatype> you cast the data from that column to specified type -
If you cast types that cannot be casted, you will receive "Invalid cast exception".

No comments:

Post a Comment