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); }