Getting table schema doesn't seem to work with System.Data.SQLite

Question

I'm using SQLite.NET to access my databases, and in one particular case, I need to get a table schema because it's application-specific.

I've been looking through the SQLite.NET forum and google, and it seems very straightforward. If I have a table called MYTABLE, I should be able to do this:

DataTable dt = Connection.GetSchema( SQLiteMetaDataCollectionNames.Columns, new string[] { null, null, "MYTABLE", null });

The problem is that, although I do get a DataTable back, it's got all of the wrong information in it. Specifically, this is what I get back for the supposed columns of MYTABLE:

• [0] {TABLE_CATALOG} object {System.Data.DataColumn}
• [1] {TABLE_SCHEMA} object {System.Data.DataColumn}
• [2] {TABLE_NAME} object {System.Data.DataColumn}
• [3] {COLUMN_NAME} object {System.Data.DataColumn}
• [4] {COLUMN_GUID} object {System.Data.DataColumn}
• [5] {COLUMN_PROPID} object {System.Data.DataColumn}
• [6] {ORDINAL_POSITION} object {System.Data.DataColumn}
• [7] {COLUMN_HASDEFAULT} object {System.Data.DataColumn}
• [8] {COLUMN_DEFAULT} object {System.Data.DataColumn}
• [9] {COLUMN_FLAGS} object {System.Data.DataColumn}
• [10] {IS_NULLABLE} object {System.Data.DataColumn}
• [11] {DATA_TYPE} object {System.Data.DataColumn}
• [12] {TYPE_GUID} object {System.Data.DataColumn}
• [13] {CHARACTER_MAXIMUM_LENGTH} object {System.Data.DataColumn}
• [14] {CHARACTER_OCTET_LENGTH} object {System.Data.DataColumn}
• [15] {NUMERIC_PRECISION} object {System.Data.DataColumn}
• [16] {NUMERIC_SCALE} object {System.Data.DataColumn}
• [17] {DATETIME_PRECISION} object {System.Data.DataColumn}
• [18] {CHARACTER_SET_CATALOG} object {System.Data.DataColumn}
• [19] {CHARACTER_SET_SCHEMA} object {System.Data.DataColumn}
• [20] {CHARACTER_SET_NAME} object {System.Data.DataColumn}
• [21] {COLLATION_CATALOG} object {System.Data.DataColumn}
• [22] {COLLATION_SCHEMA} object {System.Data.DataColumn}
• [23] {COLLATION_NAME} object {System.Data.DataColumn}
• [24] {DOMAIN_CATALOG} object {System.Data.DataColumn}
• [25] {DOMAIN_NAME} object {System.Data.DataColumn}
• [26] {DESCRIPTION} object {System.Data.DataColumn}
• [27] {PRIMARY_KEY} object {System.Data.DataColumn}
• [28] {EDM_TYPE} object {System.Data.DataColumn}
• [29] {AUTOINCREMENT} object {System.Data.DataColumn}
• [30] {UNIQUE} object {System.Data.DataColumn}

Can anyone tell me what I've done wrong here?

Answer 1

It's quite possible that SQLiteConnection.GetSchema is broken; most programs do not need this functionality.

You can get what you need by executing a PRAGMA table_info(MYTABLE) command; you should get a data reader with one row for each column.