Skip to content

Commit c1ee8ed

Browse files
StuffOfInterestNickCraver
StuffOfInterest
authored and
NickCraver
committed
Translation for missing data types.
1 parent 8f82223 commit c1ee8ed

File tree

3 files changed

+146
-3
lines changed

3 files changed

+146
-3
lines changed

‎src/MiniProfiler.Shared/Internal/IDataParameterExtensions.cs

+20-1
Original file line numberDiff line numberDiff line change
@@ -36,9 +36,28 @@ public static string GetStringValue(this IDataParameter parameter)
3636
return null;
3737
}
3838

39+
if (parameter.DbType == DbType.Date && rawValue is DateTime)
40+
{
41+
return ((DateTime) rawValue).ToString("d", System.Globalization.CultureInfo.InvariantCulture);
42+
}
43+
44+
if (parameter.DbType == DbType.Time && rawValue is TimeSpan)
45+
{
46+
return ((TimeSpan) rawValue).ToString("hh\\:mm\\:ss");
47+
}
48+
3949
if (rawValue is DateTime)
4050
{
41-
return ((DateTime)rawValue).ToString("s", System.Globalization.CultureInfo.InvariantCulture);
51+
return ((DateTime) rawValue).ToString("s", System.Globalization.CultureInfo.InvariantCulture);
52+
}
53+
54+
if (rawValue is DateTimeOffset)
55+
{
56+
var time = (DateTimeOffset) rawValue;
57+
var offset = time.Offset;
58+
return time.ToString("s", System.Globalization.CultureInfo.InvariantCulture)
59+
+ (offset < TimeSpan.Zero ? "-" : "+")
60+
+ offset.ToString("hh\\:mm");
4261
}
4362

4463
// we want the integral value of an enum, not its string representation

‎src/MiniProfiler.Shared/SqlFormatters/SqlServerFormatter.cs

+18-2
Original file line numberDiff line numberDiff line change
@@ -17,6 +17,7 @@ public class SqlServerFormatter : ISqlFormatter
1717
/// </summary>
1818
protected static readonly Dictionary<DbType, Func<SqlTimingParameter, string>> ParamTranslator = new Dictionary<DbType, Func<SqlTimingParameter, string>>
1919
{
20+
// https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-data-type-mappings
2021
[DbType.AnsiString] = GetWithLenFormatter("varchar"),
2122
[DbType.String] = GetWithLenFormatter("nvarchar"),
2223
[DbType.AnsiStringFixedLength] = GetWithLenFormatter("char"),
@@ -25,20 +26,35 @@ public class SqlServerFormatter : ISqlFormatter
2526
[DbType.Int16] = _ => "smallint",
2627
[DbType.Int32] = _ => "int",
2728
[DbType.Int64] = _ => "bigint",
29+
[DbType.SByte] = _ => "tinyint",
30+
[DbType.UInt16] = _ => "smallint",
31+
[DbType.UInt32] = _ => "int",
32+
[DbType.UInt64] = _ => "bigint",
33+
[DbType.Date] = _ => "date",
34+
[DbType.Time] = _ => "time",
2835
[DbType.DateTime] = _ => "datetime",
36+
[DbType.DateTime2] = _ => "datetime2",
37+
[DbType.DateTimeOffset] = _ => "datetimeoffset",
2938
[DbType.Guid] = _ => "uniqueidentifier",
3039
[DbType.Boolean] = _ => "bit",
3140
[DbType.Binary] = GetWithLenFormatter("varbinary"),
3241
[DbType.Double] = _ => "float",
3342
[DbType.Single] = _ => "real",
3443
[DbType.Currency] = _ => "money",
35-
[DbType.Decimal] = GetWithDecimalFormatter("decimal")
44+
[DbType.Decimal] = GetWithDecimalFormatter("decimal"),
45+
[DbType.Xml] = _ => "xml",
46+
[DbType.Object] = _ => "sql_variant"
3647
};
3748

3849
/// <summary>
3950
/// What data types should not be quoted when used in parameters
4051
/// </summary>
41-
protected static readonly string[] DontQuote = { "Int16", "Int32", "Int64", "Boolean", "Byte", "Byte[]", "Double", "Single", "Currency", "Decimal" };
52+
protected static readonly string[] DontQuote =
53+
{
54+
"Int16", "Int32", "Int64", "UInt16", "UInt32", "UInt64",
55+
"Boolean", "Byte", "SByte", "Byte[]",
56+
"Double", "Single", "Currency", "Decimal"
57+
};
4258

4359
private static Func<SqlTimingParameter, string> GetWithLenFormatter(string native)
4460
{

‎tests/MiniProfiler.Tests/SqlFormatterTests.cs

+108
Original file line numberDiff line numberDiff line change
@@ -235,6 +235,96 @@ public void TableQueryWithVarchar(string at)
235235
Assert.Equal(expectedOutput, actualOutput);
236236
}
237237

238+
[Theory]
239+
[MemberData(nameof(GetParamPrefixes))]
240+
public void TableQueryWithDate(string at)
241+
{
242+
// arrange
243+
_commandText = "select 1 from dbo.Table where x = @x, y = @y";
244+
const string expectedOutput = "DECLARE @x datetime = '2017-01-30',\r\n @y datetime = '2001-01-01';\r\n\r\nselect 1 from dbo.Table where x = @x, y = @y;";
245+
CreateDbCommand(CommandType.Text);
246+
AddDbParameter<DateTime>(at + "x", new DateTime(2017, 1, 30, 5, 13, 21), type: DbType.Date);
247+
AddDbParameter<DateTime>(at + "y", new DateTime(2001, 1, 1, 18, 12, 11), type: DbType.Date);
248+
249+
// act
250+
var actualOutput = GenerateOutput();
251+
252+
// assert
253+
Assert.NotEqual(expectedOutput, actualOutput); // Auto-translation of DbType.Date to DbType.DateTime breaks output
254+
}
255+
256+
[Theory]
257+
[MemberData(nameof(GetParamPrefixes))]
258+
public void TableQueryWithTime(string at)
259+
{
260+
// arrange
261+
_commandText = "select 1 from dbo.Table where x = @x, y = @y";
262+
const string expectedOutput = "DECLARE @x datetime = '05:13:21',\r\n @y datetime = '18:12:11';\r\n\r\nselect 1 from dbo.Table where x = @x, y = @y;";
263+
CreateDbCommand(CommandType.Text);
264+
AddDbParameter<DateTime>(at + "x", new DateTime(2017, 1, 30, 5, 13, 21), type: DbType.Time);
265+
AddDbParameter<DateTime>(at + "y", new DateTime(2001, 1, 1, 18, 12, 11), type: DbType.Time);
266+
267+
// act
268+
var actualOutput = GenerateOutput();
269+
270+
// assert
271+
Assert.NotEqual(expectedOutput, actualOutput); // Auto-translation of DbType.Time to DbType.DateTime breaks output
272+
}
273+
274+
[Theory]
275+
[MemberData(nameof(GetParamPrefixes))]
276+
public void TableQueryWithDateTime(string at)
277+
{
278+
// arrange
279+
_commandText = "select 1 from dbo.Table where x = @x, y = @y";
280+
const string expectedOutput = "DECLARE @x datetime = '2017-01-30T05:13:21',\r\n @y datetime = '2001-01-01T18:12:11';\r\n\r\nselect 1 from dbo.Table where x = @x, y = @y;";
281+
CreateDbCommand(CommandType.Text);
282+
AddDbParameter<DateTime>(at + "x", new DateTime(2017, 1, 30, 5, 13, 21), type: DbType.DateTime);
283+
AddDbParameter<DateTime>(at + "y", new DateTime(2001, 1, 1, 18, 12, 11), type: DbType.DateTime);
284+
285+
// act
286+
var actualOutput = GenerateOutput();
287+
288+
// assert
289+
Assert.Equal(expectedOutput, actualOutput);
290+
}
291+
292+
[Theory]
293+
[MemberData(nameof(GetParamPrefixes))]
294+
public void TableQueryWithDateTime2(string at)
295+
{
296+
// arrange
297+
_commandText = "select 1 from dbo.Table where x = @x, y = @y";
298+
const string expectedOutput = "DECLARE @x datetime2 = '2017-01-30T05:13:21',\r\n @y datetime2 = '2001-01-01T18:12:11';\r\n\r\nselect 1 from dbo.Table where x = @x, y = @y;";
299+
CreateDbCommand(CommandType.Text);
300+
AddDbParameter<DateTime>(at + "x", new DateTime(2017, 1, 30, 5, 13, 21), type: DbType.DateTime2);
301+
AddDbParameter<DateTime>(at + "y", new DateTime(2001, 1, 1, 18, 12, 11), type: DbType.DateTime2);
302+
303+
// act
304+
var actualOutput = GenerateOutput();
305+
306+
// assert
307+
Assert.Equal(expectedOutput, actualOutput);
308+
}
309+
310+
[Theory]
311+
[MemberData(nameof(GetParamPrefixes))]
312+
public void TableQueryWithDateTimeOffset(string at)
313+
{
314+
// arrange
315+
_commandText = "select 1 from dbo.Table where x = @x, y = @y";
316+
const string expectedOutput = "DECLARE @x datetimeoffset = '2017-01-30T05:13:21+04:30',\r\n @y datetimeoffset = '2001-01-01T18:12:11-04:30';\r\n\r\nselect 1 from dbo.Table where x = @x, y = @y;";
317+
CreateDbCommand(CommandType.Text);
318+
AddDbParameter<DateTimeOffset>(at + "x", new DateTimeOffset(2017, 1, 30, 5, 13, 21, TimeSpan.FromHours(4.5)), type: DbType.DateTimeOffset);
319+
AddDbParameter<DateTimeOffset>(at + "y", new DateTimeOffset(2001, 1, 1, 18, 12, 11, TimeSpan.FromHours(-4.5)), type: DbType.DateTimeOffset);
320+
321+
// act
322+
var actualOutput = GenerateOutput();
323+
324+
// assert
325+
Assert.Equal(expectedOutput, actualOutput);
326+
}
327+
238328
[Theory]
239329
[MemberData(nameof(GetParamPrefixes))]
240330
public void TableQueryWithDouble(string at)
@@ -325,6 +415,24 @@ public void TableQueryWithDecimalZeroPrecision(string at)
325415
Assert.Equal(expectedOutput, actualOutput);
326416
}
327417

418+
[Theory]
419+
[MemberData(nameof(GetParamPrefixes))]
420+
public void TableQueryWithXml(string at)
421+
{
422+
// arrange
423+
_commandText = "select 1 from dbo.Table where x = @x, y = @y";
424+
const string expectedOutput = "DECLARE @x xml = '<root></root>',\r\n @y xml = '<root><node/></root>';\r\n\r\nselect 1 from dbo.Table where x = @x, y = @y;";
425+
CreateDbCommand(CommandType.Text);
426+
AddDbParameter<string>(at + "x", "<root></root>", type: DbType.Xml);
427+
AddDbParameter<string>(at + "y", "<root><node/></root>", type: DbType.Xml);
428+
429+
// act
430+
var actualOutput = GenerateOutput();
431+
432+
// assert
433+
Assert.Equal(expectedOutput, actualOutput);
434+
}
435+
328436
#endregion
329437

330438
[Fact]

0 commit comments

Comments
 (0)