public void TestToFromUtcEnumerableDateTime()
{
DateTime[] expected = new DateTime[]
{
new DateTime(1993, 8, 23, 12, 0, 0),
new DateTime(2000, 1, 1)
};
SqlDbTypeSize valueField = new SqlDbTypeSize(SqlDbType.DateTime);
using (SqlConnection conn = new SqlConnection(Constants.DATABASE_CONNECTION_STRING))
using (SqlCommand command = conn.GetSqlCommand())
{
conn.Open();
// Build the command
command.CommandText =
@"SELECT v
FROM @vals";
// Make the parameters
command.Parameters.AddWithValue("@vals", expected, valueField);
// Run the command
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
List <DateTime> actual = new List <DateTime>(expected.Length);
while (reader.Read())
{
actual.Add(reader.GetDateTime("v"));
}
CollectionAssert.AreEqual(expected, actual);
}
}
public void TestIntParameters()
{
int[] expected = new int[] { 1, 2, 3, 7, 11, -46, int.MinValue, int.MaxValue, 0 };
SqlDbTypeSize valueField = new SqlDbTypeSize(SqlDbType.Int);
using (SqlConnection conn = new SqlConnection(Constants.DATABASE_CONNECTION_STRING))
using (SqlCommand command = conn.GetSqlCommand())
{
conn.Open();
// Build the command
command.CommandText =
@"SELECT v
FROM @vals";
// Make the parameters
command.Parameters.AddWithValue("@vals", expected, valueField);
// Run the command
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
List <int> actual = new List <int>(expected.Length);
while (reader.Read())
{
actual.Add(reader.GetInt("v"));
}
CollectionAssert.AreEqual(expected, actual);
}
}
public void TestToFromUtcScalarDateTime2()
{
DateTime expected = new DateTime(1993, 8, 23, 12, 0, 0);
SqlDbTypeSize valueField = new SqlDbTypeSize(SqlDbType.DateTime2);
using (SqlConnection conn = new SqlConnection(Constants.DATABASE_CONNECTION_STRING))
using (SqlCommand command = conn.GetSqlCommand())
{
conn.Open();
// Build the command
command.CommandText = "SELECT @dt AS dt";
// Make the parameters
command.Parameters.AddWithValue("@dt", expected, valueField);
// Run the command
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
DateTime actual = reader.GetDateTime("dt");
Assert.AreEqual(expected, actual);
}
}
public void Bug1Repro()
{
// Strings of lengths longer than the first were being silently truncated to the length of the first one
// so just test by selecting the strings back out, with some later strings being longer than the first
string[] expected = new string[] { "abd", "abcdefg", "def", "fghjkm" };
SqlDbTypeSize valueField = new SqlDbTypeSize(SqlDbType.VarChar);
using (SqlConnection conn = new SqlConnection(Constants.DATABASE_CONNECTION_STRING))
using (SqlCommand command = conn.GetSqlCommand())
{
conn.Open();
// Build the command
command.CommandText =
@"SELECT v
FROM @vals";
// Make the parameters
command.Parameters.AddWithValue("@vals", expected, valueField);
// Run the command
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
List <string> actual = new List <string>(expected.Length);
while (reader.Read())
{
actual.Add(reader.GetString("v"));
}
CollectionAssert.AreEqual(expected, actual);
}
}
private void testNullableInt(int?[] expected)
{
SqlDbTypeSize valueField = new SqlDbTypeSize(SqlDbType.Int);
using (SqlConnection conn = new SqlConnection(Constants.DATABASE_CONNECTION_STRING))
using (SqlCommand command = conn.GetSqlCommand())
{
conn.Open();
// Build the command
command.CommandText =
@"SELECT v
FROM @vals";
// Make the parameters
command.Parameters.AddWithValue("@vals", expected.Cast <object>(), valueField);
// Run the command
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
List <int?> actual = new List <int?>();
while (reader.Read())
{
actual.Add(reader.GetNullableInt("v"));
}
CollectionAssert.AreEqual(expected, actual);
}
}
/// <summary>
/// Add a parameter for a custom User Defined Table Type (UDTT) with a single column.
/// </summary>
public static SqlParameter AddWithValue(this SqlParameterCollection parameters, string paramName,
IEnumerable <object> values, SqlDbTypeSize typeSize, string tableTypeName, string fieldName = "v")
{
// Validation
if (parameters == null)
{
throw new ArgumentNullException(nameof(parameters));
}
if (paramName == null)
{
throw new ArgumentNullException(nameof(paramName));
}
if (typeSize == null)
{
throw new ArgumentNullException(nameof(typeSize));
}
if (tableTypeName == null)
{
throw new ArgumentNullException(nameof(tableTypeName));
}
if (fieldName == null)
{
throw new ArgumentNullException(nameof(fieldName));
}
IEnumerable <SqlDataRecord> dataRecords = toSqlDataRecord(values, typeSize, fieldName);
SqlParameter param = new SqlParameter(paramName, SqlDbType.Structured, -1)
{
TypeName = tableTypeName,
Value = dataRecords
};
return(parameters.Add(param));
}
private void testNullableInt(int?[] expected)
{
IEnumerable <object[]> objRows = expected.Select(i => new object[] { i });
SqlDbTypeSize[] fieldTypeSizes = new SqlDbTypeSize[]
{
new SqlDbTypeSize(SqlDbType.Int)
};
string[] fieldNames = new string[]
{
"i"
};
using (SqlConnection conn = new SqlConnection(Constants.DATABASE_CONNECTION_STRING))
using (SqlCommand command = conn.GetSqlCommand())
{
conn.Open();
// Build the command
command.CommandText =
@"SELECT *
FROM @vals";
// Make the parameters
command.Parameters.AddWithValue("@vals", objRows, fieldTypeSizes, "udtt_UnitTests_int_nullable", fieldNames);
}
}
public void TestToFromScalarTimeSpan()
{
TimeSpan expected = new TimeSpan(1, 3, 37);
SqlDbTypeSize valueField = new SqlDbTypeSize(SqlDbType.Time);
using (SqlConnection conn = new SqlConnection(Constants.DATABASE_CONNECTION_STRING))
using (SqlCommand command = conn.GetSqlCommand())
{
conn.Open();
// Build the command
command.CommandText = "SELECT @ts AS ts";
// Add the parameters
command.Parameters.AddWithValue("@ts", expected, valueField);
// Run the command
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
reader.Read();
TimeSpan actual = reader.GetTimeSpan("ts");
Assert.AreEqual(expected, actual);
}
}
private static SqlMetaData calculateSqlMetaData(IEnumerable <object> values, SqlDbTypeSize typeSize,
out IEnumerable <object> convertedValues, string fieldName)
{
SqlMetaData valueMetaData;
// Handle special cases where the data must be converted
switch (typeSize.SqlDbType)
{
// DateTime2 would get inferred as DateTime, which has a smaller range, so pass them as ISO8601 formatted strings (YYYY-MM-DDThh:mm:ss.nnnnnnn)
case SqlDbType.DateTime2:
valueMetaData = new SqlMetaData(fieldName, SqlDbType.Char, "YYYY-MM-DDThh:mm:ss.nnnnnnn".Length);
convertedValues =
values.Cast <DateTime>()
.Select(dt => dt.ToString("yyyy-MM-ddTHH\\:mm\\:ss.fffffff", CultureInfo.InvariantCulture));
break;
// Date would get inferred as DateTime, which has a smaller range, so pass YYYY-MM-DD strings
case SqlDbType.Date:
valueMetaData = new SqlMetaData(fieldName, SqlDbType.Char, 10);
convertedValues =
values.Cast <DateTime>().Select(dt => dt.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture));
break;
// Not a special case
default:
// No value conversion necessary
convertedValues = values;
// Try and convert the specified Type Size to an SqlMetaData object, but some types (e.g. Int)
// we can't pass to the constructor, but we can let SqlMetaData infer them from the data,
// so do that.
if (!typeSize.tryToSqlMetaData(fieldName, out valueMetaData))
{
// Note: If ever this is being reached, the data that is reaching it should be used to improve
// SqlDbTypeSize.tryToSqlMetaData, as if all of the data is NULL then we won't be able to infer
// a value and just have to use a *sensible* default
object firstValueNotNull = values.FirstOrDefault(v => v != null);
// If we have a value that isn't null
if (firstValueNotNull != null)
{
valueMetaData = SqlMetaData.InferFromValue(firstValueNotNull, fieldName);
}
// Otherwise, we don't know how to generate the SqlMetaData for this SqlDbTypeSize, and
// we don't have any values to infer it from!
// Default to NVarchar which will at least stop anything from blowing up, but could cause
// performance issues in SQL Server.
else
{
valueMetaData = new SqlMetaData(fieldName, SqlDbType.NVarChar, -1);
}
}
break;
}
return(valueMetaData);
}
public void TestMultiParameter()
{
int[] expectedInts = new int[] { 1, 2, 3, 7, 11, -46, int.MinValue, int.MaxValue, 0 };
long[] expectedLongs = new long[] { -3, 2, 1, 11, 7, -46, 0, long.MaxValue, long.MinValue };
Assert.AreEqual(expectedInts.Length, expectedLongs.Length);
object[][] objRows = new object[expectedInts.Length][];
for (int i = 0; i < objRows.Length; i++)
{
objRows[i] = new object[] { expectedInts[i], expectedLongs[i] };
}
SqlDbTypeSize[] fieldTypeSizes = new SqlDbTypeSize[]
{
new SqlDbTypeSize(SqlDbType.Int),
new SqlDbTypeSize(SqlDbType.BigInt)
};
string[] fieldNames = new string[]
{
"i",
"l"
};
using (SqlConnection conn = new SqlConnection(Constants.DATABASE_CONNECTION_STRING))
using (SqlCommand command = conn.GetSqlCommand())
{
conn.Open();
// Build the command
command.CommandText =
@"SELECT *
FROM @vals";
// Make the parameters
command.Parameters.AddWithValue("@vals", objRows, fieldTypeSizes, "udtt_UnitTests_int_long", fieldNames);
// Run the command
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
List <int> actualInts = new List <int>();
List <long> actualLongs = new List <long>();
while (reader.Read())
{
actualInts.Add(reader.GetInt("i"));
actualLongs.Add(reader.GetLong("l"));
}
CollectionAssert.AreEqual(expectedInts, actualInts);
CollectionAssert.AreEqual(expectedLongs, actualLongs);
}
}
/// <summary>
/// Takes an IEnumerable of values equivelant to a single SQL Server types (int => int, long => bigint etc...)
/// and returns an IEnumerable<SqlDataRecord> representing them
/// </summary>
private static IEnumerable <SqlDataRecord> toSqlDataRecord(IEnumerable <object> enumerableValues,
SqlDbTypeSize typeSize, string fieldName)
{
// Optimisation: Prevent multiple enumerations
object[] values = enumerableValues as object[] ?? enumerableValues.ToArray();
// Convert the values array to one with one column per row (for the one value)
object[][] rowValues = new object[values.Length][];
for (int i = 0; i < values.Length; i++)
{
rowValues[i] = new object[] { values[i] };
}
return(toSqlDataRecord(rowValues, new SqlDbTypeSize[] { typeSize }, new string[] { fieldName }));
}
public static SqlParameter AddWithValue(this SqlParameterCollection parameters, string paramName,
IEnumerable <object> values, SqlDbTypeSize typeSize, bool nullable = true)
{
// TODO: Should this logic be moved to SqlDbTypeSize.ToSqlMetaData() ??
// Since we don't do anything with the sizes, all table types get declared as max length.
// It wouldn't make sense to try and use a char(max) for something that should have a normal
// fixed length (e.g. char(3)), so treat all fixed length strings as their variable length equivelants
switch (typeSize.SqlDbType)
{
case SqlDbType.Char:
typeSize = new SqlDbTypeSize(SqlDbType.VarChar, typeSize.Size);
break;
case SqlDbType.NChar:
typeSize = new SqlDbTypeSize(SqlDbType.NVarChar, typeSize.Size);
break;
}
return(parameters.AddWithValue(paramName, values, typeSize, getTableTypeName(typeSize, nullable)));
}
public static SqlParameter AddWithValue(this SqlParameterCollection parameters,
string paramName, object value, SqlDbTypeSize typeSize)
{
// Validation
if (parameters == null)
{
throw new ArgumentNullException(nameof(parameters));
}
if (paramName == null)
{
throw new ArgumentNullException(nameof(paramName));
}
if (typeSize == null)
{
throw new ArgumentNullException(nameof(typeSize),
nameof(typeSize) + String.Format("{0} is null for paramName {1}", nameof(typeSize), paramName));
}
return(parameters.addWithValue(paramName, value, typeSize.SqlDbType, typeSize.Size));
}
public void TestSingleParameter()
{
// Could easily use built in generic table type, but might want something custom (like a primary key)
int[] toAdd = new int[] { 1, 2, 3, 7, 11, -46, int.MinValue, int.MaxValue, 0 };
SqlDbTypeSize valueField = new SqlDbTypeSize(SqlDbType.Int);
using (SqlConnection conn = new SqlConnection(Constants.DATABASE_CONNECTION_STRING))
using (SqlCommand command = conn.GetSqlCommand())
{
conn.Open();
// Build the command
command.CommandText =
@"SELECT i
FROM @vals";
// Make the parameters
command.Parameters.AddWithValue("@vals", toAdd.Cast <object>(), valueField, "udtt_UnitTests_int_pk",
"i");
// Run the command
command.Prepare();
SqlDataReader reader = command.ExecuteReader();
List <int> actual = new List <int>(toAdd.Length);
while (reader.Read())
{
actual.Add(reader.GetInt("i"));
}
// Has PK ASC, so values will come back out sorted
IEnumerable <int> expected = toAdd.OrderBy(i => i);
CollectionAssert.AreEqual(expected, actual);
}
}
public static SqlParameter AddWithValue(this SqlParameterCollection parameters, string paramName,
IEnumerable <long> values, SqlDbTypeSize typeSize)
{
return(parameters.AddWithValue(paramName, values.Cast <object>(), typeSize, getTableTypeName(typeSize, false)));
}
private static string getTableTypeName(SqlDbTypeSize typeSize, bool nullable)
{
return(String.Format("dbo.TableType_Generic_{0}{1}", typeSize.SqlDbType, nullable ? "_Nullable" : ""));
}
