Postgresql - 动态创建数据库和表

Postgresql - Create Database & Table dynamically

提问by Joe Grasso

This code is not working. Can anyone direct me where I can find examples of creating a Postgresql database and table on the fly with C#?

此代码不起作用。谁能指导我在哪里可以找到使用 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();

The db is created but I get a silent fail on creating the table.

数据库已创建，但我在创建表时出现无提示失败。

回答by Clodoaldo Neto

I would do this:

我会这样做：

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

The use of varhere is not recommended. I used it as I don't know what are the returned types but you should.

var不推荐使用这里。我使用它是因为我不知道返回的类型是什么，但您应该这样做。

Notice the use of a raw string (@). It makes string building simple.

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

Do not use identifiers surrounded by double quotes in Postgresql unless the identifier is otherwise illegal. It will make you life much harder.

不要在 Postgresql 中使用双引号括起来的标识符，除非标识符是非法的。它会让你的生活变得更加艰难。

回答by rhyen brock

seems like you simply forget to invoke ExecuteNonQuerymethod of m_createtbl_cmd:

好像你只是忘记调用ExecuteNonQuery方法m_createtbl_cmd:

m_createtbl_cmd.ExecuteNonQuery();

Also you can simplify it using DynORM library: http://dynorm.codeplex.com/

您也可以使用 DynORM 库简化它：http://dynorm.codeplex.com/

Hope it helps!

希望能帮助到你！

回答by gyslinn

This is what worked for me to verify the existence of any Postgres database with C#:

这就是我用 C# 验证任何 Postgres 数据库是否存在的方法：

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

** The if used in the try-catch statement could simply check if the return of the ExecuteScalar is null for non-existent DB and not-null if it exists.

** try-catch 语句中使用的 if 可以简单地检查 ExecuteScalar 的返回对于不存在的 DB 是否为 null，如果存在则为 not-null。

回答by Joe Grasso

Solution:

解决方案：

    // 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();

This works but is there a shorter way to do this? I had to create two Npgsql connections. I don't know, just doesn't look very elegant to me.

这有效，但有没有更短的方法来做到这一点？我必须创建两个 Npgsql 连接。我不知道，只是对我来说看起来不太优雅。