Transaction scope of multiple statements in SqlDataTable

Transaction scope of multiple statements in SqlDataTable

Postby chris901 » Thu 08 Sep 2016 17:28

Hello,

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?
chris901
 
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: Transaction scope of multiple statements in SqlDataTable

Postby 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;";
        pgConnection.Open();

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


Return to dotConnect for PostgreSQL