Running Regular Queries Through ArcSDE

Question

Working on an .net arcobjects app that's connecting to a Geodatabase (Oracle) through ArcSDE. I make a bunch of edits inside a EditSession but the data I am touching have a bunch of foreign Key constraints that I have to enforce by inserting/updating a bunch of records (non spatial) before I commit/flush/close my edit session.

Is there a way to execute those queries through the same session being used by ArcSDE without having to deal with Table feature classes? Something that allow me to run pure SQL through an edit session?

Answer 1

You can execute sql statements in the same connection, but arbitrary selections become more difficult. You can take a look at the IWorkspace::ExecuteSql method for DDL or DML. Selections have to be done through either a query layer or a QueryDef.

Answer 2

I don't think so. If you want to use the edit session, I think you are going to have to do it the ArcObjects way with table feature classes.

If your intent issue a SQL transaction and the editSession and combine into one transaction, you might be able to...

 start a normal Oracle transaction 
 try {
 update the foreign tables with just SQL
 start the editSession
 if (error) throw error and undo, else close editSession
 commit the Oracle transaction 
} catch { if errors in editSession, rollback}

• giscope: Thanks Awesomo.. That's exactly what I am doing right now but I thought there might be a better way.. The issue is that the Oracle session and the one started by the editSession are not the same, so the edit I do to the foreign table are not visible to the editsession until I commit them.