Solved

consuming the results of Linq query, convert to datatable

Posted on 2010-09-22
5
629 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

734 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