How to return a strongly-typed object from a SubSonic SPROC call with SPROC parameters?

Question

I have a data caller method that returns a SubSonic collection of type ItemDatumCollection.

The sproc is executed as follows:

itemDatumCollection.LoadAndCloseReader(sp.GetReader());

However, I am unable to access the output parameters of the sproc in this fashion, as I am able to do when calling sp.GetDataSet() as follows:

            itemsDataSet = sp.GetDataSet();

            actualNumberOfResults = ((Int64)sp.OutputValues[1]);
            numberOfResultsReturned = ((Int64)sp.OutputValues[2]);

Is there a way to access the sproc's output parameters with the first method - returning a SubSonic strongly-typed collection from my sproc call?

Thanks.

Answer 1

I wanted to do something similar, and ended up changing most of the StoredProcedures.tt file, and I think some of the internals of SubSonic. Without getting too into detail, I changed by .tt file so that it would generate this for a given stored proc:

    public void COMPANIES_ACTIVATE_PACKAGE(long PI_COMPANY_ID, string PI_ACTIVE, long PI_USER_ID, out long PO_ERRCODE, out string PO_ERRMSG, out string PO_ORA_ERRMSG){
        StoredProcedure sp=new StoredProcedure("COMPANIES.ACTIVATE_PACKAGE",this.Provider);
        sp.Command.AddParameter("PI_COMPANY_ID",PI_COMPANY_ID,DbType.Decimal);
        sp.Command.AddParameter("PI_ACTIVE",PI_ACTIVE,DbType.AnsiString);
        sp.Command.AddParameter("PI_USER_ID",PI_USER_ID,DbType.Decimal);
        sp.Command.AddOutputParameter("PO_ERRCODE",DbType.AnsiString);
        sp.Command.AddOutputParameter("PO_ERRMSG",DbType.AnsiString);
        sp.Command.AddOutputParameter("PO_ORA_ERRMSG",DbType.AnsiString);
        sp.Execute();
        var prms = sp.Command.Parameters;
        PO_ERRCODE = ConvertValue<long>(prms.GetParameter("PO_ERRCODE").ParameterValue);
        PO_ERRMSG = ConvertValue<string>(prms.GetParameter("PO_ERRMSG").ParameterValue);
        PO_ORA_ERRMSG = ConvertValue<string>(prms.GetParameter("PO_ORA_ERRMSG").ParameterValue);
    }

So basically I pass in any sproc parameters, and have 'out' parameters defined for the return values.

Also not shown here, but if I have an InOut parameter, then I pass it by ref instead of out.

Then in my actual application code, I can just call the stored proc like any other function:

long errorCode;
string errorMsg, oraErrorMsg;

db.COMPANIES_ACTIVATE_PACKAGE(123, "Y", 456, out errorCode, out errorMsg, out oraErrorMsg);

if(errorCode > 0)
    // ... handle error...

I don't know if this will plug right in to SS without further changes, but this is my .tt file. You might be able to use it, or at least get some ideas of where to go:

StoredProcedures.tt :

<#@ template language="C#" debug="False" hostspecific="True"  #>
<#@ output extension=".cs" #>
<#@ include file="DB2DataProvider.ttinclude" #>
<#
    var sps = GetSPs(); 
    if(sps.Count>0){ 
#>  
using System;
using System.Data;
using System.ComponentModel;
using SubSonic;
using SubSonic.Schema;
using SubSonic.DataProviders;

namespace <#=Namespace#>{
    public partial class <#=DatabaseName#>DB{

        public T ConvertValue<T>(object paramVal)
        {
            if (paramVal == null || Convert.IsDBNull(paramVal))  // if the value is null, return the default for the desired type.
                return default(T);
            if (typeof(T) == paramVal.GetType())  // if types are already equal, no conversion needed. just cast.
                return (T)paramVal;
            else // types don't match. try to convert.
            {
                var conversionType = typeof(T);
                if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                {
                    NullableConverter nullableConverter = new NullableConverter(conversionType);
                    conversionType = nullableConverter.UnderlyingType;
                }
                return (T)Convert.ChangeType(paramVal, conversionType);
            }
        }

<#  foreach(var sp in sps){#>
        public void <#=sp.CleanName#>(<#=sp.ArgList#>){
            StoredProcedure sp=new StoredProcedure("<#=sp.Name#>",this.Provider);
<#      foreach(var par in sp.Parameters) {
          if(par.In && !par.Out) {#>
            sp.Command.AddParameter("<#=par.Name#>",<#=par.CleanName#>,DbType.<#=par.DbType#>);
<#        } else if(!par.In && par.Out) {#>
            sp.Command.AddOutputParameter("<#=par.Name#>",DbType.<#=par.DbType#>);
<#        } else {#>
            sp.Command.AddParameter("<#=par.Name#>",<#=par.CleanName#>,DbType.<#=par.DbType#>,ParameterDirection.InputOutput);
<#      }}#>
            sp.Execute();
<#      bool hasOut = false;
        foreach(var par in sp.Parameters) {
          if(par.Out)
            hasOut = true;
        }
        if(hasOut) {
#>
            var prms = sp.Command.Parameters;
<#      }
        foreach(var par in sp.Parameters) {
          if(par.Out) {#>
            <#=par.Name#> = ConvertValue<<#=par.SysType#>>(prms.GetParameter("<#=par.Name#>").ParameterValue);
<#      }}#>
        }
<#  }#>

    }

}
<#  }#>

I'm pretty sure I also had to change the query that loads stored proc data in the database provider .ttinclude file to load the column that indicates if it is In, Out, or InOut parameter type.

...Hope this helps in some way...