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);
}
}
}