Ejemplo n.º 1
0
        public void RunSProc(string instance, string databaseName, string sprocName, string username, string password)
        {
            // Create database connection
            var connection = string.IsNullOrEmpty(username) ? new DBConnectionInformation(instance, databaseName) : new DBConnectionInformation(instance, databaseName, username, password);
            var batch      = String.Format("EXEC {0}", sprocName);

            using (var block = new RedGate.Shared.SQL.ExecutionBlock.ExecutionBlock())
            {
                block.AddBatch(batch);
                var executor = new RedGate.Shared.SQL.ExecutionBlock.BlockExecutor();
                try
                {
                    executor.ExecuteBlock(block, connection);
                }
                catch (Exception)
                {
                }
            }
        }
Ejemplo n.º 2
0
        public void CreateTables(string instance, string databaseName, string username, string password)
        {
            // Create database connection
            var connection = string.IsNullOrEmpty(username) ? new DBConnectionInformation(instance, databaseName) : new DBConnectionInformation(instance, databaseName, username, password);

            var batch1 = @"IF OBJECT_ID(N'[dbo].[Employees]', 'U') IS NOT NULL
                           BEGIN
                                DROP TABLE Employees
                           END
                           CREATE TABLE Employees (
                                EmpId INT IDENTITY,
                                EmpName VARCHAR(16),
                                Phone VARCHAR(16)
                           )";

            var batch2 = @"INSERT INTO Employees (EmpName, Phone)
                            VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')";

            var batch3 = @"IF OBJECT_ID(N'[dbo].[Suppliers]', 'U') IS NOT NULL
                           BEGIN
                               DROP TABLE Suppliers
                           END                            
                           CREATE TABLE Suppliers(
                               SupplierId INT IDENTITY,
                               SupplierName VARCHAR(64),
                               Fax VARCHAR(16)
                           )";

            var batch4 = @"INSERT INTO Suppliers (SupplierName, Fax)
                           VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')";

            using (var block = new RedGate.Shared.SQL.ExecutionBlock.ExecutionBlock())
            {
                block.AddBatch(batch1);
                block.AddBatch(batch2);
                block.AddBatch(batch3);
                block.AddBatch(batch4);
                var executor = new RedGate.Shared.SQL.ExecutionBlock.BlockExecutor();
                executor.ExecuteBlock(block, connection);
            }
        }
Ejemplo n.º 3
0
        public void CreateSprocs(string instance, string databaseName, string username, string password)
        {
            // Create database connection
            var connection = string.IsNullOrEmpty(username) ? new DBConnectionInformation(instance, databaseName) : new DBConnectionInformation(instance, databaseName, username, password);
            var batch1     = @"IF OBJECT_ID(N'[dbo].[UserADeadlock]') IS NULL
                            EXEC sp_executesql	N'CREATE PROCEDURE UserADeadlock AS
	                            BEGIN
		                            BEGIN TRANSACTION
			                            UPDATE dbo.Employees SET EmpName = ''Mary'' WHERE EmpId = 1
			                            WAITFOR DELAY ''00:01''
			                            UPDATE dbo.Suppliers SET Fax = ''555-1212'' WHERE SupplierId = 1
		                            ROLLBACK
				
	                            END'"    ;

            var batch2 = @"IF OBJECT_ID(N'[dbo].[UserBDeadlock]') IS NULL
                            EXEC sp_executesql	N'CREATE PROCEDURE UserBDeadlock AS
		                        BEGIN
			                        BEGIN TRANSACTION
				                        UPDATE dbo.Suppliers SET Fax = ''555-1212'' WHERE SupplierId = 1
				                        WAITFOR DELAY ''00:01''
				                        UPDATE dbo.Employees SET EmpName = ''Mary'' WHERE EmpId = 1	
			                        ROLLBACK
		                        END'"        ;

            var batch3 = @"IF OBJECT_ID(N'[dbo].[UserABlocking]') IS NULL
                            EXEC sp_executesql	N'CREATE PROCEDURE UserABlocking AS
	                            BEGIN
		                            BEGIN TRANSACTION
			                            UPDATE dbo.Suppliers SET Fax = ''555-1212'' WHERE SupplierId = 1
	                                    WAITFOR DELAY ''00:10''
		                            ROLLBACK
				
	                            END'"    ;

            var batch4 = @"IF OBJECT_ID(N'[dbo].[UserBBlocking]') IS NULL
                            EXEC sp_executesql	N'CREATE PROCEDURE UserBBlocking AS
	                            BEGIN
		                            BEGIN TRANSACTION
			                            UPDATE dbo.Employees SET EmpName = ''Mary'' WHERE EmpId = 1	
	                                    UPDATE dbo.Suppliers SET Fax = ''555-1212'' WHERE SupplierId = 1
		                            ROLLBACK
				
	                            END'"    ;

            var batch5 = @"IF OBJECT_ID(N'[dbo].[UserCBlocking]') IS NULL
                            EXEC sp_executesql	N'CREATE PROCEDURE UserCBlocking AS
	                            BEGIN
		                            BEGIN TRANSACTION
			                            UPDATE dbo.Employees SET EmpName = ''Martha'' WHERE EmpId = 1
		                            ROLLBACK
				
	                            END'"    ;

            using (var block = new RedGate.Shared.SQL.ExecutionBlock.ExecutionBlock())
            {
                block.AddBatch(batch1);
                block.AddBatch(batch2);
                block.AddBatch(batch3);
                block.AddBatch(batch4);
                block.AddBatch(batch5);
                var executor = new RedGate.Shared.SQL.ExecutionBlock.BlockExecutor();
                executor.ExecuteBlock(block, connection);
            }
        }