PostgreSQL - 动态创建数据库和表格

Question

这段代码无法工作。有没有人能够指导我在哪里找到使用C#动态创建Postgresql数据库和表的示例？

     const string connStr = "Server=localhost;Port=5432;
                          User Id=postgres;Password=enter;Database=postgres";

        var m_conn = new NpgsqlConnection(connStr);

        // creating a database in Postgresql
        m_createdb_cmd = new NpgsqlCommand("CREATE DATABASE IF NOT EXISTS  \"testDb\" " +
                                       "WITH OWNER = \"postgres\" " +
                                       "ENCODING = 'UTF8' " +
                                       "CONNECTION LIMIT = -1;", m_conn);

        // creating a table in Postgresql
        m_createtbl_cmd = new NpgsqlCommand(
            "CREATE TABLE MyTable(CompanyName VARCHAR(150))";

        m_conn.Open();
        m_createdb_cmd.ExecuteNonQuery();
        m_createtbl_cmd.Connection = m_conn;
        m_conn.Close();

数据库已创建，但在创建表时我遇到了无声的失败。

- Joe Grasso

Answer 1

我会这样做：

string connStr = "Server=localhost;Port=5432;User Id=postgres;Password=enter;";
var m_conn = new NpgsqlConnection(connStr);
var m_createdb_cmd = new NpgsqlCommand(@"
    CREATE DATABASE IF NOT EXISTS testDb
    WITH OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;
    ", m_conn);
m_conn.Open();
m_createdb_cmd.ExecuteNonQuery();
m_conn.Close();

connStr = "Server=localhost;Port=5432;User Id=postgres;Password=enter;Database=testDb";
m_conn = new NpgsqlConnection(connStr);
m_createtbl_cmd = new NpgsqlCommand(
   "CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)"
   , m_conn);
m_conn.Open();
m_createtbl_cmd.ExecuteNonQuery();
m_conn.Close();

不建议在这里使用var。我使用它是因为我不知道返回的类型，但你应该知道。

注意使用原始字符串(@)。它使得字符串构建更简单。

在Postgresql中不要使用双引号括起来的标识符，除非该标识符本身非法。否则会让你的生活变得更加困难。

- Clodoaldo Neto

---

“IF NOT EXISTS” 似乎不被支持。您是否使用了额外的插件来使其工作？ - AgostinoX

@AgostinoX 模拟PostgreSQL的CREATE DATABASE IF NOT EXISTS - Andrei Krasutski

Answer 2

您可以将ConnectionString传递给此函数：

  private static string GetConnectionString(string postgreSqlConnectionString)
        {
            NpgsqlConnectionStringBuilder connBuilder = new()
            {
                ConnectionString = postgreSqlConnectionString
            };

            string dbName = connBuilder.Database;

            var masterConnection = postgreSqlConnectionString.Replace(dbName, "postgres");

            using (NpgsqlConnection connection = new(masterConnection))
            {
                connection.Open();
                using var checkIfExistsCommand = new NpgsqlCommand($"SELECT 1 FROM pg_catalog.pg_database WHERE datname = '{dbName}'", connection);
                var result = checkIfExistsCommand.ExecuteScalar();

                if (result == null)
                {
                    using var command = new NpgsqlCommand($"CREATE DATABASE \"{dbName}\"", connection);
                    command.ExecuteNonQuery();
                }
            }

            postgreSqlConnectionString = masterConnection.Replace("postgres", dbName);

            return postgreSqlConnectionString;
        }

这将从ConnectionString中检索dbname，然后检查它是否已经存在。如果不存在，则使用给定的dbname创建一个新的。

您应该在Startup类的ConfigureServices中使用上述函数，如下所示：

 public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<MyDbContext>(options =>
        {    
                options.UseNpgsql(GetConnectionString(Configuration["YourConnectionString"]));
        });
    }

- Kaveh Naseri

---

如果我想向用户致以问候并使其成为数据库的所有者，该怎么办呢？这将非常方便，因为在首次安装中，我们将使用管理员密码创建具有数据库权限的用户。 - MSH

Answer 3

看起来你只是忘记调用m_createtbl_cmd的ExecuteNonQuery方法：

m_createtbl_cmd.ExecuteNonQuery();

你可以使用DynORM库来简化它：http://dynorm.codeplex.com/。希望这能帮到你！

- rhyen brock

Answer 4

这是我用来验证任何Postgres数据库是否存在的C#代码：

以下是我的解决方案：

private bool chkDBExists(string connectionStr, string dbname)
{
    using (NpgsqlConnection conn = new NpgsqlConnection(connectionStr))
    {
        using (NpgsqlCommand command = new NpgsqlCommand
            ($"SELECT DATNAME FROM pg_catalog.pg_database WHERE DATNAME = '{dbname}'", conn))
        {
            try
            {
                conn.Open();
                var i = command.ExecuteScalar();
                conn.Close();
                if (i.ToString().Equals(dbname)) //always 'true' (if it exists) or 'null' (if it doesn't)
                    return true;
                else return false;
            }
            catch (Exception e) { return false; }
        }
    }
}

在try-catch语句中使用的if语句可以简单地检查ExecuteScalar的返回值是否为null，以确定非存在的数据库是否存在，如果不为null，则存在。

- gyslinn

Answer 5

解决方案：

    // 1. Connect to server to create database:
    const string connStr = "Server=localhost;Port=5432;User Id=postgres;Password=enter;";

    // 2. Connect to server to create table:
    const string connStr2 = "Server=localhost;Port=5432;User Id=postgres;Password=enter;Database=testDb";


    var m_conn = new NpgsqlConnection(connStr); // db connction
    var m_conn2 = new NpgsqlConnection(connStr2); // table connection

    // creating a database in Postgresql
    m_createdb_cmd = new NpgsqlCommand("CREATE DATABASE IF NOT EXISTS  \"testDb\" " +
                                   "WITH OWNER = \"postgres\" " +
                                   "ENCODING = 'UTF8' " +
                                   "CONNECTION LIMIT = -1;", m_conn);

    // creating a table in Postgresql
    m_createtbl_cmd = new NpgsqlCommand
       {
       CommandText ="CREATE TABLE table1(ID CHAR(256) CONSTRAINT id PRIMARY KEY, Title CHAR)"
       };

       m_createtbl_cmd.Connection = m_conn2;

 // 3.. Make connection and create

        // open connection to create DB
        m_conn.Open();
        m_createdb_cmd.ExecuteNonQuery();
        m_conn.Close();

        // open connection to create table
        m_conn2.Open();
        m_createtbl_cmd.ExecuteNonQuery();
        m_conn2.Close();

这个方法可以工作，但有没有更简洁的方式？我不得不创建两个Npgsql连接。我不知道，对我来说看起来不太优雅。

- Joe Grasso