SQLite ADO .NET - how to correctly use query builder?

Question

Hello.

The following code works perfectly:

  var oDb = new SQLiteConnection();
  oDb.ConnectionString = String.Format(
      "Data Source={0};"
    + "Synchronous=Full;",
    "test.db" );
  oDb.Open();
  SQLiteCommand oCmd = new SQLiteCommand( oDb );
  oCmd.CommandText =  "create table tests ( ";
  oCmd.CommandText += "  id guid primary key not null";
  oCmd.CommandText += ", name text default 'none' )";
  oCmd.ExecuteNonQuery();

But if i try to use a query builder (to automatically escape strings):

  var oDb = new SQLiteConnection();
  oDb.ConnectionString = String.Format(
      "Data Source={0};"
    + "Synchronous=Full;",
    "test.db" );
  oDb.Open();
  SQLiteCommand oCmd = new SQLiteCommand( oDb );
  oCmd.CommandText =  "create table tests ( ";
  oCmd.CommandText += "  id guid primary key not null";
  oCmd.CommandText += ", name text default @param )";
  var oParam = new SQLiteParameter( "@param", "none" );
  oCmd.Parameters.Add( oParam );
  oCmd.ExecuteNonQuery();

Exception is raised:

SQLite error
near "@param": syntax error

Can anyone please spare a bit of knowledge and hint me what i'm doing wrong?

Answer 1

Although I haven't tested I, this should work: oCmd.Parameters.AddWithValue("@param", "none"); Edit: it works if you add () around the @param:

Edit2: Well, the ExecuteNonQuery() does work, but the result is not the expected result. @param doesn't get replaced with the acutal param. Do not use this code! (now a community wiki. maybe somebody is able to fix it?)

SQLiteCommand oCmd = new SQLiteCommand( oDb ); 
oCmd.CommandText =  "create table tests ( ";
oCmd.CommandText += "  id guid primary key not null";
oCmd.CommandText += ", name text default (@param) )";
var oParam = new SQLiteParameter("@param", "none");
oCmd.Parameters.Add(oParam);
oCmd.ExecuteNonQuery();