Obtain RETURNING ID to Output CommandType.Text parameter

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
asapronov
Posts: 5
Joined: Wed 01 Aug 2012 13:02

Obtain RETURNING ID to Output CommandType.Text parameter

Post by asapronov » Wed 01 Aug 2012 13:18

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
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Obtain RETURNING ID to Output CommandType.Text parameter

Post by Pinturiccio » Fri 03 Aug 2012 10:09

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
Posts: 5
Joined: Wed 01 Aug 2012 13:02

Re: Obtain RETURNING ID to Output CommandType.Text parameter

Post by asapronov » Fri 03 Aug 2012 18:42

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

Post Reply