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");
}