Postgresql - Create Database & Table dynamically

问题:

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#?

     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.

回答1:

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 var here is not recommended. I used it as I don't know what are the returned types but you should.

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.

回答2:

seems like you simply forget to invoke ExecuteNonQuery method of m_createtbl_cmd:

m_createtbl_cmd.ExecuteNonQuery();

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

Hope it helps!

回答3:

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.

回答4:

This is what worked for me to verify the existence of any Postgres database with 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; }
        }
    }
}

** 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.