Solved

consuming the results of Linq query, convert to datatable

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

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

840 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