Executing multiple commands via pgsqlconnection object

Executing multiple commands via pgsqlconnection object

Postby ccampbell » Thu 17 Jun 2010 22:52

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.
ccampbell
 
Posts: 29
Joined: Tue 01 Jun 2010 17:31
Location: Oregon

Postby Shalex » Fri 18 Jun 2010 09:14

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.
Shalex
Devart Team
 
Posts: 7774
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL