Solved

Convert a IList into a datatable and filter the datatable

Posted on 2010-09-01
5
1,477 Views
Last Modified: 2016-09-19
Hi,

I would like to be able to convert an IList into a datatable and then also filter the datatable. How can I do this?

(Would prefer an answer made in vb.net)

Peter
0
Comment
Question by:peternordberg
5 Comments
 
LVL 9

Expert Comment

by:puru1981
ID: 33575575
public static DataTable convertToTable(object[] objs)
        {
            Type t = objs.GetType();
            DataTable dt=new DataTable();
            Type elementType=null;
            foreach (object obj in objs)
            {
                elementType = obj.GetType();
                break;
            }
           
            if (!t.IsArray)
                throw new Exception("Can't convert this object to Table");
            else
            {
                MemberInfo[] fis = elementType.GetMembers(BindingFlags.Public | BindingFlags.Instance);
                foreach (MemberInfo fi in fis)
                {
                    if (fi.MemberType == MemberTypes.Property)
                    {
                        PropertyInfo pi = fi as PropertyInfo;
                        dt.Columns.Add(pi.Name, pi.PropertyType);
                    }
                    else if (fi.MemberType == MemberTypes.Field)
                    {
                        FieldInfo pi = fi as FieldInfo;
                        dt.Columns.Add(pi.Name, pi.FieldType);
                    }
                }
                foreach (object obj in objs)
                {
                    int i = 0;
                    object[] fieldValues = new object[dt.Columns.Count];
                    foreach (DataColumn c in dt.Columns)
                    {
                        MemberInfo mi = elementType.GetMember(c.ColumnName)[0];
                        if (mi.MemberType == MemberTypes.Property)
                        {
                            PropertyInfo pi = mi as PropertyInfo;
                            fieldValues[i] = pi.GetValue(obj, null);
                        }
                        i++;
                    }
                    dt.Rows.Add(fieldValues);

                }
            }
            return dt;
        }


0
 
LVL 16

Expert Comment

by:kris_per
ID: 33576947

See if the following would work for your data:

1. Convert the List into xml using XmlSerializer
2. Then load the xml in DataSet
Now DataSet.Tables property will have the data in a DataTable object
3. Use DataView to filter the data from DataTable.


Imports System.Collections.Generic
Imports System.Data
Imports System.IO
Imports System.Xml.Serialization

Namespace WindowsFormsApplication4
	Public Class MyObject
		Public Name As String
		Public Id As Integer
	End Class

	Public Class Sample
		Public Sub SampleMethod()
			Dim list As New List(Of MyObject)()
			list.Add(New MyObject() With { _
				Key .Name = "one", _
				Key .Id = 1 _
			})
			list.Add(New MyObject() With { _
				Key .Name = "two", _
				Key .Id = 2 _
			})

			' serialize the list to xml
			Dim ser As New XmlSerializer(GetType(List(Of MyObject)))
			Dim stream As Stream = New FileStream("temp.xml", FileMode.Create)
			ser.Serialize(stream, list)
			stream.Dispose()

			' load the xml into a dataset
			Dim ds As New DataSet()
			ds.ReadXml("temp.xml")

			' get the datatable
			Dim dataTable As DataTable = ds.Tables(0)

			' filter it
			Dim dataView As New DataView(dataTable, "Name='one'", "Name", DataViewRowState.CurrentRows)

			' now dataView contains the filtered data
		End Sub
	End Class
End Namespace

Open in new window

0
 
LVL 9

Accepted Solution

by:
abhinayp86 earned 500 total points
ID: 33580305

private DataTable ListToDataTable<T>(List<T> list)
    {
        DataTable dt = new DataTable();

        foreach (PropertyInfo info in typeof(T).GetProperties())
        {
            dt.Columns.Add(new DataColumn(info.Name, info.PropertyType));
        }
        foreach (T t in list)
        {
            DataRow row = dt.NewRow();
            foreach (PropertyInfo info in typeof(T).GetProperties())
            {
                row[info.Name] = info.GetValue(t, null);
            }
            dt.Rows.Add(row);
        }
        return dt;
    }

Open in new window

0
 

Expert Comment

by:Anindita De
ID: 41804792
Hi,
I am trying to take a gridview in ViewState, for that I need to store an IList value(which is returning database value) into DataTable so trying to convert the IList into Datatable and implemented the above code but this is not working for Ilist, Here is my code as follows:
void bindGrid(int iFacilityId)
        {
            BLRoom objRoomBL = new BLRoom(System.Configuration.ConfigurationManager.ConnectionStrings["dbGuestHouseConnectionString"].ConnectionString);

            IList lst_BORoom = null;
            string strStatus = "Success";

            lst_BORoom = objRoomBL.GetAllRoombyFacilityId(iFacilityId, out strStatus);
            if (strStatus == "Success")
            {
                if (lst_BORoom.Count > 0)
                {
                    //DataTable dtCurrentTable = (DataTable)ViewState["lst_BORoom"];  
                    //DataTable dt = ConvertListToDataTable(Convert.ToString(lst_BORoom));
                    //ViewState["ExistingTable"] = lst_BORoom.;
                    DataTable dtoldRooms = new DataTable();
                    dtoldRooms = ConvertListToDataTable(lst_BORoom);
                   
                    trGrid.Visible = true;
                    trSection.Visible = true;
                    GridRoom.DataSource = lst_BORoom;
                    GridRoom.DataBind();
                }
                else
                {
                    trGrid.Visible = false;
                    trSection.Visible = false;
                    DisplayMessage("No rooms configured for this facility.", "red", true);
                }
            }
            else
            {
                trGrid.Visible = false;
                trSection.Visible = false;
                DisplayMessage("Unable to Load: Error in loading data.", "red", true);
            }

           

        }
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 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