Search code examples
c#postgresqlnpgsql

Npgsql for c# how to access table scripts, views, foreign and primary keys and script them out

I am coding in c# with the Npgsql package. I have connected to Postgresql like this

  string connString = String.Format("Server={0};Port={1};User Id= {2};Password={3};Database={4};", serverName, "5432", username, password,databaseName);
  NpgsqlConnection conn = new NpgsqlConnection(connString);
  conn.Open();

Is there any way that I could access the the tables,views,primary keys and foreign keys and generate create scripts for them from c#?

Solution

  • I'm fairly certain Npgsql does not have any built-in way of generating CREATE scripts, but you can access schema information like the second answer to this question.

    You can also have a look at how pgAdmin III generates the CREATE scripts by looking at the various pgadmin/schema/pg*.cpp source files, for example, here is how pgAdmin III generates SQL for a table. You can also have a look at how psql describes tables here, and there is the monstrous pg_dump utility too.

    You could possibly just call pg_dump with the appropriate flags from within your app and process the output accordingly, but this could introduce distribution issues if your app's license is incompatible with whatever license pg_dump falls under.

    If calling pg_dump is out of the question, you could possibly create a library from the pgAdmin III or pg_dump source code and use that within your app (if licensing permits). The last thing you want to do is roll your own implementation.