public override DatabaseSchema LoadDatabaseSchema(string databaseName, DatabaseVersion version)
{
DatabaseSchema result = new DatabaseSchema();
var connectionString = RepositoryLocation;
using (var connection = new System.Data.SqlClient.SqlConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "DatabaseSchema_Load";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(){
ParameterName = "@databaseName",
SqlDbType = System.Data.SqlDbType.NVarChar,
Direction = System.Data.ParameterDirection.Input,
Value = databaseName
});
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(){
ParameterName = "@databaseVersion",
SqlDbType = System.Data.SqlDbType.NVarChar,
Direction = System.Data.ParameterDirection.Input,
Value = version.ToString()
});
command.Parameters.Add(new System.Data.SqlClient.SqlParameter(){
ParameterName = "@ReturnValue",
Direction = System.Data.ParameterDirection.ReturnValue,
});
using(var reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Name = reader["Name"].ToString();
result.Version = new DatabaseVersion(reader["Version"].ToString());
}
reader.NextResult();
while (reader.Read())
{
Materialize(result, reader);
}
}
}
}
return result;
}
public override DatabaseSchema LoadDatabaseSchema(string databaseName, DatabaseVersion version)
{
DatabaseSchema schema = null;
var dbDir = RepositoryLocation + @"\" + databaseName;
if (!Directory.Exists(dbDir))
{
return schema;
}
var versionDir = dbDir + @"\" + version.ToString();
if (!Directory.Exists(versionDir))
{
return schema;
}
var repoDir = versionDir;
var dbInfo = Directory.GetFiles(repoDir, "DatabaseSchema.info").FirstOrDefault();
var sprocDir = Directory.GetDirectories(repoDir, "StoredProcedures").FirstOrDefault();
var dbSerializer = new XmlSerializer(typeof(DatabaseSchema));
using (var fileStream = new FileStream(dbInfo,FileMode.Open))
{
schema = (DatabaseSchema) dbSerializer.Deserialize(fileStream);
}
schema.StoredProcedures = new List<StoredProcedure>();
var sprocSerializer = new XmlSerializer(typeof (StoredProcedure));
foreach(var sprocInfo in Directory.GetFiles(sprocDir,"*.info"))
{
using (var fileStream = new FileStream(sprocInfo,FileMode.Open))
{
var sproc = (StoredProcedure) sprocSerializer.Deserialize(fileStream);
schema.StoredProcedures.Add(sproc);
}
}
return schema;
}
private void ensureDatabaseIsSetup(DatabaseVersion version)
{
if (databaseFiles.ContainsKey(version))
{
return;
}
string dbName = databaseBaseName + "_" + version;
string connectionString = ConfigurationManager.ConnectionStrings[version.ToString()].ConnectionString;
// Create paths to datafiles
var dataFiles = new string[GetNumberOfFiles()];
for (int i = 0; i < GetNumberOfFiles(); i++)
{
dataFiles[i] = Path.Combine(DataFileRootPath, dbName + "_" + i + "." + (i == 0 ? "mdf" : "ndf"));
}
databaseFiles[version] = dataFiles;
// Create CREATE DATABASE statement
string createStatement = @"
CREATE DATABASE
[<DBNAME>]
ON PRIMARY " ;
// Add data files, trim trailing comma
for (int i = 0; i < dataFiles.Length; i++)
{
createStatement += @"
(
NAME = N'<DBNAME>_" + i + @"',
FILENAME = N'" + dataFiles[i] + @"',
SIZE = 3MB,
FILEGROWTH = 1MB
)," ;
}
createStatement = createStatement.Substring(0, createStatement.Length - 1);
// Finish off CREATE statement
createStatement += @"
LOG ON
(
NAME = N'<DBNAME>_log',
FILENAME = N'<TEMPPATH>\<DBNAME>.ldf',
SIZE = 1MB,
FILEGROWTH = 1MB
)" ;
// Connect to DB and CREATE database
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var cmd = new SqlCommand(replaceDBParameters(createStatement, dbName), conn);
cmd.ExecuteNonQuery();
cmd.CommandText = replaceDBParameters("ALTER DATABASE [<DBNAME>] SET PAGE_VERIFY CHECKSUM", dbName);
cmd.ExecuteNonQuery();
try
{
using (var userConn = new SqlConnection(connectionString + ";Initial Catalog=" + dbName))
{
userConn.Open();
try
{
RunSetupQueries(userConn, version);
}
finally
{
new SqlCommand("USE master", userConn).ExecuteNonQuery();
}
}
}
finally
{
cmd.CommandText = replaceDBParameters(@"
ALTER DATABASE [<DBNAME>] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC master.dbo.sp_detach_db @dbname = N'<DBNAME>'" , dbName);
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Set SQL2X Queue version to code version
/// </summary>
/// <returns>MVC View with outcome</returns>
//[HttpGet("increaseversiontolatest")]
public ActionResult IncreaseVersionToLatest()
{
string returnMessage = string.Empty;
try {
DatabaseManager databaseManager =
new DatabaseManager(
databaseName: "NorSolutionPim",
toRemote: true // always remote here
);
returnMessage += "Connection" + "\r\n";
returnMessage +=
" "
+ databaseManager.ConnectionStringSanitized
.Replace("=", " = ")
.Replace(";", "\r\n ");
returnMessage += "" + "\r\n";
returnMessage += "" + "\r\n";
string whyNot = string.Empty;
if (databaseManager.IsConnectionGood(out whyNot))
{
returnMessage += DateTime.UtcNow + " " + "Connection is good" + "\r\n";
}
if (databaseManager.IsDatabaseGood())
{
returnMessage += DateTime.UtcNow + " " + "Database is good" + "\r\n";
}
if (databaseManager.IsVersionTableGood())
{
returnMessage += DateTime.UtcNow + " " + "Version table is good" + "\r\n";
}
DatabaseVersion databaseVersion = databaseManager.minimumVersion;
try {
databaseVersion = databaseManager.VersionTableLastEntry();
} catch (Exception ex) {
returnMessage += DateTime.UtcNow + " " + ex.Message;
}
returnMessage += DateTime.UtcNow + " " + "Database Version is; " + databaseVersion.ToString() + "\r\n";
returnMessage += DateTime.UtcNow + " " + "Code Version is; " + databaseManager.maximumVersion.ToString() + "\r\n";
returnMessage += DateTime.UtcNow + " " + "Start Script" + "\r\n";
// execute next script
DatabaseScript nextScript = databaseManager.NextScript();
if (nextScript != null)
{
returnMessage += DateTime.UtcNow + " " + "Script Executed" + " " + "( " + databaseManager.Execute(nextScript, haltOnException: true) + " )" + "\r\n";
if (databaseManager.IsVersionTableGood()) // make sure table exists
{
returnMessage += DateTime.UtcNow + " " + databaseManager.DefaultVersion(nextScript.DatabaseVersion) + "\r\n";
}
returnMessage += DateTime.UtcNow + " " + "End Script" + "\r\n";
// check if version is as expected
try {
databaseVersion = databaseManager.VersionTableLastEntry();
} catch (Exception ex) {
returnMessage += DateTime.UtcNow + " " + ex.Message;
}
// run next script if previous went through
if (nextScript.DatabaseVersion == databaseVersion)
{
returnMessage += DateTime.UtcNow + " " + "Database version is at expected version, running next script";
ViewData["KeepRunning"] = "true";
}
else
{
returnMessage += DateTime.UtcNow + " " + "Database version is not at expected version, halting run to latest";
}
}
else
{
returnMessage += DateTime.UtcNow + " " + "Database version is at last version";
}
} catch (System.Exception ex) {
returnMessage = ExtractException(ex);
}
ViewData["Message"] = returnMessage;
return(View());
}
/// <summary>
/// Increase SQL2X Queue version
/// </summary>
/// <returns>MVC View with outcome</returns>
//[HttpGet("increaseversion")]
public ActionResult IncreaseVersion()
{
string returnMessage = string.Empty;
try {
DatabaseManager databaseManager =
new DatabaseManager(
databaseName: "NorSolutionPim",
toRemote: true // always remote here
);
returnMessage += "Connection" + "\r\n";
returnMessage +=
" "
+ databaseManager.ConnectionStringSanitized
.Replace("=", " = ")
.Replace(";", "\r\n ");
returnMessage += "" + "\r\n";
returnMessage += "" + "\r\n";
string whyNot = string.Empty;
if (databaseManager.IsConnectionGood(out whyNot))
{
returnMessage += DateTime.UtcNow + " " + "Connection is good" + "\r\n";
}
if (databaseManager.IsDatabaseGood())
{
returnMessage += DateTime.UtcNow + " " + "Database is good" + "\r\n";
}
if (databaseManager.IsVersionTableGood())
{
returnMessage += DateTime.UtcNow + " " + "Version table is good" + "\r\n";
}
DatabaseVersion databaseVersion = databaseManager.minimumVersion;
try {
databaseVersion = databaseManager.VersionTableLastEntry();
} catch (Exception ex) {
returnMessage += DateTime.UtcNow + " " + ex.Message;
}
returnMessage += DateTime.UtcNow + " " + "Database Version is; " + databaseVersion.ToString() + "\r\n";
returnMessage += DateTime.UtcNow + " " + "Code Version is; " + databaseManager.maximumVersion.ToString() + "\r\n";
returnMessage += DateTime.UtcNow + " " + "Start Script" + "\r\n";
// execute next script
returnMessage += databaseManager.ExecuteNextScript();
returnMessage += DateTime.UtcNow + " " + "End Script" + "\r\n";
} catch (System.Exception ex) {
returnMessage = ExtractException(ex);
}
ViewData["Message"] = returnMessage;
return(View());
}
/// <summary>
/// Set SQL2X Queue version to zero
/// </summary>
/// <returns>MVC View with outcome</returns>
//[HttpGet("SetVersionZero")]
public ActionResult SetVersionZero()
{
string returnMessage = string.Empty;
try {
DatabaseManager databaseManager =
new DatabaseManager(
databaseName: "NorSolutionPim",
toRemote: true // always remote here
);
returnMessage += "Connection" + "\r\n";
returnMessage +=
" "
+ databaseManager.ConnectionStringSanitized
.Replace("=", " = ")
.Replace(";", "\r\n ");
returnMessage += "" + "\r\n";
returnMessage += "" + "\r\n";
string whyNot = string.Empty;
if (databaseManager.IsConnectionGood(out whyNot))
{
returnMessage += DateTime.UtcNow + " " + "Connection is good" + "\r\n";
}
if (databaseManager.IsDatabaseGood())
{
returnMessage += DateTime.UtcNow + " " + "Database is good" + "\r\n";
}
if (databaseManager.IsVersionTableGood())
{
returnMessage += DateTime.UtcNow + " " + "Version table is good" + "\r\n";
}
// set version zero
try {
returnMessage += DateTime.UtcNow + " " + databaseManager.DefaultVersion(new DatabaseVersion());
} catch (Exception ex) {
returnMessage += DateTime.UtcNow + " " + ex.Message;
}
// get version
DatabaseVersion databaseVersion = databaseManager.minimumVersion;
try {
databaseVersion = databaseManager.VersionTableLastEntry();
} catch (Exception ex) {
returnMessage += DateTime.UtcNow + " " + ex.Message;
}
// if database version is minimum version then run to where default version table is created
if (databaseVersion == databaseManager.minimumVersion)
{
returnMessage =
databaseManager.ExecuteScriptsBetween(
databaseManager.minimumVersion,
databaseManager.defaultVersion,
breakOnCreationCheckmark: false
);
}
returnMessage += DateTime.UtcNow + " " + "Database Version is; " + databaseVersion.ToString() + "\r\n";
returnMessage += DateTime.UtcNow + " " + "Code Version is; " + databaseManager.maximumVersion.ToString() + "\r\n";
} catch (System.Exception ex) {
returnMessage = ExtractException(ex);
}
ViewData["Message"] = returnMessage;
return(View());
}
private void ensureDatabaseIsSetup(DatabaseVersion version)
{
if (databaseFiles.ContainsKey(version))
return;
string dbName = databaseBaseName + "_" + version;
string connectionString = ConfigurationManager.ConnectionStrings[version.ToString()].ConnectionString;
// Create paths to datafiles
var dataFiles = new string[GetNumberOfFiles()];
for (int i = 0; i < GetNumberOfFiles(); i++)
dataFiles[i] = Path.Combine(DataFileRootPath, dbName + "_" + i + "." + (i == 0 ? "mdf" : "ndf"));
databaseFiles[version] = dataFiles;
// Create CREATE DATABASE statement
string createStatement = @"
CREATE DATABASE
[<DBNAME>]
ON PRIMARY ";
// Add data files, trim trailing comma
for (int i = 0; i < dataFiles.Length; i++)
createStatement += @"
(
NAME = N'<DBNAME>_" + i + @"',
FILENAME = N'" + dataFiles[i] + @"',
SIZE = 3MB,
FILEGROWTH = 1MB
),";
createStatement = createStatement.Substring(0, createStatement.Length - 1);
// Finish off CREATE statement
createStatement += @"
LOG ON
(
NAME = N'<DBNAME>_log',
FILENAME = N'<TEMPPATH>\<DBNAME>.ldf',
SIZE = 1MB,
FILEGROWTH = 1MB
)";
// Connect to DB and CREATE database
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var cmd = new SqlCommand(replaceDBParameters(createStatement, dbName), conn);
cmd.ExecuteNonQuery();
cmd.CommandText = replaceDBParameters("ALTER DATABASE [<DBNAME>] SET PAGE_VERIFY CHECKSUM", dbName);
cmd.ExecuteNonQuery();
try
{
using (var userConn = new SqlConnection(connectionString + ";Initial Catalog=" + dbName))
{
userConn.Open();
try
{
RunSetupQueries(userConn, version);
}
finally
{
new SqlCommand("USE master", userConn).ExecuteNonQuery();
}
}
}
finally
{
cmd.CommandText = replaceDBParameters(@"
ALTER DATABASE [<DBNAME>] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC master.dbo.sp_detach_db @dbname = N'<DBNAME>'", dbName);
cmd.ExecuteNonQuery();
}
}
}