Problems with transfering single quotation marks in the command

Question

I am writing a C#.NET WinForms application. I am currently implementing and testing a few database queries with NpgsqlFactory. I have the problem that (I assume) the characters '' and ', ' are not recognized correctly. When I run this line

STRING_AGG(CONCAT(XY.PropertyNameHere, ''), ', ' ORDER BY XY.PropertyNameHere) AS TableNameHere,

In pgAdmin4, everything works, but not in the program. I have already tried to build different strings, i.e. "', '" or ", ", but I get different error messages.

42P01: missing entry in FROM clause for table »projectNameHere«.

String has a length of zero.

Apparently I cannot transfer these characters correctly in the command.

Answer 1

Solution

I have now found a way that works. It's a mixture of all your answers and comments.

The first thing I've learned is that I should put the table names in quotes more often, so that capitalization doesn't matter. The error "missing entry in FROM clause for table »...«" is caused by this.
But that doesn't matter now, because I have added the SQL file to the project, read it completely and execute it. There are no more problems with character handling.

using System.IO;
using System.Collections.Generic;

C# (excerpt)

string path = @"C:\Users\path\file.sql";
if (!File.Exists(path))
{
    return List<T> // insert whatever you need;
}
string script = File.ReadAllText(path);
using (Npgsql.NpgsqlConnection connection = new Npgsql.NpgsqlConnection(connectionString))
{
    using (Npgsql.NpgsqlCommand m_createdb_cmd = new Npgsql.NpgsqlCommand(script, connection))
    {
        connection.Open();
        using (System.Data.Common.DbDataReader dr = m_createdb_cmd.ExecuteReader())
        {
             while (dr.Read())
             {
               // Process each row here
             } 
         }
         connection.Close();
     }
}