Devart Forums

Code: Select all

// **********************************************
// Calls insertion methods for parent and child tables.
// **********************************************
Protected void Insertion
    Boolean blnError = false;
    using (TransactionScope trnScope = new TransactionScope())
    {
        // Insertion in parent table.
        ParentTable objParentTable as new ParentTable();
        blnError = ParentTable.Insert();
        
        // Insertion in child table.
        if (!blnError)
        {
            ChildTable objChildTable = new ChildTable();
            blnError = ChildTable.Insert();
        }

        if (!blnError)
            trnScope.Complete();
    }

// **********************************************
// Parent and child table methods are part of a class library.
// **********************************************
protected Boolean ParentTable()
{
    // Insertion in the parent table takes place here.
    // The parent table has a serial field which is the primary key.
    // The insert command has a RETURNING clause to get such
    // a serial field which will be used by the ChildTable method.
    // I tested with and without TransactionScope here.
    // If an exception is caught then TRUE is returned, else FALSE is returned.
}

protected Boolean ChildTable()
{
    // Insertion in the child table takes place here.
    // The primary key, which is the value of the serial field generated
    // in the ParentTable() method, is used here to relate the child records
    // with the parent record. It seems that the parent record is not 
    // inserted yet, but until the transaction is completed.
    // I tested with and without TransactionScope here.
    // If an exception is caught then TRUE is returned, else FALSE is returned.
}

Code: Select all

DDL:

CREATE TABLE parent
(
  id integer NOT NULL,
  data character varying,
  CONSTRAINT parent_pkey PRIMARY KEY (id)
);
CREATE TABLE child
(
  id integer NOT NULL,
  parent_id integer,
  CONSTRAINT child_pkey PRIMARY KEY (id),
  CONSTRAINT child_parent_id_fkey FOREIGN KEY (parent_id)
      REFERENCES parent (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

======================

C#:

    class Program {
        static void Main(string[] args) {
            string connStr = "server=db;port=5438;uid=postgres;pwd=postgres;database=postgres;";
            using (PgSqlConnection conn = new PgSqlConnection(connStr)){
                conn.Open();
                PgSqlTransaction trans = conn.BeginTransaction();
                ParentTable(conn);
                ChildTable(conn);
                trans.Commit();
            }
        }
        static protected void ParentTable(PgSqlConnection cs){
                PgSqlCommand cmd = cs.CreateCommand();
                cmd.CommandText = "insert into parent values (1,'some data')";
                cmd.ExecuteNonQuery();
        }
        static protected void ChildTable(PgSqlConnection cs){
                PgSqlCommand cmd = cs.CreateCommand();
                cmd.CommandText = "insert into child values (1,1);";
                cmd.ExecuteNonQuery();
        }
    }

Code: Select all

    class Program {
        static void Main(string[] args) {
            bool isSuccess = false;
            string errText = null;
            string connStr = "server=db;port=5438;uid=postgres;pwd=postgres;database=postgres;";
            try {
                using (PgSqlConnection conn = new PgSqlConnection(connStr)) {
                    conn.Open();
                    PgSqlTransaction trans = conn.BeginTransaction();
                    ParentTable(conn);
                    ChildTable(conn);
                    trans.Commit();
                    isSuccess = true;
                }
            }
            catch (PgSqlException ex) {
                isSuccess = false;
                errText = ex.Message;
            }
            finally {
                using (PgSqlConnection conn = new PgSqlConnection(connStr)) {
                    conn.Open();
                    PgSqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "insert into mylog (id, message) values (:p1,:p2)";
                    cmd.Parameters.Add("p1", PgSqlType.Int).Value = getId();
                    cmd.Parameters.Add("p2", PgSqlType.VarChar).Value = (isSuccess == true) ? "success" : errText;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        static protected void ParentTable(PgSqlConnection cs) {
            PgSqlCommand cmd = cs.CreateCommand();
            cmd.CommandText = "insert into parent values (4,'some data')";
            cmd.ExecuteNonQuery();
        }
        static protected void ChildTable(PgSqlConnection cs) {
            PgSqlCommand cmd = cs.CreateCommand();
            cmd.CommandText = "insert into child values (4,4);";
            cmd.ExecuteNonQuery();
        }
    }