System.Reflection, how to get field & property position?

Question

Question: I use the below class to serialize a csv file to

List<MyCsvFileType>

Then I convert the List<MyCsvFileType> to a datatable using

MyConvertTo

(listed below as well). The problem now is, MyConvertTo changes the position of the columns, meaning all properties come after all public variables.

The problem this causes, is that afterwards I use SqlBulkCopy to copy the DataTable into a database table. And unfortunately, SqlBulkCopy uses the indexes of the column rather than the column name for automapping datatable to database-table.

Which means I get nice error messages because it tries to insert the alphanumeric AP_FL_CADKey in the float area field (because my database-tables have the same layout as the class)...

Is there any way I can get the position of the fields & properties in MyConvertTo ? So I don't have to change my database tables (lots) or write my own implementation of BulkCopy , because I don't want to set the mapping between datatable and database-table manually as well ?

It should be possible, in the worst case by sorting the by the fields offsets...

Class:

[FileHelpers.IgnoreFirst]
[FileHelpers.IgnoreEmptyLines]
public class MyCsvFileType
{
    public string FL_CADKey;
    public string FL_DWG;
    public string FL_ObjID;

    public string FL_Area;

    public double FL_Area_double
    {
        get
        {
            if (string.IsNullOrEmpty(this.FL_Area))
                return 0.0;
            else
                return System.Convert.ToDouble(this.FL_Area);
        }

    } // End Property PP_Area_double


    public string AP_FL_CADKey;


    public System.Guid UID
    {

        get
        {
            return System.Guid.NewGuid();
        }

    } // End Property UID
}

MyConvertTo:

public static DataTable MyConvertTo(dynamic custs)
{
    DataTable dt = MyCreateTable(custs);

    System.Data.DataRow dr = null;
    foreach (dynamic cli in custs)
    {
        Console.WriteLine();

        Type t = cli.GetType();

        dr = dt.NewRow();

        foreach (System.Reflection.FieldInfo fi in t.GetFields())
        {
            dr[fi.Name] = fi.GetValue(cli);
        }

        foreach (System.Reflection.PropertyInfo pi in t.GetProperties())
        {
            dr[pi.Name] = pi.GetValue(cli, null);
        }

        dt.Rows.Add(dr);
    } // Next cli

    return dt;
}



public static DataTable MyCreateTable(dynamic custs)
    {
        DataTable table = new DataTable("Table_1");

        foreach (dynamic cli in custs)
        {
            Console.WriteLine();

            Type t = cli.GetType();

            foreach (System.Reflection.FieldInfo fi in t.GetFields())
            {
                table.Columns.Add(fi.Name, fi.FieldType);
                //Console.WriteLine(fi.Name + ": " + fi.GetValue(cli));
            }

            foreach (System.Reflection.PropertyInfo pi in t.GetProperties())
            {
                table.Columns.Add(pi.Name, pi.PropertyType);
                //Console.WriteLine(pi.Name + ": " + pi.GetValue(cli, null));
            }

            break;
        } // Next cli

        return table;
    }

Answer 1

The order of the fields returned is not guaranteed to be the same, although commonly is the order defined.

I suggest you use Linq to order the array returned in a consistent way and then use your convert.

Answer 2

Can you not just set the ColumnMappings in the SqlBulkCopy instance, to add an identity mapping based on column names?

If the data source and the destination table have the same number of columns, and the ordinal position of each source column within the data source matches the ordinal position of the corresponding destination column, the ColumnMappings collection is unnecessary. However, if the column counts differ, or the ordinal positions are not consistent, you must use ColumnMappings to make sure that data is copied into the correct columns.

E.g. as you add each column, just call .Add(ColumnName,ColumnName).