How to get the first column from a table obtained from GetSchema?

Question

First off, I apologize for the title. I don't think it accurately describes what I'm trying to do without more explanation.

I'm using an ODBCConnection and the GetSchema() method to obtain a list of tables in schema a database. This works fine. However, I have a new requirement where I need to obtain either the first column name or the name of the primary key column. I'd like to know how to obtain both, but if I have to choose, I'll take the first column name.

Here's the code I'm using to obtain the list of tables:

private List<string> GetSortedSchemaList(string strConnection, string strSchema)
{
    List<string> lstSortedSchemaList = new List<string>();

    using (OdbcConnection odbcCon = new OdbcConnection(strConnection))
    {
        try
        {
            odbcCon.Open();

            using (DataTable tableSchema = odbcCon.GetSchema("TABLES"))
            {                       
                OdbcCommand odbcCmd = new OdbcCommand();

                foreach (DataRow row in tableSchema.Rows)
                {
                    string tableSchem = row["TABLE_SCHEM"].ToString();
                    if (strSchema != "ABC")
                    {
                        lstSortedSchemaList.Add(row["TABLE_SCHEM"] + "." + row["TABLE_NAME"]);
                    }
                }
            }

            odbcCon.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString() + "\nFailed to connect to data source");
        }
    }

Then I call the previous method in the following method:

private void GetTableCounts
    (string strConnection, string strSchemaName, int intTotalTables, BackgroundWorker worker, DoWorkEventArgs e)
{
    List<string> lstSchema = new List<string>(GetSortedSchemaList(strConnection, strSchemaName));

    using (OdbcConnection odbcCon = new OdbcConnection(strConnection))
    {
        try
        {
            odbcCon.Open();
            if (strSchemaName == "ABC")
            {
                AppendTextBoxPM(strSchemaName + " tables processing...\r\n");
            }

            OdbcCommand odbcCmd = new OdbcCommand();

            foreach (var i in lstSchema)
            {
                var item = i;
                Debug.Write((lstSchema.IndexOf(item) + 1) + ". Item = " + item + "\r\n");
                odbcCmd.CommandText = "SELECT Count(ID) FROM " + item;
                odbcCmd.Connection = odbcCon;
                if (strSchemaName == "ABC")
                {
                    AppendTextBoxPM(item + " Count = " + Convert.ToInt32(odbcCmd.ExecuteScalar()) + "\r\n");
                }

                int intPercentComplete = (int)((float)(lstSchema.IndexOf(item) + 1) / (float)intTotalTables * 100);

                Thread.Sleep(100);
                worker.ReportProgress(intPercentComplete);
                ModifyLabel(" (" + (lstSchema.IndexOf(item) + 1) + " out of " + intTotalTables + " processed)");
            }
            odbcCmd.Dispose();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString() + "\nFailed to connect to data source");
            return;
        }
    }
}

So, I can get the list of tables and I'm able to run ODBC command to query those tables. However, the query I'm using, SELECT Count(ID) FROM Item, won't work because not every table has a field called ID. Hence the reason for grabbing the first field. I can't use SELECT Count(*) FROM Item because the database I'm using allows calculated fields and performing a Count(*) causes the query/queries to take an incredibly long time to complete because it is counting all the calculations (or so I'm told by my vendor).

Anyway, I don't quite understand how the GetSchema() method works even after reading thoroughly and reviewing every relevant SO post, Google search result, etc. I understand that I have to drill down with GetSchema() to get the column names, but I'm not sure how to apply that in my situation since all the examples I've seen display all the columns at once using a foreach statement/loop.

Bottom-line, as I'm adding the table to the lstSortedSchemaList list, I'd also like to get the first column name for that table. It can be obtained in the first code snippet and concatenated to the item I'm adding to the list (and I can parse it) or obtained in the second code snippet if that's more elegant.

Everything I tried that followed the examples I found across the Inter-webs just output "TABLE_CAT" for the column name. I basically sent the tableSchema data table to another method and used another foreach loop with a DataRow and an embedded foreach with a DataColumn, but that didn't work like I thought it might.

Thanks for the help.

Answer 1

Once you have the name of the table you could call again the GetSchema method to retrieve the columns name of a specific table ordering them by the field ORDINAL_POSITION

DataTable schemaCols = con.GetSchema("Columns");
DataRow[] rows = schemaCols.Select("TABLE_NAME='" + tableName + "'", "ORDINAL_POSITION");
string firstCol = rows[0]["COLUMN_NAME"].ToString();
Console.WriteLine(firstCol);