System.Data.SQLite

I've learned all the basics... but i'm stuck... i've been trying this for 2 hours and finally am forced to ask.

Dim SchemaTable = SQLconnect.GetSchema(SQLiteMetaDataCollectionNames.Tables)
    For int As Integer = 0 To SchemaTable.Rows.Count - 1
    MsgBox(SchemaTable.Rows(int)!TABLE_TYPE.ToString)
    If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then
        ListBox1.Items.Add(SchemaTable.Rows(int)!TABLE_NAME.ToString())
    End If
Next

I know this is completely wrong now... but how exactly do I get the list of tables. Answer in either C# or VB... i know both I'm just working in vb right now.

Thank you so much :).

Try this:

 Dim seleccionSQL as string="SELECT name FROM SQLITE_MASTER where type='table'"

Dim conexion As SQLite.SQLiteConnection 

conexion = New SQLiteConnection _
            ("Data Source=" & strPathBD _
            & ";UseUTF16Encoding=False;Synchronous=Normal; Version=3;New=False")

conexion.Open()

Dim da As IDbDataAdapter = New SQLiteDataAdapter
da.SelectCommand = New SQLiteCommand(seleccionSQL, conexion)
Dim ds as new DataSet
da.Fill(ds)
conexion.Close()

Then, use something like this to obtain every table name:

ds.tables(0).rows(0).Item(0).tostring

ds.tables(0).rows(1).Item(0).tostring

...and so on.

Opto ut valeas, puerule. 

You neglected to tell me how you're stuck, and what's wrong ... your call to GetSchema() looks fine.

I don't know much about VB syntax, so your ! table access methods seem really funky and weird to me ... but without knowing why you're stuck, I can't really recommend the next step.

And for the record -- apologies to Toni -- but don't use his suggestion :)  It's SQLite-specific and only works for certain kinds of schema queries.  GetSchema() is much more robust, somewhat DB-neutral, and can provide massively more detailed information at the column, index and foreign key levels.

Robert

Thank you so much. Worked perfectly.

Robert do you know the answer in C# then. I know both of them pretty well.

And to tell you the truth I don't know what the ! means. I saw an example online and thought if the namespaces were simular it might work. Almost.

I would love to have a system that was better standards but... if i can't find a way that works I just have to stick with what does.

My example was a complete guess... so really if i can get a list of the tables in anyway it would be great (and once again my code probably doesn't make sense that i gave it was just a hack job that i kind of though made sense).

Easiest thing to do is just run this line in the debugger ... 

Dim SchemaTable = SQLconnect.GetSchema(SQLiteMetaDataCollectionNames.Tables)

Then view the SchemaTable variable in the DataTable viewer (the magnifying glass thing) in the debug window.  It'll grid-out the entire DataTable for you so you can see what the columns are in the data table.

 Thanks robert... i forgot I used "table" not "TABLE"

 just took you saying look over it again for me to notice (and look back). And no i don't know how or why "!" does something. I'll figure out later.

SUCH a SIMPLE fix 

Dim SchemaTable = SQLconnect.GetSchema(SQLiteMetaDataCollectionNames.Tables)
        For int As Integer = 0 To SchemaTable.Rows.Count - 1
            If SchemaTable.Rows(int)!TABLE_TYPE.ToString() = "table" Then
                ListBox1.Items.Add(SchemaTable.Rows(int)!TABLE_NAME.ToString())
            End If
        Next

You are right!  I will bear it in mind next time.

Thanks 

Hi! Sorry, really starting with sql, sqlite, c#... So my question may be stupid:

How to 'translate' the famous '!' in c# (If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "table")?

Actually I can get the values using this strong (but uggly) code : 

DataTable dt = conn.GetSchema(SQLiteMetaDataCollectionNames.Tables);

for (int i=0; i<dt.Rows.Count;i++)

{ 

    if (dt.Rows[i].ItemArray[3 /* e.g. TABLE_TYPE */].ToString()  == "table")

        .../... 

 }

Thanks

Lo 

Hi,

I too am trying to get a list of tables in an SQLite DB, using C#. I've had a look around but can't find any example code for what I guess it a common task.

Looking in the debugger I can see how the code provided by Lo above works, but as mentioned it doesn't seem very robust to be using array indices when these could change in future (ItemArray[3]). So what's the right way to do this?

My current code:

---------------------- 

DataTable dt = myConnection.GetSchema(SQLiteMetaDataCollectionNames.Tables);

foreach (DataRow dr in dt.Rows)
{
    /* find the table name in a robust way! */

}

---------------------

Thanks! 

Excuting this query will return the table names:

 SELECT name FROM sqlite_master WHERE type='table'

 See http://www.sqlite.org/faq.html#q7 for futher details.