Generalize converting a Linq-to-SQL object representing a table to a DataTable

Question

I have the following class:

    [Table(Name = "test")]
    public class Test
    {
        private string _Name;
        [Column(Storage = "_Name")]
        public string Name
        {
            get { return this._Name; }
            set { this._Name = value; }
        }

        private int _Age;
        [Column(Storage = "_Age")]
        public int Age
        {
            get { return this._Age; }
            set { this._Age = value; }
        }

        public static DataTable ConvertListToDataTable(List<Test> table) // Used for SqlBulkCopy
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Age", typeof(int));

            foreach (Test row in table)
            {
                DataRow dr = dt.NewRow();
                dr["Name"] = row.Name;
                dr["Age"] = row.Age;

                dt.Rows.Add(dr);
            }

            return dt;
        }
    }

But I'd like to abstract that ConvertListToDataTable method out into a parent base class. Is that possible using reflection? I wouldn't even know where to start.

Note the DataTable will have columns that exactly match (in name without the _ and type) all the properties of the class with a [Column] attribute

This is the reason I want the DataTable:

public void testBulkInsert(string connection, string table, DataTable dt)
    {
        using (SqlConnection con = new SqlConnection(connection))
        {
            con.Open();

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))
            {
                bulkCopy.DestinationTableName = table;

                bulkCopy.WriteToServer(dt);
            }
        }
    }

Look at this [answer](http://stackoverflow.com/a/564373/961113) from Marc Gravell, that has a generic method to convert `List` to datatable. — Habib, Oct 24 '14 at 16:15
@Habib so... I can use his second code block but instead of as an extension method, use it directly inside my bulk insert function and just pass a `List — Dan, Oct 24 '14 at 16:24
I would suggest you have to it as an extension method, and you can use it with any `IList`. In your case once you have extension method set, then you can call your method like: `testBulkInsert(connectionString, tableName, table.ToToDataTable())` — Habib, Oct 24 '14 at 16:29
Dan, if that doesn't solve your issue, or you are looking for something different then please let me know, I will vote to reopen the question. — Habib, Oct 24 '14 at 16:50
@Habib thanks - I might only get to try this on Monday. But I'll give it a shot — Dan, Oct 24 '14 at 17:03
@Habib Thanks - while I don't understand the code, it does the job! — Dan, Oct 27 '14 at 08:53
@Habib I'm actually getting an error if I try use a `Decimal?` type at the line `table.Columns.Add(prop.Name, prop.PropertyType);` of the code you linked to. Any idea how to make this work for nullable types? — Dan, Oct 27 '14 at 15:26
See the next answer http://stackoverflow.com/a/5805044/961113 — Habib, Oct 27 '14 at 15:28

Generalize converting a Linq-to-SQL object representing a table to a DataTable

0 Answers0