Ejemplo n.º 1
0
 public void Initialize()
 {
     using (var dbContext = DataContextCreator.CreateTestContext())
     {
         SqlScriptRunner.RunAddTestDataScript(dbContext);
     }
 }
Ejemplo n.º 2
0
 public void Dispose()
 {
     SqlScriptRunner.ExecCommand(_connection, "DROP SCHEMA public CASCADE;");
     SqlScriptRunner.ExecCommand(_connection, "CREATE SCHEMA public;");
     SqlScriptRunner.ExecCommand(_connection, "GRANT ALL ON SCHEMA public TO postgres;");
     SqlScriptRunner.ExecCommand(_connection, "GRANT ALL ON SCHEMA public TO public;");
 }
Ejemplo n.º 3
0
        public void TestCleanWithSkip()
        {
            seedFKData(GetConnection(), GetDbType());

            DbDataReader reader = SqlScriptRunner.ExecCommandWithDataReader(GetConnection(), "SELECT * FROM Bar;");

            Assert.True(reader.HasRows);
            reader.Close();

            reader = SqlScriptRunner.ExecCommandWithDataReader(GetConnection(), "SELECT * FROM Foo;");
            Assert.True(reader.HasRows);
            reader.Close();

            //clean all except Foo
            CleanDb(new List <string>()
            {
                "Foo"
            });
            reader = SqlScriptRunner.ExecCommandWithDataReader(GetConnection(), "SELECT * FROM Foo;");
            Assert.True(reader.HasRows);
            reader.Close();

            reader = SqlScriptRunner.ExecCommandWithDataReader(GetConnection(), "SELECT * FROM Bar;");
            Assert.False(reader.HasRows);
            reader.Close();
        }
 public void SetUp()
 {
     _context    = new ApplicationDbContext();
     _repository = new Infrastructure.Data.StoredProcedureRepository(_context);
     SqlScriptRunner.SetUpDatabase();
     SeedDatabase();
 }
Ejemplo n.º 5
0
        public void TestScriptCollectionsExpansionWithChanges()
        {
            Stream stream       = UnitTestHelper.UnpackEmbeddedResource("Scripting.TestTemplateSqlScript.txt");
            var    scriptRunner = new SqlScriptRunner(stream, Encoding.UTF8);

            Assert.AreEqual(5, scriptRunner.TemplateParameters.Count,
                            "Not the expected number of template parameters. Make sure it merges correctly.");

            string expectedDefault =
                UnitTestHelper.UnpackEmbeddedResource("Scripting.TestTemplateSqlScriptExpectedChanges.txt",
                                                      Encoding.UTF8);

            scriptRunner.TemplateParameters["subtext_db_name"].Value   = "SubtextDB";
            scriptRunner.TemplateParameters["dottext_db_name"].Value   = "dbDotText";
            scriptRunner.TemplateParameters["dotTextDbUser"].Value     = "haacked";
            scriptRunner.TemplateParameters["someOtherTemplate"].Value = "NotABlogId";

            string expected = expectedDefault.Trim();
            string result   = scriptRunner.ScriptCollection.ExpandedScriptText.Trim();

            expected = expected.Replace("" + (char)13, ""); //Ugly hack!  I know. I'll Explain later.
            result   = result.Replace("" + ((char)13), ""); //Ugly hack!  I know. I'll Explain later.

            UnitTestHelper.AssertStringsEqualCharacterByCharacter(expected, result);
        }
Ejemplo n.º 6
0
 protected void lbtGenerateTestData_Click(object sender, EventArgs e)
 {
     SqlScriptRunner.RunScript(Server.MapPath("~/App_Data/SQL/Data/Clean-Data.sql"));
     SqlScriptRunner.RunScript(Server.MapPath("~/App_Data/SQL/Data/Create-Data.sql"));
     ltlMessage.Text = MessageFormatter.GetFormattedSuccessMessage("Test Data Generated.");
     Page.DataBind();
 }
Ejemplo n.º 7
0
 public void TearDown()
 {
     _context.Dispose();
     _sqlParameterFactory    = null;
     _storedProcedureFactory = null;
     SqlScriptRunner.ClearDatabase();
 }
Ejemplo n.º 8
0
 public void Initialize()
 {
     using (var dbContext = new brothershipEntities(ConnectionStrings.TEST_CONNECTION_STRING_NAME))
     {
         SqlScriptRunner.RunAddTestDataScript(dbContext);
     }
 }
Ejemplo n.º 9
0
        public void SqlScriptRunner_RunWithErrors()
        {
            SqlTestDatabase dbTest;
            SqlConnection   con;

            QueryDisposition[] dispositions;

            using (dbTest = SqlTestDatabase.Create())
            {
                con = new SqlConnection(dbTest.ConnectionInfo);
                con.Open();

                try
                {
                    dispositions = new SqlScriptRunner(CreateSchema).Run(con, true);
                    Assert.AreEqual(3, dispositions.Length);
                    for (int i = 0; i < dispositions.Length; i++)
                    {
                        Assert.IsNull(dispositions[i].Exception);
                        Assert.IsNull(dispositions[i].Message);
                    }

                    dispositions = new SqlScriptRunner("select * from bar\r\ngo\r\nselect * from foo\r\ngo\r\n").Run(con, true);
                    Assert.AreEqual(2, dispositions.Length);
                    Assert.IsNotNull(dispositions[0].Exception);
                    Assert.IsNull(dispositions[1].Exception);

                    dispositions = new SqlScriptRunner("select * from bar\r\ngo\r\nselect * from foo\r\ngo\r\n").Run(con, false);
                    Assert.AreEqual(1, dispositions.Length);
                    Assert.IsNotNull(dispositions[0].Exception);

                    dispositions = new SqlScriptRunner("select * from foo\r\ngo\r\nselect * from bar\r\ngo\r\n").Run(con, true);
                    Assert.AreEqual(2, dispositions.Length);
                    Assert.IsNull(dispositions[0].Exception);
                    Assert.IsNotNull(dispositions[1].Exception);

                    dispositions = new SqlScriptRunner("select * from foo\r\ngo\r\nselect * from bar\r\ngo\r\n").Run(con, false);
                    Assert.AreEqual(2, dispositions.Length);
                    Assert.IsNull(dispositions[0].Exception);
                    Assert.IsNotNull(dispositions[1].Exception);

                    dispositions = new SqlScriptRunner(DeleteSchema).Run(con, true);
                    Assert.AreEqual(2, dispositions.Length);
                    for (int i = 0; i < dispositions.Length; i++)
                    {
                        Assert.IsNull(dispositions[i].Exception);
                        Assert.IsNull(dispositions[i].Message);
                    }
                }
                finally
                {
                    new SqlScriptRunner(DeleteSchema).Run(con, true);
                    con.Close();
                }
            }
        }
Ejemplo n.º 10
0
        /// <summary>
        /// Grants access to the database.
        /// </summary>
        private void GrantOnly()
        {
            PackageEntry  schemaFile  = package["/Schema/Schema.sql"];
            PackageEntry  delProcFile = package["/Schema/DeleteProcs.sql"];
            PackageEntry  grantFile   = package["/Schema/GrantAccess.sql"];
            PackageEntry  funcFolder  = package["/Funcs"];
            PackageEntry  procFolder  = package["/Procs"];
            string        script;
            string        cs;
            SqlConnection sqlCon;

            QueryDisposition[] qd;

            if (schemaFile == null || !schemaFile.IsFile)
            {
                throw new InvalidOperationException("Invalid Database Package: /Schema/Schema.sql missing.");
            }

            if (delProcFile == null || !delProcFile.IsFile)
            {
                throw new InvalidOperationException("Invalid Database Package: /Schema/DeleteProcs.sql missing.");
            }

            if (grantFile == null || !grantFile.IsFile)
            {
                throw new InvalidOperationException("Invalid Database Package: /Schema/GrantAccess.sql missing.");
            }

            cs     = string.Format("server={0};database={1};{2}", server, database, dbParams.AdminSecurity);
            sqlCon = new SqlConnection(cs);
            sqlCon.Open();

            try
            {
                // Grant access to the application account

                if (account == null)
                {
                    script = Helper.FromAnsi(grantFile.GetContents());
                    script = script.Replace("%account%", account);
                    qd     = new SqlScriptRunner(script).Run(sqlCon, true);

                    for (int i = 0; i < qd.Length; i++)
                    {
                        if (qd[i].Message != null)
                        {
                            throw new InvalidOperationException(qd[i].Message);
                        }
                    }
                }
            }
            finally
            {
                sqlCon.Close();
            }
        }
Ejemplo n.º 11
0
        /// <summary>
        /// Executes the script.
        /// </summary>
        /// <remarks>
        /// Use script.Execute(transaction) to do the work. We will also pull the
        /// status of our script exection from here.
        /// </remarks>
        /// <param name="scriptName">Name of the script.</param>
        /// <param name="transaction">The current transaction.</param>
        /// <param name="dbUserName">Name of the DB owner.</param>
        public static void ExecuteScript(string scriptName, SqlTransaction transaction, string dbUserName)
        {
            var scriptRunner = new SqlScriptRunner(UnpackEmbeddedScript(scriptName), Encoding.UTF8);

            if (!string.IsNullOrEmpty(dbUserName))
            {
                scriptRunner.TemplateParameters.SetValue("dbUser", dbUserName);
            }
            scriptRunner.Execute(transaction);
        }
Ejemplo n.º 12
0
        private static Task ExecuteNonQuery(SqlConnection cnx, string sql)
        {
            var runner = new SqlScriptRunner(sql);

            using (var transaction = cnx.BeginTransaction())
            {
                runner.Execute(transaction);
                transaction.Commit();
            }

            return(Task.CompletedTask);
        }
Ejemplo n.º 13
0
        public void SetUp()
        {
            var builder = new DbContextOptionsBuilder <ApplicationDbContext>()
                          .UseSqlServer(ConnectionStringProvider.GetConnectionString());

            _context                = new ApplicationDbContext(builder.Options);
            _sqlParameterFactory    = new SqlParameterFactory(new CollectionToDataTableConverter());
            _storedProcedureFactory = new StoredProcedureFactory(_sqlParameterFactory);

            SqlScriptRunner.SetUpDatabase();
            SeedDatabase();
        }
Ejemplo n.º 14
0
        public void RunAll()
        {
            var jsonSettings = new JsonSerializerSettings
            {
                ContractResolver = new CamelCasePropertyNamesContractResolver()
            };

            try
            {
                // Backup SQL Server files.
                var backupScriptPath = ConfigurationManager.AppSettings["SqlScriptPath"];
                _log.LogEntries.Add(new ActivityLog.LogEntry(DateTime.Now, "Backing up SQL Server files to " + backupScriptPath));
                var scriptRunner = new SqlScriptRunner(backupScriptPath);
                scriptRunner.Run();
                _log.LogEntries.Add(new ActivityLog.LogEntry(DateTime.Now, "Done Backing up SQL Server files."));

                // Delete backups over x days old.
                _log.LogEntries.Add(new ActivityLog.LogEntry(DateTime.Now, "Cleaning up files older than " + ConfigurationManager.AppSettings["MaxAgeOfBackupsInDays"] + " days."));
                var cleaner = new FolderCleaner(ConfigurationManager.AppSettings["SqlBackupPath"]);
                cleaner.Run();
                _log.LogEntries.Add(new ActivityLog.LogEntry(DateTime.Now, "Done cleaning up files. " + cleaner.DeletedFiles.Count + " files were deleted."));
                if (cleaner.DeletedFiles.Count > 0)
                {
                    _log.LogEntries.Add(new ActivityLog.LogEntry(DateTime.Now, JsonConvert.SerializeObject(cleaner.DeletedFiles)));
                }
            }
            catch (Exception ex)
            {
                _log.LogEntries.Add(new ActivityLog.LogEntry(DateTime.Now, "Error Occurred", true));
                _log.LogEntries.Add(new ActivityLog.LogEntry(DateTime.Now, JsonConvert.SerializeObject(ex, Formatting.Indented, jsonSettings), true));
                throw ex;
            }
            finally
            {
                // TODO: Email a confirmation that this task was performed.
                var msg = new MailMessage();
                msg.To.Add(new MailAddress(ConfigurationManager.AppSettings["NotificationRecipient"]));
                msg.From    = new MailAddress(ConfigurationManager.AppSettings["NotificationFromAddress"]);
                msg.Subject = ConfigurationManager.AppSettings["NotificationSubject"];

                if (_log.HasErrors)
                {
                    msg.Subject  = "Error Occurred " + msg.Subject;
                    msg.Priority = MailPriority.High;
                }

                msg.Body       = GetEmailBody();
                msg.IsBodyHtml = false;
                var client = new SmtpClient();
                client.Send(msg);
            }
        }
Ejemplo n.º 15
0
 protected void buttonInstall_Click(object sender, EventArgs e)
 {
     try
     {
         SqlScriptRunner.RunScript(Server.MapPath("~") + @"\App_Data\SQL\Schema\Create-Schema.sql");
         SqlScriptRunner.RunScript(Server.MapPath("~") + @"\App_Data\SQL\Data\Create-Data.sql");
         labelMessage.Text = MessageFormatter.GetFormattedSuccessMessage("Congratulations! Database installation successful. Click <a href=\"../../../default.aspx\">here </a>to start using Employee Info Starter Kit.");
     }
     catch (Exception ex)
     {
         labelMessage.Text = MessageFormatter.GetFormattedErrorMessage("Database installation failed. Please provide a appropriate creadential that has permission to install database. <br>" + ex.ToString());
     }
 }
Ejemplo n.º 16
0
        public void TestScriptCollectionsDefaultExpansion()
        {
            Stream stream       = UnitTestHelper.UnpackEmbeddedResource("Scripting.TestTemplateSqlScript.txt");
            var    scriptRunner = new SqlScriptRunner(stream, Encoding.UTF8);

            Assert.AreEqual(5, scriptRunner.TemplateParameters.Count,
                            "Not the expected number of template parameters. Make sure it merges correctly.");

            string expectedDefault =
                UnitTestHelper.UnpackEmbeddedResource("Scripting.TestTemplateSqlScriptExpectedDefault.txt",
                                                      Encoding.UTF8);

            Assert.AreEqual(expectedDefault, scriptRunner.ScriptCollection.ExpandedScriptText);
        }
        ////////////////////////////////////////////////////////////////
        /// <summary>
        /// Initializes underlying storage.
        /// </summary>
        /// <remarks>
        /// Storage will be created and initialized or
        /// recreated if existed before.
        ///
        /// This implementation uses <see cref="SqlScriptRunner"/> to
        /// execute DB creation script.
        ///
        /// DB creation script shall be specified during
        /// provider creation.
        /// </remarks>
        public void InitializeStorage()
        {
            if (m_createDbScriptPath == null)
            {
                throw new Exception("Database creation script wasn't specified");
            }

            string connString;

            // Use DbManager.Connection to access connection string
            using (DbManager db = new DbManager())
            {
                connString = db.Connection.ConnectionString;
            }
            // Use script runner to execute script
            SqlScriptRunner runner = new SqlScriptRunner(connString);

            runner.Execute(m_createDbScriptPath);
        }
Ejemplo n.º 18
0
        public void TestAllClean()
        {
            seedFKData(GetConnection(), GetDbType());

            DbDataReader reader = SqlScriptRunner.ExecCommandWithDataReader(GetConnection(), "SELECT * FROM Bar;");

            Assert.True(reader.HasRows);
            reader.Close();

            reader = SqlScriptRunner.ExecCommandWithDataReader(GetConnection(), "SELECT * FROM Foo;");
            Assert.True(reader.HasRows);
            reader.Close();

            //clean all
            CleanDb(null);
            reader = SqlScriptRunner.ExecCommandWithDataReader(GetConnection(), "SELECT * FROM Foo;");
            Assert.False(reader.HasRows);
            reader.Close();

            reader = SqlScriptRunner.ExecCommandWithDataReader(GetConnection(), "SELECT * FROM Bar;");
            Assert.False(reader.HasRows);
            reader.Close();
        }
Ejemplo n.º 19
0
        public static void seedFKData(DbConnection connection, DatabaseType type = DatabaseType.H2)
        {
            SqlScriptRunner.ExecCommand(connection, "CREATE TABLE Foo(x int, primary key (x));");
            SqlScriptRunner.ExecCommand(connection, "CREATE TABLE Bar(y int, primary key (y));");

            switch (type)
            {
            case DatabaseType.MS_SQL_SERVER:
            case DatabaseType.POSTGRES:
                SqlScriptRunner.ExecCommand(connection, "alter table Bar add constraint FK foreign key (y) references Foo;");
                break;

            case DatabaseType.MYSQL:
                SqlScriptRunner.ExecCommand(connection, "alter table Bar add foreign key (y) references Foo(x);");
                break;

            default:
                throw new InvalidOperationException("NOT SUPPORT");
            }

            SqlScriptRunner.ExecCommand(connection, "INSERT INTO Foo (x) VALUES (42)");
            SqlScriptRunner.ExecCommand(connection, "INSERT INTO Bar (y) VALUES (42)");
        }
Ejemplo n.º 20
0
        /// <summary>
        /// Sets up the database.
        /// </summary>
        internal static void SetupDatabase()
        {
            var connectionString = GetConnectionString();

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                var createDbStmt = GetTextFromEmbededFile("DataAccess.Sql.Test.Scripts.CreateDb.sql");
                using (var cmd = new SqlCommand(createDbStmt, connection))
                {
                    cmd.ExecuteNonQuery();
                }

                var queryString = GetTextFromEmbededFile("DataAccess.Sql.Test.Scripts.SetupDb.sql");
                var runner      = new SqlScriptRunner(queryString);

                using (var transaction = connection.BeginTransaction())
                {
                    runner.Execute(transaction);
                    transaction.Commit();
                }
            }
        }
Ejemplo n.º 21
0
        public void SqlScriptRunner_Parse()
        {
            SqlScriptRunner runner;

            runner = new SqlScriptRunner("");
            Assert.AreEqual(0, runner.Count);

            runner = new SqlScriptRunner("create database foo");
            Assert.AreEqual(1, runner.Count);
            Assert.AreEqual("create database foo\r\n", runner[0]);

            runner = new SqlScriptRunner("create database foo\r\ngo");
            Assert.AreEqual(1, runner.Count);
            Assert.AreEqual("create database foo\r\n", runner[0]);

            runner = new SqlScriptRunner("create database foo\r\ngo\r\n");
            Assert.AreEqual(1, runner.Count);
            Assert.AreEqual("create database foo\r\n", runner[0]);

            runner = new SqlScriptRunner("create database foo\r\ngo\r\n\r\n");
            Assert.AreEqual(1, runner.Count);
            Assert.AreEqual("create database foo\r\n", runner[0]);

            runner = new SqlScriptRunner("create database foo\r\ndrop database foo\r\n    go    \r\n\r\n");
            Assert.AreEqual(1, runner.Count);
            Assert.AreEqual("create database foo\r\ndrop database foo\r\n", runner[0]);

            runner = new SqlScriptRunner("create database foo\r\ngo\r\ndrop database foo\r\ngo");
            Assert.AreEqual(2, runner.Count);
            Assert.AreEqual("create database foo\r\n", runner[0]);
            Assert.AreEqual("drop database foo\r\n", runner[1]);

            runner = new SqlScriptRunner("create database foo\r\n    GO    \r\ndrop database foo\r\nGO");
            Assert.AreEqual(2, runner.Count);
            Assert.AreEqual("create database foo\r\n", runner[0]);
            Assert.AreEqual("drop database foo\r\n", runner[1]);
        }
Ejemplo n.º 22
0
        public void Initialize()
        {
            Helper.InitializeApp(Assembly.GetExecutingAssembly());

            this.ADSettings   = new ADTestSettings();
            this.DB           = SqlTestDatabase.Create();
            this.AuthFilePath = Path.GetTempFileName();

            //-------------------------------------------------------------
            // Initialize file authentication

            Helper.WriteToFile(this.AuthFilePath, @"

file.com;file1;file-password1
file.com;file2;file-password2
");
            this.Accounts.Add(new AuthTestAccount(AuthTestExtensionType.File, "file.com", "file1", "file-password1"));
            this.Accounts.Add(new AuthTestAccount(AuthTestExtensionType.File, "file.com", "file2", "file-password2"));

            //-------------------------------------------------------------
            // Initialize RADIUS authentication

            RadiusServerSettings radiusSettings = new RadiusServerSettings();

            radiusSettings.NetworkBinding = NetworkBinding.Parse("ANY:52111");
            radiusSettings.Devices.Add(new RadiusNasInfo(IPAddress.Loopback, this.RadiusSecret));
            radiusSettings.Devices.Add(new RadiusNasInfo(NetHelper.GetActiveAdapter(), this.RadiusSecret));

            this.RadiusServer = new RadiusServer();
            this.RadiusServer.Start(radiusSettings);
            this.RadiusServer.LoadAccountsFromString(@"

radius.com;radius1;radius-password1
radius.com;radius2;radius-password2
");
            this.Accounts.Add(new AuthTestAccount(AuthTestExtensionType.Radius, "radius.com", "radius1", "radius-password1"));
            this.Accounts.Add(new AuthTestAccount(AuthTestExtensionType.Radius, "radius.com", "radius2", "radius-password2"));

            //-------------------------------------------------------------
            // Initialize config authentication

            Config.SetConfig(@"

Accounts[0] = config.com;config1;config-password1
Accounts[1] = config.com;config2;config-password2
");
            this.Accounts.Add(new AuthTestAccount(AuthTestExtensionType.Config, "config.com", "config1", "config-password1"));
            this.Accounts.Add(new AuthTestAccount(AuthTestExtensionType.Config, "config.com", "config2", "config-password2"));

#if TEST_AD
            //-------------------------------------------------------------
            // Initialize active directory authentication

#if !TEST_AD_LDAP
            if (ADSettings.NasSecret != string.Empty)   // Disable the test if the NAS secret is blank
#endif
            this.Accounts.Add(new AuthTestAccount(AuthTestExtensionType.Ldap, ADSettings.Domain, ADSettings.Account, ADSettings.Password));
#endif

            //-------------------------------------------------------------
            // Initalize ODBC authentication

            SqlConnection   sqlCon = null;
            SqlScriptRunner scriptRunner;
            MacroProcessor  processor;
            string          initScript =
                @"
create table Accounts (

Realm           varchar(64),
Account         varchar(64),
Password        varchar(64),
MD5             varbinary(128),
SHA1            varbinary(128),
SHA256          varbinary(128),
SHA512          varbinary(128)
)
go

insert into Accounts(Realm,Account,Password,MD5,SHA1,SHA256,SHA512)
values ('odbc.com','odbc1','odbc-password1',$(md5-1),$(sha1-1),$(sha256-1),$(sha512-1))

insert into Accounts(Realm,Account,Password,MD5,SHA1,SHA256,SHA512)
values ('odbc.com','odbc2','odbc-password2',$(md5-2),$(sha1-2),$(sha256-2),$(sha512-2))

go
";
            try
            {
                processor = new MacroProcessor();
                processor.Add("md5-1", SqlHelper.Literal(MD5Hasher.Compute("odbc-password1")));
                processor.Add("sha1-1", SqlHelper.Literal(SHA1Hasher.Compute("odbc-password1")));
                processor.Add("sha256-1", SqlHelper.Literal(SHA256Hasher.Compute("odbc-password1")));
                processor.Add("sha512-1", SqlHelper.Literal(SHA512Hasher.Compute("odbc-password1")));

                processor.Add("md5-2", SqlHelper.Literal(MD5Hasher.Compute("odbc-password2")));
                processor.Add("sha1-2", SqlHelper.Literal(SHA1Hasher.Compute("odbc-password2")));
                processor.Add("sha256-2", SqlHelper.Literal(SHA256Hasher.Compute("odbc-password2")));
                processor.Add("sha512-2", SqlHelper.Literal(SHA512Hasher.Compute("odbc-password2")));

                initScript = processor.Expand(initScript);

                sqlCon       = DB.OpenConnection();
                scriptRunner = new SqlScriptRunner(initScript);
                scriptRunner.Run(sqlCon);

                this.Accounts.Add(new AuthTestAccount(AuthTestExtensionType.Odbc, "odbc.com", "odbc1", "odbc-password1"));
                this.Accounts.Add(new AuthTestAccount(AuthTestExtensionType.Odbc, "odbc.com", "odbc2", "odbc-password2"));
            }
            finally
            {
                if (sqlCon != null)
                {
                    sqlCon.Close();
                }
            }
        }
Ejemplo n.º 23
0
        /// <summary>
        /// Upgrades the database returning true on success.
        /// </summary>
        private bool Upgrade()
        {
            Package      package       = wizard.Package;
            PackageEntry schemaFile    = package["/Schema/Schema.sql"];
            PackageEntry delProcFile   = package["/Schema/DeleteProcs.sql"];
            PackageEntry grantFile     = package["/Schema/GrantAccess.sql"];
            PackageEntry funcFolder    = package["/Funcs"];
            PackageEntry procFolder    = package["/Procs"];
            PackageEntry upgradeFolder = package["/Upgrade"];
            Version      curVersion    = new Version(wizard.SetupState["CurSchemaVersion"]);

            UpgradeScript[] upgradeScripts;
            int             opCount;
            string          script;
            SqlConnection   sqlCon;

            QueryDisposition[] qd;
            int pos;

            if (schemaFile == null || !schemaFile.IsFile)
            {
                MessageBox.Show("Invalid Database Package: /Schema/Schema.sql missing.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            if (delProcFile == null || !delProcFile.IsFile)
            {
                MessageBox.Show("Invalid Database Package: /Schema/DeleteProcs.sql missing.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            if (grantFile == null || !grantFile.IsFile)
            {
                MessageBox.Show("Invalid Database Package: /Schema/GrantAccess.sql missing.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            if (upgradeFolder == null || !upgradeFolder.IsFolder || upgradeFolder.Children.Length == 0)
            {
                MessageBox.Show("Invalid Database Package: There are no upgrade scripts.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            // Build the set of upgrade scripts to be run.

            ArrayList list = new ArrayList();

            foreach (PackageEntry file in upgradeFolder.Children)
            {
                Version ver;

                if (!file.IsFile)
                {
                    continue;
                }

                try
                {
                    ver = new Version(file.Name.Substring(0, file.Name.Length - 4));
                }
                catch
                {
                    continue;
                }

                list.Add(new UpgradeScript(ver, Helper.FromAnsi(file.GetContents())));
            }

            if (list.Count == 0)
            {
                MessageBox.Show("Invalid Database Package: There are no upgrade scripts.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            list.Sort(new UpgradeComparer());

            for (pos = 0; pos < list.Count; pos++)
            {
                if (((UpgradeScript)list[pos]).Version > curVersion)
                {
                    break;
                }
            }

            if (pos >= list.Count)
            {
                MessageBox.Show("Invalid Database Package: There are no upgrade scripts.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            upgradeScripts = new UpgradeScript[list.Count - pos];
            list.CopyTo(pos, upgradeScripts, 0, upgradeScripts.Length);

            // Count the number of operations we're going to perform and
            // initialize the progress bar.

            opCount = 0;
            opCount++;      // Delete functions and stored procedures
            opCount++;      // Run the schema script
            opCount++;      // Grant access

            opCount += upgradeScripts.Length;

            if (funcFolder != null)
            {
                opCount += funcFolder.Children.Length;
            }

            if (procFolder != null)
            {
                opCount += procFolder.Children.Length;
            }

            progressBar.Minimum = 0;
            progressBar.Maximum = opCount;
            progressBar.Step    = 1;

            sqlCon = new SqlConnection(conString);

            try
            {
                sqlCon.Open();
            }
            catch (Exception e)
            {
                Append("Error: " + e.Message);
                return(false);
            }

            try
            {
                // Remove the functions and procedures

                Append("Removing functions and procedures");
                script = Helper.FromAnsi(delProcFile.GetContents());
                qd     = new SqlScriptRunner(script).Run(sqlCon, true);

                for (int i = 0; i < qd.Length; i++)
                {
                    if (qd[i].Message != null)
                    {
                        Append(qd[i].Message);
                        return(false);
                    }
                }

                progressBar.Value++;

                // Run the update scripts

                foreach (UpgradeScript us in upgradeScripts)
                {
                    script = us.Script;
                    Append("Upgrading schema to version {0}", us.Version);

                    qd = new SqlScriptRunner(script).Run(sqlCon, true);

                    for (int i = 0; i < qd.Length; i++)
                    {
                        if (qd[i].Message != null)
                        {
                            Append(qd[i].Message);
                            return(false);
                        }
                    }

                    progressBar.Value++;
                }

                // Add the functions

                if (funcFolder != null)
                {
                    foreach (PackageEntry file in funcFolder.Children)
                    {
                        if (!file.IsFile)
                        {
                            continue;
                        }

                        Append("Adding: {0}", file.Name);

                        script = Helper.FromAnsi(file.GetContents());
                        qd     = new SqlScriptRunner(script).Run(sqlCon, true);

                        for (int i = 0; i < qd.Length; i++)
                        {
                            if (qd[i].Message != null)
                            {
                                Append(qd[i].Message);
                                return(false);
                            }
                        }

                        progressBar.Value++;
                    }
                }

                // Add the procedures

                if (procFolder != null)
                {
                    foreach (var file in procFolder.Children)
                    {
                        if (!file.IsFile)
                        {
                            continue;
                        }

                        Append("Adding: {0}", file.Name);

                        script = Helper.FromAnsi(file.GetContents());
                        qd     = new SqlScriptRunner(script).Run(sqlCon, true);

                        for (int i = 0; i < qd.Length; i++)
                        {
                            if (qd[i].Message != null)
                            {
                                Append(qd[i].Message);
                                return(false);
                            }
                        }

                        progressBar.Value++;
                    }
                }

                // Grant access to the application account

                Append("Granting access to: {0}", wizard.SetupState["account"]);

                script = Helper.FromAnsi(grantFile.GetContents());
                script = script.Replace("%account%", wizard.SetupState["account"]);

                qd = new SqlScriptRunner(script).Run(sqlCon, true);

                for (int i = 0; i < qd.Length; i++)
                {
                    if (qd[i].Message != null)
                    {
                        Append(qd[i].Message);
                        return(false);
                    }
                }

                progressBar.Value++;
            }
            catch (Exception e)
            {
                Append("Error: " + e.Message);
                return(false);
            }
            finally
            {
                sqlCon.Close();
            }

            return(true);
        }
Ejemplo n.º 24
0
 public static void CleanSchema()
 {
     SqlScriptRunner.RunScript(srciptRoot + "Schema\\Clean-Schema.sql");
 }
Ejemplo n.º 25
0
 public static void GenerateTestData()
 {
     SqlScriptRunner.RunScript(srciptRoot + "Data\\Create-Data.sql");
 }
Ejemplo n.º 26
0
 public static void CleanTestData()
 {
     SqlScriptRunner.RunScript(srciptRoot + "Data\\Clean-Data.sql");
 }
Ejemplo n.º 27
0
        /// <summary>
        /// Installs the package returning true on success.
        /// </summary>
        private bool Install()
        {
            Package       package     = wizard.Package;
            PackageEntry  schemaFile  = package["/Schema/Schema.sql"];
            PackageEntry  delProcFile = package["/Schema/DeleteProcs.sql"];
            PackageEntry  grantFile   = package["/Schema/GrantAccess.sql"];
            PackageEntry  funcFolder  = package["/Funcs"];
            PackageEntry  procFolder  = package["/Procs"];
            int           opCount;
            string        script;
            SqlConnection sqlCon;

            QueryDisposition[] qd;

            if (schemaFile == null || !schemaFile.IsFile)
            {
                MessageBox.Show("Invalid Database Package: /Schema/Schema.sql missing.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            if (delProcFile == null || !delProcFile.IsFile)
            {
                MessageBox.Show("Invalid Database Package: /Schema/DeleteProcs.sql missing.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            if (grantFile == null || !grantFile.IsFile)
            {
                MessageBox.Show("Invalid Database Package: /Schema/GrantAccess.sql missing.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            // Count the number of operations we're going to perform and
            // initialize the progress bar.

            opCount = 0;
            opCount++;      // Delete functions and stored procedures
            opCount++;      // Run the schema script
            opCount++;      // Create database user
            opCount++;      // Grant access

            if (funcFolder != null)
            {
                opCount += funcFolder.Children.Length;
            }

            if (procFolder != null)
            {
                opCount += procFolder.Children.Length;
            }

            progressBar.Minimum = 0;
            progressBar.Maximum = opCount;
            progressBar.Step    = 1;

            sqlCon = new SqlConnection(conString);

            try
            {
                sqlCon.Open();
            }
            catch (Exception e)
            {
                Append("Error: " + e.Message);
                return(false);
            }

            try
            {
                // Remove the functions and procedures

                Append("Removing functions and procedures");
                script = Helper.FromAnsi(delProcFile.GetContents());
                qd     = new SqlScriptRunner(script).Run(sqlCon, true);

                for (int i = 0; i < qd.Length; i++)
                {
                    if (qd[i].Message != null)
                    {
                        Append(qd[i].Message);
                        return(false);
                    }
                }

                progressBar.Value++;

                // Create the schema

                Append("Creating the database schema");

                script = Helper.FromAnsi(schemaFile.GetContents());
                qd     = new SqlScriptRunner(script).Run(sqlCon, true);

                for (int i = 0; i < qd.Length; i++)
                {
                    if (qd[i].Message != null)
                    {
                        Append(qd[i].Message);
                        return(false);
                    }
                }

                progressBar.Value++;

                // Add the functions

                if (funcFolder != null)
                {
                    foreach (var file in funcFolder.Children)
                    {
                        if (!file.IsFile)
                        {
                            continue;
                        }

                        Append("Adding: {0}", file.Name);

                        script = Helper.FromAnsi(file.GetContents());
                        qd     = new SqlScriptRunner(script).Run(sqlCon, true);

                        for (int i = 0; i < qd.Length; i++)
                        {
                            if (qd[i].Message != null)
                            {
                                Append(qd[i].Message);
                                return(false);
                            }
                        }

                        progressBar.Value++;
                    }
                }

                // Add the procedures

                if (procFolder != null)
                {
                    foreach (var file in procFolder.Children)
                    {
                        if (!file.IsFile)
                        {
                            continue;
                        }

                        Append("Adding: {0}", file.Name);

                        script = Helper.FromAnsi(file.GetContents());
                        qd     = new SqlScriptRunner(script).Run(sqlCon, true);

                        for (int i = 0; i < qd.Length; i++)
                        {
                            if (qd[i].Message != null)
                            {
                                Append(qd[i].Message);
                                return(false);
                            }
                        }

                        progressBar.Value++;
                    }
                }

                // Create a database user for the login if necessary

                SqlContext ctx = new SqlContext(conString);
                DataTable  dt;

                try
                {
                    ctx.Open();

                    dt = ctx.ExecuteTable(ctx.CreateCommand("select name from sysusers where name='{0}'", wizard.SetupState["account"]));
                    if (dt.Rows.Count == 0)
                    {
                        // The database user doesn't already exist, so create one.

                        Append("Creating database user: {0}", wizard.SetupState["account"]);
                        ctx.Execute(ctx.CreateCommand("create user {0} from login {0}", wizard.SetupState["account"]));
                    }
                }
                catch (Exception e)
                {
                    ctx.Close();

                    Append("Error: " + e.Message);
                    return(false);
                }

                progressBar.Value++;

                // Grant access to the application account

                Append("Granting access to: {0}", wizard.SetupState["account"]);

                script = Helper.FromAnsi(grantFile.GetContents());
                script = script.Replace("%account%", wizard.SetupState["account"]);

                qd = new SqlScriptRunner(script).Run(sqlCon, true);

                for (int i = 0; i < qd.Length; i++)
                {
                    if (qd[i].Message != null)
                    {
                        Append(qd[i].Message);
                        return(false);
                    }
                }

                progressBar.Value++;
            }
            catch (Exception e)
            {
                Append("Error: " + e.Message);
                return(false);
            }
            finally
            {
                sqlCon.Close();
            }

            return(true);
        }
Ejemplo n.º 28
0
        public void SqlScriptRunner_Run()
        {
            SqlTestDatabase dbTest;
            SqlConnection   con;
            SqlCommand      cmd;
            SqlDataReader   reader;
            int             cRows;

            QueryDisposition[] dispositions;

            using (dbTest = SqlTestDatabase.Create())
            {
                con = new SqlConnection(dbTest.ConnectionInfo);
                con.Open();

                try
                {
                    dispositions = new SqlScriptRunner(CreateSchema).Run(con, true);
                    Assert.AreEqual(3, dispositions.Length);
                    for (int i = 0; i < dispositions.Length; i++)
                    {
                        Assert.IsNull(dispositions[i].Exception);
                        Assert.IsNull(dispositions[i].Message);
                    }

                    reader = null;
                    try
                    {
                        cmd             = con.CreateCommand();
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = "select * from Foo";

                        reader = cmd.ExecuteReader();

                        cRows = 0;
                        while (reader.Read())
                        {
                            cRows++;
                        }

                        Assert.AreEqual(1, cRows);
                    }
                    finally
                    {
                        if (reader != null)
                        {
                            reader.Close();
                        }
                    }

                    dispositions = new SqlScriptRunner(DeleteSchema).Run(con, true);
                    Assert.AreEqual(2, dispositions.Length);
                    for (int i = 0; i < dispositions.Length; i++)
                    {
                        Assert.IsNull(dispositions[i].Exception);
                        Assert.IsNull(dispositions[i].Message);
                    }
                }
                finally
                {
                    new SqlScriptRunner(DeleteSchema).Run(con, true);
                    con.Close();
                }
            }
        }
Ejemplo n.º 29
0
        /// <summary>
        /// Grants access to the database returning true on success.
        /// </summary>
        private bool GrantOnly()
        {
            Package       package     = wizard.Package;
            PackageEntry  schemaFile  = package["/Schema/Schema.sql"];
            PackageEntry  delProcFile = package["/Schema/DeleteProcs.sql"];
            PackageEntry  grantFile   = package["/Schema/GrantAccess.sql"];
            PackageEntry  funcFolder  = package["/Funcs"];
            PackageEntry  procFolder  = package["/Procs"];
            int           opCount;
            string        script;
            SqlConnection sqlCon;

            QueryDisposition[] qd;

            if (schemaFile == null || !schemaFile.IsFile)
            {
                MessageBox.Show("Invalid Database Package: /Schema/Schema.sql missing.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            if (delProcFile == null || !delProcFile.IsFile)
            {
                MessageBox.Show("Invalid Database Package: /Schema/DeleteProcs.sql missing.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            if (grantFile == null || !grantFile.IsFile)
            {
                MessageBox.Show("Invalid Database Package: /Schema/GrantAccess.sql missing.",
                                wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return(false);
            }

            // Count the number of operations we're going to perform and
            // initialize the progress bar.

            opCount = 0;
            opCount++;      // Grant access

            progressBar.Minimum = 0;
            progressBar.Maximum = opCount;
            progressBar.Step    = 1;

            sqlCon = new SqlConnection(conString);

            try
            {
                sqlCon.Open();
            }
            catch (Exception e)
            {
                Append("Error: " + e.Message);
                return(false);
            }

            try
            {
                // Grant access to the application account

                Append("Granting access to: {0}", wizard.SetupState["account"]);

                script = Helper.FromAnsi(grantFile.GetContents());
                script = script.Replace("%account%", wizard.SetupState["account"]);

                qd = new SqlScriptRunner(script).Run(sqlCon, true);

                for (int i = 0; i < qd.Length; i++)
                {
                    if (qd[i].Message != null)
                    {
                        Append(qd[i].Message);
                        return(false);
                    }
                }

                progressBar.Value++;
            }
            catch (Exception e)
            {
                Append("Error: " + e.Message);
                return(false);
            }
            finally
            {
                sqlCon.Close();
            }

            Append("\r\n\r\n\r\n\r\n");
            return(true);
        }
Ejemplo n.º 30
0
 public static void CreateSchema()
 {
     SqlScriptRunner.RunScript(srciptRoot + "Schema\\Create-Schema.sql");
 }