Thursday, 20 November 2014

Linq query to datatable conversion

Recently I have encounter with Linq after a long time and I was required to convert linq result in to datatable. It take a while but I found the method i used in past. I hope it will help you too.

 1. use AsEnumerable() in your query.and you will be good to get a function CopyToDataTable() to get datatable.
  Example:
var query =
    from order in orders.AsEnumerable()
    join detail in details.AsEnumerable()
    on order.Field<int>("SalesOrderID") equals
        detail.Field<int>("SalesOrderID")
    where order.Field<bool>("OnlineOrderFlag") == true
    && order.Field<DateTime>("OrderDate").Month == 8
    select new
    {
        SalesOrderID =
            order.Field<int>("SalesOrderID"),
        SalesOrderDetailID =
            detail.Field<int>("SalesOrderDetailID"),
        OrderDate =
            order.Field<DateTime>("OrderDate"),
        ProductID =
            detail.Field<int>("ProductID")
    };

DataTable orderTable = query.CopyToDataTable(); 
 
Original Source : http://msdn.microsoft.com/en-us/library/bb386921%28v=vs.110%29.aspx

2. use this coustom method to get datatable

 Example:
public DataTable ToDataTable(System.Data.Linq.DataContext ctx, object query)
{
     if (query == null)
     {
          throw new ArgumentNullException("query");
     }
    
     IDbCommand cmd = ctx.GetCommand(query as IQueryable);
     SqlDataAdapter adapter = new SqlDataAdapter();
     adapter.SelectCommand = (SqlCommand)cmd;
     DataTable dt = new DataTable("sd");

     try
     {
          cmd.Connection.Open();
          adapter.FillSchema(dt, SchemaType.Source);
          adapter.Fill(dt);
     }
     finally
     {
          cmd.Connection.Close();
     }
     return dt;
}

For getting datatable

var vr = from country in objDataContext.CountryMaster
                        select new {country.CID,country.CName};

DataTable dt = ToDataTable(objDataContext,vr);

Original source : http://www.c-sharpcorner.com/uploadfile/VIMAL.LAKHERA/convert-a-linq-query-resultset-to-a-datatable/

No comments:

Post a comment