How to use Output Parameter with Insight.Database

Question

I am learning Insight.Database and would like to convert the following to use a Repository.

This version works:

using (SqlConnection connection = GetConnection())
        {
            string errorMessage = string.Empty;
            connection.Open();
            var selectCommand = new SqlCommand("cds_InsertUtilityIntroRate", connection);
            selectCommand.CommandType = CommandType.StoredProcedure;
            selectCommand.Parameters.Add(new SqlParameter() { ParameterName = "@utility_id", Value = introRateParameters.UtilityId });
            selectCommand.Parameters.Add(new SqlParameter() { ParameterName = "@start_date", Value = introRateParameters.StartDate });
            selectCommand.Parameters.Add(new SqlParameter() { ParameterName = "@intro_rate", Value = introRateParameters.IntroRate });
            selectCommand.Parameters.Add(new SqlParameter() { ParameterName = "@commodity_id", Value = introRateParameters.CommodityId });
            selectCommand.Parameters.Add(new SqlParameter() { ParameterName = "@zone", Value = introRateParameters.Zone });
            var output = new SqlParameter() {ParameterName = "@error", DbType = DbType.String, Size = 1000};
            output.Direction = ParameterDirection.Output;
            selectCommand.Parameters.Add(output);
            selectCommand.ExecuteNonQuery();

            if (output.Value is string)
            {
                errorMessage = output.Value.ToString();
            }

            return errorMessage;
        }

This is what I have put together for the repository:

public interface  IIntroRateRespository
{
    int cds_InsertUtilityIntroRate(int utility_id, int commodity_id, DateTime start_date, decimal intro_rate, string zone, string error);
}

This is the code that calls it via Insight.Database:

string errorMessage = string.Empty;
        var conn = GetConnection();
        var repository = conn.As<IIntroRateRespository>();
        int results = repository.cds_InsertUtilityIntroRate(introRateParameters.UtilityId, introRateParameters.CommodityId, introRateParameters.StartDate,
            introRateParameters.IntroRate, introRateParameters.Zone, errorMessage);

        Console.Write(errorMessage);

The issue is that there is a output parameter, and I am not sure how that is supposed to be wired up. When I run it, it error with a null reference exception:

    at Insight.Database.DBConnectionExtensions.<>c__DisplayClass10`1.<ExecuteScalar>b__f         (IDbCommand _, IDataReader __) in  c:\projects.net\Insight\Insight.Database\Insight.Database\Extensions\DBConnectionExtensions.cs:line 392
   at Insight.Database.DBConnectionExtensions.ExecuteAndAutoClose[T](IDbConnection connection, Func`2 getCommand, Func`3 translate, CommandBehavior commandBehavior) in c:\projects.net\Insight\Insight.Database\Insight.Database\Extensions\DBConnectionExtensions.cs:line 1488
   at Insight.Database.DBConnectionExtensions.ExecuteAndAutoClose[T](IDbConnection connection, Func`2 getCommand, Func`3 translate, Boolean closeConnection) in c:\projects.net\Insight\Insight.Database\Insight.Database\Extensions\DBConnectionExtensions.cs:line 1456
   at Insight.Database.DBConnectionExtensions.ExecuteScalar[T](IDbConnection connection, String sql, Object parameters, CommandType commandType, Boolean closeConnection, Nullable`1 commandTimeout, IDbTransaction transaction, Object outputParameters) in c:\projects.net\Insight\Insight.Database\Insight.Database\Extensions\DBConnectionExtensions.cs:line 386

Answer 1

To use an output parameter for an interface implementation, just mark the parameter as an out parameter:

interface IFoo {
     void MyProc(int inParameter, out outParameter, ref refParameter);
}

So for yours, it should just be:

public interface IIntroRateRespository
{
    int cds_InsertUtilityIntroRate(int utility_id, int commodity_id, 
        DateTime start_date, decimal intro_rate, string zone, out string error);
}

Also, if your parameters class looks like this:

class IntroRateParams {
    public int UtilityId;
    public DateTime StartDate;
    public decimal IntroRate;
    public int CommodityId;
    public string Zone;
    public string Error;
}

Then you can just define the interface as:

public interface IIntroRateRespository
{
    int cds_InsertUtilityIntroRate(IntroRateParams parameters);
}

When you pass a single object into a method, Insight will attempt to extract the parameters from fields. It will also automatically reflect output parameters back into the object. So:

IntroRateParams p = /* from somewhere */
var repo = connection.As<IIntroRateRepository();
int id = repo.cds_InsertUtilityIntroRate(p);
var error = p.Error;

As for the NullReferenceException: Insight is generating the NullReferenceException because you have defined the interface as returning an int. Insight is calling ExecuteScalar and trying to cast the result to an int, but your procedure isn't returning any rows.

If your procedure doesn't return any rows, make sure that your method returns void.

public interface IIntroRateRespository
{
    void cds_InsertUtilityIntroRate(int utility_id, int commodity_id, 
        DateTime start_date, decimal intro_rate, string zone, out string error);
}

I'll update the library so the exception is clearer in this case.