System.Data.SQLite

Great original post! I have a question: Is it somehow possible to pass a kind of "ANY" value as a parameter. Say I have parametrized query with just one parameter. Usually standard value is used as a parameter, but sometimes I need to use a value that covers all values, including empty and null. Is that possible? An example: SELECT count(*) FROM items WHERE item=? Is there a value that could be passed to the query as a parameter that would select all item values and in effect ignore the whole WHERE clause? Thanks for any ideas or information!

Omit the WHERE clause if you want all rows. For example: SELECT count(*) from items

Thanks for the reply! Well, the problem is I'd still like to use just one parametrized query, only sometimes supplying a kind of "any value" as a certain parameter. So the WHERE clause will always be there, but I do not know how to make it accept "any value". Maybe using nothing after the equal sign?

You are not being clear. If you do not want to constraint what is returned, then omitting the WHERE clause is the correct approach. If you have some other intention in mind, then you have to explain it in more detail.

Ah, right, sorry, I'll try to be more specific, but thanks for the responses so far. So, I have this command text: "REMOVE FROM combos WHERE trackname=? AND carname=?". I'd love to have this as one pre-parsed parametrized query that is always ready to execute, as the first post in this thread suggests. Many times both parameters have specific values, which is no problem and everything works great. But sometimes the first parameter has no value and in that case the trackname=? part should always return TRUE, so that basically only carname is the limiting factor. Other times carname is not specified and only trackname would limit the results. And yet sometimes neither trackname nor carname is specified, which means the whole combos table should be erased. So, I hope the situation is clearer a bit now. Currently I must have 4 different but very similar queries and call the one in which the inapplicable WHERE part is not needed. But is it possible to have just one query that would accept a parameter representing kind of "any value"?

To my knowledge, this is not possible.

OK, thanks a lot... :)

Robert Simpson:

You, like so many before you (and many to come after!) have forgotten the cardinal rule of SQLite ... TRANSACTIONS!

 

Yes, when I first wrote my program that inserts data from an MS SQLServer DB to an SQLite DB, I knew about transactions but didn't understand how much they affected the speed of updates/inserts/deletions. Nor did I know how to manually start and commit them from my code. After reading this post http://sqlite.phxsoftware.com/forums/p/134/465.aspx#465, my code went from "Will this program never finish updating" to "I'm not going to have enough time to finish my coffee".

 Here's the code I came up with for fast parameterized inserting:

   Public Sub FastInsertMany(ByRef crrntTable As DataTable)
        ' Insert new data into the SQLite DB file. 
        ' This insert code is based on the code found at http://sqlite.phxsoftware.com/forums/p/134/465.aspx#465
        ' See section called "Fastest universal way to insert data using standard ADO.NET constructs"

        Dim cnnSQLite As SQLite.SQLiteConnection = New SQLite.SQLiteConnection(<SQLiteConnectionString>)

        Dim ltTransaction As SQLite.SQLiteTransaction

        Dim cmmSQLite As SQLite.SQLiteCommand = New SQLite.SQLiteCommand(cnnSQLite)

        cmmSQLite.CommandType = CommandType.Text

        cmmSQLite.CommandText = "INSERT INTO " & crrntTable.TableName & Me.getInsertColumns(crrntTable.Columns)

        For Each crrntColumn As DataColumn In crrntTable.Columns
            cmmSQLite.Parameters.AddWithValue("@" & crrntColumn.ColumnName, crrntTable.Rows(0).Item(crrntColumn.ColumnName))
        Next
        cnnSQLite.Open()
        ltTransaction = cnnSQLite.BeginTransaction
        For Each crrntRow As DataRow In crrntTable.Rows
            For Each crrntColumn As DataColumn In crrntTable.Columns
                cmmSQLite.Parameters.Item("@" & crrntColumn.ColumnName).Value = crrntRow.Item(crrntColumn.ColumnName)
            Next
            cmmSQLite.ExecuteNonQuery()
        Next
        ltTransaction.Commit()
        cnnSQLite.Close()
    End Sub

    Private Function getInsertColumns(ByRef crrntColumns As DataColumnCollection) As String

        ' build the columns and parameter variables used in the SQLlite insert command

        getInsertColumns = " (,) VALUES (;)"
        For Each crrntColumn As DataColumn In crrntColumns
            getInsertColumns = getInsertColumns.Insert(getInsertColumns.LastIndexOf(","c), crrntColumn.ColumnName & ",")
            getInsertColumns = getInsertColumns.Insert(getInsertColumns.LastIndexOf(";"c), "@" & crrntColumn.ColumnName & ";")
        Next
        getInsertColumns = getInsertColumns.Remove(getInsertColumns.LastIndexOf(","c) - 1, 2)
        getInsertColumns = getInsertColumns.Remove(getInsertColumns.LastIndexOf(";"c) - 1, 2).Replace(";"c, ","c)
    End Function