Transaction scope of multiple statements in SqlDataTable

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Transaction scope of multiple statements in SqlDataTable

Post by chris901 » Thu 08 Sep 2016 17:28


I am using a PgSqlDataTable and have mutliple statements in the InsertCommand.

The UnpreparedExecute property is set to True so it executes all those commands.

I am wondering about the transaction scope of those statements.

Is a transaction automatically generated for this?

Devart Team
Posts: 7973
Joined: Thu 14 Aug 2008 12:44

Re: Transaction scope of multiple statements in SqlDataTable

Post by Shalex » Tue 13 Sep 2016 16:51

Code: Select all

    var monitor = new PgSqlMonitor() { IsActive = true };

    using (var pgConnection = new PgSqlConnection()) {
        pgConnection.ConnectionString = "server=db;port=5441;uid=postgres;pwd=postgres;database=test;unicode=true;";

        PgSqlDataTable myDataTable = new PgSqlDataTable("SELECT * FROM Dept", pgConnection);
        myDataTable.Active = true;
        myDataTable.Rows.Add(new object[] { 1, "Sales2" });
        myDataTable.InsertCommand = new PgSqlCommand(
            "INSERT INTO Dept (DeptNo, DName, Loc) VALUES (:DeptNo, :DName, 'Unverified');"+
            "INSERT INTO Dept (DeptNo, DName, Loc) VALUES (:DeptNo, :DName, 'Unverified');"
            , pgConnection);
        myDataTable.InsertCommand.Parameters.Add("DeptNo", PgSqlType.Int, 0, "DeptNo");
        myDataTable.InsertCommand.Parameters.Add("DName", PgSqlType.VarChar, 15, "DName");
        myDataTable.InsertCommand.UnpreparedExecute = true;
        myDataTable.Update(); // fails with duplicate key, no records are inserted
This code inserts no records. Actually, the provider doesn't create a transaction, but the server treats the command (with two insert statements) to be an atomic operation.

Post Reply