Obtain RETURNING ID to Output CommandType.Text parameter

Obtain RETURNING ID to Output CommandType.Text parameter

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

Re: Obtain RETURNING ID to Output CommandType.Text parameter

Postby 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);
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44

Re: Obtain RETURNING ID to Output CommandType.Text parameter

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


Return to dotConnect for PostgreSQL