dotnet / dotnet/SqlClient

SqlDataReader.GetSqlDateTime does not support DATETIME2

Open
#846 50 comments 5 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.