public void Initialize() { using (var dbContext = DataContextCreator.CreateTestContext()) { SqlScriptRunner.RunAddTestDataScript(dbContext); } }
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;"); }
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(); }
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); }
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(); }
public void TearDown() { _context.Dispose(); _sqlParameterFactory = null; _storedProcedureFactory = null; SqlScriptRunner.ClearDatabase(); }
public void Initialize() { using (var dbContext = new brothershipEntities(ConnectionStrings.TEST_CONNECTION_STRING_NAME)) { SqlScriptRunner.RunAddTestDataScript(dbContext); } }
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(); } } }
/// <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(); } }
/// <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); }
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); }
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(); }
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); } }
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()); } }
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); }
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(); }
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)"); }
/// <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(); } } }
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]); }
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(); } } }
/// <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); }
public static void CleanSchema() { SqlScriptRunner.RunScript(srciptRoot + "Schema\\Clean-Schema.sql"); }
public static void GenerateTestData() { SqlScriptRunner.RunScript(srciptRoot + "Data\\Create-Data.sql"); }
public static void CleanTestData() { SqlScriptRunner.RunScript(srciptRoot + "Data\\Clean-Data.sql"); }
/// <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); }
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(); } } }
/// <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); }
public static void CreateSchema() { SqlScriptRunner.RunScript(srciptRoot + "Schema\\Create-Schema.sql"); }