Solved

consuming the results of Linq query, convert to datatable

Posted on 2010-09-22
5
630 Views
Last Modified: 2013-11-11
I have a LINQ query that selects records from the table.
1)  I am wondering how can I convert this to datatable so I can bind this to a datagrid.
2) should I even be converting this to a datatable, is there a better way for working with the record set?
i am working in asp.net 4.0 application in vb.net
Any suggestions would be great. thanks
0
Comment
Question by:TrialUser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Expert Comment

by:joriszwaenepoel
ID: 33737525
Do you mean the gridview control?  The DataSource does not need to be a datatable.  A List or other collection (LINQ  query result) will work just as well for databinding.

myGridView.DataSource = from item in myList where ....
0
 
LVL 9

Expert Comment

by:abhinayp86
ID: 33737735
Here is an example i use in my proj, for how to Linq-query a datatable n bind it to grdview(works for record set as well)
DataTable dtTemp = new DataTable();
        dtTemp = (DataTable)ViewState["table"];
        DataTable dtNewTable = new DataTable();
        dtNewTable = dtTemp.Copy();
        IEnumerable<DataRow> ienNewDataRow = default(IEnumerable<DataRow>);
ienNewDataRow = dtTemp.AsEnumerable()
                    .Where(t => t.Field<string>("CategoryNM") == ddlCategory.Text);
if (ienNewDataRow.Count() > 0)
                dtNewTable = ienNewDataRow.CopyToDataTable();
            else
                dtNewTable = dtTemp.Clone();
gvResults.DataSource = dtNewTable;
        gvResults.DataBind();

Open in new window

0
 
LVL 5

Expert Comment

by:vivekpv10
ID: 33742469
for converting linq result into a datatable,use this function..
eg.
var drevlinq = from drelinq_obj in context.drev where userid == drelinq_obj.user_master.user_id select new { drelinq_obj.dreid };
 DataTable devtab = LINQToDataTable(drevlinq);

public DataTable LINQToDataTable<T>(System.Collections.Generic.IEnumerable<T> varlist)
    {
        DataTable dtReturn = new DataTable();

        // column names 
        PropertyInfo[] oProps = null;

        if (varlist == null) return dtReturn;
    

        foreach (T rec in varlist)
        {
            // Use reflection to get property names, to create table, Only first time, others 
            //   will follow 
            if (oProps == null)
            {
                oProps = ((Type)rec.GetType()).GetProperties();
                foreach (PropertyInfo pi in oProps)
                {
                    Type colType = pi.PropertyType;

                    if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
                    == typeof(Nullable<>)))
                    {
                        colType = colType.GetGenericArguments()[0];
                    }

                    dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
                }
            }

            DataRow dr = dtReturn.NewRow();

            foreach (PropertyInfo pi in oProps)
            {
                dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
                (rec, null);
            }

            dtReturn.Rows.Add(dr);
        }
        return dtReturn;
    }

Open in new window

0
 
LVL 5

Accepted Solution

by:
vivekpv10 earned 500 total points
ID: 33742512
ok..posting vb.net code also...
Public Function LINQToDataTable(Of T)(varlist As System.Collections.Generic.IEnumerable(Of T)) As DataTable
	Dim dtReturn As New DataTable()

	' column names 
	Dim oProps As PropertyInfo() = Nothing

	If varlist Is Nothing Then
		Return dtReturn
	End If
	'   T rec = default(T);

	For Each rec As T In varlist
		' Use reflection to get property names, to create table, Only first time, others 
		'   will follow 
		If oProps Is Nothing Then
			oProps = DirectCast(rec.[GetType](), Type).GetProperties()
			For Each pi As PropertyInfo In oProps
				Dim colType As Type = pi.PropertyType

				If (colType.IsGenericType) AndAlso (colType.GetGenericTypeDefinition() = GetType(Nullable(Of ))) Then
					colType = colType.GetGenericArguments()(0)
				End If

				dtReturn.Columns.Add(New DataColumn(pi.Name, colType))
			Next
		End If

		Dim dr As DataRow = dtReturn.NewRow()

		For Each pi As PropertyInfo In oProps
			dr(pi.Name) = If(pi.GetValue(rec, Nothing) Is Nothing, DBNull.Value, pi.GetValue(rec, Nothing))
		Next

		dtReturn.Rows.Add(dr)
	Next
	Return dtReturn
End Function

Open in new window

0
 

Author Comment

by:TrialUser
ID: 33744419
Vivek:

Ur code works great to convert to datatable. does what I wanted. Thanks a ton for posting the vb version.

But I was wondering if it is also possible to convert it to a typed dataset. I am opening that as a separate question tough.

If it is possible, please respond. Thanks a ton.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question