Obtain RETURNING ID to Output CommandType.Text parameter

[asapronov]

Hi

I am trying to execute SQL code like this:

INSERT INTO web50.m_map(description, provider, date_create, layers_file_name, ctime, cuser_id, mtime, muser_id)
VALUES (:DESCRIPTION, :PROVIDER, now(), 'lala.xml', now(), :USER_ID, now(), :USER_ID)
RETURNING map_id AS :ID;

in _cmd.ExecuteNonQuery();

_cmd is PgSqlCommand with CommandType.Text
:ID - PgSqlParameter with Output (or even ReturnValue) direction

But I got error "Parameter 'ID' is missing"

Is there a way how to implement my task without stored procedure ?

Thanks

[Pinturiccio]

The Output value of the ParameterDirection enum can be used only inside a procedure or function. So you should use the ReturnValue value of the ParameterDirection enum. Below is the code returning the ID of the inserted row:

Code: Select all

PgSqlConnection conn = new PgSqlConnection("host=***;port=5439;uid=***;pwd=***;");
conn.Open();

PgSqlCommand comm = new PgSqlCommand(@"INSERT INTO m_map(description, provider, date_create, layers_file_name, 
ctime, cuser_id, mtime, muser_id)
VALUES (:DESCRIPTION, :PROVIDER, now(), 'lala.xml', now(), :USER_ID, now(), :USER_ID) RETURNING map_id", conn);

comm.Parameters.Add("DESCRIPTION", PgSqlType.Text).Value = "2";
comm.Parameters.Add("PROVIDER", PgSqlType.Text).Value = "2";
comm.Parameters.Add("USER_ID", PgSqlType.Int).Value = 2;
PgSqlParameter param = new PgSqlParameter() { ParameterName = "map_id", Direction = 
System.Data.ParameterDirection.ReturnValue };
comm.Parameters.Add(param);
comm.ExecuteNonQuery();
Console.WriteLine(param.Value);
conn.Close();

If the parameter has the Direction property set as ReturnValue, then the value from the result set column having the name equal to the parameter name will be assigned to the Value property of this parameter. In our case the result set contains only one column with the name 'map_id'.

This can also be done without the parameter, by executing the ExecuteScalar method:

Code: Select all

object result_id = comm.ExecuteScalar();
Console.WriteLine(result_id);

[asapronov]

Thanks a lot!

The way with ExecuteQuery known but unhappily I cant use it by project architecture restrictions

But ReturnValue usage tuning works fine

I can little improve example using in last line:

Code: Select all

RETURNING map_id AS "ID";