dotConnect for SQLite enhances SQL handling capabilities with usage of parameters in SQL queries. You can make execution of a query very flexible using several simple techniques. This article describes some basics you must be acquainted with when working with parameters in dotConnect for SQLite.
The article consists of following sections:

• Parameters basics
• Using automatic parameters synchronization
• Performance issues

Parameters basics

In general, a parameter is a placeholder for a variable that contains some value of some type when executing a general-purpose query. Parameter is represented by SQLiteParameter class. All parameters that take part in query execution constitute a collection that can be accessed through SQLiteCommand.Parameters property.

SQLite supports two kinds of parameters: named and unnamed.

Unnamed parameters can be specified as the '?' character (the simplest form), or the '$' character followed by ordinal of the parameter in the whole collection, for example, '$2' denotes second parameter in the collection. The following query

    INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)

declares that three parameters are required to run the query properly. To set parameters for this query you can use the next code:

SQLiteCommand myCommand1;
...
myCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
myCommand1.Parameters.Add("param1", 30);
myCommand1.Parameters.Add("param2", "SALES");
myCommand1.Parameters.Add("param3", "CHICAGO");

Dim myCommand1 as SQLiteCommand
...
myCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)"
myCommand1.Parameters.Add("param1", 30)
myCommand1.Parameters.Add("param2", "SALES")
myCommand1.Parameters.Add("param3", "CHICAGO")

Named parameters require accordance with names of SQLiteParameter instances in the collection. Named parameters are declared using ':' or '@' prefix followed by name of the parameter. Note that the name of the SQLiteParameter object in the collection of the command should contain the '@' prefix if parameter in CommandText is used with the '@' prefix. If the parameter in CommandText contains the ':' prefix, the name of the SQLiteParameter object in the command's collection should be used without any prefix. You can use these prefixes at any combinations to specify parameters. There are two main advantages of named parameters. First, you do not have to care about the order in which parameters are created. Second, named parameter can appear more than once in query text, but you have to create only one instance of it in Parameters collection.

For example, a simple Update statement that requires named parameters might look like the following:

    UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = @deptno

To set parameters for this query you can use the next code:

SQLiteCommand myCommand1;
...
myCommand1.CommandText = "UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = @deptno";
myCommand1.Parameters.Add("@deptno", 20);
myCommand1.Parameters.Add("dname", "SALES");
myCommand1.Parameters.Add("loc", "NEW YORK");

Dim myCommand1 as SQLiteCommand
...
myCommand1.CommandText = "UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = @deptno"
myCommand1.Parameters.Add("@deptno", 20)
myCommand1.Parameters.Add("dname", "SALES")
myCommand1.Parameters.Add("loc", "NEW YORK")

Using automatic parameters synchronization

The behavior described above assumes that SQLiteCommand.ParameterCheck is false (by default). By turning it on you enable automatic synchronization of query text and SQLiteCommand.Parameters collection. In this mode all parameters are checked for validity, new ones are added if necessary, and redundant parameters are deleted. Thus you do not have to take care about quantity of items in SQLiteCommand.Parameters collection, you can specify only the ones you really need. The synchronization is done as follows:

• If quantity of unnamed parameters in the query text is more than quantity of parameters in SQLiteCommand.Parameters collection, new parameters are created and added to it, with SQLiteParameter.Value assigned to DBNull. SQLiteParameter.SQLiteType is set to SQLiteType.Text. This property is updated to a correct type when a value is assigned to the parameter.
• If quantity of parameters in the query text is less than in SQLiteCommand.Parameters collection, redundant parameters are deleted from it.
• If named parameters are mentioned in the text but are missing in the collection, they are added to collection. If named parameters that exist in the collection are not found in the query text, they are deleted from the collection.
• Properties of parameters are not altered, except for the ones created during the synchronization process.

The synchronization takes place when:

• SQLiteCommand.ParameterCheck is set to true
• SQLiteCommand.CommandType is set to "Text"
• SQLiteCommand.CommandText is altered
• Query is prepared
• Query is executed

Performance issues

In general, setting SQLiteCommand.ParameterCheck property to true leads to some performance loss.
The synchronization is performed on client, so performance reduces slightly.

To optimize multiple execution of a query you can manually call the SQLiteCommand.Prepare method that creates a compiled version of the query on server and forces synchronization. After the query is prepared, it is not advised to alter SQLiteCommand.Text or SQLiteParameter.Direction properties because otherwise, the SQLiteCommand becomes unprepared again. The recommended practice is to set up query text and parameters, execute SQLiteCommand.Prepare method, and afterwards change only parameters' values.

See Also

SQLiteCommand Class  | SQLiteParameter Class