Solved

consuming the results of Linq query, convert to datatable

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now