How to create roles for postgresql in C#?

Question

0.00/5 (No votes)

See more:

Hello,
I am trying to create a Win Form in C#, that will be used to add users (login roles) to my PostgreSQL server.

The SQL query for Postgres is:

SQL

"CREATE USER john LOGIN PASSWORD 'abc';"

The extended query will be:

SQL

"CREATE USER john LOGIN PASSWORD 'abc' IN ROLE "members" CREATEROLE;"

Here is the C# code I use:

C#

public static void CreateUser(NpgsqlConnection pgConnection, TextBox[] txtCredentials)
{
 try
  {
   if (pgConnection.State != ConnectionState.Open)
      pgConnection.Open();
   using (var pgCommand = new NpgsqlCommand())
    {
     pgCommand.Parameters.Clear();
     pgCommand.Parameters.AddWithValue("@user", txtCredentials[0].Text);
     pgCommand.Parameters.AddWithValue("@pw", txtCredentials[1].Text);
     pgCommand.CommandText = "CREATE USER @user LOGIN PASSWORD @pw;";
     pgCommand.Connection = pgConnection;
     pgCommand.ExecuteNonQuery();
    }
  }
   catch (NpgsqlException pgMsg)
  {
    MessageBox.Show(pgMsg.Message);
  }
}

When it reaches ".ExecuteNonQuery()", it throws an error with the following message: 42601: syntax error at or near "$1"

I have no clue where might be the problem, because the query with parameters in C# looks the same as the one in SQL.
Can you please share an advice?
Thank you

What I have tried:

I have tried to use "CREATE ROLE" instead "CREATE USER", but still the same result.

Posted 30-Jul-16 10:58am

Stelig

Add a Solution

Comments

camaron182 30-Jul-16 23:11pm

can you include the SQL command when you provide values for txtCredentials? it could be PostgreSQL syntax

Stelig 31-Jul-16 5:51am

I am using TextBox[] txtCredentials = {txtUser, txtPw};
and then call the method.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2016-07-31T00:07:00

When I use the following, it works, but of course many will protest against not using a parameterized query:

C#

public static void CreateUser(NpgsqlConnection pgConnection, string user, string password)
{
    try
    {
        string sql = string.Format("CREATE USER {0} LOGIN PASSWORD '{1}';", user, password);
        pgConnection.Open();

        using (var pgCommand = new NpgsqlCommand())
        {
            pgCommand.Connection = pgConnection;
            pgCommand.CommandText = sql;
            pgCommand.ExecuteNonQuery();
        }
    }
    catch (NpgsqlException pgMsg)
    {
        MessageBox.Show(pgMsg.Message);
    }
}