Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit226bdb8

Browse files
authored
Fix | Fix DateTimeOffset size in TdsValueSetter.cs class file. (#2453)
1 parentaefd723 commit226bdb8

File tree

3 files changed

+171
-3
lines changed

3 files changed

+171
-3
lines changed

‎src/Microsoft.Data.SqlClient/src/Microsoft/Data/SqlClient/TdsValueSetter.cs

Lines changed: 27 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -697,10 +697,34 @@ internal void SetDateTimeOffset(DateTimeOffset value)
697697
shortoffset=(short)value.Offset.TotalMinutes;
698698

699699
#ifNETCOREAPP
700-
Span<byte>result=stackallocbyte[9];
700+
// In TDS protocol:
701+
// https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/786f5b8a-f87d-4980-9070-b9b7274c681d
702+
//
703+
// date is represented as one 3 - byte unsigned integer that represents the number of days since January 1, year 1.
704+
//
705+
// time(n) is represented as one unsigned integer that represents the number of 10^-n,
706+
// (10 to the power of negative n), second increments since 12 AM within a day.
707+
// The length, in bytes, of that integer depends on the scale n as follows:
708+
// 3 bytes if 0 <= n < = 2.
709+
// 4 bytes if 3 <= n < = 4.
710+
// 5 bytes if 5 <= n < = 7.
711+
// For example:
712+
// DateTimeOffset dateTimeOffset = new DateTimeOffset(2024, 1, 1, 23, 59, 59, TimeSpan.Zero); // using scale of 0
713+
// time = 23:59:59, scale is 1, is represented as 863990 in 3 bytes or { 246, 46, 13, 0, 0, 0, 0, 0 } in bytes array
714+
715+
Span<byte>result=stackallocbyte[8];
716+
717+
// https://learn.microsoft.com/en-us/dotnet/api/system.buffers.binary.binaryprimitives.writeint64bigendian?view=net-8.0
718+
// WriteInt64LittleEndian requires 8 bytes to write the value.
701719
BinaryPrimitives.WriteInt64LittleEndian(result,time);
702-
BinaryPrimitives.WriteInt32LittleEndian(result.Slice(5),days);
703-
_stateObj.WriteByteSpan(result.Slice(0,8));
720+
// The DateTimeOffset length is variable depending on the scale, 1 to 7, used.
721+
// If length = 8, 8 - 5 = 3 bytes is used for time.
722+
// If length = 10, 10 - 5 = 5 bytes is used for time.
723+
_stateObj.WriteByteSpan(result.Slice(0,length-5));// this writes the time value to the state object using dynamic length based on the scale.
724+
725+
// Date is represented as 3 bytes. So, 3 bytes are written to the state object.
726+
BinaryPrimitives.WriteInt32LittleEndian(result,days);
727+
_stateObj.WriteByteSpan(result.Slice(0,3));
704728
#else
705729
_stateObj.WriteByteArray(BitConverter.GetBytes(time),length-5,0);// time
706730
_stateObj.WriteByteArray(BitConverter.GetBytes(days),3,0);// date

‎src/Microsoft.Data.SqlClient/tests/ManualTests/Microsoft.Data.SqlClient.ManualTesting.Tests.csproj

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -204,6 +204,7 @@
204204
<CompileInclude="SQL\TransactionTest\TransactionEnlistmentTest.cs" />
205205
<CompileInclude="SQL\UdtTest\SqlServerTypesTest.cs" />
206206
<CompileInclude="SQL\UdtTest\UdtBulkCopyTest.cs" />
207+
<CompileInclude="SQL\UdtTest\UdtDateTimeOffsetTest.cs" />
207208
<CompileInclude="SQL\UdtTest\UdtTest.cs" />
208209
<CompileInclude="SQL\UdtTest\UdtTest2.cs" />
209210
<CompileInclude="SQL\UdtTest\UdtTestHelpers.cs" />
Lines changed: 143 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,143 @@
1+
// Licensed to the .NET Foundation under one or more agreements.
2+
// The .NET Foundation licenses this file to you under the MIT license.
3+
// See the LICENSE file in the project root for more information.
4+
5+
usingSystem;
6+
usingSystem.Data;
7+
usingMicrosoft.Data.SqlClient.Server;
8+
usingXunit;
9+
10+
namespaceMicrosoft.Data.SqlClient.ManualTesting.Tests
11+
{
12+
publicclassDateTimeOffsetList:SqlDataRecord
13+
{
14+
publicDateTimeOffsetList(DateTimeOffsetdateTimeOffset)
15+
:base(newSqlMetaData("dateTimeOffset",SqlDbType.DateTimeOffset,0,1))// this is using scale 1
16+
{
17+
this.SetValues(dateTimeOffset);
18+
}
19+
}
20+
21+
publicclassDateTimeOffsetVariableScale:SqlDataRecord
22+
{
23+
publicDateTimeOffsetVariableScale(DateTimeOffsetdateTimeOffset,intscale)
24+
:base(newSqlMetaData("dateTimeOffset",SqlDbType.DateTimeOffset,0,(byte)scale))// this is using variable scale
25+
{
26+
this.SetValues(dateTimeOffset);
27+
}
28+
}
29+
30+
publicclassUdtDateTimeOffsetTest
31+
{
32+
privatereadonlystring_connectionString=null;
33+
privatereadonlystring_udtTableType=DataTestUtility.GetUniqueNameForSqlServer("DataTimeOffsetTableType");
34+
35+
publicUdtDateTimeOffsetTest()
36+
{
37+
_connectionString=DataTestUtility.TCPConnectionString;
38+
}
39+
40+
// This unit test is for the reported issue #2423 using a specific scale of 1
41+
[ConditionalFact(typeof(DataTestUtility),nameof(DataTestUtility.AreConnStringsSetup),nameof(DataTestUtility.IsNotAzureServer),nameof(DataTestUtility.IsNotAzureSynapse))]
42+
publicvoidSelectFromSqlParameterShouldSucceed()
43+
{
44+
usingSqlConnectionconnection=new(_connectionString);
45+
connection.Open();
46+
SetupUserDefinedTableType(connection,_udtTableType);
47+
48+
try
49+
{
50+
DateTimeOffsetdateTimeOffset=newDateTimeOffset(2024,1,1,23,59,59,500,TimeSpan.Zero);
51+
varparam=newSqlParameter
52+
{
53+
ParameterName="@params",
54+
SqlDbType=SqlDbType.Structured,
55+
TypeName=$"dbo.{_udtTableType}",
56+
Value=newDateTimeOffsetList[]{newDateTimeOffsetList(dateTimeOffset)}
57+
};
58+
59+
using(varcmd=connection.CreateCommand())
60+
{
61+
cmd.CommandText="SELECT * FROM @params";
62+
cmd.Parameters.Add(param);
63+
varresult=cmd.ExecuteScalar();
64+
Assert.Equal(dateTimeOffset,result);
65+
}
66+
}
67+
finally
68+
{
69+
DataTestUtility.DropUserDefinedType(connection,_udtTableType);
70+
}
71+
}
72+
73+
// This unit test is to ensure that time in DateTimeOffset with all scales are working as expected
74+
[ConditionalFact(typeof(DataTestUtility),nameof(DataTestUtility.AreConnStringsSetup),nameof(DataTestUtility.IsNotAzureServer),nameof(DataTestUtility.IsNotAzureSynapse))]
75+
publicvoidDateTimeOffsetAllScalesTestShouldSucceed()
76+
{
77+
stringtvpTypeName=DataTestUtility.GetUniqueNameForSqlServer("tvpType");
78+
79+
usingSqlConnectionconnection=new(_connectionString);
80+
connection.Open();
81+
82+
try
83+
{
84+
// Use different scale for each test: 0 to 7
85+
intfromScale=0;
86+
inttoScale=7;
87+
88+
for(intscale=fromScale;scale<=toScale;scale++)
89+
{
90+
DateTimeOffsetdateTimeOffset=newDateTimeOffset(2024,1,1,23,59,59,TimeSpan.Zero);
91+
92+
// Add sub-second offset corresponding to the scale being tested
93+
TimeSpansubSeconds=TimeSpan.FromTicks((long)(TimeSpan.TicksPerSecond/Math.Pow(10,scale)));
94+
dateTimeOffset=dateTimeOffset.Add(subSeconds);
95+
96+
DataTestUtility.DropUserDefinedType(connection,tvpTypeName);
97+
SetupDateTimeOffsetTableType(connection,tvpTypeName,scale);
98+
99+
varparam=newSqlParameter
100+
{
101+
ParameterName="@params",
102+
SqlDbType=SqlDbType.Structured,
103+
Scale=(byte)scale,
104+
TypeName=$"dbo.{tvpTypeName}",
105+
Value=newDateTimeOffsetVariableScale[]{newDateTimeOffsetVariableScale(dateTimeOffset,scale)}
106+
};
107+
108+
using(varcmd=connection.CreateCommand())
109+
{
110+
cmd.CommandText="SELECT * FROM @params";
111+
cmd.Parameters.Add(param);
112+
varresult=cmd.ExecuteScalar();
113+
Assert.Equal(dateTimeOffset,result);
114+
}
115+
}
116+
}
117+
finally
118+
{
119+
DataTestUtility.DropUserDefinedType(connection,tvpTypeName);
120+
}
121+
}
122+
123+
privatestaticvoidSetupUserDefinedTableType(SqlConnectionconnection,stringtableTypeName)
124+
{
125+
using(SqlCommandcmd=connection.CreateCommand())
126+
{
127+
cmd.CommandType=CommandType.Text;
128+
cmd.CommandText=$"CREATE TYPE{tableTypeName} AS TABLE ([Value] DATETIMEOFFSET(1) NOT NULL) ";
129+
cmd.ExecuteNonQuery();
130+
}
131+
}
132+
133+
privatestaticvoidSetupDateTimeOffsetTableType(SqlConnectionconnection,stringtableTypeName,intscale)
134+
{
135+
using(SqlCommandcmd=connection.CreateCommand())
136+
{
137+
cmd.CommandType=CommandType.Text;
138+
cmd.CommandText=$"CREATE TYPE{tableTypeName} AS TABLE ([Value] DATETIMEOFFSET({scale}) NOT NULL) ";
139+
cmd.ExecuteNonQuery();
140+
}
141+
}
142+
}
143+
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp