用的ExecuteScalar在插入时检索与Npgsql序列号 [英] Retrieve serial ID with Npgsql when inserting with ExecuteScalar
问题描述
我试图插入一行到PostgreSQL表带有串行主键,我需要它插入后检索此列。我得到了这样的事情:
I'm trying to insert a row into a PostgreSQL table with a serial primary key and I need to retrieve this column after it was inserted. I got something like this:
表国家报有3列:ID,派斯,资本; id是一个串行列,是它的主键。
The table "pais" has 3 columns: id, pais, capital; id is a serial column and is its primary key.
NpgsqlCommand query = new NpgsqlCommand("insert into pais(nombre, capital) values(@nombre, @capital)", conn);
query.Parameters.Add(new NpgsqlParameter("nombre", NpgsqlDbType.Varchar));
query.Parameters.Add(new NpgsqlParameter("capital", NpgsqlDbType.Varchar));
query.Prepare();
query.Parameters[0].Value = this.textBox1.Text;
query.Parameters[1].Value = this.textBox2.Text;
Object res = query.ExecuteScalar();
Console.WriteLine(res);
这插入上表中的行,但资源值为空。如果我的nexval('table_sequence')INSERT也返回null。
It inserts the row on the table but "res" value is null. If I insert with the nexval('table_sequence') also returns null.
我怎么可以返回表的ID你知道吗?我缺少的东西吗?
Any idea of how can I return the id of the table? Am I missing something?
在此先感谢
推荐答案
为了选择插入你需要使用的最后一个标识: CURRVAL(sequencename)
In order to select the last identity inserted you need to use: currval(sequencename)
所以你的select语句看起来应该像
so your select statement should look like:
NpgsqlCommand query = new NpgsqlCommand("insert into pais(nombre, capital) values(@nombre, @capital);select currval('table_sequence');", conn);
这篇关于用的ExecuteScalar在插入时检索与Npgsql序列号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!