Bug Description
Trying to call a stored procedure which has an output parameter of type varchar (sproc attached)
p_BreakglassRequest_Update.txt
Returning int or datetime output parameters seem to work fine, but varchar gives the exception below.
Exception Message:
An unhandled exception has occurred while executing the request. System.InvalidOperationException: String[22]: the Size property has an invalid size of 0.
at Microsoft.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at Microsoft.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Boolean inSchema, SqlParameterCollection parameters)
at Microsoft.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteReaderInternal(CommandBehavior behavior, AsyncCallback callback, Object stateObject, Int32 timeout, Boolean inRetry, Boolean asyncWrite)
at Microsoft.Data.SqlClient.SqlCommand.BeginExecuteReaderAsyncCallback(AsyncCallback callback, Object stateObject)
at System.Threading.Tasks.TaskFactory`1.FromAsyncImpl(Func`3 beginMethod, Func`2 endFunction, Action`1 endAction, Object state, TaskCreationOptions creationOptions)
at System.Threading.Tasks.TaskFactory`1.FromAsync(Func`3 beginMethod, Func`2 endMethod, Object state)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location ---
at RepoDb.DbConnectionExtension.ExecuteScalarAsyncInternal[TResult](IDbConnection connection, String commandText, Object param, Nullable`1 commandType, String cacheKey, Nullable`1 cacheItemExpiration, Nullable`1 commandTimeout, IDbTransaction transaction, ICache cache, CancellationToken cancellationToken, Type entityType, IEnumerable`1 dbFields, Boolean skipCommandArrayParametersCheck)
at LevelUp.Core.Services.Repositories.BreakglassRequestRepository.UpdateAsync(BreakglassRequest breakglassRequest) in D:\Dev\Infrastructure\LevelUp\LevelUp.Core\Services\Repositories\BreakglassRequestRepository.cs:line 124
at LevelUp.Pages.Breakglass.EditRequestModel.DoPostAction(Action applyPropertyChangesAction, Boolean forceValidate) in D:\Dev\Infrastructure\LevelUp\LevelUp\Pages\Breakglass\EditRequest.cshtml.cs:line 185
at LevelUp.Pages.Breakglass.EditRequestModel.OnPostSubmitAsync() in D:\Dev\Infrastructure\LevelUp\LevelUp\Pages\Breakglass\EditRequest.cshtml.cs:line 99
Schema and Model:
Please share to us the schema of the table (not actual) that could help us replicate the issue if necessary.
CREATE TABLE [dbo].[BreakglassRequest](
[Id] [int] IDENTITY(1,1) NOT NULL,
[RequestType] [varchar](20) NOT NULL,
[BreakglassUser] [varchar](50) NOT NULL,
[ClientCode] [varchar](3) NOT NULL,
[Applications] [varchar](1000) NOT NULL,
[StartDateTime] [datetime] NOT NULL,
[EndDateTime] [datetime] NOT NULL,
[Justification] [varchar](1500) NOT NULL,
[Status] [varchar](20) NOT NULL,
[ErrorMessage] [varchar](100) NULL,
[CreatedBy] [varchar](50) NOT NULL,
[CreatedDateTime] [datetime] NOT NULL,
[ModifiedBy] [varchar](50) NULL,
[ModifiedDateTime] [datetime] NULL,
[ApprovedBy] [varchar](50) NULL,
[ApprovedDateTime] [datetime] NULL,
[CancelledBy] [varchar](50) NULL,
[CancelledDateTime] [datetime] NULL,
[RowVersion] [timestamp] NOT NULL
CONSTRAINT [PK_BreakglassRequest] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
And also the model that corresponds the schema.
public class BreakglassRequest
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
public BreakglassRequestType RequestType { get; set; }
public string BreakglassUser { get; set; }
public string ClientCode { get; set; }
public string Applications { get; set; }
public DateTime StartDateTime { get; set; }
public DateTime EndDateTime { get; set; }
public string Justification { get; set; }
public BreakglassStatus Status { get; set; } = BreakglassStatus.Pending;
public string ErrorMessage { get; set; }
public string CreatedBy { get; set; }
public DateTime CreatedDateTime { get; set; }
public string ModifiedBy { get; set; }
public DateTime? ModifiedDateTime { get; set; }
public string ApprovedBy { get; set; }
public DateTime? ApprovedDateTime { get; set; }
public string CancelledBy { get; set; }
public DateTime? CancelledDateTime { get; set; }
[Timestamp]
public byte[] RowVersion { get; set; }
}Library Version:
RepoDb v1.12.7 and RepoDb.SqlServer v1.1.3
Original Code Attempt:
In my own repository class (not inheriting from RepoDb repositories) my code looked like this:
public async Task<ConcurrencyFailureInfo> UpdateAsync(BreakglassRequest breakglassRequest)
{
DirectionalQueryField lastUpdateUserField = new DirectionalQueryField("LastUpdateUser", typeof(string), ParameterDirection.Output);
DirectionalQueryField lastUpdateDateTimeField = new DirectionalQueryField("LastUpdateDateTime", typeof(DateTime), ParameterDirection.Output);
using (SqlConnection connection = new SqlConnection(m_connectionString))
{
int success = await connection.ExecuteScalarAsync<int>("p_BreakglassRequest_Update",
new []
{
new QueryField("Id", breakglassRequest.Id),
new QueryField("BreakglassUser", breakglassRequest.BreakglassUser),
new QueryField("Applications", breakglassRequest.Applications),
new QueryField("StartDateTime", breakglassRequest.StartDateTime),
new QueryField("EndDateTime", breakglassRequest.EndDateTime),
new QueryField("Justification", breakglassRequest.Justification ?? SqlString.Null),
new QueryField("Status", breakglassRequest.Status.ToString()),
new QueryField("ErrorMessage", breakglassRequest.ErrorMessage ?? SqlString.Null),
new QueryField("ModifiedBy", breakglassRequest.ModifiedBy ?? SqlString.Null),
new QueryField("ModifiedDateTime", breakglassRequest.ModifiedDateTime ?? SqlDateTime.Null),
new QueryField("ApprovedBy", breakglassRequest.ApprovedBy ?? SqlString.Null),
new QueryField("ApprovedDateTime", breakglassRequest.ApprovedDateTime ?? SqlDateTime.Null),
new QueryField("CancelledBy", breakglassRequest.CancelledBy ?? SqlString.Null),
new QueryField("CancelledDateTime", breakglassRequest.CancelledDateTime ?? SqlDateTime.Null),
new QueryField("RowVersion", breakglassRequest.RowVersion),
lastUpdateUserField,
lastUpdateDateTimeField
},
CommandType.StoredProcedure);
if (success == 0)
return null;
}
ConcurrencyFailureInfo info = new ConcurrencyFailureInfo();
info.LastUpdateUser = lastUpdateUserField.GetValue<string>();
info.LastUpdateDateTime = lastUpdateDateTimeField.GetValue<DateTime>();
m_logger.LogInformation($"Failed to update in DB as request {breakglassRequest.Id} was modified by user {info.LastUpdateUser} at {info.LastUpdateDateTime}");
return info;
}Note that I found a second issue (mentioned on Gitter) to do with the above code in trying to pass that RowVersion property to the sproc. If you comment out the "lastUpdateUserField, LastUpdateDateTimeField" parameters and also comment out the related code in the sproc, I found I could not get the sproc to be invoked, with it complaining about a non matching number of parameters. If I also commented out the RowVersion QueryField above (and corresponding lines in the sproc) then the sproc works fine.
My workaround for all of this currently was to eliminate the output parameters completely from the sproc, requiring an additional db call in the situation where there is a concurrency issue (not a major deal given the likelihood of it happening but not ideal nonetheless). In making that change I was then able to eliminate using "QueryField" arguments to the sproc call, and instead use the code below, which incidentally solved my issue with the Timestamp parameter property to the sproc. So I "think" there is an issue with passing a QueryField parameter to a Timestamp property in the sproc given this now works for me, but I understand if you are sceptical...
...
using (SqlConnection connection = new SqlConnection(m_connectionString))
{
int success = await connection.ExecuteScalarAsync<int>("p_BreakglassRequest_Update",
new
{
Id = breakglassRequest.Id,
BreakglassUser = breakglassRequest.BreakglassUser,
Applications = breakglassRequest.Applications,
StartDateTime = breakglassRequest.StartDateTime,
EndDateTime = breakglassRequest.EndDateTime,
Justification = breakglassRequest.Justification ?? SqlString.Null,
Status = breakglassRequest.Status.ToString(),
ErrorMessage = breakglassRequest.ErrorMessage ?? SqlString.Null,
ModifiedBy = breakglassRequest.ModifiedBy ?? SqlString.Null,
ModifiedDateTime = breakglassRequest.ModifiedDateTime ?? SqlDateTime.Null,
ApprovedBy = breakglassRequest.ApprovedBy ?? SqlString.Null,
ApprovedDateTime = breakglassRequest.ApprovedDateTime ?? SqlDateTime.Null,
CancelledBy = breakglassRequest.CancelledBy ?? SqlString.Null,
CancelledDateTime = breakglassRequest.CancelledDateTime ?? SqlDateTime.Null,
RowVersion = breakglassRequest.RowVersion,
},
CommandType.StoredProcedure);
if (success == 0)
return null;
}
...
Bug Description
Trying to call a stored procedure which has an output parameter of type varchar (sproc attached)
p_BreakglassRequest_Update.txt
Returning int or datetime output parameters seem to work fine, but varchar gives the exception below.
Exception Message:
Schema and Model:
Please share to us the schema of the table (not actual) that could help us replicate the issue if necessary.
And also the model that corresponds the schema.
Library Version:
RepoDb v1.12.7 and RepoDb.SqlServer v1.1.3
Original Code Attempt:
In my own repository class (not inheriting from RepoDb repositories) my code looked like this:
Note that I found a second issue (mentioned on Gitter) to do with the above code in trying to pass that RowVersion property to the sproc. If you comment out the "lastUpdateUserField, LastUpdateDateTimeField" parameters and also comment out the related code in the sproc, I found I could not get the sproc to be invoked, with it complaining about a non matching number of parameters. If I also commented out the RowVersion QueryField above (and corresponding lines in the sproc) then the sproc works fine.
My workaround for all of this currently was to eliminate the output parameters completely from the sproc, requiring an additional db call in the situation where there is a concurrency issue (not a major deal given the likelihood of it happening but not ideal nonetheless). In making that change I was then able to eliminate using "QueryField" arguments to the sproc call, and instead use the code below, which incidentally solved my issue with the Timestamp parameter property to the sproc. So I "think" there is an issue with passing a QueryField parameter to a Timestamp property in the sproc given this now works for me, but I understand if you are sceptical...