My .NET application uses SQLCommand to execute queries and the DB SPs. In one such SP that also returns the SQL 'timestamp' and that is read as byte array into the application. This byte array is incorrect causing DB to throw errors in the workflow later.
Here is the code:
public void ExecCommand(string strCommand,
CommandType CommandType,
IParameters IParameters,
ref IRows[] IRowsArr)
{
SqlDataReader dataReader = null;
if (this.m_sqlTransaction == null) //if not in transaction
OpenConnection();
this.MapTosqlParameters(IParameters, dbCommand);
SqlCommand dbCommand = this.CreateCommand();
dbCommand.CommandType = CommandType;
dbCommand.CommandText = Normalize(strCommand);
try
{
// Trace the sql Command string and its Execution Start Time
using (var sqlLogger = CreateSQLLogger(dbCommand))
{
dataReader = dbCommand.ExecuteReader();
}
}
catch (DbException ex)
{
LogError(ex);
throw;
}
}
private void MapTosqlParameters(IParameters Parameters, SqlCommand SqlCommand)
{
SqlParameter sqlParameter = null;
Object objValue;
Type Type;
TypeCode TypeCode;
SqlDbType SqlDbType;
// Convert Input Parameter to sql Type Parameter
SqlCommand.CommandText = Normalize(SqlCommand.CommandText);
if (Parameters != null)
{
foreach (IParameter Parameter in Parameters)
{
objValue = Parameter.objValue;
if (objValue is VMS.Varis.Common.Shared.IImage)
{
TypeCode = TypeCode.Object;
SqlDbType = SqlDbType.VarBinary;
objValue = ((IImage)objValue).GetJPG();
}
//Assign the Timestamp mapping here because typecode for timestamp is coming as byte[] as this can be used for anything .. Images, files etc..
else if (objValue != null &&
(objValue == VDBNullEq.HstryTimeStamp || objValue.GetType().Name == "Byte[]"))
//&& ((byte[])objValue).Length == 8))
{
TypeCode = TypeCode.Object;
SqlDbType = SqlDbType.Timestamp;
}
else
{
Type = (objValue != null ? objValue.GetType() : Parameter.Type);
TypeCode = System.Type.GetTypeCode(Type);
//For DataTable, m_parameterMaps does not return value as TypeCode does not contain equivalent value for SqlDbType.Structured.
SqlDbType = Type == (typeof(DataTable)) ? SqlDbType.Structured : m_parameterMaps.GetValue(TypeCode);
}
if (objValue != null && objValue != DBNull.Value && TypeCode == System.TypeCode.String)
{
objValue = Normalize(((string)objValue));
}
sqlParameter = SqlCommand.Parameters.Add(Parameter.strName, SqlDbType);
sqlParameter.Direction = Parameter.eParameterDirection;
sqlParameter.SourceColumn = Parameter.SourceColumn;
sqlParameter.SourceVersion = Parameter.SourceVersion;
if ((Parameter.eParameterDirection == ParameterDirection.Output) ||
(Parameter.eParameterDirection == ParameterDirection.InputOutput))
{
switch (SqlDbType)
{
case SqlDbType.NChar:
sqlParameter.Size = 4000;
break;
case SqlDbType.NVarChar:
sqlParameter.Size = 8000;
break;
}
}
//NullConversionHandler does not return value for TypeCode DataTable so set value explicitely for
if (SqlDbType == SqlDbType.Structured)
{
sqlParameter.Value = objValue ?? DBNull.Value;
}
else
{
sqlParameter.Value = (objValue == null || Compare(m_nullConversionHandler.GetNullValue(TypeCode), objValue)) ? DBNull.Value : objValue;
}
if (SqlDbType == SqlDbType.NChar && sqlParameter.Value == DBNull.Value)
{
sqlParameter.Value = string.Empty;
}
if (sqlParameter.Value is System.DateTime)
{
if (sqlParameter.Value != DBNull.Value)
{
if (((System.DateTime)sqlParameter.Value).Year > 9999 || ((System.DateTime)sqlParameter.Value).Year < 1900)
{
//throw new Exception(VDBResStrings.GetString("msgDateTimeValueOutOfBounds"));
// The error should be thrown only if the date year is less than 1900 and the time component is not 12:00 AM
if (((System.DateTime)sqlParameter.Value).TimeOfDay != TimeSpan.Zero)
{
throw new ArgumentException(VDBResStrings.GetString("msgDateTimeValueOutOfBounds"));
}
}
}
}
}
}
}
Workflow: -Create a new record, add some parameters. It first asks to save the information. -Information is saved and a timestamp is returned by the DB SP. -This timestamp is converted into bye array in the .NET code and here byte array is wrong -Add some additional parameters and save the same record. .NET code passes on the same byte array to the SP that has timestamp check, causing mismatch and error is thrown.
Whereas, the follow workflow works fine -Create a new record, add some parameters. It first asks to save the information. --Information is saved and a timestamp is returned by the DB SP. -This timestamp is converted into bye array in the .NET code and here byte array is wrong again. -Close the record and reopen(this causes calling another SP that returns the timestamp) and add some new parameters and save.
Note: The same workflow causing issue works fine in .NET Framework V4.7 but fails in V4.8
