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".
they have to be used "as is"., not the limitations various databases put on parameter names