ADO.NET中的PostgreSQL参数化插入

Question

我正在使用NpgSQL与PostgreSQL和ADO.NET。请原谅我的问题很简单，因为我刚开始使用PostgreSQL和NpgSQL这个星期。

像下面这样的代码是可以正常工作的：

[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();
}

当我输入参数时，出现了以下错误信息： Npgsql.NpgsqlException：ERROR：42703：列“_firstname”不存在

[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();
}

- Greg Finzer

---

大写字母 <> 小写字母。要么只使用小写字母，要么引用你的标识符（使用双引号）。 - wildplasser

我刚刚尝试了所有小写参数名称，但仍然出现相同的错误。 - Greg Finzer

在 psql（或 pgadmin）中，检查您的列的实际名称。 - wildplasser

据我所知，包括postgresql驱动在内的大多数驱动程序都遵循微软的约定，使用“@”前缀而不是下划线（“_”）来标识参数。 - Mark Rotteveel

看起来必须是一个@符号。这很奇怪，因为@对于函数参数无效。 - Greg Finzer

@Greg''Wildman''Finzer 这与此无关，这是由驱动程序处理的，而不是服务器。 - Mark Rotteveel

Answer 1

评论中的回复是正确的：

1. Npgsql不支持使用下划线（_）作为参数占位符的标记。您应该使用@或：（例如@FirstName或:FirstName，而不是_FistName）。
2. 除非使用双引号，否则PostgreSQL将自动将表和列名转换为小写。要么对所有内容使用小写名称（更简单），要么在SQL查询中引用标识符。

因此，您的代码应该大致如下：

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);

- Shay Rojansky

---

我不同意第二个建议。一般情况下不应使用带引号的标识符。如果您使用带引号的字段进行定义，那么每次引用该字段时都必须使用它们。因此，这只有在值得的情况下才更好（但实际上并不值得）。大小写与下划线的使用是有争议的。在没有带引号的标识符的情况下，使用大小写名称也可以正常工作，并且有时会更可取...这取决于项目。 - Kasey Speakman

使用大小写名称而不使用引号标识符确实不能正常工作，因为PostgreSQL将把名称折叠为小写。这意味着Ident1和ident1会产生冲突。引用标识符并没有特别错误 - 这是标准的PostgreSQL实践，并且在需要混合大小写时是必需的。例如，将C#属性名称映射到数据库列的O/RM（例如Entity Framework）通常会引用所有标识符。 - Shay Rojansky

您可以使用大小写名称发出定义查询和数据查询，它们将正常工作。Postgres将使用其版本的“不区分大小写”处理它们，这意味着它们将被转换为小写。无法将小写名称映射到具有命名对象属性的对象关系映射(ORM)可能也会遇到使用下划线的其他建议的问题。创建带引号标识符列的ORM正在创造一种债务，未来用户将不得不偿还。 - Kasey Speakman

一切都会“很好”，直到你偶然拥有两个标识符，如果折叠为小写字母，则它们是相同的。这就是为什么任何将标识符从区分大小写的语言（如C#）映射到PostgreSQL的O/RM绝对必须引用的原因，否则迟早两个属性将折叠成相同的小写字母并出现问题。 - Shay Rojansky

如果您控制所有访问数据库的应用程序，并确保所有标识符都是唯一的，那么不引用也可以。如果您正在编写一个通用组件，例如 O/RM，则很有可能必须引用。这完全取决于上下文。 - Shay Rojansky