public async Task ExecuteReader_WithReturnResultSet_ExecutesSuccessfully()
{
SqlProgram readerTest = await SqlProgram.Create((Connection)DifferentLocalDatabaseConnectionString,
name : "spUltimateSproc");
dynamic result = readerTest.ExecuteReader <dynamic>(
reader =>
{
Assert.IsTrue(reader.Read());
var res = new
{
Name = reader.GetValue <string>(0),
Age = reader.GetValue <int>(1),
Balance = reader.GetValue <decimal>(2),
IsValued = reader.GetValue <bool>(3)
};
Assert.IsFalse(reader.Read());
Assert.IsFalse(reader.NextResult());
return(res);
});
Assert.IsNotNull(result);
// Read the sproc defaults.
Assert.AreEqual("A Test String", result.Name);
Assert.AreEqual(5, result.Age);
Assert.AreEqual(200.15M, result.Balance);
Assert.AreEqual(false, result.IsValued);
}
public async Task ExecuteReader_WithOutputParameters_ExecutesSuccessfully()
{
SqlProgram <int, Out <int>, Out <int> > program =
await SqlProgram <int, Out <int>, Out <int> > .Create((Connection)LocalDatabaseConnectionString, "spOutputParameters");
const int inputVal = 123;
const int inputOutputVal = 321;
Out <int> inputOutput = new Out <int>(inputOutputVal);
Out <int> output = new Out <int>();
string result = program.ExecuteReader(
(reader) =>
{
Assert.IsTrue(reader.Read());
string res = reader.GetString(0);
Assert.IsFalse(reader.Read());
Assert.IsFalse(reader.NextResult());
return(res);
},
inputVal,
inputOutput,
output);
Assert.AreEqual("<foo>bar</foo>", result);
Assert.IsNull(inputOutput.OutputError, inputOutput.OutputError?.Message);
Assert.IsNull(output.OutputError, output.OutputError?.Message);
Assert.AreEqual(inputOutputVal * 2, inputOutput.OutputValue.Value);
Assert.AreEqual(inputVal, output.OutputValue.Value);
}
public async Task ExecuteReader_WithSerializableObjectParameter_ReturnsByteArray()
{
SqlProgram <TestSerializableObject> serializeObjectTest = await SqlProgram <TestSerializableObject> .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakeByteArray");
TestSerializableObject objecToSerialize = new TestSerializableObject {
String1 = Random.RandomString(), String2 = Random.RandomString()
};
serializeObjectTest.ExecuteReader(
reader =>
{
Assert.IsTrue(reader.Read());
Assert.IsInstanceOfType(reader.GetValue(0), typeof(byte[]));
// Deserialize object
TestSerializableObject deserializedObject =
(TestSerializableObject)reader.GetObjectByName(reader.GetName(0));
// Check we don't have same object instance.
Assert.IsFalse(ReferenceEquals(objecToSerialize, deserializedObject));
// Check equality of object instances using equality method.
Assert.AreEqual(objecToSerialize, deserializedObject);
},
objecToSerialize);
}
public async Task ExecuteReader_ExecutesSuccessfully()
{
SqlProgram readerTest = await SqlProgram.Create((Connection)DifferentLocalDatabaseConnectionString,
name : "spUltimateSproc");
readerTest.ExecuteReader();
}
public async Task ExecuteReader_WithEnumerableKeyValuePairParameter_ReturnsTwoColumnTableMatchingTheParameterTypes()
{
SqlProgram <IEnumerable <KeyValuePair <int, string> > > tableTypeTest =
await SqlProgram <IEnumerable <KeyValuePair <int, string> > > .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakesKvpTable");
string str1 = Random.RandomString(10, false);
string str2 = Random.RandomString(10, false);
string str3 = Random.RandomString(10, false);
IDictionary <int, string> result = tableTypeTest.ExecuteReader(
reader =>
{
IDictionary <int, string> resultSet = new Dictionary <int, string>();
while (reader.Read())
{
resultSet.Add(reader.GetValue <int>(0), reader.GetValue <string>(1));
}
return(resultSet);
},
new Dictionary <int, string>
{
{ 0, str1 },
{ 1, str2 },
{ 2, str3 }
});
Assert.AreEqual(3, result.Count);
Assert.AreEqual(str1, result[0]);
Assert.AreEqual(str2, result[1]);
Assert.AreEqual(str3, result[2]);
}
public async Task TestSqlProgram_ExecuteReader()
{
int rowCount = 0;
// ReSharper disable once PossibleNullReferenceException
await Task.WhenAll(
Enumerable.Range(0, Loops)
.Select(
async _ =>
{
SqlProgram <string, int, decimal, bool> program =
await
DatabasesConfiguration.Active.GetSqlProgram <string, int, decimal, bool>(
"test",
ReturnsTableProcedureName,
"@stringParam",
"@intParam",
"@decimalParam",
"@boolParam",
true);
Assert.IsNotNull(program);
program.ExecuteReader(
(dataReader) =>
{
Assert.IsNotNull(dataReader);
while (dataReader.Read())
{
// Operation modes
Interlocked.Increment(ref rowCount);
Assert.AreEqual("system", dataReader.GetString(0), "Strings are not equal");
Assert.AreEqual(-1, dataReader.GetInt32(1), "Ints are not equal");
Assert.AreEqual(100M, dataReader.GetDecimal(2), "decimals are not equal");
Assert.AreEqual(true, dataReader.GetBoolean(3), "bools are not equal");
}
dataReader.NextResult();
while (dataReader.Read())
{
// Guids
Interlocked.Increment(ref rowCount);
}
},
"system",
-1,
100M,
true);
}));
Assert.AreEqual(Loops, rowCount, "Loop count and row count should be equal");
}
public async Task TestNoParams()
{
SqlProgram program = await DatabasesConfiguration.Active.GetSqlProgram("test", ReturnsTableProcedureName);
program.ExecuteReader(
reader =>
{
while (reader.Read())
{
}
});
}
public async Task ExecuteReader_WithByteArrayParameter_ReturnsSameArrayInReader()
{
SqlProgram <byte[]> byteArrayTest = await SqlProgram <byte[]> .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakeByteArrayLength10");
int length = Random.Next(1, 10);
byte[] testParam = new byte[length];
Random.NextBytes(testParam);
byteArrayTest.ExecuteReader(
reader =>
{
if (reader.Read())
{
CollectionAssert.AreEqual(
testParam,
(ICollection)reader.GetValue(0));
}
},
testParam);
}
public async Task ExecuteReader_WithTupleParameter_ExecutesSuccessfully()
{
SqlProgram <IEnumerable <Tuple <int, string, bool> > > tableTypeTest =
await SqlProgram <IEnumerable <Tuple <int, string, bool> > > .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakesTupleTable");
string str1 = Random.RandomString(10, false);
string str2 = Random.RandomString(10, false);
IList <dynamic> result =
tableTypeTest.ExecuteReader(
reader =>
{
IList <dynamic> resultSet = new List <dynamic>();
while (reader.Read())
{
resultSet.Add(
new
{
IntColumn = reader.GetValue <int>(0),
StringColumn = reader.GetValue <string>(1),
BoolColumn = reader.GetValue <bool>(2)
});
}
return(resultSet);
},
new List <Tuple <int, string, bool> >
{
new Tuple <int, string, bool>(1, str1, false),
new Tuple <int, string, bool>(2, str2, true)
});
Assert.AreEqual(2, result.Count);
Assert.AreEqual(1, result[0].IntColumn);
Assert.AreEqual(2, result[1].IntColumn);
Assert.AreEqual(str1, result[0].StringColumn);
Assert.AreEqual(str2, result[1].StringColumn);
Assert.IsFalse(result[0].BoolColumn);
Assert.IsTrue(result[1].BoolColumn);
}
public async Task ExecuteReader_WithAllParametersSet_ExecutesAndReturnsExpectedResult()
{
SqlProgram <string, int, decimal, bool> readerTest =
await SqlProgram <string, int, decimal, bool> .Create((Connection)DifferentLocalDatabaseConnectionString, "spUltimateSproc");
dynamic result = readerTest.ExecuteReader <dynamic>(
c =>
{
c.SetParameter("@stringParam", AString);
c.SetParameter("@intParam", AInt);
c.SetParameter("@decimalParam", ADecimal);
c.SetParameter("@boolParam", ABool);
},
reader =>
{
Assert.IsTrue(reader.Read());
var res = new
{
Name = reader.GetValue <string>(0),
Age = reader.GetValue <int>(1),
Balance = reader.GetValue <decimal>(2),
IsValued = reader.GetValue <bool>(3)
};
Assert.IsFalse(reader.Read());
Assert.IsFalse(reader.NextResult());
return(res);
});
Assert.IsNotNull(result);
Assert.AreEqual(AString, result.Name);
Assert.AreEqual(AInt, result.Age);
Assert.AreEqual(ADecimal, result.Balance);
Assert.AreEqual(ABool, result.IsValued);
}
public async Task ExecuteReader_WithEnumerableIntParameter_ReturnsSingleColumnTableMatchingTheParameterType()
{
SqlProgram <IEnumerable <int> > tableTypeTest =
await SqlProgram <IEnumerable <int> > .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakesIntTable");
IList <int> result = tableTypeTest.ExecuteReader(
reader =>
{
List <int> resultSet = new List <int>();
while (reader.Read())
{
resultSet.Add(reader.GetValue <int>(0));
}
return(resultSet);
},
new[] { 0, 1, 2, 3 });
Assert.AreEqual(4, result.Count);
for (int i = 0; i < result.Count; i++)
{
Assert.AreEqual(i, result[i]);
}
}
public async Task ExecuteReader_WithByteArrayParameterTooLong_ThrowsExceptionWhenTypeConstraintModeError()
{
// This Sql Program is configured in the app.config to use TypeConstraintMode Error, so will throw error
// if byte[] is truncated.
SqlProgram <byte[]> byteArrayTest = await DatabasesConfiguration.GetConfiguredSqlProgram <byte[]>(
"test",
"spTakeByteArrayLength10",
"@byteArrayParam");
byte[] testParam = new byte[11];
Random.NextBytes(testParam);
byteArrayTest.ExecuteReader(
reader =>
{
if (reader.Read())
{
CollectionAssert.AreEqual(
testParam,
(ICollection)reader.GetValue(0));
}
},
testParam);
}
public async Task ExecuteReader_WithNestedTupleParameter_ExecutesSuccessfully()
{
SqlProgram <IEnumerable <Tuple <int, string, bool, bool, decimal, decimal, double, Tuple <string, short, TestSerializableObject, byte, DateTime, DateTime, XElement, Tuple <int, long, int, int> > > > > tupleTableTypeTest =
await SqlProgram <IEnumerable <Tuple <int, string, bool, bool, decimal, decimal, double, Tuple <string, short, TestSerializableObject, byte, DateTime, DateTime, XElement, Tuple <int, long, int, int> > > > > .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakesMultiTupleTable");
var rows =
new List
<
Tuple
<int, string, bool, bool, decimal, decimal, double,
Tuple
<string, short, TestSerializableObject, byte, DateTime, DateTime, XElement,
Tuple <int, long, int, int> > > >();
for (int i = 0; i < Random.Next(3, 10); i++)
{
rows.Add(ExtendedTuple.Create(
Random.RandomInt32(),
Random.RandomString(50, false),
false,
true,
RandomSqlSafeDecimal(),
RandomSqlSafeDecimal(),
Random.RandomDouble(),
Random.RandomString(),
Random.RandomInt16(),
new TestSerializableObject {
String1 = Random.RandomString(), String2 = Random.RandomString()
},
Random.RandomByte(),
RandomSqlSafeDateTime(),
RandomSqlSafeDateTime(),
new XElement("Test", new XAttribute("attribute", Random.Next())),
Random.RandomInt32(),
Random.RandomInt64(),
Random.RandomInt32(),
Random.RandomInt32()));
}
var indexer =
typeof(
Tuple
<int, string, bool, bool, decimal, decimal, double,
Tuple <string, short, TestSerializableObject, byte, DateTime, DateTime, XElement, Tuple <int, long, int, int> >
>
).GetTupleIndexer();
tupleTableTypeTest.ExecuteReader(
reader =>
{
int r = 0;
while (reader.Read())
{
var tuple = rows[r++];
for (int c = 0; c < 18; c++)
{
// Get the tuple value that was passed into the sproc.
object cell = indexer(tuple, c);
// Check collections (e.g. byte[])
ICollection cellCollection = cell as ICollection;
if (cellCollection != null)
{
ICollection resultCollection =
reader.GetValue(c) as ICollection;
Assert.IsNotNull(
resultCollection,
"The db did not return a collection");
CollectionAssert.AreEqual(cellCollection, resultCollection);
continue;
}
// Check serialized object
TestSerializableObject serializedObject =
cell as TestSerializableObject;
if (serializedObject != null)
{
// Deserialize object
TestSerializableObject deserializedObject =
(TestSerializableObject)reader.GetObjectByName(reader.GetName(c));
// Check we don't have same object instance.
Assert.IsFalse(ReferenceEquals(serializedObject, deserializedObject));
// Check equality of object instances using equality method.
Assert.AreEqual(serializedObject, deserializedObject);
continue;
}
// Check XELement
XElement xelement = cell as XElement;
if (xelement != null)
{
XElement result = XElement.Parse(reader.GetString(c));
Assert.AreEqual(xelement.ToString(), result.ToString());
continue;
}
Assert.AreEqual(cell, reader.GetValue(c));
}
}
},
rows);
}