Support of datetime2

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for universal data access
Post Reply
antp
Posts: 3
Joined: Tue 08 Apr 2014 14:00

Support of datetime2

Post by antp » Tue 08 Apr 2014 14:08

Hi,
As we needed a millisecond precision we switched from SQL Server's datetime datatype to datetime2.
I found that when using .NET's DB components I would have to specify the type System.Data.SqlDbType.DateTime2 for the parameters, but I do not see how I can do it with dotConnect, as the only datetime type is UniDbType.DateTime (mapped to SQL Server's datetime type, as specified in the documentation).
When testing it seems that indeed dotConnect Universal is still using datetime internally, as I do not achieve a millisecond-precision, but only the precision to 3 milliseconds of the standard datetime.
Is there a way to go around that problem?
Or would it require a fix in dotConnect?
I tested with the latest Pro version, 3.50.622, and we work with Visual Studio 2008 & .NET 3.5

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Support of datetime2

Post by Pinturiccio » Fri 11 Apr 2014 12:52

You are right, currently there is no UniDbType that is mapped to the datetime2 type on SQL Server. We will investigate the possibility to create UniDbType that will be mapped to the SQL Server datetime2 type and post here about the results as soon as possible.

As a temporary workaround, don't specify UniDbType for the parameter, instead assign DbType.DateTime2 to the DbType property.

Here is a small sample of working with datetime2:
DDL script:

Code: Select all

CREATE TABLE dbo.DT (
  id int IDENTITY,
  val datetime2 NULL,
  CONSTRAINT PK_DT PRIMARY KEY (id)
)
C# code:

Code: Select all

UniConnection conn = new UniConnection("your connection string");
conn.Open();
UniCommand comm = new UniCommand("INSERT INTO DT(val) VALUES (@val)", conn);
UniParameter par = new UniParameter();
par.ParameterName = "val";
par.DbType = DbType.DateTime2;

par.Value = "12-10-25 12:32:10.1234567";
comm.Parameters.Add(par);
comm.ExecuteNonQuery();

comm.CommandText = "select val from DT where ID=1";
comm.Parameters.Clear();
var reader = comm.ExecuteReader();
reader.Read();
DateTime d = reader.GetDateTime(0);
Console.WriteLine(d.ToString("dd.mm.yyyy hh.mm.ss.fffffff"));

antp
Posts: 3
Joined: Tue 08 Apr 2014 14:00

Re: Support of datetime2

Post by antp » Wed 16 Apr 2014 11:09

Thanks!
It seems to work fine by creating the parameter that way (and converting .NET's DateTime to a string when assigning it to the Value member of the parameter).
I'll use this workaround until it is implemented as a proper type then.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Support of datetime2

Post by Pinturiccio » Thu 17 Apr 2014 12:32

We have added the DateTime2 member to the UniDbType enumeration. We will post here when the corresponding build of dotConnect Universal is available for download.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: Support of datetime2

Post by Pinturiccio » Thu 24 Apr 2014 16:33

New build of dotConnect Universal 3.50.643 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/univer ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?t=29431

antp
Posts: 3
Joined: Tue 08 Apr 2014 14:00

Re: Support of datetime2

Post by antp » Wed 13 Aug 2014 09:33

Hello,
Thanks, I confirm that the new release works fine with datetime2 when setting the parameter type to UniDbType.DateTime2 (tested with 3.50.712)
Sorry for the long delay for replying, but I couldn't upgrade the "old" 3.20 version I was using until now (I was still using the workaround you gave) because we were using the same version in several builds; but now I was forced to upgrade due to unsupported features for Oracle in 3.20, which seem to work fine in 3.50 :)

Post Reply