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
Obtain RETURNING ID to Output CommandType.Text parameter
-
- Devart Team
- Posts: 2420
- Joined: Wed 02 Nov 2011 09:44
Re: Obtain RETURNING ID to Output CommandType.Text parameter
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:
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
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();
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);
Re: Obtain RETURNING ID to Output CommandType.Text parameter
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:
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";