Oracle provider does not return auto-incremented primary key values

[stackwave]

The SubSonic providers for SQLite and SQLServer appear to use those databases' special syntax to retrieve auto-incremented primary key values. Oracle does not support auto-incrementing columns; one must explicitly create a sequence object, then use a database trigger to pull a value from the sequence on each insert into the target table. However, this causes a problem in the SimpleRepository if one expects to get a value back from the database after a call to Add, because no such value will be returned.

This is probably best solved heuristically. If a numeric primary key exists for a table, use Oracle's "RETURNING INTO" syntax to retrieve the primary key value in an output parameter.

I've hacked up the OracleGenerator, DbDataProvider, and SimpleRepository to get things working for myself, but I'm sure there's a more elegant way to do it. I've currently got the following at the end of the OracleGenerator's BuildInsertStatement:

sb.AppendFormat(" RETURNING {0}.{1} INTO :row_id", i.Table.Name, i.Table.PrimaryKey.Name);
sb.AppendLine();

Then, in DbDataProvider, I added a new OracleInsertParameter property. If I detect a parameter named ":row_id" just before that method returns the reader, I assign that parameter to the new property.

if (cmd.Parameters.Contains(":row_id"))
{
    OracleInsertParameter = cmd.Parameters[":row_id"];
}

Then in the SimpleRepository.Add method I tweaked the using statement as follows:

using(var rdr = item.ToInsertQuery(_provider).ExecuteReader())
{
    if (rdr.Read())
    {
        result = rdr[0];
    }
    else
    {
        if (_provider is OracleDataProvider)
        {
            return (_provider as OracleDataProvider).OracleInsertParameter.Value;
        }
    }
}

It's ugly, but it did the trick.