Example #1
0
        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);
            }
        }
Example #2
0
        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);
            }
        }
Example #3
0
        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);
        }
Example #4
0
        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);
            }
        }
Example #5
0
        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);
            }
        }
Example #6
0
        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);
            }
        }
Example #7
0
        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);
            }
        }
Example #8
0
        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);
            }
        }
Example #9
0
        private static DatabaseModel.ResultSets FetchResultSets(ConnectionType connectionType)
        {
            var databaseAssistant = DatabaseAssistant.GetDatabaseAssistant(connectionType);

            using (var connection = databaseAssistant.CreateConnection(DatabaseHelpers.GetDefaultConnectionString(connectionType)))
            {
                connection.Open();
                return(databaseAssistant.GetResultSets(connection, "Test Proc"));
            }
        }
Example #10
0
        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);
        }
Example #11
0
        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);
            }
        }
Example #12
0
        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);
        }
Example #13
0
        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);
            }
        }
Example #14
0
        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);
        }
Example #15
0
 private static FunctionResult Execute(ConnectionType connectionType, string storedProcedureName, DatabaseModel.ProcedureParameters parameters = null, params object[] parameterValues)
 {
     return(Execute(connectionType, DatabaseHelpers.GetDefaultConnectionString(connectionType), storedProcedureName, parameters, parameterValues, false));
 }
Example #16
0
        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);
            }
        }
Example #17
0
        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);
        }
Example #18
0
        private FunctionResult Execute(DbBulkCopy.ConnectionType connectionType, string tableName)
        {
            string connectionString = DatabaseHelpers.GetDefaultConnectionString(ToCommonConnectionType(connectionType));

            return(Execute(connectionType, connectionString, tableName));
        }