Getting Time column value from DB is not working (2.1.35) #2057
Comments
On the last: yes, you've provided a lot of context, and that's great, but I want to minimize the room for confusion in how I interpret it. An illustrative Execute or Query demo leaves a lot less room for ambiguity |
Example: using Dapper;
using Microsoft.Data.SqlClient;
namespace Whatever
{
public class MyExampleTest
{
[Test]
public async Task Send_DateOnly_TimeOnly_via_Dictionary()
{
#region Arrange
using var sqlConnection = new SqlConnection("SomeConnectionString");
sqlConnection.Open();
string createDateAndTimeTable = @"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'U' AND name = 'DateAndTime')
CREATE TABLE [DateAndTime] (
[Id] [INT] IDENTITY(1,1) NOT NULL,
[DateTime] [DATETIME] NOT NULL,
[DateOnly] [DATE] NOT NULL,
[TimeOnly] [TIME] NOT NULL,
CONSTRAINT [PK_DateAndTime] PRIMARY KEY CLUSTERED
( [Id] ASC )
)";
var command = new SqlCommand(createDateAndTimeTable, sqlConnection);
command.ExecuteNonQuery();
command.Dispose();
var dateTime = new DateTime(2001, 2, 3, 4, 5, 6);
var dateOnly = new DateOnly(2002, 3, 4);
var timeOnly = new TimeOnly(7, 8, 9);
var parameters = new Dictionary<string, object>
{
{ "p0", dateTime },
{ "p1", dateOnly },
{ "p2", timeOnly },
};
#endregion
#region Act
string query = "" +
"INSERT INTO [DateAndTime] ([DateTime], [DateOnly], [TimeOnly]) " +
"VALUES (@p0, @p1, @p2); " +
"SELECT CAST(SCOPE_IDENTITY() AS BIGINT)";
// Here it fails with the following error:
// System.NotSupportedException : The member p1 of type System.DateOnly cannot be used as a parameter value.
object id = await sqlConnection.ExecuteScalarAsync(query, parameters);
#endregion
#region Assert
Assert.That((long)id, Is.GreaterThan(0));
#endregion
}
[Test]
public async Task Send_DateOnly_TimeOnly_via_DynamicParameters()
{
#region Arrange
using var sqlConnection = new SqlConnection("SomeConnectionString");
sqlConnection.Open();
string createDateAndTimeTable = @"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'U' AND name = 'DateAndTime')
CREATE TABLE [DateAndTime] (
[Id] [INT] IDENTITY(1,1) NOT NULL,
[DateTime] [DATETIME] NOT NULL,
[DateOnly] [DATE] NOT NULL,
[TimeOnly] [TIME] NOT NULL,
CONSTRAINT [PK_DateAndTime] PRIMARY KEY CLUSTERED
( [Id] ASC )
)";
var command = new SqlCommand(createDateAndTimeTable, sqlConnection);
command.ExecuteNonQuery();
command.Dispose();
var dateTime = new DateTime(2001, 2, 3, 4, 5, 6);
var dateOnly = new DateOnly(2002, 3, 4);
var timeOnly = new TimeOnly(7, 8, 9);
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", dateTime);
dynamicParameters.Add("p1", dateOnly);
dynamicParameters.Add("p2", timeOnly);
#endregion
#region Act
string query = "" +
"INSERT INTO [DateAndTime] ([DateTime], [DateOnly], [TimeOnly]) " +
"VALUES (@p0, @p1, @p2); " +
"SELECT CAST(SCOPE_IDENTITY() AS BIGINT)";
// Here it fails with the following error:
// System.NotSupportedException : The member p1 of type System.DateOnly cannot be used as a parameter value.
object id = await sqlConnection.ExecuteScalarAsync(query, dynamicParameters);
#endregion
#region Assert
Assert.That((long)id, Is.GreaterThan(0));
#endregion
}
[Test]
public async Task Send_DateOnly_TimeOnly_via_DynamicParameters_Set_DbType_Get_Row()
{
#region Arrange
using var sqlConnection = new SqlConnection("SomeConnectionString");
sqlConnection.Open();
string createDateAndTimeTable = @"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'U' AND name = 'DateAndTime')
CREATE TABLE [DateAndTime] (
[Id] [INT] IDENTITY(1,1) NOT NULL,
[DateTime] [DATETIME] NOT NULL,
[DateOnly] [DATE] NOT NULL,
[TimeOnly] [TIME] NOT NULL,
CONSTRAINT [PK_DateAndTime] PRIMARY KEY CLUSTERED
( [Id] ASC )
)";
var command = new SqlCommand(createDateAndTimeTable, sqlConnection);
command.ExecuteNonQuery();
command.Dispose();
var dateTime = new DateTime(2001, 2, 3, 4, 5, 6);
var dateOnly = new DateOnly(2002, 3, 4);
var timeOnly = new TimeOnly(7, 8, 9);
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", dateTime);
dynamicParameters.Add("p1", dateOnly, System.Data.DbType.Date); // <--
dynamicParameters.Add("p2", timeOnly, System.Data.DbType.Time); // <--
#endregion
#region Act
string query = "" +
"INSERT INTO [DateAndTime] ([DateTime], [DateOnly], [TimeOnly]) " +
"VALUES (@p0, @p1, @p2); " +
"SELECT CAST(SCOPE_IDENTITY() AS BIGINT)";
// Here it works!
object id = await sqlConnection.ExecuteScalarAsync(query, dynamicParameters);
// Now, we are gonna get that row.
dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", (long)id);
query = "SELECT * FROM [DateAndTime] WHERE [Id] = @p0";
// Here it fails with the following error (using class "DateAndTime1" as T, declared below):
// System.Data.DataException : Error parsing column 2 (DateOnly=3/4/2002 12:00:00 AM - DateTime)
// Inner Exception: System.InvalidCastException : Invalid cast from 'System.DateTime' to 'System.DateOnly'.
var dateAndTimes = await sqlConnection.QueryAsync<DateAndTime1>(query, dynamicParameters);
#endregion
#region Assert
Assert.That(dateAndTimes.Count(), Is.EqualTo(1));
#endregion
}
class DateAndTime1
{
public int Id { get; set; }
public DateTime DateTime { get; set; }
public DateOnly DateOnly { get; set; }
public TimeOnly TimeOnly { get; set; }
}
[Test]
public async Task Send_DateOnly_TimeOnly_via_DynamicParameters_Set_DbType_Get_Row_DateTime_Properties()
{
#region Arrange
using var sqlConnection = new SqlConnection("SomeConnectionString");
sqlConnection.Open();
string createDateAndTimeTable = @"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'U' AND name = 'DateAndTime')
CREATE TABLE [DateAndTime] (
[Id] [INT] IDENTITY(1,1) NOT NULL,
[DateTime] [DATETIME] NOT NULL,
[DateOnly] [DATE] NOT NULL,
[TimeOnly] [TIME] NOT NULL,
CONSTRAINT [PK_DateAndTime] PRIMARY KEY CLUSTERED
( [Id] ASC )
)";
var command = new SqlCommand(createDateAndTimeTable, sqlConnection);
command.ExecuteNonQuery();
command.Dispose();
var dateTime = new DateTime(2001, 2, 3, 4, 5, 6);
var dateOnly = new DateOnly(2002, 3, 4);
var timeOnly = new TimeOnly(7, 8, 9);
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", dateTime);
dynamicParameters.Add("p1", dateOnly, System.Data.DbType.Date);
dynamicParameters.Add("p2", timeOnly, System.Data.DbType.Time);
#endregion
#region Act
string query = "" +
"INSERT INTO [DateAndTime] ([DateTime], [DateOnly], [TimeOnly]) " +
"VALUES (@p0, @p1, @p2); " +
"SELECT CAST(SCOPE_IDENTITY() AS BIGINT)";
// Here it works!
object id = await sqlConnection.ExecuteScalarAsync(query, dynamicParameters);
// Now, we are gonna get that row.
dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", (long)id);
query = "SELECT * FROM [DateAndTime] WHERE [Id] = @p0";
// Here it fails with the following error (using class "DateAndTime2" as T, declared below):
// System.Data.DataException : Error parsing column 3 (TimeOnly=07:08:09 - Object)
// Inner Exception: System.InvalidCastException : Object must implement IConvertible.
var dateAndTimes = await sqlConnection.QueryAsync<DateAndTime2>(query, dynamicParameters);
#endregion
#region Assert
Assert.That(dateAndTimes.Count(), Is.EqualTo(1));
#endregion
}
class DateAndTime2
{
public int Id { get; set; }
public DateTime DateTime { get; set; }
public DateTime DateOnly { get; set; }
public DateTime TimeOnly { get; set; }
}
}
} |
|
Can you try 2.1.37? This is "unlisted" on NuGet because of an unrelated
issue, but: it is 2.1.375 that adds date/time-only support.
…On Sun, 17 Mar 2024, 15:37 rlrecalde, ***@***.***> wrote:
- Dapper: v2.1.35
- .NET: 6.0
- Database providers: I tested the following (for DbConnection object):
- MsSql: Microsoft.Data.SqlClient v5.2.0
- MySql: MySqlConnector v2.2.7
- PostgreSql: Npgsql v6.0.10
Example:
using Dapper;using Microsoft.Data.SqlClient;
namespace Whatever{
public class MyExampleTest
{
[Test]
public async Task Send_DateOnly_TimeOnly_via_Dictionary()
{
#region Arrange
using var sqlConnection = new SqlConnection("SomeConnectionString");
sqlConnection.Open();
string createDateAndTimeTable = @" IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'U' AND name = 'DateAndTime') CREATE TABLE [DateAndTime] ( [Id] [INT] IDENTITY(1,1) NOT NULL, [DateTime] [DATETIME] NOT NULL, [DateOnly] [DATE] NOT NULL, [TimeOnly] [TIME] NOT NULL, CONSTRAINT [PK_DateAndTime] PRIMARY KEY CLUSTERED ( [Id] ASC ) )";
var command = new SqlCommand(createDateAndTimeTable, sqlConnection);
command.ExecuteNonQuery();
command.Dispose();
var dateTime = new DateTime(2001, 2, 3, 4, 5, 6);
var dateOnly = new DateOnly(2002, 3, 4);
var timeOnly = new TimeOnly(7, 8, 9);
var parameters = new Dictionary<string, object>
{
{ "p0", dateTime },
{ "p1", dateOnly },
{ "p2", timeOnly },
};
#endregion
#region Act
string query = "" +
"INSERT INTO [DateAndTime] ([DateTime], [DateOnly], [TimeOnly]) " +
"VALUES ***@***.***, @p1, @p2); " +
"SELECT CAST(SCOPE_IDENTITY() AS BIGINT)";
// Here it fails with the following error:
// System.NotSupportedException : The member p1 of type System.DateOnly cannot be used as a parameter value.
object id = await sqlConnection.ExecuteScalarAsync(query, parameters);
#endregion
#region Assert
Assert.That((long)id, Is.GreaterThan(0));
#endregion
}
[Test]
public async Task Send_DateOnly_TimeOnly_via_DynamicParameters()
{
#region Arrange
using var sqlConnection = new SqlConnection("SomeConnectionString");
sqlConnection.Open();
string createDateAndTimeTable = @" IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'U' AND name = 'DateAndTime') CREATE TABLE [DateAndTime] ( [Id] [INT] IDENTITY(1,1) NOT NULL, [DateTime] [DATETIME] NOT NULL, [DateOnly] [DATE] NOT NULL, [TimeOnly] [TIME] NOT NULL, CONSTRAINT [PK_DateAndTime] PRIMARY KEY CLUSTERED ( [Id] ASC ) )";
var command = new SqlCommand(createDateAndTimeTable, sqlConnection);
command.ExecuteNonQuery();
command.Dispose();
var dateTime = new DateTime(2001, 2, 3, 4, 5, 6);
var dateOnly = new DateOnly(2002, 3, 4);
var timeOnly = new TimeOnly(7, 8, 9);
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", dateTime);
dynamicParameters.Add("p1", dateOnly);
dynamicParameters.Add("p2", timeOnly);
#endregion
#region Act
string query = "" +
"INSERT INTO [DateAndTime] ([DateTime], [DateOnly], [TimeOnly]) " +
"VALUES ***@***.***, @p1, @p2); " +
"SELECT CAST(SCOPE_IDENTITY() AS BIGINT)";
// Here it fails with the following error:
// System.NotSupportedException : The member p1 of type System.DateOnly cannot be used as a parameter value.
object id = await sqlConnection.ExecuteScalarAsync(query, dynamicParameters);
#endregion
#region Assert
Assert.That((long)id, Is.GreaterThan(0));
#endregion
}
[Test]
public async Task Send_DateOnly_TimeOnly_via_DynamicParameters_Set_DbType_Get_Row()
{
#region Arrange
using var sqlConnection = new SqlConnection("SomeConnectionString");
sqlConnection.Open();
string createDateAndTimeTable = @" IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'U' AND name = 'DateAndTime') CREATE TABLE [DateAndTime] ( [Id] [INT] IDENTITY(1,1) NOT NULL, [DateTime] [DATETIME] NOT NULL, [DateOnly] [DATE] NOT NULL, [TimeOnly] [TIME] NOT NULL, CONSTRAINT [PK_DateAndTime] PRIMARY KEY CLUSTERED ( [Id] ASC ) )";
var command = new SqlCommand(createDateAndTimeTable, sqlConnection);
command.ExecuteNonQuery();
command.Dispose();
var dateTime = new DateTime(2001, 2, 3, 4, 5, 6);
var dateOnly = new DateOnly(2002, 3, 4);
var timeOnly = new TimeOnly(7, 8, 9);
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", dateTime);
dynamicParameters.Add("p1", dateOnly, System.Data.DbType.Date); // <--
dynamicParameters.Add("p2", timeOnly, System.Data.DbType.Time); // <--
#endregion
#region Act
string query = "" +
"INSERT INTO [DateAndTime] ([DateTime], [DateOnly], [TimeOnly]) " +
"VALUES ***@***.***, @p1, @p2); " +
"SELECT CAST(SCOPE_IDENTITY() AS BIGINT)";
// Here it works!
object id = await sqlConnection.ExecuteScalarAsync(query, dynamicParameters);
// Now, we are gonna get that row.
dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", (long)id);
query = "SELECT * FROM [DateAndTime] WHERE [Id] = @p0";
// Here it fails with the following error (using class "DateAndTime1" as T, declared below):
// System.Data.DataException : Error parsing column 2 (DateOnly=3/4/2002 12:00:00 AM - DateTime)
// Inner Exception: System.InvalidCastException : Invalid cast from 'System.DateTime' to 'System.DateOnly'.
var dateAndTimes = await sqlConnection.QueryAsync<DateAndTime1>(query, dynamicParameters);
#endregion
#region Assert
Assert.That(dateAndTimes.Count(), Is.EqualTo(1));
#endregion
}
class DateAndTime1
{
public int Id { get; set; }
public DateTime DateTime { get; set; }
public DateOnly DateOnly { get; set; }
public TimeOnly TimeOnly { get; set; }
}
[Test]
public async Task Send_DateOnly_TimeOnly_via_DynamicParameters_Set_DbType_Get_Row_DateTime_Properties()
{
#region Arrange
using var sqlConnection = new SqlConnection("SomeConnectionString");
sqlConnection.Open();
string createDateAndTimeTable = @" IF NOT EXISTS (SELECT * FROM sys.objects WHERE [type] = 'U' AND name = 'DateAndTime') CREATE TABLE [DateAndTime] ( [Id] [INT] IDENTITY(1,1) NOT NULL, [DateTime] [DATETIME] NOT NULL, [DateOnly] [DATE] NOT NULL, [TimeOnly] [TIME] NOT NULL, CONSTRAINT [PK_DateAndTime] PRIMARY KEY CLUSTERED ( [Id] ASC ) )";
var command = new SqlCommand(createDateAndTimeTable, sqlConnection);
command.ExecuteNonQuery();
command.Dispose();
var dateTime = new DateTime(2001, 2, 3, 4, 5, 6);
var dateOnly = new DateOnly(2002, 3, 4);
var timeOnly = new TimeOnly(7, 8, 9);
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", dateTime);
dynamicParameters.Add("p1", dateOnly, System.Data.DbType.Date);
dynamicParameters.Add("p2", timeOnly, System.Data.DbType.Time);
#endregion
#region Act
string query = "" +
"INSERT INTO [DateAndTime] ([DateTime], [DateOnly], [TimeOnly]) " +
"VALUES ***@***.***, @p1, @p2); " +
"SELECT CAST(SCOPE_IDENTITY() AS BIGINT)";
// Here it works!
object id = await sqlConnection.ExecuteScalarAsync(query, dynamicParameters);
// Now, we are gonna get that row.
dynamicParameters = new DynamicParameters();
dynamicParameters.Add("p0", (long)id);
query = "SELECT * FROM [DateAndTime] WHERE [Id] = @p0";
// Here it fails with the following error (using class "DateAndTime2" as T, declared below):
// System.Data.DataException : Error parsing column 3 (TimeOnly=07:08:09 - Object)
// Inner Exception: System.InvalidCastException : Object must implement IConvertible.
var dateAndTimes = await sqlConnection.QueryAsync<DateAndTime2>(query, dynamicParameters);
#endregion
#region Assert
Assert.That(dateAndTimes.Count(), Is.EqualTo(1));
#endregion
}
class DateAndTime2
{
public int Id { get; set; }
public DateTime DateTime { get; set; }
public DateTime DateOnly { get; set; }
public DateTime TimeOnly { get; set; }
}
}}
—
Reply to this email directly, view it on GitHub
<#2057 (comment)>
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AAAEHMBJTRPOLFS6YBOTZQLYYW2CFBFKMF2HI4TJMJ2XIZLTSOBKK5TBNR2WLJDUOJ2WLJDOMFWWLO3UNBZGKYLEL5YGC4TUNFRWS4DBNZ2F6YLDORUXM2LUPGBKK5TBNR2WLJDUOJ2WLJDOMFWWLLTXMF2GG2C7MFRXI2LWNF2HTAVFOZQWY5LFUVUXG43VMWSG4YLNMWVXI2DSMVQWIX3UPFYGLLDTOVRGUZLDORPXI6LQMWWES43TOVSUG33NNVSW45FGORXXA2LDOOJIFJDUPFYGLKTSMVYG643JORXXE6NFOZQWY5LFU4YTMMJTGM2DLAVEOR4XAZNFNFZXG5LFUV3GC3DVMWVDEMJYHEZDKMJZGY42O5DSNFTWOZLSUZRXEZLBORSQ>
.
You are receiving this email because you commented on the thread.
Triage notifications on the go with GitHub Mobile for iOS
<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675>
or Android
<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>
.
|
|
Hi, Marc. I've downloaded release version 2.1.37. So, I've created my own test class and put those four tests I've sent you before. Test #1 (sending a Dictionary<string, object> with DateOnly and TimeOnly values)It failed. Test #2 (sending a DynamicParameters with no DbType specified)It worked! Test #3 (getting the row into a class with DateOnly and TimeOnly properties)It worked! Test #4 (getting the row into a class with DateTime and TimeSpan properties for the 'Date' and 'Time' columns)It worked! Therefore, as long as I work with DynamicParameters instead of Dictionary<string, object> for sending parameters and correctly declare DateOnly and TimeOnly properties for 'Date' and 'Time' columns for getting values from the DB, I will be able to work properly with this new 2.1.37 version. Thank you very much! |
|
Hi, I can see this release reverts the unrelated breaking change in 2.1.37, so I was expecting to have DataOnly support but it errors with "InvalidCastException: Unable to cast object of type 'System.DateTime' to type 'System.DateOnly'." The column in the database is "Date" on MS SQL 2019 and I using .Net 8 |
|
The DateTime change should still be there. Which client are you using? System.Data.SqlClient or Microsoft.Data.SqlClient? |
|
Hi, Thanks for the help |
|
It cannot work without the client provider also supporting the feature, which the legacy one: does not . |
|
Hi, Marc. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I tried to use DateOnly and TimeOnly, and this is what happend:
When I sent a Dictionary<string, object> object (as well as a DynamicParameters with no DbType's specified) to the 'param' parameter for an Insert statement, dapper couldn't map those values because of the "[typeof(DateOnly)] = TypeMapEntry.DoNotSetFieldValue" entry on 'typeMap' variable at 'SqlMapper.cs'.
So, I tried with a DynamicParameters with DbType's specified (DbType.Date and DbType.Time, respectively) and it worked.
Then, I wanted to query that row ('date' column and 'time' column) and make dapper to map them to my DateOnly and TimeOnly properties (of my class T from query), and it failed.
The error message: "Error parsing column 2 (dateonly=3/4/2002 12:00:00 AM - DateTime)".
The exception:
at Dapper.SqlMapper.ThrowDataException(...)
at Dapper.SqlMapper.ReadRow(...)
So, I changed those properties to DateTime, expecting to get at least date value and time value on them.
For the date value from the 'date' column, it worked.
For the time value from the 'time' column, it didn't.
The error message: "Error parsing column 3 (TimeOnly=07:08:09 - Object)"
The exception:
at Dapper.SqlMapper.ThrowDataException(...)
at Dapper.SqlMapper.ReadRow(...)
So, there is no way to get values from a Time column.
The text was updated successfully, but these errors were encountered: