2

I am using Dapper with SQLite.

I have a table that is defined like so:

CREATE TABLE Example (
   id            INTEGER NOT NULL,
   "some-field"  TEXT  
);

I wrote a console application in C# that inserts values into the table above:

public void Insert(int id, string somefield)
{
    using(var db = new SQLiteConnection("..."))
    {
        string sql = "INSERT INTO Example (Id, [some-field]) VALUES (@id, @somefield)";
        db.Execute(sql, new { id, somefield });
    }
}

Notice that I had to rename the parameter in the sql statement. My program actually dynamically builds the sql insert statement, but for the sake of this question, let's assume that I want to pass a parameter matching the name of the column:

   INSERT INTO Example (id, [some-field]) VALUES (
     @id,           -- this is fine
     [@some-field]  -- SQL logic error: no such column @some-field
     @[some-field]  -- SQL logic error: unrecognized token: "@"
     @"some-field"  -- same error as above
     "@some-field"  -- works, but inserts the literal value, not the passed in parameter
   );

I even tried to pass a dictionary as parameter:

public void Insert(int id, string somefield)
{
   var parameters = new Dictionary<string, object>()
   {
       ("id", id),
       ("some-field", somefield),      
       ("@some-field", somefield),     
       ("@\"some-field\"", somefield), 
   };
   ...
   db.Execute(sql, parameters);
   ...

Is it possible to write an insert statement that has columns with dashes in their names, and then assign values to parameters having the same names as the column-names?

as I said, the column names are dynamically computed, and they have to be used "as is".

2
  • 1
    Seems the real problem is they have to be used "as is"., not the limitations various databases put on parameter names Commented Jan 9 at 10:43
  • @PanagiotisKanavos I did exactly that, as suggested by you and Guru Commented Jan 9 at 11:54

1 Answer 1

1

From the Binding Values To Prepared Statements SQLite docs (emphasis mine):

In the SQL statement text input to sqlite3_prepare_v2() and its variants, literals may be replaced by a parameter that matches one of following templates:

  • ?
  • ?NNN
  • :VVV
  • @VVV
  • $VVV

In the templates above, NNN represents an integer literal, and VVV represents an alphanumeric identifier. The values of these parameters (also called "host parameter names" or "SQL parameters") can be set using the sqlite3_bind_*() routines defined here.

- is not a part of alphanumeric hence it can't be used as part of parameter name, so you need to sanitize the name of the column to use it as parameter name.

Sign up to request clarification or add additional context in comments.

1 Comment

I kept the column names "as-is", I had the liberty of "sanitizing" the parameter names as you suggested. My code works. Thanks

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.