SqlClient: SqlDataReader incorrectly returns null ROWVERSION as an empty byte[]
When returned in a resultset a null ROWVERSION is returned as an empty byte[], instead of DbNull.Value. This problem does not occur when returning using a parameter value, or using a resultset in ODBC (so not a TDS issue).
Here’s e a repro:
using System;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Threading;
using System.Threading.Tasks;
using System.Transactions;
namespace ConsoleApp14
{
class Program
{
static void Main(string[] args)
{
using (var con = new SqlConnection("Server=localhost;database=tempdb;Integrated Security=true"))
{
con.Open();
var cmd = con.CreateCommand();
cmd.CommandText = "select cast(null as rowversion) rv";
using (var rdr = cmd.ExecuteReader())
{
rdr.Read();
var allowDbNull = rdr.GetColumnSchema()[0].AllowDBNull;
var isNull = rdr.IsDBNull(0);
var val = rdr[0];
Console.WriteLine($"SqlClient: AllowDbNull {allowDbNull} IsDbNull: {isNull} {val.GetType().Name} {val}");
}
}
using (var con = new SqlConnection("Server=localhost;database=tempdb;Integrated Security=true"))
{
con.Open();
var cmd = con.CreateCommand();
cmd.CommandText = "select @val = cast(null as rowversion) ";
var p = cmd.Parameters.Add(new SqlParameter("@val", System.Data.SqlDbType.Timestamp));
p.Direction = System.Data.ParameterDirection.Output;
cmd.ExecuteNonQuery();
{
SqlBinary val = (SqlBinary) p.SqlValue;
Console.WriteLine($"SqlClient (parameter): IsDbNull: {val.IsNull} {val.GetType().Name} {val}");
}
}
using (var con = new OdbcConnection("Driver={ODBC Driver 17 for SQL Server};Server=localhost;Trusted_Connection=yes"))
{
con.Open();
var cmd = con.CreateCommand();
cmd.CommandText = "select cast(null as rowversion) rv";
using (var rdr = cmd.ExecuteReader())
{
rdr.Read();
var allowDbNull = rdr.GetSchemaTable().Rows[0]["AllowDBNull"];
var isNull = rdr.IsDBNull(0);
var val = rdr[0];
Console.WriteLine($"ODBC: AllowDbNull {allowDbNull} IsDbNull: {isNull} {val.GetType().Name} {val}");
}
}
}
}
}
Expected behavior
SqlClient: AllowDbNull True IsDbNull: True DBNull SqlClient (parameter): IsDbNull: True SqlBinary Null ODBC: AllowDbNull True IsDbNull: True DBNull
Actual Behavior
SqlClient: AllowDbNull True IsDbNull: False Byte[] System.Byte[] SqlClient (parameter): IsDbNull: True SqlBinary Null ODBC: AllowDbNull True IsDbNull: True DBNull
About this issue
- Original URL
- State: closed
- Created 5 years ago
- Comments: 23 (10 by maintainers)
Opened https://github.com/dotnet/SqlClient/issues/1684 to track it.
I just tested with the repro at the top of this issue. Using Microsoft.Data.SqlClient 4.1 the behavior is correct, the ROWVERSION is returned as Null. Using System.Data.SqlClient 4.8.3, the old behavior is the same, the ROWVERSION is returned as an empty byte[].
That is a slightly weird behaviour. That value is null as far as sql is concerned, if you try to datalength or null check it you’ll get consistent behaviour for a null value. You should not get an exception when asking for the value through GetSqlBinary
Sql Management Studio uses this library so a change here would also affect it. It may be that management studio isn’t prepared for the SqlBinary.Null value and doesn’t check for IsNull instead just asks for the length and formats it. That would explain the display weirdness there. @DavoudEshtehari is there anyone on the management studio team we could ping on that?
It should be in all current versions, this is quite an old issue.
Can you make your test sql into a small standalone c# test program and open a new issue with it please? that way it will get tracked correctly and be easier to reproduce and possibly fix.
I was just working on fixing this.
Question, should the AppContext switch default to the new behaviour (to return SqlBinary.Null) or the old behaviour? 3.0.0 will contain other breaking changes so I think it might be good to default to the new behaviour and have the switch return it to the old behaviour. Is that ok?