Solved

consuming the results of Linq query, convert to datatable

Posted on 2010-09-22
5
627 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

679 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