none
Converting DataTable to List of objects RRS feed

  • Question

  • I am trying to convert my DataTable to a list of objects but I am getting an error saying "specified cast is not valid"  when I am now trying to create a list from the data I got from the database
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp3
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
            private void Form1_Load(object sender, EventArgs e)
            {
                myConnectionString = "Data Source=" + ".\\SQLServerInstance" + @";Initial Catalog = " + "DatabaseName" + @";Integrated Security=SSPI;";
            }
            
            string myConnectionString;
            private void button2_Click(object sender, EventArgs e)
            {
                SqlConnection myGetTaxTypeConnection = new SqlConnection(myConnectionString);
                SqlDataAdapter myGetTaxTypeDataAdapter = new SqlDataAdapter("SELECT * FROM Settings_TaxType", myGetTaxTypeConnection);
                SqlCommandBuilder myGetTaxTypeCommandBuilder = new SqlCommandBuilder(myGetTaxTypeDataAdapter);
                DataTable myGetTaxTypeDataTable = new DataTable();
                myGetTaxTypeDataTable.Reset();
                myGetTaxTypeDataAdapter.Fill(myGetTaxTypeDataTable);
    
                List<myTaxType> myTaxTypesList = (from myDataRow in myGetTaxTypeDataTable.AsEnumerable()
                select new myTaxType()
                {
                        TaxTypeID = myDataRow.Field<int>("TaxTypeID"),
                        TaxType = myDataRow.Field<string>("TaxType"),
                        TaxRate = myDataRow.Field<decimal>("TaxRate"),
                        TaxStartDate = myDataRow.Field<DateTime>("TaxStartDate"),
                        TaxEndDate = myDataRow.Field<DateTime>("TaxEndDate"),
                        Discontinued = myDataRow.Field<bool>("Discontinued")
                }).ToList();
            }
            class myTaxType
            {
                public int TaxTypeID { get; set; }
                public string TaxType { get; set; }
                public decimal TaxRate { get; set; }
                public DateTime TaxStartDate { get; set; }
                public DateTime TaxEndDate { get; set; }
                public bool Discontinued { get; set; }
            }        
        }
    }
    
    When this method did not work I tried a different approach like this and it is also giving the same error
    List<TaxType> listName = myGetTaxTypeDataTable.AsEnumerable().Select(m => new TaxType()
    {
        TaxTypeID = m.Field<int>("TaxTypeID"),
        myTaxType = m.Field<string>("TaxType"),
        TaxRate = m.Field<decimal>("TaxRate"),
        TaxStartDate = m.Field<DateTime>("TaxStartDate"),
        TaxEndDate = m.Field<DateTime>("TaxEndDate")
    }).ToList();
    Please help me . I dont want to loop

    If you think it you can achieve it

    Monday, February 18, 2019 11:03 AM

All replies

  • This is the structure of the table I am getting the data from
    CREATE TABLE [dbo].[Settings_TaxType](
    	[TaxTypeID] [bigint] IDENTITY(1,1) NOT NULL,
    	[TaxType] [nvarchar](40) NOT NULL,
    	[TaxRate] [decimal](18, 3) NULL,
    	[TaxStartDate] [datetime] NULL,
    	[TaxEndDate] [datetime] NULL,
    	[Discontinued] [bit] NOT NULL CONSTRAINT [DF_TaxType_Discontiued]  DEFAULT ((0)),
     CONSTRAINT [PK_TaxType] PRIMARY KEY CLUSTERED 
    (
    	[TaxTypeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO



    If you think it you can achieve it

    Monday, February 18, 2019 11:05 AM
  • Why bother with a datatable when you just could have used a datareader and a List<T>.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

    You can even give column name too.

    https://www.akadia.com/services/dotnet_data_reader.html

    new List<t>

    sql stuff

    reader loop

      create new custom object

      populate object from datareader

      List.Add(object)

    end loop

    Of course, in using the reader, you may have to convert the data to data needed for object property uing a Cast() or Convert().

    Monday, February 18, 2019 12:00 PM
  • There are probably alternate ways to do this (as suggested by DA924x) - and it would help if you indicated the exact line that was failing - but your issue is probably caused because you are trying to cast TaxTypeID, which is a bigint column in the database, to an int.

    Try redefining your property as a long in your myTaxType class:

     public long TaxTypeID { get; set; }

    And then set it using:

     TaxTypeID = myDataRow.Field<long>("TaxTypeID"),


    Monday, February 18, 2019 12:09 PM
  • If the class properties match the types in the database table then the following is an option.

    public static class ExtensionMethods
    {
        public static DataTable ToDataTable<T>(this IList<T> data)
        {
            var properties = TypeDescriptor.GetProperties(typeof(T));
            var table = new DataTable();
    
            foreach (PropertyDescriptor prop in properties)
            {
                table.Columns.Add(prop.Name, 
                    Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }
    
            foreach (T item in data)
            {
                var row = table.NewRow();
    
                foreach (PropertyDescriptor prop in properties)
                {
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                }
    
                table.Rows.Add(row);
            }
    
            return table;
        }
    }

    Example usage

    var peep = new List<Person>();
    var dt = peep.ToDataTable();
    If the types don't match then change types in the concrete class to match the database table.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, February 18, 2019 12:21 PM
    Moderator
  • Hi 

    Is your problem solved? If so, please post "Mark as answer" to the appropriate answer, so that it will help other members to find a solution quickly if they face a similar issue.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Thursday, February 21, 2019 6:52 AM
    Moderator
  • Hi

    using System.Linq;

    List<DataRow> list=dt.AsEnumerable().ToList();

    Best Regards.

    Thursday, February 21, 2019 10:49 AM