PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I return the result of the insert query into C# variable (PotgreSQL)?

I have to rewrite a SQL query to make it work with PostgreSQL. Initially the end of the query was like

returning rowid into ?

And with this command took my C# variable as a parameter. But PostgreSQL doesn’t have into in its returning construction. How can I make PostgreSQL work the same way?

I tried to use returning but I can’t connect it with variable.

Tags:

2

Answers


  1. 0

    You SQL query needs to looks something like this

    PRINCIPAL_INSERT = $"insert into mytable (Id, Email, .....) values(@Id, @Email, .....) RETURNING Id";

    So you just specify the name of the column that you are returning from the query.

    Then you code can look like this

                var id = await connection.ExecuteScalarAsync(PRINCIPAL_INSERT, entity);
    Login or Signup to reply.
  2. 0

    In this case I usually create and use an InsertQuery function in a Query class like below:

    internal static string InsertQuery(string TableName, string Properties, string PropertyValues, string PkColumnName)
{
    return string.Format("INSERT INTO {0} ({1}) VALUES ({2}) RETURNING {3};", TableName, Properties, PropertyValues, PkColumnName);
}

    I assume you have a connection name like conn. Here’s how to use the InsertQuery function

    string[] parameters = propParameters.Split(',');
var cmdText = Query.InsertQuery(TableName, properties, propParameters, pkColumnName);
var sqlCmd = new NpgsqlCommand(cmdText, conn);
for (int i = 0; i < parameters.Length; i++)
{
    sqlCmd.Parameters.AddWithValue(parameters[i], dbTypes[i], paramValues[i]);
}
int resultId = Convert.ToInt32(sqlCmd.ExecuteScalar());
conn.Close();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search