Here's my console app code:
Code: Select all
Module Module1
Sub Main()
Try
Dim databaseMonitor As New Devart.Data.Oracle.OracleMonitor()
databaseMonitor.IsActive = True
' This first section happens some time before the user will return (web application)
Dim insertLastWeek As New TestDataContext.TestDataContext()
Dim aChild As New TestDataContext.Childtable()
aChild.Mydata = "Hello"
insertLastWeek.Childtables.InsertOnSubmit(aChild)
insertLastWeek.SubmitChanges()
' ========================================================================
' ========================================================================
' the user has returned to website and we need
' to create the parentTable record
Dim dc As New TestDataContext.TestDataContext()
Dim c = dc.Childtables.FirstOrDefault(Function(w) w.Parenttableid Is Nothing)
Dim p As New TestDataContext.Parenttable()
p.Mydata = "Good Bye"
'' Test One - fail
'c.Parenttable = p
'' Test Two - fail
'p.Childtables.Add(c)
'' Test Three - fail
'dc.Parenttables.InsertOnSubmit(p)
'c.Parenttable = p
' Test Four - Success... but in two database transactions.
dc.Parenttables.InsertOnSubmit(p)
dc.SubmitChanges()
p.Childtables.Add(c)
' Always submit
dc.SubmitChanges()
Catch ex As Exception
Console.WriteLine("Error Occured!!")
showError(ex, 1)
End Try
Console.WriteLine()
Console.WriteLine("-- End of program. Press any key to close --")
Console.ReadKey(True)
End Sub
Private Sub showError(ByVal ex As Exception, ByVal count As Integer)
Console.WriteLine()
Console.WriteLine(count.ToString() & ": " & ex.Message)
If ex.InnerException IsNot Nothing Then showError(ex.InnerException, count + 1)
End Sub
End Module
Code: Select all
/*
-- Drop talbes and values
DROP TABLE PARENTTABLE CASCADE CONSTRAINTS PURGE;
DROP TABLE CHILDTABLE CASCADE CONSTRAINTS PURGE;
-- Drop sequences
DROP SEQUENCE PARENTTABLE_SEQ;
DROP SEQUENCE CHILDTABLE_SEQ;
*/
-- Create the Tables
CREATE TABLE parenttable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL
);
CREATE TABLE childtable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL,
parenttableid NUMBER,
CONSTRAINT parenttable_childtable
FOREIGN KEY (parenttableid)
REFERENCES parenttable (id)
);
-- Create the Sequences
CREATE SEQUENCE PARENTTABLE_SEQ NOCACHE;
CREATE SEQUENCE CHILDTABLE_SEQ NOCACHE;
-- Create the Triggers
CREATE TRIGGER PARENTTABLE_BEFORE_INSERT
BEFORE INSERT ON PARENTTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT PARENTTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/
CREATE TRIGGER CHILDTABLE_BEFORE_INSERT
BEFORE INSERT ON CHILDTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT CHILDTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/
Zach