Oracle Stored Procedures in SubSonic 2.2

Question

Howdy!

I'm still not getting my SP's generated in SubSonic 2.2 against an Oracle 10g DB. The Tables and Views generate up perfectly. Also this product is multiplatform so we're gen'ing up SubSonic libraries against SQL 2K5 and that works great for Tables/Views and SP's.

I recall on the old forums there was a bug in earlier versions of the Provider, so I'm not sure if this is still the same issue, or if I'm doing something wrong on my end? Also SubStage (the UI) chokes when invoking the provider on Oracle (no issues with SQL). So I'm figuring there's something in the SPs that are choking it [?]

Thanks! Real close to migrating to SS from our current home grown DAL.

Answer 1

I was able to fix a lot of the issues with the Oracle Provider in SubSonic in version 2.1 and most all of my fixes made it into 2.2. I didn't work on fixing the SP generation portion of the Oracle Provider as I only had one or two SPs. Even if the SP generation isn't working you can still use SPs with the Oracle Provider. I simply added the SPs by hand (see below) as a partial class in the altered folder I use to add functionality to the classes SubSonic generates.

example

    public partial class SPs
{
    public static decimal CreateSp(string username, string labelNote)
    {
        Decimal returnId = 0;
        SubSonic.StoredProcedure sp = new StoredProcedure("User.MySP");
        sp.Command.Parameters.Add("username", username, DbType.String);
        sp.Command.Parameters.Add("labelnote", labelNote, DbType.String);
        sp.Command.Parameters.Add("returnId", returnId, DbType.Decimal, ParameterDirection.Output);
        sp.Execute();
        return Convert.ToDecimal(sp.Command.Parameters.Find(delegate(QueryParameter qp) { return qp.ParameterName == "returnId"; }).ParameterValue);
    }

}