Support of datetime2

Support of datetime2

Postby 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
antp
 
Posts: 3
Joined: Tue 08 Apr 2014 14:00

Re: Support of datetime2

Postby 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"));
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Support of datetime2

Postby 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.
antp
 
Posts: 3
Joined: Tue 08 Apr 2014 14:00

Re: Support of datetime2

Postby 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: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Support of datetime2

Postby 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/universal/download.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
Pinturiccio
Devart Team
 
Posts: 1983
Joined: Wed 02 Nov 2011 09:44

Re: Support of datetime2

Postby 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 :)
antp
 
Posts: 3
Joined: Tue 08 Apr 2014 14:00


Return to dotConnect Universal