public void TestExecuteRollback(ConnectionType connectionType, object isolationLevel) { DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", connectionType); DatabaseObjectCreation.CreateTestProc(@"CREATE PROCEDURE ""Test Proc"" AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0); END;", connectionType); try { var functionResult = Execute(connectionType, DatabaseHelpers.GetDefaultConnectionString(connectionType), isolationLevel); Assert.Catch <Exception>( () => RunExecutionPath(functionResult.ExecutionPathResult, transaction => { var command = transaction.GetDbTransaction().Connection.CreateCommand(); command.Transaction = transaction.GetDbTransaction(); command.CommandText = "\"Test Proc\""; command.CommandType = CommandType.StoredProcedure; command.ExecuteNonQuery(); throw new Exception("Bad things are happening!!"); })); DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", connectionType); Assert.AreEqual(0, results.Rows.Count); } finally { DatabaseObjectCreation.RemoveTestProc(connectionType); DatabaseObjectCreation.RemoveTestTable(connectionType); } }
public void TestExecuteResultSetsRowByRow() { var parameters = DatabaseObjectCreation.CreateResultSetsTestProc(this.connectionType); try { FunctionResult result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, null, true, OutputOption.RowByRow); var executionPathResults = result.ExecutionPathResult.ToList(); Assert.AreEqual(3, executionPathResults.Count()); Assert.AreEqual("Result1", executionPathResults[0].Name); var row = executionPathResults[0].Value; Assert.AreEqual(1, row.Counter); Assert.AreEqual("one", row.StringValue); Assert.AreEqual("Result1", executionPathResults[1].Name); row = executionPathResults[1].Value; Assert.AreEqual(2, row.Counter); Assert.AreEqual("two", row.StringValue); Assert.AreEqual("Result2", executionPathResults[2].Name); row = executionPathResults[2].Value; Assert.AreEqual("qwer", row.StringValue); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestExecuteResultSetsListOfRows() { var parameters = DatabaseObjectCreation.CreateResultSetsTestProc(this.connectionType); try { var result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, null, true, OutputOption.ListOfRows).Value; Assert.AreEqual(2, result.Result1Rows.Count); var row = result.Result1Rows[0]; Assert.AreEqual(1, row.Counter); Assert.AreEqual("one", row.StringValue); row = result.Result1Rows[1]; Assert.AreEqual(2, row.Counter); Assert.AreEqual("two", row.StringValue); Assert.AreEqual(1, result.Result2Rows.Count); row = result.Result2Rows[0]; Assert.AreEqual("qwer", row.StringValue); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestExecuteResultSetRowByRowWithInvalidNumberOfResultSets() { var parameters = DatabaseObjectCreation.CreateResultSetTestProc(this.connectionType); try { FunctionResult result = Execute(this.connectionType.ToConnectionTypeSelection(), null, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 2 }, FetchResultSets(this.connectionType), 1000, OutputOption.RowByRow); var executionPathResults = result.ExecutionPathResult.ToList(); Assert.AreEqual(2, executionPathResults.Count()); Assert.AreEqual("Result1", executionPathResults[0].Name); var row = executionPathResults[0].Value; Assert.AreEqual(1, row.Counter); Assert.AreEqual("one", row.StringValue); row = executionPathResults[1].Value; Assert.AreEqual(2, row.Counter); Assert.AreEqual("two", row.StringValue); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestExecuteResultSetRowByRowWithCustomType() { var parameters = DatabaseObjectCreation.CreateResultSetTestProc(this.connectionType); try { var customType = TypeReference.CreateGeneratedType(new TypeProperty("Name", typeof(string))); var resultSet = new DatabaseModel.ResultSet { CustomType = customType }; resultSet.Fields.Add(new DatabaseModel.ResultSetField("Counter", DatabaseModel.DataType.Int32, string.Empty)); resultSet.Fields.Add(new DatabaseModel.ResultSetField("StringValue", DatabaseModel.DataType.String, "Name")); FunctionResult result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 2 }, new DatabaseModel.ResultSets { resultSet }, OutputOption.RowByRow); var executionPathResults = result.ExecutionPathResult.ToList(); Assert.AreEqual(2, executionPathResults.Count()); Assert.AreEqual("Result", executionPathResults[0].Name); Assert.AreEqual(Names.GetValidName(customType.Name), executionPathResults[0].Value.GetType().Name); Assert.AreEqual("one", executionPathResults[0].Value.Name); Assert.AreEqual("two", executionPathResults[1].Value.Name); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestExecuteParametersAndReturnValue() { if (this.connectionType == ConnectionType.Oracle) { // Cannot return a value in an Oracle stored procedure - have to use OUT parameters return; } var parameters = new DatabaseModel.ProcedureParameters(DatabaseObjectCreation.CreateParameterTestProc(this.connectionType)); try { List <byte> bytes = new List <byte>(new byte[] { 1, 2, 3, 4, 5 }); dynamic result = Execute(this.connectionType, "Test Proc", parameters, 1, 1.0, "Qwer", string.Empty, new DateTime(1987, 1, 24), DatabaseModel.DefaultDateTime, bytes, new List <byte>()).Value; Assert.AreEqual(1, result.ResultParameters.RETURN_VALUE); Assert.AreEqual("Qwer", result.ResultParameters.StringValueOut); Assert.AreEqual(new DateTime(1987, 1, 24), result.ResultParameters.DateValueOut); Assert.AreEqual(bytes, result.ResultParameters.BytesValueOut); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestGetResultSetsForStoredProcedureWithDynamicSQL() { string createProcedureStatement = GetProcedureWithDynamicSQL(this.connectionType); DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType); DatabaseModel.ResultSets resultSets = null; try { DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType); using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType))) { connection.Open(); Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc"))); resultSets = databaseAssistant.GetResultSets(connection, "Test Proc"); } } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } Assert.AreEqual(1, resultSets.Count); var fields = resultSets[0].Fields; Assert.AreEqual(1, fields.Count); var field = fields[0]; Assert.AreEqual("Rowcount_Returned", field.ColumnName); Assert.AreEqual("Rowcount_Returned", field.OutputName); }
public void TestGetStoredProcedureDoesNotLoadFunction() { string createProcedureStatement = GetProcedureStatement(this.connectionType); string createFunctionStatement = GetFunctionStatement(this.connectionType); DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType); DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType); DatabaseObjectCreation.CreateTestFunction(createFunctionStatement, this.connectionType); try { DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType); using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType))) { connection.Open(); Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc"))); Assert.IsFalse(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("TestFunction"))); } } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); DatabaseObjectCreation.RemoveTestFunction(this.connectionType); DatabaseObjectCreation.RemoveTestTable(this.connectionType); } }
public void TestExecuteParameterAndResultSetsRowByRow() { var parameters = DatabaseObjectCreation.CreateParameterAndResultSetsTestProc(this.connectionType); try { List <byte> bytes = new List <byte>(new byte[] { 1, 2, 3, 4, 5 }); FunctionResult result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 2, 1.0, "Qwer", string.Empty, new DateTime(1987, 1, 24), DatabaseModel.DefaultDateTime, bytes, new List <byte>() }, true, OutputOption.RowByRow); var executionPathResults = result.ExecutionPathResult.ToList(); Assert.AreEqual(3, executionPathResults.Count()); Assert.AreEqual("Result1", executionPathResults[0].Name); var row = executionPathResults[0].Value; Assert.AreEqual("qwer", row.StringValue); Assert.AreEqual("Result2", executionPathResults[1].Name); row = executionPathResults[1].Value; Assert.AreEqual(1, row.Counter); Assert.AreEqual("Qwer", row.StringValue); row = executionPathResults[2].Value; Assert.AreEqual(2, row.Counter); Assert.AreEqual("Qwer", row.StringValue); Assert.AreEqual("Qwer", result.Value.ResultParameters.StringValueOut); Assert.AreEqual(new DateTime(1987, 1, 24), result.Value.ResultParameters.DateValueOut); Assert.AreEqual(bytes, result.Value.ResultParameters.BytesValueOut); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestExecuteParameterAndResultSetsFirstRow( [Values(OutputOption.FirstRow, OutputOption.FirstRowElseEmptyRow)] OutputOption outputOption) { var parameters = DatabaseObjectCreation.CreateParameterAndResultSetsTestProc(this.connectionType); try { List <byte> bytes = new List <byte>(new byte[] { 1, 2, 3, 4, 5 }); var result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 2, 1.0, "Qwer", string.Empty, new DateTime(1987, 1, 24), DatabaseModel.DefaultDateTime, bytes, new List <byte>() }, true, outputOption).Value; Assert.AreEqual("Qwer", result.ResultParameters.StringValueOut); Assert.AreEqual(new DateTime(1987, 1, 24), result.ResultParameters.DateValueOut); Assert.AreEqual(bytes, result.ResultParameters.BytesValueOut); Assert.AreEqual("qwer", result.Result1.StringValue); Assert.AreEqual(1, result.Result2.Counter); Assert.AreEqual("Qwer", result.Result2.StringValue); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestExecuteResultSetFirstRowNoRows() { var parameters = DatabaseObjectCreation.CreateResultSetTestProc(this.connectionType); Assert.That(() => Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 0 }, true, OutputOption.FirstRow), Throws.Exception.TypeOf <ExecuteException>() .With.Property("Message").EqualTo("No rows returned for Result.\r\nSee Code and Parameter properties for more information.")); DatabaseObjectCreation.RemoveTestProc(this.connectionType); }
public void TestExecuteWithExpressionsInSQLString(ConnectionType connectionType) { DatabaseObjectCreation.CreateTestTable(connectionType); try { string sql = @"INSERT INTO TestTable (IntValue, DoubleValue, StringValue, DateValue, BytesValue) VALUES (@{SomeIntValue - 3}, @{NullableDoubleValue}, @{Name + "" "" + Surname}, @{Date}, @{Bytes})" ; Execute(connectionType.ToConnectionTypeSelection(), null, sql, ExecuteSQLShared.ReturnModeType.FirstRowElseEmptyRow, new ResultType(), false, new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 1, 30), new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 2, null), new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 3, "John Doe"), new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 4, DateTime.Today), new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 5, new List <byte> { 1, 2, 3 })); sql = @"SELECT INTVALUE, DOUBLEVALUE, STRINGVALUE, DATEVALUE, BYTESVALUE FROM TestTable WHERE IntValue = @{TheIntValue}"; var resultType = new ResultType(); resultType.Fields.Add(new ResultTypeField { ColumnName = "INTVALUE", Type = typeof(int), Name = "IntValue" }); resultType.Fields.Add(new ResultTypeField { ColumnName = "DOUBLEVALUE", Type = typeof(double), Name = "DoubleValue" }); resultType.Fields.Add(new ResultTypeField { ColumnName = "STRINGVALUE", Type = typeof(string), Name = "StringValue" }); resultType.Fields.Add(new ResultTypeField { ColumnName = "DATEVALUE", Type = typeof(DateTime), Name = "DateValue" }); resultType.Fields.Add(new ResultTypeField { ColumnName = "BYTESVALUE", Type = typeof(List <byte>), Name = "BytesValue" }); dynamic dataOut = Execute(connectionType.ToConnectionTypeSelection(), null, sql, ExecuteSQLShared.ReturnModeType.ListOfRows, resultType, false, new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 1, 30)).Value; Assert.AreEqual(1, dataOut.Count); dynamic row = dataOut[0]; Assert.AreEqual(30, row.IntValue); Assert.AreEqual(0, row.DoubleValue); Assert.AreEqual("John Doe", row.StringValue); Assert.AreEqual(DateTime.Today, row.DateValue); Assert.AreEqual(new byte[] { 1, 2, 3 }, row.BytesValue); } finally { DatabaseObjectCreation.RemoveTestTable(connectionType); } }
public void TestExecute([Values(DbBulkCopy.ConnectionType.SqlServer, DbBulkCopy.ConnectionType.Oracle)] DbBulkCopy.ConnectionType connectionType) { DatabaseObjectCreation.CreateTestTable(ToCommonConnectionType(connectionType)); try { var functionResult = Execute(connectionType, "TESTTABLE"); foreach (var nextResult in functionResult.ExecutionPathResult) { dynamic row = new ExpandoObject(); row.IntValue = row.INTVALUE = 1; row.DoubleValue = row.DOUBLEVALUE = 1.2; row.StringValue = row.STRINGVALUE = "Qwer"; row.DateValue = row.DATEVALUE = new DateTime(2015, 5, 13); row.BytesValue = row.BYTESVALUE = new List <byte> { 1, 2, 3 }; nextResult.Value.Write = row; row = new ExpandoObject(); row.IntValue = row.INTVALUE = 2; row.DoubleValue = row.DOUBLEVALUE = null; row.StringValue = row.STRINGVALUE = null; row.DateValue = row.DATEVALUE = null; row.BytesValue = row.BYTESVALUE = null; nextResult.Value.Write = row; } var results = DatabaseHelpers.GetDataTable(@"SELECT * FROM TESTTABLE", ToCommonConnectionType(connectionType)); Assert.AreEqual(2, results.Rows.Count); object[] rowItems = results.Rows[0].ItemArray; Assert.AreEqual(1, rowItems[0]); Assert.AreEqual(1.2, rowItems[1]); Assert.AreEqual("Qwer", rowItems[2]); Assert.AreEqual(new DateTime(2015, 5, 13), rowItems[3]); Assert.AreEqual(new byte[] { 1, 2, 3 }, rowItems[4]); rowItems = results.Rows[1].ItemArray; Assert.AreEqual(2, rowItems[0]); Assert.AreEqual(DBNull.Value, rowItems[1]); Assert.AreEqual(DBNull.Value, rowItems[2]); Assert.AreEqual(DBNull.Value, rowItems[3]); Assert.AreEqual(DBNull.Value, rowItems[4]); } finally { DatabaseObjectCreation.RemoveTestTable(ToCommonConnectionType(connectionType)); } }
public void TestExecuteResultSetFirstRowElseEmptyRowNoRows() { var parameters = DatabaseObjectCreation.CreateResultSetTestProc(this.connectionType); try { var result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, new object[] { 0 }, true, OutputOption.FirstRowElseEmptyRow).Value; Assert.AreEqual(0, result.Result.Counter); Assert.AreEqual(string.Empty, result.Result.StringValue); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestExecuteNoParametersAndNoReturnValue() { DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType); DatabaseObjectCreation.CreateTestProc(@"CREATE PROCEDURE ""Test Proc"" AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0); END;", this.connectionType); try { Execute(this.connectionType, "Test Proc"); DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType); Assert.AreEqual(1, results.Rows.Count); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); DatabaseObjectCreation.RemoveTestTable(this.connectionType); } }
public void TestExecuteParametersAndNoReturnValue() { var parameters = new DatabaseModel.ProcedureParameters(DatabaseObjectCreation.CreateParameterTestProc(this.connectionType).Where(p => p.Direction != DatabaseModel.ParameterDirection.ReturnValue)); try { List <byte> bytes = new List <byte>(new byte[] { 1, 2, 3, 4, 5 }); dynamic result = Execute(this.connectionType, "Test Proc", parameters, 1, 1.0, "Qwer", string.Empty, new DateTime(1987, 1, 24), DatabaseModel.DefaultDateTime, bytes, new List <byte>()).Value; Assert.AreEqual("Qwer", result.ResultParameters.StringValueOut); Assert.AreEqual(new DateTime(1987, 1, 24), result.ResultParameters.DateValueOut); Assert.AreEqual(bytes, result.ResultParameters.BytesValueOut); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestTableData() { var columns = DatabaseObjectCreation.CreateTestTable(this.connectionType); DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType); try { using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType))) { connection.Open(); Assert.IsTrue(databaseAssistant.GetTableNames(connection).Any(t => t.IndexOf("TestTable", StringComparison.InvariantCultureIgnoreCase) != -1)); } } finally { DatabaseObjectCreation.RemoveTestTable(this.connectionType); } Assert.AreEqual(5, columns.Count); var column = columns[0]; Assert.AreEqual("INTVALUE", column.Name.ToUpper()); Assert.AreEqual(DatabaseModel.DataType.Int32, column.DataType); Assert.IsFalse(column.IsNullable); column = columns[1]; Assert.AreEqual("DOUBLEVALUE", column.Name.ToUpper()); Assert.AreEqual(DatabaseModel.DataType.Double, column.DataType); Assert.IsTrue(column.IsNullable); column = columns[2]; Assert.AreEqual("STRINGVALUE", column.Name.ToUpper()); Assert.AreEqual(DatabaseModel.DataType.String, column.DataType); Assert.IsTrue(column.IsNullable); column = columns[3]; Assert.AreEqual("DATEVALUE", column.Name.ToUpper()); Assert.AreEqual(this.connectionType == ConnectionType.OleDb ? DatabaseModel.DataType.String : DatabaseModel.DataType.Date, column.DataType); Assert.IsTrue(column.IsNullable); column = columns[4]; Assert.AreEqual("BYTESVALUE", column.Name.ToUpper()); Assert.AreEqual(DatabaseModel.DataType.Binary, column.DataType); Assert.IsTrue(column.IsNullable); }
public void TestExecuteResultSetsFirstRow( [Values(OutputOption.FirstRow, OutputOption.FirstRowElseEmptyRow)] OutputOption outputOption) { var parameters = DatabaseObjectCreation.CreateResultSetsTestProc(this.connectionType); try { var result = Execute(this.connectionType, DatabaseHelpers.GetDefaultConnectionString(this.connectionType), "Test Proc", parameters, null, true, outputOption).Value; Assert.AreEqual(1, result.Result1.Counter); Assert.AreEqual("one", result.Result1.StringValue); Assert.AreEqual("qwer", result.Result2.StringValue); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestExecuteNoParametersAndReturnValue() { if (this.connectionType == ConnectionType.Oracle) { // Cannot return a value in an Oracle stored procedure - have to use OUT parameters return; } var parameters = DatabaseObjectCreation.CreateTestProc(@"CREATE PROCEDURE ""Test Proc"" AS BEGIN RETURN 1 END;", this.connectionType); try { dynamic result = Execute(this.connectionType, "Test Proc", parameters).Value; Assert.AreEqual(1, result.ResultParameters.RETURN_VALUE); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } }
public void TestExecuteWithTransaction(ConnectionType connectionType) { DatabaseObjectCreation.CreateTestTable(connectionType); try { string sql = @"INSERT INTO TestTable (IntValue) VALUES (@{Value})"; foreach (var transaction in new BeginTransaction.BeginTransactionX(connectionType, Helpers.DatabaseHelpers.GetDefaultConnectionString(connectionType), IsolationLevel.ReadCommitted)) { Execute(transaction, sql, new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 1, 0)); Execute(transaction, sql, new ParameterValue(ExecuteSQLShared.SqlValuePropertyPrefix + 1, 1)); } object rowCount = Helpers.DatabaseHelpers.ExecuteSqlScalar("SELECT COUNT(*) FROM TestTable", connectionType); int rowCountInt = connectionType == ConnectionType.Oracle ? (int)(decimal)rowCount : (int)rowCount; Assert.AreEqual(2, rowCountInt); } finally { DatabaseObjectCreation.RemoveTestTable(connectionType); } }
public void TestExecuteWithTransaction() { DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType); DatabaseObjectCreation.CreateTestProc(@"CREATE PROCEDURE ""Test Proc"" AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0); END;", this.connectionType); try { foreach (var transaction in new BeginTransaction.BeginTransactionX(this.connectionType, Helpers.DatabaseHelpers.GetDefaultConnectionString(this.connectionType), IsolationLevel.ReadCommitted)) { Execute(transaction, "Test Proc"); Execute(transaction, "Test Proc"); } DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType); Assert.AreEqual(2, results.Rows.Count); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); DatabaseObjectCreation.RemoveTestTable(this.connectionType); } }
public void TestExecuteWithTransactionAndResultSetOnException() { string createProcedureStatement = this.connectionType == ConnectionType.Oracle ? @"CREATE PROCEDURE ""Test Proc"" ( ResultSet OUT SYS_REFCURSOR ) AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0); OPEN ResultSet FOR SELECT 'qwer' AS ""StringValue"" FROM Dual; END;" : @"CREATE PROCEDURE ""Test Proc"" AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0); SELECT 'qwer' AS StringValue; END;" ; DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType); var parameters = DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType); try { Assert.Throws <Exception>(() => { foreach (var transaction in new BeginTransaction.BeginTransactionX(this.connectionType, Helpers.DatabaseHelpers.GetDefaultConnectionString(this.connectionType), IsolationLevel.ReadCommitted)) { var result = Execute(transaction, "Test Proc", parameters, new object[0], true, OutputOption.FirstRow).Value; Assert.AreEqual("qwer", result.Result.StringValue); throw new Exception("Bad things are happening!!"); } }); DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType); Assert.AreEqual(0, results.Rows.Count); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); DatabaseObjectCreation.RemoveTestTable(this.connectionType); } }
public void TestExecuteWithTransactionOnSqlError() { string createProcedureStatement = this.connectionType == ConnectionType.Oracle ? @"CREATE PROCEDURE ""Test Proc"" ( ""@IntValue"" INT ) AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0 / ""@IntValue""); END;" : @"CREATE PROCEDURE ""Test Proc"" ( @IntValue INT ) AS BEGIN INSERT INTO TestTable (IntValue) VALUES (0 / @IntValue); END;" ; DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType); var parameters = DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType); try { Assert.Catch <ExecuteException>(() => { foreach (var transaction in new BeginTransaction.BeginTransactionX(this.connectionType, Helpers.DatabaseHelpers.GetDefaultConnectionString(this.connectionType), IsolationLevel.ReadCommitted)) { Execute(transaction, "Test Proc", parameters, new object[] { 1 }); Execute(transaction, "Test Proc", parameters, new object[] { 0 }); } }); DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType); Assert.AreEqual(0, results.Rows.Count); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); DatabaseObjectCreation.RemoveTestTable(this.connectionType); } }
public void TestExecuteWhereInputParametersMatchFunctionPropertyNames() { DatabaseObjectCreation.CreateTestTable("CREATE TABLE TESTTABLE ( INTVALUE1 INT, INTVALUE2 INT)", this.connectionType); string createProcedureStatement = (this.connectionType == ConnectionType.Oracle) ? @"CREATE PROCEDURE ""Test Proc"" ( ""@{0}"" INT, ""@{1}"" INT ) AS BEGIN INSERT INTO TESTTABLE (""INTVALUE1"",""INTVALUE2"") VALUES (""@{0}"",""@{1}""); END;" : @"CREATE PROCEDURE ""Test Proc"" ( @{0} INT, @{1} INT ) AS BEGIN INSERT INTO TestTable (IntValue1, IntValue2) VALUES(@{0}, @{1}) END;" ; var parameters = new DatabaseModel.ProcedureParameters( DatabaseObjectCreation.CreateTestProc(string.Format(createProcedureStatement, ExecuteStoredProcedureShared.ParametersPropertyName, DbShared.TransactionPropertyName), connectionType) .Where(p => p.Direction != DatabaseModel.ParameterDirection.ReturnValue)); try { Execute(this.connectionType, "Test Proc", parameters, 1, 2); DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TESTTABLE", this.connectionType); Assert.AreEqual(1, results.Rows.Count); Assert.AreEqual(1, results.Rows[0][0]); Assert.AreEqual(2, results.Rows[0][1]); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); DatabaseObjectCreation.RemoveTestTable(this.connectionType); } }
public void TestGetResultSetForStoredProcedureDataDoesNotRunProcedure() { string createProcedureStatement = GetProcedureStatement(this.connectionType); DatabaseObjectCreation.CreateTestTable("CREATE TABLE TestTable ( IntValue INT )", this.connectionType); DatabaseObjectCreation.CreateTestProc(createProcedureStatement, this.connectionType); DatabaseModel.ResultSets resultSets; try { DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType); using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType))) { connection.Open(); Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc"))); resultSets = databaseAssistant.GetResultSets(connection, "Test Proc"); } DataTable results = DatabaseHelpers.GetDataTable("SELECT * FROM TestTable", this.connectionType); Assert.AreEqual(0, results.Rows.Count); } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); DatabaseObjectCreation.RemoveTestTable(this.connectionType); } Assert.AreEqual(1, resultSets.Count); var fields = resultSets[0].Fields; Assert.AreEqual(1, fields.Count); var field = fields[0]; Assert.AreEqual("StringValue", field.ColumnName); Assert.AreEqual("StringValue", field.OutputName); }
public void TestGetResultSetsForStoredProcedure() { var parameters = DatabaseObjectCreation.CreateParameterAndResultSetsTestProc(this.connectionType).Where(p => (p.Direction != DatabaseModel.ParameterDirection.ReturnValue) && (p.DataType != DatabaseModel.DataType.RefCursor)).ToList(); DatabaseModel.ResultSets resultSets; DatabaseAssistant databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(this.connectionType); try { using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(this.connectionType))) { connection.Open(); Assert.IsTrue(databaseAssistant.GetStoredProcedureNames(connection).Any(p => p.Contains("Test Proc"))); resultSets = databaseAssistant.GetResultSets(connection, "Test Proc"); } } finally { DatabaseObjectCreation.RemoveTestProc(this.connectionType); } Assert.AreEqual(8, parameters.Count); var parameter = parameters[0]; Assert.AreEqual("@IntValue", parameter.Name); Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction); Assert.AreEqual(DatabaseModel.DataType.Int32, parameter.DataType); parameter = parameters[1]; Assert.AreEqual("@DoubleValue", parameter.Name); Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction); Assert.AreEqual(DatabaseModel.DataType.Double, parameter.DataType); parameter = parameters[2]; Assert.AreEqual("@StringValue", parameter.Name); Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction); Assert.AreEqual(DatabaseModel.DataType.String, parameter.DataType); if (this.connectionType != ConnectionType.Oracle) { Assert.AreEqual(20, parameter.Size); } parameter = parameters[3]; Assert.AreEqual("@StringValueOut", parameter.Name); Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction); Assert.AreEqual(DatabaseModel.DataType.String, parameter.DataType); if (this.connectionType != ConnectionType.Oracle) { Assert.AreEqual(20, parameter.Size); } parameter = parameters[4]; Assert.AreEqual("@DateValue", parameter.Name); Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction); Assert.AreEqual(DatabaseModel.DataType.Date, parameter.DataType); parameter = parameters[5]; Assert.AreEqual("@DateValueOut", parameter.Name); Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction); Assert.AreEqual(DatabaseModel.DataType.Date, parameter.DataType); parameter = parameters[6]; Assert.AreEqual("@BytesValue", parameter.Name); Assert.AreEqual(DatabaseModel.ParameterDirection.In, parameter.Direction); Assert.AreEqual(DatabaseModel.DataType.Binary, parameter.DataType); if (this.connectionType != ConnectionType.Oracle) { Assert.AreEqual(50, parameter.Size); } parameter = parameters[7]; Assert.AreEqual("@BytesValueOut", parameter.Name); Assert.AreEqual(DatabaseModel.ParameterDirection.InOut, parameter.Direction); Assert.AreEqual(DatabaseModel.DataType.Binary, parameter.DataType); if (this.connectionType != ConnectionType.Oracle) { Assert.AreEqual(50, parameter.Size); } Assert.AreEqual(2, resultSets.Count); var fields = resultSets[0].Fields; Assert.AreEqual(1, fields.Count); var field = fields[0]; Assert.AreEqual("StringValue", field.ColumnName); Assert.AreEqual("StringValue", field.OutputName); fields = resultSets[1].Fields; Assert.AreEqual(2, fields.Count); field = fields[0]; Assert.AreEqual("Counter", field.ColumnName); Assert.AreEqual("Counter", field.OutputName); field = fields[1]; Assert.AreEqual("StringValue", field.ColumnName); Assert.AreEqual("StringValue", field.OutputName); Assert.AreEqual(DatabaseModel.DataType.String, field.DataType); }