Building .NET apps on an Oracle DB

For the past few months I've been working on building a C# app that hits an Oracle database and it's been an adventure.  One problem that I've really plowed into is with the merging statement (though it's mostly self-inflicted). 

While writing my business objects, say for instance a MasterAccount class, I have a Master_Account table in the DB and the class and DB have obviously the same fields.  If I instantiate a MasterAccount object like so:

MasterAccount ma = new MasterAccount(masterAccountId);

Then the class hit's the DB and fills all of the fields with data.  If I construct one like this however:

MasterAccount ma = new MasterAccount();
ma.AccountName = "Some Name";
ma.EmailAddress = "some@email.address"; // etc, etc

then the MasterAccountId (PK in the Database) would be set to 0.  Then I have a method, Save, that I could call and it would run a merge (aka upsert) where if the object's MasterAccountId was already in the database it would update, it it wasn't (like if it was still the default 0) then it would insert the record and generate it's own Master_Account_Id (DB side).  The problem is my stored procedure for the upsert is giving me all kinds of trouble because it's not letting me use my parameter values as a row for merge matching. 

When you're starting in the software biz, there's always something new to learn.

 

del.icio.us tags: Oracle, C#, Merge Insert