PostgreSQL Parameterized Insert with ADO.NET

Question

I am using NpgSQL with PostgreSQL and ADO.NET. Forgive the simplicity of the question as I just started using PostgreSQL and NpgSQL this week.

Something like this works fine:

[Test]
public void InsertNoParameters()
{
    NpgsqlConnection conn = new NpgsqlConnection("Host=localhost; Database=postgres; User ID=postgres; Password=password");
    conn.Open();

    IDbCommand command = conn.CreateCommand();
    string sql = "INSERT INTO Customers (FirstName,LastName) VALUES ('Test','Tube')";
    command.CommandText = sql;
    command.ExecuteNonQuery();
    conn.Close();
}

When I put in parameters I get the error message: Npgsql.NpgsqlException : ERROR: 42703: column "_firstname" does not exist

[Test]
public void InsertWithParameters()
{
NpgsqlConnection conn = new NpgsqlConnection("Host=localhost; Database=postgres; User ID=postgres; Password=password");
conn.Open();

IDbCommand command = conn.CreateCommand();
string sql = "INSERT INTO Customers (FirstName,LastName) VALUES (_FirstName,_LastName)";
command.CommandText = sql;

var parameter = command.CreateParameter();
parameter.ParameterName = "_FirstName";
parameter.Value = "Test";
command.Parameters.Add(parameter);

parameter = command.CreateParameter();
parameter.ParameterName = "_LastName";
parameter.Value = "Tube";
command.Parameters.Add(parameter);

command.ExecuteNonQuery();
conn.Close();
}

Answer 1

The responses in the comments are correct:

1. Npgsql doesn't support _ as a parameter placeholder notation. You should be using @ or : (so @FirstName or :FirstName, not _FirstName).
2. PostgreSQL will automatically lower-case your table and column names unless they are double-quoted. Either use lower-case names for everything (simpler) or quote identifiers in your SQL queries.

So your code should look more or less like this:

IDbCommand command = conn.CreateCommand();
string sql = "INSERT INTO Customers (first_name, last_name) VALUES (@FirstName,@LastName)";
command.CommandText = sql;

var parameter = command.CreateParameter();
parameter.ParameterName = "FirstName";
parameter.Value = "Test";
command.Parameters.Add(parameter);