C# SqlServer.FetchStoredProcedures方法代码示例

// ReSharper disable InconsistentNaming
        public void SqlServer_FetchStoredProcedures_FuncReturnsSPs()
// ReSharper restore InconsistentNaming
        {
            //------------Setup for test--------------------------
            var factory = new Mock<IDbFactory>();
            var mockCommand = new Mock<IDbCommand>();
            var mockReader = new Mock<IDataReader>();
            var queue = new Queue<DataTable>();

            mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(mockReader.Object);
            mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");

            var helpTextCommand = new Mock<IDbCommand>();
            helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(new Mock<IDataReader>().Object);
            var somethingAdded = false;
            var funcAdded = false;
            factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, GlobalConstants.SchemaQuery)).Returns(mockCommand.Object);
            factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.StoredProcedure, "Dave.Bob")).Returns(mockCommand.Object);
            factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, "sp_helptext 'Dave.Bob'")).Returns(helpTextCommand.Object);
            DataTable dt = new DataTable();
            dt.Columns.Add("ROUTINE_NAME");
            dt.Columns.Add("ROUTINE_TYPE");
            dt.Columns.Add("SPECIFIC_SCHEMA");
            dt.Rows.Add(new object[] { "Bob", "SQL_SCALAR_FUNCTION", "Dave" });
            queue.Enqueue(dt);

            queue.Enqueue(new DataTable()); // no params

            factory.Setup(a => a.CreateTable(mockReader.Object, LoadOption.OverwriteChanges)).Returns(queue.Dequeue);
            var conn = new Mock<IDbConnection>();
            conn.Setup(a => a.State).Returns(ConnectionState.Open);
            var sqlServer = new SqlServer(factory.Object);
            try
            {
                PrivateObject pvt = new PrivateObject(sqlServer);
                pvt.SetField("_connection", conn.Object);
                //------------Execute Test---------------------------
                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procProcessor = (command, list, arg3, a) =>
                {
                    somethingAdded = true; return true;
                };
                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> funcProcessor = (command, list, arg3, a) =>
                {
                    funcAdded = true; return true;
                };

                sqlServer.FetchStoredProcedures(procProcessor, funcProcessor);
                Assert.IsTrue(funcAdded);
                Assert.IsFalse(somethingAdded);



                //------------Assert Results-------------------------
            }
            finally
            {
                sqlServer.Dispose();
            }
        }

// ReSharper disable InconsistentNaming
        public void SqlServer_FetchStoredProcedures_TableValuesProcWithParamsFuncReturnsSPs()
// ReSharper restore InconsistentNaming
        {
            //------------Setup for test--------------------------
            var factory = new Mock<IDbFactory>();
            var mockCommand = new Mock<IDbCommand>();
            var mockReader = new Mock<IDataReader>();
            var queue = new Queue<DataTable>();
            var param = new Mock<IDataParameterCollection>();
            mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(mockReader.Object);
            mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
            mockCommand.Setup(a => a.Parameters).Returns(param.Object);
            var helpTextCommand = new Mock<IDbCommand>();
            helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(new Mock<IDataReader>().Object);
            var somethingAdded = false;
            var funcAdded = false;
            factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, GlobalConstants.SchemaQuery)).Returns(mockCommand.Object);
            factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.StoredProcedure, "Dave.Bob")).Returns(mockCommand.Object);
            factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, "sp_helptext 'Dave.Bob'")).Returns(helpTextCommand.Object);
            DataTable dt = new DataTable();

            dt.Columns.Add("ROUTINE_NAME");
            dt.Columns.Add("ROUTINE_TYPE");
            dt.Columns.Add("SPECIFIC_SCHEMA");
            dt.Rows.Add(new object[] { "Bob", "SQL_TABLE_VALUED_FUNCTION", "Dave" });
            queue.Enqueue(dt);

            var dtParams = new DataTable();
            dtParams.Columns.Add("PARAMETER_NAME");
            dtParams.Columns.Add("DATA_TYPE");
            dtParams.Columns.Add("CHARACTER_MAXIMUM_LENGTH", typeof(int));
            dtParams.Rows.Add(new object[] { "@moo", SqlDbType.VarChar, 25 });
            queue.Enqueue(dtParams); // no params

            factory.Setup(a => a.CreateTable(It.IsAny<IDataReader>(), It.IsAny<LoadOption>())).Returns(queue.Dequeue);
            var conn = new Mock<IDbConnection>();
            conn.Setup(a => a.State).Returns(ConnectionState.Open);
            var sqlServer = new SqlServer(factory.Object);
            try
            {
                PrivateObject pvt = new PrivateObject(sqlServer);
                pvt.SetField("_connection", conn.Object);
                //------------Execute Test---------------------------
                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procProcessor = (command, list, arg3, a) =>
                {
                    somethingAdded = true; return true;
                };
                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> funcProcessor = (command, list, arg3, a) =>
                {
                    Assert.AreEqual("select * from Dave.Bob(@moo)", a);
                    funcAdded = true; return true;
                };

                sqlServer.FetchStoredProcedures(procProcessor, funcProcessor);
                Assert.IsTrue(funcAdded);
                Assert.IsFalse(somethingAdded);
                param.Verify(a=>a.Add(It.IsAny<object>()),Times.Once());


                //------------Assert Results-------------------------
            }
            finally
            {
                sqlServer.Dispose();
            }
        }

// ReSharper disable InconsistentNaming
        public void SqlServer_FetchStoredProcedures_EmptyReturnsNothing()
// ReSharper restore InconsistentNaming
        {
            //------------Setup for test--------------------------
            var factory = new Mock<IDbFactory>();
            var mockCommand = new Mock<IDbCommand>();
            var mockReader = new Mock<IDataReader>();
            mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Returns(mockReader.Object);
            var somethingAdded = false;
            factory.Setup(a => a.CreateCommand(It.IsAny<IDbConnection>(), CommandType.Text, GlobalConstants.SchemaQuery)).Returns(mockCommand.Object);
            DataTable dt = new DataTable();
            dt.Columns.Add("ROUTINE_NAME");
            dt.Columns.Add("ROUTINE_TYPE");
            dt.Columns.Add("SPECIFIC_SCHEMA");
            factory.Setup(a => a.CreateTable(mockReader.Object, LoadOption.OverwriteChanges)).Returns(dt);
            var conn = new Mock<IDbConnection>();
            conn.Setup(a => a.State).Returns(ConnectionState.Open);
            var sqlServer = new SqlServer(factory.Object);
            try
            {
                PrivateObject pvt = new PrivateObject(sqlServer);
                pvt.SetField("_connection",conn.Object);
                //------------Execute Test---------------------------
                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procProcessor = (command, list, arg3, a) => { somethingAdded = true; return true; };

                 sqlServer.FetchStoredProcedures(procProcessor,procProcessor);
                Assert.IsFalse(somethingAdded);

                

                //------------Assert Results-------------------------
            }
            finally
            {
                sqlServer.Dispose();
            }
        }

// ReSharper disable InconsistentNaming
        public void SqlServer_FetchStoredProcedures_FunctionProcessorIsNull_ThrowsArgumentNullException()
// ReSharper restore InconsistentNaming
        {
            //------------Setup for test--------------------------
            var sqlServer = new SqlServer();
            try
            {
                //------------Execute Test---------------------------
                Func<IDbCommand, List<IDbDataParameter>, string,string, bool> procProcessor = (command, list, arg3,a) => false;

                sqlServer.FetchStoredProcedures(procProcessor, null);

                //------------Assert Results-------------------------
            }
            finally
            {
                sqlServer.Dispose();
            }
        }

        public void SqlServer_FetchTableValuedFunctions_AssertSelectTextIsDifferent()
        {
            //------------Setup for test--------------------------
            var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();

            List<IDbDataParameter> procedureCommandParameters = null;
            string procedureHelpText = null;
            string select = "";
            var sqlServer = new SqlServer();
            try
            {
                sqlServer.Connect(dbSource.ConnectionString);

                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> functionProcessor = (dbCommand, list, helpText, bob) =>
                {
                    if (dbCommand.CommandText == "dbo.bob")
                    {
                        procedureCommandParameters = list;
                        procedureHelpText = helpText;
                        select = bob;
                    }
                    return true;
                };
                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procedureProcessor = (dbCommand, list, helpText, bob) => true;

                //------------Execute Test---------------------------
                sqlServer.FetchStoredProcedures(procedureProcessor, functionProcessor, true);
            }
            finally
            {
                sqlServer.Dispose();
            }

            //------------Assert Results-------------------------
            Assert.AreEqual("@country", procedureCommandParameters.First().ParameterName);
            Assert.IsTrue(procedureHelpText.Contains(@"insert into @Countries
	select CountryID from dbo.Country"));
            Assert.AreEqual("select * from dbo.bob(@country)", select);
        }

        public void SqlServer_FetchStoredProcedures_WithClrTypeStoredProcedure_CorrectDataReturned()
        {
            //------------Setup for test--------------------------
            var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();

            List<IDbDataParameter> procedureCommandParameters = null;
            string procedureHelpText = null;

            var sqlServer = new SqlServer();
            try
            {
                sqlServer.Connect(dbSource.ConnectionString);

                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procedureProcessor = (dbCommand, list, helpText, bob) =>
                {
                    if (dbCommand.CommandText == "Warewolf.RunWorkflowForSql")
                    {
                        procedureCommandParameters = list;
                        procedureHelpText = helpText;
                    }
                    return true;
                };
                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> functionProcessor = (dbCommand, list, helpText, bob) => true;

                //------------Execute Test---------------------------
                sqlServer.FetchStoredProcedures(procedureProcessor, functionProcessor, true);
            }
            finally
            {
                sqlServer.Dispose();
            }

            //------------Assert Results-------------------------
            SqlServerTestUtils.Verify_FetchStoredProcedures_WarewolfRunForSql(procedureCommandParameters, procedureHelpText);
        }

        public void SqlServer_FetchStoredProcedures_CorrectDataReturned()
        {
            //------------Setup for test--------------------------
            var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();

            IDbCommand procedureCommand = null;
            List<IDbDataParameter> procedureCommandParameters = null;
            string procedureHelpText = null;

            IDbCommand functionCommand = null;
            List<IDbDataParameter> functionCommandParameters = null;
            string functionHelpText = null;

            var sqlServer = new SqlServer();
            try
            {
                sqlServer.Connect(dbSource.ConnectionString);

                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> procedureProcessor = (dbCommand, list, helpText, bob) =>
                {
                    if (dbCommand.CommandText == "dbo.Pr_CitiesGetCountries")
                    {
                        procedureCommand = dbCommand;
                        procedureCommandParameters = list;
                        procedureHelpText = helpText;
                    }
                    return true;
                };
                Func<IDbCommand, List<IDbDataParameter>, string, string, bool> functionProcessor = (dbCommand, list, helpText, bob) =>
                {
                    if (dbCommand.CommandText == "dbo.fn_Greeting")
                    {
                        functionCommand = dbCommand;
                        functionCommandParameters = list;
                        functionHelpText = helpText;
                    }
                    return true;
                };

                //------------Execute Test---------------------------
                sqlServer.FetchStoredProcedures(procedureProcessor, functionProcessor, true);
            }
            finally
            {
                sqlServer.Dispose();
            }

            //------------Assert Results-------------------------
            SqlServerTestUtils.Verify_FetchStoredProcedures_Pr_CitiesGetCountries(procedureCommand, procedureCommandParameters, procedureHelpText);
            SqlServerTestUtils.Verify_FetchStoredProcedures_Fn_Greeting(functionCommand, functionCommandParameters, functionHelpText);
        }