Transaction scope of multiple statements in SqlDataTable

[chris901]

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?

[Shalex]

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.