如何在C＃中为postgresql创建角色？ [英] How to create roles for postgresql in C#?

问题描述

您好，

我正在尝试在C＃中创建一个Win Form，用于将用户（登录角色）添加到我的PostgreSQL服务器。



Postgres的SQL查询是：

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:

"CREATE USER john LOGIN PASSWORD 'abc';"

扩展查询将是：

The extended query will be:

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

以下是我使用的C＃代码：

Here is the C# code I use:

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

当它到达.ExecuteNonQuery（）时，它会抛出一条错误，并显示以下消息： 42601：syntax错误在$ 1或附近



我不知道问题可能出在哪里，因为C＃中的参数查询看起来与一个相同在SQL中。

你能分享一下建议吗？

谢谢



我有什么尝试过：



我试图使用CREATE ROLE而不是CREATE USER，但结果仍然相同。

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.

推荐答案

1



我不知道哪里可能是问题，因为带有C＃参数的查询看起来和SQL中的一样。

你能分享一下建议吗？

谢谢



< b>我尝试了什么：



我试图使用CREATE ROLE代替CREATE USER，但结果仍然相同。

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.

当我使用以下内容时，它是wo rks，但当然很多人会抗议不使用参数化查询：

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

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

这篇关于如何在C＃中为postgresql创建角色？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！