Executing multiple commands via pgsqlconnection object

[ccampbell]

Hi, in PostgreSQL you can load and execute .sql files that contain multiple sql statements (create table, create view, Copy From, ect.) seperated by a semi colon ";". My question is, is there a way to execute the sql in a .sql file via the pgsql connection object, or for that matter, any other object. When I tried it I got a message saying it could only process one command at a time. Thanks.

[Shalex]

You can execute several DDL/DML statements as one block in several ways:
1) via PgSqlScript. For example, you can read your sql file with standard System.IO.StreamReader class and pass its content to the Devart.Data.Oracle.OracleScript instance:

Code: Select all

using (PgSqlConnection conn = new PgSqlConnection("server=db;port=5434;uid=***;pwd=***;database=postgres;")) {
    conn.Open();
    StreamReader stream = new StreamReader("d:\Temp\myscript.sql");
    string sql = stream.ReadToEnd();
    stream.Close();
    PgSqlScript script = new PgSqlScript(sql, conn);
    script.Execute();
}

Please refer to http://www.devart.com/dotconnect/postgr ... cript.html .

2) via PgSqlCommand. Please note you should set PgSqlCommand.UnpreparedExecute to true if you execute several statements via CommandText using Protocol 3.0. Please refer to http://www.devart.com/forums/viewtopic.php?t=14354.