[SQLite3] handling returned data from PRAGMA table_info("table_name") in C#?

[bbranded]

Hello, I'm attempting to query a list of tables from an SQLite3 database and have come across the query PRAGMA table_info("table_name") in the SQLite docs. Here is my code:

OdbcConnection DbConnection;
OdbcDataReader DbReader;

DbConnection = new OdbcConnection("DSN=" + odbcConnName);

DbConnection.Open();

OdbcCommand DbCommand = DbConnection.CreateCommand();
//check to make sure column names are present
DbCommand.CommandText = ("PRAGMA table_info(bandwidth_records)");
DbReader = DbCommand.ExecuteReader();

            while (DbReader.Read())
            {
                Trace.WriteLine(DbReader\["type"\].ToString());
            }

DbReader.Close();
DbConnection.Close();

However, DbReader.Read() is not returning true. Very weird. Any ideas? Thanks, Matt

[loyal ginger]

I am not sure what was wrong with your case, but a test on my system returned the correct info. Are you sure you want to use the PRAGMA commands in your programs? Based on the documentation future versions of SQLite will not guarantee the backward compatability for these PRAGMA commands.

[Dimitri Witkowski]

This will not work in SQLite via ODBC (if you use SQLite.Data.Dll everything will be ok) However, using ODBC you can get table fields too. Here's a sample:

DbConnection.Open();
DataTable dt = DbConnection.GetSchema("Columns");
foreach (var col in dt.Columns) {
Console.Write(col + "\t");
}
foreach (DataRow row in dt.Rows) {
foreach (object field in row.ItemArray) {
Console.Write(field + "\t");
}
Console.WriteLine();
}

See my article about Windows 7 Taskbar timer here on CodeProject

[Dimitri Witkowski]

Hm.. loyal ginger, which SQLite-ODBC driver have you used? I want it too

See my article about Windows 7 Taskbar timer here on CodeProject

[bbranded]

Thanks! I will look into the DataTable class more. Perfect.

[Dimitri Witkowski]

DataTable class itself isn't exactly what you need - it just contains the data. You should look at GetSchema method of DbConnection class. This method returns the metaschema of data for different databases.

See my article about Windows 7 Taskbar timer here on CodeProject

[bbranded]

Thanks! DbConnection class

[bbranded]

Is there a given interface to the ODBC items:

TABLE_CAT
TABLE_SCHEM
TABLE_NAME
COLUMN_NAME
DATA_TYPE
TYPE_NAME
COLUMN_SIZE
BUFFER_LENGTH
DECIMAL_DIGITS
NUM_PREC_RADIX
NULLABLE
REMARKS
COLUMN_DEF
SQL_DATA_TYPE
SQL_DATETIME_SUB
CHAR_OCTET_LENGTH
ORDINAL_POSITION
IS_NULLABLE

to only parse certain "fields?" Thanks for your time, Matt

[Dimitri Witkowski]

What do you mean as interface to the ODBC items? I don't completely understand your question. Please be more concrete, maybe I'll be able to help you.

See my article about Windows 7 Taskbar timer here on CodeProject