How to return names of all columns?

Question

Searching, I found the PRAGMA as a possible solution for my problem, but it only returns the index of each column. There's any other method to return all columns names?

I thought using a For to go through my column indexes returning their names would works fine, but I dont exactly know how the syntax of this would be, either the stop condition.

void FillColumnList()
        {
            try
            {
                string check = "SELECT * FROM PRAGMA table_info(Produtos)";
                sqlCon.Open();
                SQLiteCommand tst2 = new SQLiteCommand(check, sqlCon);
                SQLiteDataReader rdr2 = tst2.ExecuteReader();                
                if (rdr2.HasRows)
                {
                    while (rdr2.Read())
                    {
                        string columns = rdr2[0].ToString();
                        Columns.Add(columns);
                    }
                    sqlCon.Close();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }            
        }

This code should return and fill the Global variable list Columns with the name of each column of "Produtos" table. Instead of it, my DataReader 'rdr2' return false in the HasRows, even when there's columns and Datas in my table "Produtos"

Olivier Jacot-Descombes Olivier Jacot-Descombes · Accepted Answer · 2019-01-19T19:01:38

You can use the connection's GetSchema method to retrieve the column information. I'm using the following code to insert information my own class TableColumn not shown here:

string[] restrictions = new string[] { null, null, tableName };

using (DataTable columns = conn.GetSchema("Columns", restrictions)) {
    int nameIndex = columns.Columns.IndexOf("COLUMN_NAME");
    int ordinalPosIndex = columns.Columns.IndexOf("ORDINAL_POSITION");
    int isNullableIndex = columns.Columns.IndexOf("IS_NULLABLE");
    int maxLengthIndex = columns.Columns.IndexOf("CHARACTER_MAXIMUM_LENGTH");
    int dataTypeIndex = columns.Columns.IndexOf("DATA_TYPE");
    int isPrimaryKeyIndex = columns.Columns.IndexOf("PRIMARY_KEY");
    int hasDefaultIndex = columns.Columns.IndexOf("COLUMN_HASDEFAULT");
    int defaultValueIndex = columns.Columns.IndexOf("COLUMN_DEFAULT");

    foreach (DataRow row in columns.Rows) {
        var col = new TableColumn {
            ColumnName = (string)row[nameIndex]
        };
        try {
            col.ColumnNameForMapping = prepareColumnNameForMapping(col.ColumnName);
        } catch (Exception ex) {
            throw new UnimatrixExecutionException("Error in delegate 'prepareColumnNameForMapping'", ex);
        }
        col.ColumnOrdinalPosition = (int)row[ordinalPosIndex];
        col.ColumnAllowsDBNull = (bool)row[isNullableIndex];
        col.ColumnMaxLength = (int)row[maxLengthIndex];

        string explicitDataType = ((string)row[dataTypeIndex]).ToLowerInvariant();
        col.ColumnDbType = GetColumnDbType(explicitDataType);
        col.ColumnIsPrimaryKey = (bool)row[isPrimaryKeyIndex];
        col.ColumnIsIdentity = explicitDataType == "integer" && col.ColumnIsPrimaryKey;
        col.ColumnIsReadOnly = col.ColumnIsIdentity;

        if ((bool)row[hasDefaultIndex]) {
            col.ColumnDefaultValue = GetDefaultValue(col.ColumnDbType, (string)row[defaultValueIndex]);
            if (col.ColumnDefaultValue == null) { // Default value could not be determined. Probably expression.
                col.AutoAction = ColumnAction.RetrieveAfterInsert;
            }
        }
        tableSchema.ColumnSchema.Add(col);
    }
}

You can simplify this code considerably if you only need the column names.

How to return names of all columns?

1 Answers