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.

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

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

Solution 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"]));
        });
    }

Comments

[AgostinoX]

"IF NOT EXISTS" seems not supported. Did you use some additional plugin to make it work?