Page 1 of 1
Executing multiple commands via pgsqlconnection object
Posted: Thu 17 Jun 2010 22:52
by 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.
Posted: Fri 18 Jun 2010 09:14
by 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.