C# SqlServer.Dispose方法代码示例

// ReSharper disable InconsistentNaming
        public void SqlServer_Connect_ConnectionStringIsInvalid_ThrowsArgumentException()
// ReSharper restore InconsistentNaming
        {
            //------------Setup for test--------------------------
            var sqlServer = new SqlServer();
            try
            {
                //------------Execute Test---------------------------
                sqlServer.Connect("xxx", CommandType.Text, null);

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

        public void SqlServer_Connect_InvalidLogin_ThrowsSqlException()
        {
            //------------Setup for test--------------------------
            var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
            dbSource.Password = Guid.NewGuid().ToString(); // Random invalid password

            var sqlServer = new SqlServer();
            try
            {
                //------------Execute Test---------------------------
                sqlServer.Connect(dbSource.ConnectionString);

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

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

            var sqlServer = new SqlServer();
            try
            {
                //------------Execute Test---------------------------
                sqlServer.Connect(dbSource.ConnectionString);

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

        // ReSharper disable InconsistentNaming
        public void SqlServer_FetchStoredProcedures_RollbackTransaction()
        // ReSharper restore InconsistentNaming
        {
            //------------Setup for test--------------------------
            var factory = new Mock<IDbFactory>();
            var mockCommand = new Mock<IDbCommand>();
            mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");
            var conn = new Mock<IDbConnection>();
            conn.Setup(a => a.State).Returns(ConnectionState.Open);
            var dbTran = new Mock<IDbTransaction>();
            conn.Setup(a => a.BeginTransaction()).Returns(dbTran.Object);
            factory.Setup(a => a.CreateConnection(It.IsAny<string>())).Returns(conn.Object);
            var sqlServer = new SqlServer(factory.Object);
            try
            {
                PrivateObject pvt = new PrivateObject(sqlServer);
                pvt.SetField("_connection", conn.Object);
                sqlServer.Connect("a");
                sqlServer.BeginTransaction();
                sqlServer.RollbackTransaction();
                factory.Verify(a => a.CreateConnection(It.IsAny<string>()));
                conn.Verify(a => a.Open());

                dbTran.Verify(a => a.Rollback());
                dbTran.Verify(a => a.Dispose());
                conn.Verify(a => a.BeginTransaction());

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

// 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_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_Connect_CommandTextIsNull_ThrowsArgumentNullException()
// ReSharper restore InconsistentNaming
        {
            //------------Setup for test--------------------------
            var sqlServer = new SqlServer();
            try
            {
                //------------Execute Test---------------------------
                sqlServer.Connect("Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;", CommandType.Text, null);

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

        // ReSharper disable InconsistentNaming
        public void SqlServer_FetchDataTable_OnException()
        // ReSharper restore InconsistentNaming
        {
            //------------Setup for test--------------------------
            var factory = new Mock<IDbFactory>();
            var mockCommand = new Mock<IDbCommand>();
       


            mockCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Throws(new DbEx("There is no text for object "));
            mockCommand.Setup(a => a.CommandText).Returns("Dave.Bob");

            var helpTextCommand = new Mock<IDbCommand>();
            helpTextCommand.Setup(a => a.ExecuteReader(It.IsAny<CommandBehavior>())).Throws(new DbEx("There is no text for object "));
            DataTable dt = new DataTable();
            dt.Columns.Add("database_name");
            dt.Rows.Add(new object[] { "Bob" });
            dt.Rows.Add(new object[] { "Dave" });
            factory.Setup(a => a.GetSchema(It.IsAny<IDbConnection>(), "Databases")).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---------------------------


                sqlServer.FetchDataTable(mockCommand.Object);
                factory.Verify(a=>a.CreateTable(It.IsAny<IDataReader>(),LoadOption.OverwriteChanges));
               


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

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

            var expected = new List<string>();
            using (var connection = new SqlConnection(dbSource.ConnectionString))
            {
                connection.Open();
                var databases = connection.GetSchema("Databases");
                connection.Close();

                var names = databases.Rows.Cast<DataRow>().Select(row => (row["database_name"] ?? string.Empty).ToString()).Distinct().OrderBy(s => s);
                expected.AddRange(names);
            }

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

                //------------Execute Test---------------------------
                var actual = sqlServer.FetchDatabases();

                //------------Assert Results-------------------------
                CollectionAssert.AreEqual(expected, actual);
            }
            finally
            {
                sqlServer.Dispose();
            }
        }

        public void SqlServer_Connect_UserIDInDomainFormat_ConnectionStringIsNotIntegratedSecurityEqualsSSPI()
        {
            //------------Setup for test--------------------------
            var dbSource = SqlServerTestUtils.CreateDev2TestingDbSource();
            dbSource.UserID = "Dev2\\TestUser";

            var sqlServer = new SqlServer();
            try
            {
                //------------Execute Test---------------------------
                try
                {
                    // expect this call to throw a 'login failed' exception
                    sqlServer.Connect(dbSource.ConnectionString);
                }
                // ReSharper disable EmptyGeneralCatchClause
                catch
                // ReSharper restore EmptyGeneralCatchClause
                {
                }

                //------------Assert Results-------------------------
                Assert.IsFalse(sqlServer.ConnectionString.Contains("Integrated Security=SSPI"));
            }
            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);
        }

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

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

                //------------Execute Test---------------------------
                var command = sqlServer.CreateCommand();

                //------------Assert Results-------------------------
                Assert.IsNotNull(command);
                Assert.IsNotNull(command.Transaction);
            }
            finally
            {
                sqlServer.RollbackTransaction();
                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();
            }
        }