views:

6

answers:

0

I'm creating a .exe in C# to import data from Excel and insert into my DataBase.

This Excel file has in each column the name and the values below and for each Table in DB it has a sub table(those that are changed on the bottom of the page in Excel) with the table name.

It's working fine, but one of the tables in my DataBase has a foreign key, so I put the related table in excel to be the last in order to make everything be inserted before. But when I call "GetSchema("TABLES")" it returns a DataTable with its Rows order by the name of the sub table.

What I need is to insert the tables from Excel to DB in the exact order they display in the Excel file.

This is a excerpt of the code I'm using to load the data from Excel:

private void LoadMetaData()
    {
        DataTable dtTables = _conn.GetSchema("TABLES");
        _tables = new List<TableDef>();

        foreach (DataRow drTab in dtTables.Rows)
        {
            string tab = drTab["TABLE_NAME"].ToString();

            if (tab.Trim().EndsWith("$"))
            {
                TableDef table = new TableDef();
                table.Columns = new List<ColumnDef>();
                table.TableName = tab.Substring(0, tab.Length - 1);

                DataTable dtColumns = _conn.GetSchema("COLUMNS");

                foreach (DataRow drCol in dtColumns.Rows)
                {
                    if (drCol["TABLE_NAME"].ToString() == tab)
                    {
                        ColumnDef col = new ColumnDef();
                        col.ColumnName = drCol["COLUMN_NAME"].ToString();
                        col.DataType = drCol["DATA_TYPE"].ToString();
                        col.Ignore = false;
                        table.Columns.Add(col);
                    }
                }
                _tables.Add(table);
            }
        }
    }