Postgresql - Create Database & Table dynamically

Question

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.

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

Answer 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!

Answer 3

You can pass the ConnectionString to this function :

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

This will retrieve dbname from ConnectionString then checks if it already exists or not. if it didn't exist it will create one with the given dbname.

You should use above function in ConfigureServices of Startup class like this :

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

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

Answer 5

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.