Re-execution of SqliteCommand with bound parameters fails

[ellisnet]

The following code fails:

string sql = "select mycolumn from mytable where ID = @id";
using (var cmd = new SqliteCommand(sql, myDbConnection)) {
  cmd.Parameters.Add(new SqliteParameter("@id", mycolumnid));
  myDbConnection.Open();  //may need to check and see if it is closed first
  var value1 = cmd.ExecuteScalar();
  var value2 = cmd.ExecuteScalar(); //this one fails
}

This appears to happen because the library tries to re-bind the parameters onto an existing SQLite statement (?); instead of creating a new SQLite statement (or before creating the second SQLite statement). So, SQLitePCL complains that binding is occurring out of order.

Current workaround:
Don't re-execute the same command. Instead, do this:

string sql = "select mycolumn from mytable where ID = @id";
using (var cmd1 = new SqliteCommand(sql, myDbConnection)) {
  cmd1.Parameters.Add(new SqliteParameter("@id", mycolumnid));
  myDbConnection.Open();  //may need to check and see if it is closed first
  var value1 = cmd1.ExecuteScalar();
}
using (var cmd2 = new SqliteCommand(sql, myDbConnection)) {
  cmd2.Parameters.Add(new SqliteParameter("@id", mycolumnid));
  myDbConnection.Open();  //may need to check and see if it is closed first
  var value2 = cmd2.ExecuteScalar(); 
}

There is probably a way to Reset the SQLite statement so that, behind the scenes, the whole SQLite process is happening over again. Will investigate.

[ellisnet]

Will make sure this problem doesn't occur in the new SimpleAdo.Sqlite library.