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
To use an output parameter for an interface implementation, just mark the parameter as an out parameter:
So for yours, it should just be:
Also, if your parameters class looks like this:
Then you can just define the interface as:
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:
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.
I'll update the library so the exception is clearer in this case.