SqlDataReader.GetSqlDateTime does not support DATETIME2
- Dominant language
- C#
- Stars
- 989
- Forks
- 340
- Avg merge
- 4d 19h
- Merged PRs (30d)
- 72
Description
### Describe the bug
It seems like DATETIME2 sql server datatype is not fully or correctly implemented in SqlDataReader.
using method GetDateTime - it handles DATETIME2 correctly
using method GetSqlDateTime - it throws an
System.InvalidCastException:
```
Exception message: Specified cast is not valid
Stack trace: at Microsoft.Data.SqlClient.SqlBuffer.get_SqlDateTime()
```
### To reproduce
```c#
[TestMethod]
public void DateTime2Mapping()
{
string Statement = $@"SELECT CAST('2020-12-11 09:17:12.123456' AS DATETIME2) AS [MyDate]";
var builder = new SqlConnectionStringBuilder()
{
InitialCatalog = "master",
DataSource = @"any sql connection",
IntegratedSecurity = true,
};
var sqlConnection = new SqlConnection(builder.ConnectionString);
sqlConnection.Open();
using (SqlCommand command = new SqlCommand(Statement))
{
command.CommandType = CommandType.Text;
command.Connection = sqlConnection;
command.CommandText = Statement;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var dtColDate = reader.GetSqlDateTime(0);
Assert.IsFalse(dtColDate.IsNull);
Console.WriteLine(dtColDate.Value);
}
}
}
}
```
### Expected behavior
I expect that GetSqlDateTime handles DATETIME2 and returns a SqlDateTime struct
### Further technical details
Microsoft.Data.SqlClient version: 2.1.0
.NET target: Framework 4.7.2
SQL Server version: 15.0.4079.3 (2019)
Operating system: Windows 10
**Additional context**
Why I found this issue, is because I was profiling our code and saw that SqlDataReader.IsDbNull took up a lot of time since we need to check every column for null, since GetXX methods does not handle DBNull - and then I found SqlDataReader.GetSqlXX methods which handles DBNull.
Basically trying to get rid of the overhead in SqlDataReader
```c#
override public bool IsDBNull(int i)
{
{
CheckHeaderIsReady(columnIndex: i);
SetTimeout(_defaultTimeoutMilliseconds);
ReadColumnHeader(i); // header data only
}
return _data[i].IsNull;
}
```
by swithing to
The IsNull property in the SqlTypes
i.e.
```c#
public bool IsNull => !this.m_fNotNull;
```
Contributor guide
Assessment
This issue has not been assessed yet.