Pass parameters to PostgreSQL query in a C# code

Question

if (oUSERDTO.EVENT_ACTION == (int)Enums.EventAction.Create && result.Equals("true"))
{
    result = "false";
    using (DBConnector oCloudConnection = new DBConnector(connectionValues[0], Convert.ToInt32(connectionValues[1])))
    {
        StringBuilder varname1 = new StringBuilder();
        varname1.Append("DO \n");
        varname1.Append("$$ \n");
        varname1.Append("BEGIN \n");
        varname1.Append("IF NOT EXISTS \n");
        varname1.Append("( \n");
        varname1.Append("SELECT 1 \n");
        varname1.Append("FROM SUSER \n");
        varname1.Append("WHERE (USER_ID=:USER_ID) \n");
        varname1.Append(") \n");
        varname1.Append("THEN \n");
        varname1.Append("INSERT INTO SUSER \n");
        varname1.Append("( \n");
        varname1.Append("USER_ID, \n");
        varname1.Append("USER_NAME, \n");
        varname1.Append("USER_DESCRIPTION, \n");
        varname1.Append(") \n");
        varname1.Append("SELECT \n");
        varname1.Append("USER_ID, \n");
        varname1.Append("USER_NAME, \n");
        varname1.Append("USER_DESCRIPTION, \n");
        varname1.Append("FROM SUSERTEMP \n");
        varname1.Append("WHERE 1=1 \n");
        varname1.Append("AND (USER_ID=:USER_ID) \n");
        varname1.Append("AND (REC_ID=:REC_ID); \n");
        varname1.Append("END IF; \n");
        varname1.Append("END");
        varname1.Append("$$; \n");

        oCloudConnection.CommandText = varname1.ToString();
        oCloudConnection.Parameters.Clear();
        oCloudConnection.Parameters.Add(new Parameter { Name = "REC_ID", Value = oUSERDTO.REC_ID });
        oCloudConnection.Parameters.Add(new Parameter { Name = "USER_ID", Value = oUSERDTO.USER_ID});                        
        if (oCloudConnection.ExecuteQuery() > 0)
        {
            result = "true";
        }
    }
}

In this code I want to pass parameter REC_ID, USER_ID to PostgreSQL query so the where condition works but I get a syntax error at or near ":" (System.Exception: '42601: syntax error at or near ":") how can I fix this error

I tried changing the code to

StringBuilder varname1 = new StringBuilder();
        varname1.Append("DO \n");
        varname1.Append("$$ \n");
        varname1.Append("BEGIN \n");
        varname1.Append("IF NOT EXISTS \n");
        varname1.Append("( \n");
        varname1.Append("SELECT 1 \n");
        varname1.Append("FROM SUSER \n");
        varname1.Append("WHERE (USER_ID=StringBuilder varname1 = new StringBuilder();
        varname1.Append("DO \n");
        varname1.Append("$$ \n");
        varname1.Append("BEGIN \n");
        varname1.Append("IF NOT EXISTS \n");
        varname1.Append("( \n");
        varname1.Append("SELECT 1 \n");
        varname1.Append("FROM SUSER \n");
        varname1.Append("WHERE (USER_ID=$2) \n");
        varname1.Append(") \n");
        varname1.Append("THEN \n");
        varname1.Append("INSERT INTO SUSER \n");
        varname1.Append("( \n");
        varname1.Append("USER_ID, \n");
        varname1.Append("USER_NAME, \n");
        varname1.Append("USER_DESCRIPTION, \n");
        varname1.Append(") \n");
        varname1.Append("SELECT \n");
        varname1.Append("USER_ID, \n");
        varname1.Append("USER_NAME, \n");
        varname1.Append("USER_DESCRIPTION, \n");
        varname1.Append("FROM SUSERTEMP \n");
        varname1.Append("WHERE 1=1 \n");
        varname1.Append("AND (USER_ID=:USER_ID) \n");
        varname1.Append("AND (REC_ID=:REC_ID); \n");
        varname1.Append("END IF; \n");
        varname1.Append("END");
        varname1.Append("$$; \n");) \n");
        varname1.Append(") \n");
        varname1.Append("THEN \n");
        varname1.Append("INSERT INTO SUSER \n");
        varname1.Append("( \n");
        varname1.Append("USER_ID, \n");
        varname1.Append("USER_NAME, \n");
        varname1.Append("USER_DESCRIPTION, \n");
        varname1.Append(") \n");
        varname1.Append("SELECT \n");
        varname1.Append("USER_ID, \n");
        varname1.Append("USER_NAME, \n");
        varname1.Append("USER_DESCRIPTION, \n");
        varname1.Append("FROM SUSERTEMP \n");
        varname1.Append("WHERE 1=1 \n");
        varname1.Append("AND (USER_ID=:$2) \n");
        varname1.Append("AND (REC_ID=:$1); \n");
        varname1.Append("END IF; \n");
        varname1.Append("END");
        varname1.Append("$$; \n");

but this cause another System.Exception: '42P02: there is no parameter $2' I even tried renaming parameters to $1 and $2 still same error came. The values are coming with out a error so error must be in query so how can I fix this

the business of this is to check if the same record in one table and if not then add that record from the second table to first table if you can do it with out DO it will be fine too

Answer 1

use where inside of a where condition to check IF EXIST

using (DBConnector oCloudConnection = new DBConnector(connectionValues[0], Convert.ToInt32(connectionValues[1])))

{ StringBuilder varname1 = new StringBuilder();

varname1.Append("INSERT INTO SUSER \n");
varname1.Append("( \n");
varname1.Append("USER_ID, \n");
varname1.Append("USER_NAME, \n");
varname1.Append("USER_DESCRIPTION \n");
varname1.Append(") \n");
varname1.Append("SELECT \n");
varname1.Append("USER_ID, \n");
varname1.Append("USER_NAME, \n");
varname1.Append("USER_DESCRIPTION \n");
***varname1.AppendLine("FROM SUSERTEMP");
varname1.AppendLine("WHERE");
varname1.AppendLine("(SUSERTEMP.USER_ID=?USER_ID)");
varname1.AppendLine("AND (SUSERTEMP.REC_ID=?REC_ID)");
varname1.AppendLine("AND NOT EXISTS");
varname1.AppendLine("(");
varname1.AppendLine("SELECT 1 ");
varname1.AppendLine("FROM SUSER");
varname1.AppendLine("WHERE SUSER.USER_ID = ?USER_ID");
varname1.AppendLine(");");***

oCloudConnection.CommandText = varname1.ToString();
oCloudConnection.Parameters.Clear();
oCloudConnection.Parameters.Add(new Parameter { Name = "REC_ID", Value = oUSERDTO.REC_ID });
oCloudConnection.Parameters.Add(new Parameter { Name = "USER_ID", Value = oUSERDTO.USER_ID});                        
if (oCloudConnection.ExecuteQuery() > 0)
{
    result = "true";
}

}