System.Data.SQLite

    Hi,
         I'm totally new to SQLite and am just a little familiar with the ADODB classes. I'm trying to design a simple database and table creation tool using C# on .NET 2.0. So far I've been able to create databases and tables using the ADO.NET wrapper found here. But where I'm failing is to read the table metadata back and displaying it on screen. I can't figure out the exact code to do this. Now my code works kind of one-way :D i.e., you can create tables - but cannot see the columns/indices and other associated metadata. Can anyone provide me with a code sample as to how to do this?

        What I did try though - was to pass a "DESCRIBE tablename" statement through the ADO.NET Provider to SQLite, hoping it would return the structure of the table, from where I can parse the data and show on screen. But DESCRIBE doesn't seem to work on SQLite.. (I know there's a .describe command - but that works only with the command line tool)

       Any help will be greatly appreciated. Code samples would be even better :)

Thanks,
m^e

What you want is the GetSchema() function on the connection.

DbConnection cnn = new SQLiteConnection("Data Source=mydb.db3");
DataSet schemaSet = new DataSet();
string[] tableInfo = new string[] { null, null, "MyTable", null };

schemaSet.Add(cnn.GetSchema("Columns", tableInfo));
schemaSet.Add(cnn.GetSchema("Indexes", tableInfo));
schemaSet.Add(cnn.GetSchema("IndexColumns", tableInfo));
schemaSet.Add(cnn.GetSchema("ForeignKeys", tableInfo));

The above code will query all the available schema information for the table "MyTable" in the database "mydb.db3" and put it in a DataSet consisting of multiple data tables.  You'll then have to wade through all that data and make sense of it :)

Robert

 

Awesome :) Thanks. But can I do that for the TABLEs too ? I mean when I simply have a database - and I've no clue what the tables are inside - can I use the above method and do something like:
schemaSet.Add(cnn.GetSchema("Tables", tableInfo));

to get the info on how many tables are there? And then maybe in a loop I go through each table and use your code to find each table specific data.. It should work, right ?

Moreover, in such a case what form would this statement take:
string[] tableInfo = new string[] { null, null, "MyTable", null };

Regards,
m^e

Of course.  Call cnn.GetSchema("Tables"); to fetch a list of all the tables in the database.

Just replace the 3rd parameter in the string array with the name of the table from the DataTable of table names.

Robert

 

Thanks :) Got it working. I might drop by for further queries about other aspects of the wrapper.. Good work on that :)

 Sorry I am new here, I am trying to retrieve the column names from the sql lite schema from a SPECIFIC table name. How can this be done..

 

 Dim SchemaColumn = SQLconnect.GetSchema(SQLite.SQLiteMetaDataCollectionNames.Columns)
                    For int As Integer = 0 To SchemaColumn.Rows.Count - 1
                    
                        Console.WriteLine(SchemaColumn.Rows(int)!COLUMN_NAME.ToString())
                        strTempData &= SchemaColumn.Rows(int)!COLUMN_NAME.ToString() & ","
                        ' End If
                    Next

 

The above retrieves ALL the columns of ALL the tables. I only need columns from a specidied table. Can this be done?

 

Thanks

 

Philip

I'm having a similar problem. If I have a table named "Test" with 3 columns, and I use the following code: DataTable dt = Connection.GetSchema( SQLiteMetaDataCollectionNames.Columns, new string[ { null, null, "Test" }); DataColumnCollection columns = dt.Columns; columns always comes back with 31 entries in its list: + [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} If I try to get all of the tables in my database, I get 7 tables, like: + [0] {TABLE_CATALOG} object {System.Data.DataColumn} + [1] {TABLE_SCHEMA} object {System.Data.DataColumn} + [2] {TABLE_NAME} object {System.Data.DataColumn} + [3] {TABLE_TYPE} object {System.Data.DataColumn} + [4] {TABLE_ID} object {System.Data.DataColumn} + [5] {TABLE_ROOTPAGE} object {System.Data.DataColumn} + [6] {TABLE_DEFINITION} object {System.Data.DataColumn} I'm not sure what I'm doing wrong here... And why is the formatting all messed up? Perhaps I need to use a different browser.

if you are having problems with this as well, the PRAGMA table_info(Test) command is one way to get the job done, and it works.