public async Task ExecuteReaderAllAsync_WithAllParametersSet_ExecutesAndReturnsExpectedResult()
{
SqlProgram <string, int, decimal, bool> readerTest = await SqlProgram <string, int, decimal, bool> .Create(
new LoadBalancedConnection(LocalDatabaseConnectionString, LocalDatabaseCopyConnectionString), "spReturnsTable");
Task <IEnumerable <dynamic> > result = readerTest.ExecuteReaderAllAsync(
c =>
{
c.SetParameter("@stringParam", AString);
c.SetParameter("@intParam", AInt);
c.SetParameter("@decimalParam", ADecimal);
c.SetParameter("@boolParam", ABool);
},
async(reader, token) =>
{
if (await reader.ReadAsync(token))
{
return(CreateDatabaseResult(reader));
}
throw new Exception("Critical Test Error");
});
Assert.IsNotNull(result);
result.Wait();
foreach (dynamic o in result.Result)
{
Assert.AreEqual(AString, o.Name);
Assert.AreEqual(AInt, o.Age);
Assert.AreEqual(ADecimal, o.Balance);
Assert.AreEqual(ABool, o.IsValued);
}
}
public async Task Constructor_WithDefaults_SetsCommandTimeoutToThirtySeconds()
{
SqlProgram program = await SqlProgram.Create((Connection)LocalDatabaseConnectionString, "spNonQuery");
Assert.IsNotNull(program);
Assert.AreEqual(TimeSpan.FromSeconds(30), program.DefaultCommandTimeout);
}
public async Task ExecuteXmlReaderAsync_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>();
XElement result = await program.ExecuteXmlReaderAsync(
async (reader, token) => XElement.Load(reader),
inputVal,
inputOutput,
output);
Assert.AreEqual("<foo>bar</foo>", result.ToString());
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 ExecuteXmlReaderAllAsync_WithOutputParametersAndMultiOut_ExecutesSuccessfully()
{
SqlProgram <int, Out <int>, Out <int> > program =
await SqlProgram <int, Out <int>, Out <int> > .Create(
new LoadBalancedConnection(LocalDatabaseConnectionString, LocalDatabaseCopyConnectionString),
"spOutputParameters");
const int inputVal = 123;
const int inputOutputVal = 321;
MultiOut <int> inputOutput = new MultiOut <int>(inputOutputVal);
MultiOut <int> output = new MultiOut <int>();
XElement[] result = (await program.ExecuteXmlReaderAllAsync(
async(reader, token) => XElement.Load(reader),
inputVal,
inputOutput,
output)).ToArray();
Assert.AreEqual(2, result.Length);
Assert.IsTrue(result.All(i => i.ToString() == "<foo>bar</foo>"));
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);
Assert.IsTrue(inputOutput.All(o => o.OutputValue.Value == inputOutputVal * 2));
Assert.IsTrue(output.All(o => o.OutputValue.Value == inputVal));
}
public async Task ExecuteReader_ExecutesSuccessfully()
{
SqlProgram readerTest = await SqlProgram.Create((Connection)DifferentLocalDatabaseConnectionString,
name : "spUltimateSproc");
readerTest.ExecuteReader();
}
public async Task ExecuteScalarAll_WithOutputParametersAndMultiOut_ExecutesSuccessfully()
{
SqlProgram <int, Out <int>, Out <int> > program =
await SqlProgram <int, Out <int>, Out <int> > .Create(
new LoadBalancedConnection(LocalDatabaseConnectionString, LocalDatabaseCopyConnectionString),
"spOutputParameters");
const int inputVal = 123;
const int inputOutputVal = 321;
MultiOut <int> inputOutput = new MultiOut <int>(inputOutputVal);
MultiOut <int> output = new MultiOut <int>();
string[] scalarResult = program.ExecuteScalarAll <string>(inputVal, inputOutput, output).ToArray();
Assert.AreEqual(2, scalarResult.Length);
Assert.IsTrue(scalarResult.All(i => i == "<foo>bar</foo>"));
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);
Assert.IsTrue(inputOutput.All(o => o.OutputValue.Value == inputOutputVal * 2));
Assert.IsTrue(output.All(o => o.OutputValue.Value == inputVal));
}
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 ExecuteNonQueryAllAsync_WithOutputParametersAndMultiOut_ExecutesSuccessfully()
{
SqlProgram <int, Out <int>, Out <int> > program =
await SqlProgram <int, Out <int>, Out <int> > .Create(
new LoadBalancedConnection(LocalDatabaseConnectionString, LocalDatabaseCopyConnectionString),
"spOutputParameters");
const int inputVal = 123;
const int inputOutputVal = 321;
MultiOut <int> inputOutput = new MultiOut <int>(inputOutputVal);
MultiOut <int> output = new MultiOut <int>();
int[] nonQueryResult = (await program.ExecuteNonQueryAllAsync(inputVal, inputOutput, output)).ToArray();
Assert.AreEqual(2, nonQueryResult.Length);
Assert.IsTrue(nonQueryResult.All(i => i == -1));
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);
Assert.IsTrue(inputOutput.All(o => o.OutputValue.Value == inputOutputVal * 2));
Assert.IsTrue(output.All(o => o.OutputValue.Value == inputVal));
}
public async Task ExecuteReaderAsync_WithManualDisposal_ExecutesAndAllowsStreaming()
{
SqlProgram <byte[]> readerTest =
await SqlProgram <byte[]> .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakeByteArray");
byte[] data = Encoding.UTF8.GetBytes(AString);
string resultString;
using (Stream stream = await readerTest.ExecuteReaderAsync(
async(reader, disposable, token) =>
{
if (await reader.ReadAsync(token))
{
return((Stream) new CloseableStream(reader.GetStream(0), disposable));
}
throw new Exception("Critical Test Error");
},
data).ConfigureAwait(false))
{
Assert.IsNotNull(stream);
using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
resultString = reader.ReadToEnd();
}
Assert.AreEqual(AString, resultString);
}
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 ExecuteNonQuery_WithTupleParameterAndOtherParameters_ExecutesSuccessfully()
{
SqlProgram <IEnumerable <Tuple <string, string, string, DateTime, short?> >, int, DateTime> tableTypeTest =
await SqlProgram <IEnumerable <Tuple <string, string, string, DateTime, short?> >, int, DateTime> .Create(
(Connection)DifferentLocalDatabaseConnectionString, "spTakesTupleTablePlusTwo");
List <TestType> myList =
new List <TestType>
{
new TestType
{
Name = Random.RandomString(100),
Body = Random.RandomString(500),
Email = Random.RandomString(100),
Created = DateTime.Today,
LoginId = null
}
};
int nonQueryResult =
tableTypeTest.ExecuteNonQuery(
myList.ToTuple(e => e.Name, e => e.Body, e => e.Email, e => e.Created, e => e.LoginId),
Random.RandomInt32());
Assert.AreEqual(-1, nonQueryResult);
}
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 ExecuteReaderAsync_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 = await program.ExecuteReaderAsync(
async (reader, token) =>
{
Assert.IsTrue(await reader.ReadAsync());
string res = reader.GetString(0);
Assert.IsFalse(await reader.ReadAsync());
Assert.IsFalse(await reader.NextResultAsync());
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 ExecuteReaderAsync_WithAllParametersSet_ExecutesAndReturnsExpectedResult()
{
SqlProgram <string, int, decimal, bool> readerTest =
await SqlProgram <string, int, decimal, bool> .Create((Connection)DifferentLocalDatabaseConnectionString, "spUltimateSproc");
Task <dynamic> result = readerTest.ExecuteReaderAsync(
c =>
{
c.SetParameter("@stringParam", AString);
c.SetParameter("@intParam", AInt);
c.SetParameter("@decimalParam", ADecimal);
c.SetParameter("@boolParam", ABool);
},
async(reader, token) =>
{
if (await reader.ReadAsync(token))
{
return(CreateDatabaseResult(reader));
}
throw new Exception("Critical Test Error");
});
Assert.IsNotNull(result);
result.Wait();
Assert.AreEqual(AString, result.Result.Name);
Assert.AreEqual(AInt, result.Result.Age);
Assert.AreEqual(ADecimal, result.Result.Balance);
Assert.AreEqual(ABool, result.Result.IsValued);
}
public async Task ExecuteNonQuery_WithMoreNullableDateTimeParameterSetToNull_ExecutesSuccessfully()
{
var nullableTypesTest =
await SqlProgram <int, DateTime?, DateTime?, DateTime?, DateTime?, int?, int?, int?> .Create((Connection)DifferentLocalDatabaseConnectionString, "spLoadsOfNullables");
nullableTypesTest.ExecuteNonQuery(1, null, null, null, null, null, null, 1);
Assert.IsNotNull(nullableTypesTest);
}
public async Task ExecuteXmlReaderAsync_WithNoParameters_ExecuteReturnsExpectedString()
{
SqlProgram program = await SqlProgram.Create((Connection)DifferentLocalDatabaseConnectionString, name : "spReturnsXml");
XElement result = await program.ExecuteXmlReaderAsync(async (reader, token) => XElement.Load(reader));
Assert.AreEqual("<foo>bar</foo>", result.ToString());
}
public async Task ExecuteNonQuery_WithNullableDateTimeParameterSetToNull_ExecutesSuccessfully()
{
SqlProgram <string, int?, decimal, bool, DateTime?> nullableTypesTest =
await SqlProgram <string, int?, decimal, bool, DateTime?> .Create((Connection)DifferentLocalDatabaseConnectionString, "spUltimateSproc");
nullableTypesTest.ExecuteNonQuery(Random.RandomString(20), Random.RandomInt32(), decimal.Zero, true, null);
Assert.IsNotNull(nullableTypesTest);
}
public async Task ExecuteNonQuery_ExecutesSuccessfully()
{
SqlProgram nonQueryTest = await SqlProgram.Create((Connection)LocalDatabaseConnectionString, name : "spNonQuery");
int nonQueryResult = nonQueryTest.ExecuteNonQuery();
Assert.AreEqual(-1, nonQueryResult);
}
public async Task ExecuteScalar_WithNoParameters_ExecuteReturnsExpectedString()
{
SqlProgram program = await SqlProgram.Create((Connection)DifferentLocalDatabaseConnectionString, name : "spReturnsScalar");
string scalarResult = program.ExecuteScalar <string>();
Assert.AreEqual("HelloWorld", scalarResult);
}
public async Task ExecuteNonQueryAsync_PassNullToNonOptionalParameter()
{
SqlProgram <XElement> program =
await SqlProgram <XElement> .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakesXml");
// ReSharper disable once RedundantArgumentDefaultValue, ArgumentsStyleLiteral
await program.ExecuteNonQueryAsync(p1Value : null);
}
public async Task Constructor_WithInvalidProgramName_ThrowsLoggingException()
{
SqlProgram <int> timeoutTest = await SqlProgram <int> .Create(
(Connection)DifferentLocalDatabaseConnectionString,
"invalidProgramName");
Assert.IsNotNull(timeoutTest);
}
public async Task ExecuteReaderAllAsync_ExecutesAndReturnsExpectedResult()
{
SqlProgram program = await SqlProgram.Create(new LoadBalancedConnection(LocalDatabaseConnectionString, LocalDatabaseCopyConnectionString), "spReturnsTable");
Task readerTask = program.ExecuteReaderAllAsync();
Assert.IsNotNull(readerTask);
}
public async Task ExecuteReaderAsync_ExecutesAndReturnsExpectedResult()
{
SqlProgram readerTest = await SqlProgram.Create((Connection)DifferentLocalDatabaseConnectionString, "spUltimateSproc");
Task readerTask = readerTest.ExecuteReaderAsync();
Assert.IsNotNull(readerTask);
}
public async Task ExecuteNonQuery_WithEnumerableIntParameter_ExecutesSuccessfully()
{
SqlProgram <IEnumerable <int> > tableTypeTest =
await SqlProgram <IEnumerable <int> > .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakesIntTable");
int nonQueryResult = tableTypeTest.ExecuteNonQuery(new[] { 1, 2, 3, 4 });
Assert.AreEqual(-1, nonQueryResult);
}
public async Task ExecuteNonQueryAsync_ExecutesProcedureSuccessfully()
{
SqlProgram <int> timeoutTest =
await SqlProgram <int> .Create((Connection)DifferentLocalDatabaseConnectionString, "spTimeoutTest");
Task <int> task = timeoutTest.ExecuteNonQueryAsync();
task.Wait();
Assert.IsTrue(task.IsCompleted);
}
public async Task ExecuteNonQuery_WithEmptyTupleParameter_ExecutesSuccessfully()
{
SqlProgram <IEnumerable <Tuple <int, string, bool> > > tableTypeTest =
await SqlProgram <IEnumerable <Tuple <int, string, bool> > > .Create((Connection)DifferentLocalDatabaseConnectionString, "spTakesTupleTable");
int nonQueryResult =
tableTypeTest.ExecuteNonQuery(Enumerable.Empty <Tuple <int, string, bool> >());
Assert.AreEqual(-1, nonQueryResult);
}
public async Task ExecuteNonQueryAsync_ExecutesSuccessfully()
{
SqlProgram nonQueryTest = await SqlProgram.Create((Connection)LocalDatabaseConnectionString, name : "spNonQuery");
Task <int> nonQueryResult = nonQueryTest.ExecuteNonQueryAsync();
Assert.IsNotNull(nonQueryResult);
nonQueryResult.Wait();
Assert.AreEqual(-1, nonQueryResult.Result);
}
public async Task ExecuteReaderAll_ExecutesSuccessfully()
{
SqlProgram readerTest =
await SqlProgram.Create(connection : new LoadBalancedConnection(LocalDatabaseConnectionString, LocalDatabaseCopyConnectionString),
name : "spNonQuery");
readerTest.ExecuteReaderAll();
// Can't really do any assertions here so test is just that it doesn't throw an exception.
}
public async Task Constructor_WithDefaultCommmandTimeoutParameter_SetsCommandTimeoutToParameterValue()
{
const int timeoutSeconds = 40;
SqlProgram program = await SqlProgram.Create((Connection)LocalDatabaseConnectionString,
name : "spNonQuery",
defaultCommandTimeout : TimeSpan.FromSeconds(timeoutSeconds));
Assert.IsNotNull(program);
Assert.AreEqual(TimeSpan.FromSeconds(timeoutSeconds), program.DefaultCommandTimeout);
}
public async Task ExecuteScalarAsync_ExecutesAndReturnsExpectedResult()
{
SqlProgram scalarTest =
await SqlProgram.Create((Connection)DifferentLocalDatabaseConnectionString, "spReturnsScalar");
Task <string> task = scalarTest.ExecuteScalarAsync <string>();
Assert.IsNotNull(task);
task.Wait();
Assert.IsTrue(task.IsCompleted);
Assert.AreEqual("HelloWorld", task.Result);
}