Generic List

Question

User-797751191 posted

Hi

  How the below code works & how i can call this by passing Sql Query

public static DataTable ToDataTable<T>(this List<T> iList)
        {
            DataTable dataTable = new DataTable();
            PropertyDescriptorCollection propertyDescriptorCollection =
                TypeDescriptor.GetProperties(typeof(T));
            for (int i = 0; i < propertyDescriptorCollection.Count; i++)
            {
                PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
                Type type = propertyDescriptor.PropertyType;

                if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
                    type = Nullable.GetUnderlyingType(type);


                dataTable.Columns.Add(propertyDescriptor.Name, type);
            }
            object[] values = new object[propertyDescriptorCollection.Count];
            foreach (T iListItem in iList)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = propertyDescriptorCollection[i].GetValue(iListItem);
                }
                dataTable.Rows.Add(values);
            }
            return dataTable;
        }

Thanks

Answer 1

User288213138 posted

Hi jsshivalik,

How the below code works

This method is to convert the list to a DataTable. I commented the code for you understanding.

public static DataTable ToDataTable<T>(this List<T> iList)
        {
            DataTable dataTable = new DataTable();
            //Creates a new collection and assign it the properties for typeof(T)
            PropertyDescriptorCollection propertyDescriptorCollection = TypeDescriptor.GetProperties(typeof(T));
            //Traverse this collection
            for (int i = 0; i < propertyDescriptorCollection.Count; i++)
            {
                //Set the PropertyDescriptor
                PropertyDescriptor propertyDescriptor = propertyDescriptorCollection[i];
                //Get the type of the property
                Type type = propertyDescriptor.PropertyType;
                //type.IsGenericType represent the current type is a generic type
                //type.GetGenericTypeDefinition() return a Type object that represents a generic type definition from which the current generic type can be constructed
                //Nullable<> represent a value type that can be assigned null
                if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
                    
                    type = Nullable.GetUnderlyingType(type);   //Return the underlying type argument of the specified nullable type.

                dataTable.Columns.Add(propertyDescriptor.Name, type);  // add propertyDescriptor.Name as the table column name
            }
            //Define an array
      
            object[] values = new object[propertyDescriptorCollection.Count];
            foreach (T iListItem in iList)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    //Assign the value of the collection propertyDescriptorCollection to value
                    values[i] = propertyDescriptorCollection[i].GetValue(iListItem);
                }
                dataTable.Rows.Add(values);  //add the values as the table value
            }
            return dataTable;
        }

how i can call this by passing Sql Query

Can you tell me why call this method by passing sql query? what is its application scenario?

Best regards,

Sam

Answer 2

User-797751191 posted

Hi Samwu

  Thank but how i will call this method

Thanks

Answer 3

User753101303 posted

Hi,

Which db access API are you using ? Some more context could help.

If you want to fill a DataTable using a SQL query you could use https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter?view=netframework-4.8  (this code sample uses a DataSet but you can use the same code with a DataTable instead).

Or do you really want to use for example Entity Framework to fill a list of objects and then fill a DataTable from this list?

Answer 4

User475983607 posted

  Thank but how i will call this method

The shared snippet is part of an extension method pattern  (it's missing the class)  that converts a List<T> into a DataTable. 

Implementation would look similar to the following.

List<MyType> items = datalayer.getMyItems();
DataTable dt = items.ToDataTable();

Extension method reference.

https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/classes-and-structs/extension-methods

Answer 5

User288213138 posted

Hi jsshivalik,

As PatriceSc said, using adapter can meet your needs.

private static DataTable dt(DataTable datatable,string connectionString, string queryString)
        {
            using (SqlConnection connection =new SqlConnection(connectionString))
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(queryString, connection);
                adapter.Fill(datatable);
                return datatable;
            }
        }

The above is the sql database. what should note is what database you are using.

Best regards,

Sam

Answer 6

User-797751191 posted

Hi Samwu

   With this i can use only Sql Database . Suppose some other Database is used at later stage then this code may not work.

Secondly how DataTable datatables works

private static DataTable dt(DataTable datatable,string connectionString, string queryString)

Thanks

Answer 7

User288213138 posted

Hi jsshivalik,

Suppose some other Database is used at later stage then this code may not work

Other databases are similarly written. you can refer to this link:

https://docs.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbdataadapter?view=netframework-4.8

Secondly how DataTable datatables works

I don't quite understand what you mean. Isn't this a variable that declares a DataTable type? 

Best regards,

sam