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

Does this answer your question? Dapper parameters not working with PostgreSQL through npgsql connection, is postgres anonymous function parameterization supported? — JGH
– JGH, Commented May 16, 2024 at 13:12
no, I want to check if the same record in one table and if not then add that record from the second table to first table but when using do parameters doesn't pass into the quary — Vinuka Osura
– Vinuka Osura, Commented May 17, 2024 at 5:51
well, yes, that's the whole point: you can't pass parameters to an anonymous do block. — JGH
– JGH, Commented May 17, 2024 at 12:32

Vinuka Osura · Accepted Answer · 2024-05-20 04:28:28Z

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

}

Collectives™ on Stack Overflow

Pass parameters to PostgreSQL query in a C# code

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related