using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
namespace VwdCms.Admin
{
public class SqlInstaller : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button btnInstall;
protected System.Web.UI.WebControls.Button btnUninstall;
protected System.Web.UI.WebControls.Label lblWarning;
protected System.Web.UI.WebControls.Label lblStatus;
protected System.Web.UI.WebControls.Label lblProvider;
protected System.Web.UI.WebControls.Label lblDatabaseName;
protected System.Web.UI.WebControls.Label lblDatabaseServer;
protected System.Web.UI.WebControls.CheckBox chkGrantExecute;
protected System.Web.UI.WebControls.TextBox txtUserName;
protected System.Web.UI.HtmlControls.HtmlGenericControl divOutput;
protected VwdCms.TreeList tlScripts;
protected VwdCms.Admin.SqlInstallerRBL rblInstallers;
private string _lpTablesFolder = null;
private string _lpSprocsFolder = null;
private string _lpDataFolder = null;
private string _lpOtherFolder = null;
private Hashtable _htSprocs = null;
private Hashtable _htTables = null;
string[] _requiredSprocs = {"dbo.vwdcms_SetScriptVersion", "dbo.vwdcms_GetScriptVersions" };
string[] _requiredTables = null;
private bool _fullyInstalled = true;
private string _installerKey = null;
private enum InstallerActions
{
Install,
Uninstall
}
protected void Page_Load(object sender, EventArgs e)
{
VwdCms.Configuration.Utilities.ProtectAdminPages();
this.divOutput.InnerHtml = " ";
this.lblWarning.Visible = false;
LoadTables();
LoadSprocs();
CheckForRequiredDatabaseObjects();
VwdCms.Configuration.CmsConfig cfg = VwdCms.Configuration.CmsConfig.Current;
VwdCms.Configuration.CmsHost host = cfg.CurrentHost;
VwdCms.Configuration.SqlInstaller installer = null;
// set the view / installer mode
if (!this.Page.IsPostBack)
{
this.rblInstallers.LoadSqlInstallers();
_installerKey = this.Page.Request.QueryString["install"];
}
string lpFolder = null;
if (string.IsNullOrEmpty(_installerKey))
{
_installerKey = this.rblInstallers.SelectedValue;
}
if (cfg.SqlInstallers.ContainsKey(_installerKey))
{
installer = cfg.SqlInstallers[_installerKey];
}
if (installer != null)
{
lpFolder = installer.Path;
}
if (string.IsNullOrEmpty(lpFolder))
{
_installerKey = "vwdcmsadmin";
lpFolder = "~/VwdCms/Admin/mssql/admin/";
}
lpFolder = this.Page.Server.MapPath(lpFolder);
_lpTablesFolder = lpFolder + "tables\\";
_lpSprocsFolder = lpFolder + "sprocs\\";
_lpDataFolder = lpFolder + "data\\";
_lpOtherFolder = lpFolder + "other\\";
ListItem itm = this.rblInstallers.Items.FindByValue(_installerKey);
this.rblInstallers.SelectedIndex = this.rblInstallers.Items.IndexOf(itm);
// hook up the button event handlers
this.btnInstall.Command += new CommandEventHandler(btnInstall_Command);
this.btnUninstall.Command += new CommandEventHandler(btnUninstall_Command);
// get the provider and connection string
this.lblProvider.Text = Membership.Provider.Name;
// load the data
GetDatabaseInfo();
LoadSqlScripts();
}
private void GetDatabaseInfo()
{
string connectionString = VwdCms.DbUtil.GetConnectionString();
if (!string.IsNullOrEmpty(connectionString))
{
string[] connStrings = connectionString.Split(';');
string[] parts = null;
foreach (string connstr in connStrings)
{
if (!string.IsNullOrEmpty(connstr))
{
parts = connstr.Split('=');
if (parts.Length == 2)
{
switch (parts[0].ToLower())
{
case "data source":
case "server":
this.lblDatabaseServer.Text = parts[1];
break;
case "database":
case "initial catalog":
this.lblDatabaseName.Text = parts[1];
break;
}
}
}
}
}
}
private bool CheckForRequiredDatabaseObjects()
{
bool missing = false;
if (_requiredTables != null && _requiredTables.Length > 0)
{
foreach (string table in _requiredTables)
{
if (!string.IsNullOrEmpty(table) && !_htTables.ContainsKey(table.ToLower()))
{
missing = true;
break;
}
}
}
if (_requiredSprocs != null && _requiredSprocs.Length > 0)
{
foreach (string sproc in _requiredSprocs)
{
if (!string.IsNullOrEmpty(sproc) && !_htSprocs.ContainsKey(sproc.ToLower()))
{
missing = true;
break;
}
}
}
this.lblWarning.Visible = missing;
return missing;
}
void btnInstall_Command(object sender, CommandEventArgs e)
{
ProcessCommands(InstallerActions.Install);
}
void btnUninstall_Command(object sender, CommandEventArgs e)
{
ProcessCommands(InstallerActions.Uninstall);
}
void ProcessCommands(InstallerActions action)
{
bool success = true;
StringBuilder sbOutput = null;
StringBuilder sbResults = new StringBuilder();
bool grantexecute = this.chkGrantExecute.Checked;
string username = this.txtUserName.Text;
string sql = null;
string qualifiedName = null;
string objectName = null;
this.divOutput.InnerHtml = string.Empty;
VwdCms.TreeNode tn = null;
VwdCms.TreeNode tnRoot = this.tlScripts.RootNode;
VwdCms.TreeNode tnParent = null;
string checkBoxKey = null;
string scriptText = null;
// get the selected scripts
Hashtable htSelected = this.tlScripts.SelectedItems;
// SPECIAL CASE FOR REQUIRED TABLES AND SPROCS - RUN THESE BEFORE ANY OTHER SCRIPS
if (action == InstallerActions.Install)
{
string scriptFile = null;
if (_requiredTables != null && _requiredTables.Length > 0)
{
foreach (string table in _requiredTables)
{
if (!string.IsNullOrEmpty(table))
{
scriptFile = table + ".sql";
checkBoxKey = TreeList.GetCheckBoxID(_lpTablesFolder + scriptFile);
if (htSelected.ContainsKey(checkBoxKey))
{
// this script is selected
scriptText = VwdCms.IO.ReadFile(_lpTablesFolder + scriptFile);
objectName = GetObjectNameUnqualified(table);
success = RunSqlScript("TABLE", objectName, scriptFile, scriptText, out sbOutput);
sbResults.Append(sbOutput.ToString());
htSelected.Remove(checkBoxKey);
}
}
}
}
if (_requiredSprocs != null && _requiredSprocs.Length > 0)
{
foreach (string sproc in _requiredSprocs)
{
if (!string.IsNullOrEmpty(sproc))
{
scriptFile = sproc + ".sql";
checkBoxKey = TreeList.GetCheckBoxID(_lpSprocsFolder + scriptFile);
if (htSelected.ContainsKey(checkBoxKey))
{
// this script is selected
scriptText = VwdCms.IO.ReadFile(_lpSprocsFolder + scriptFile);
objectName = GetObjectNameUnqualified(sproc);
success = RunSqlScript("PROCEDURE", objectName, scriptFile, scriptText, out sbOutput);
sbResults.Append(sbOutput.ToString());
htSelected.Remove(checkBoxKey);
if (success && grantexecute && !string.IsNullOrEmpty(username))
{
sql = "GRANT EXECUTE ON [" + sproc + "] TO [" + username + "] ";
success = RunSqlCommand("Grant Execute to '" + username + "'", sql , null, null, out sbOutput);
}
}
}
}
}
}
// ************************************************
// execute the selected create table scripts
// ************************************************
tnParent = tnRoot.Nodes["tables"];
foreach (KeyValuePair <string, VwdCms.TreeNode> kvp in tnParent.Nodes)
{
tn = kvp.Value;
checkBoxKey = TreeList.GetCheckBoxID(tn);
if (htSelected.ContainsKey(checkBoxKey))
{
// this script is selected
scriptText = VwdCms.IO.ReadFile(tn.Key);
qualifiedName = GetObjectNameQualified(tn);
switch (action)
{
case InstallerActions.Install:
objectName = GetObjectNameUnqualified(qualifiedName);
success = RunSqlScript("TABLE", objectName, tn.CheckBoxValue, scriptText, out sbOutput);
sbResults.Append(sbOutput.ToString());
break;
case InstallerActions.Uninstall:
sql = "DROP TABLE " + qualifiedName;
success = RunSqlCommand("Uninstall " + qualifiedName, sql, null, null, out sbOutput);
sbResults.Append(sbOutput.ToString());
break;
}
}
}
// ************************************************
// execute the selected create sproc scripts
// ************************************************
tnParent = tnRoot.Nodes["sprocs"];
foreach (KeyValuePair<string, VwdCms.TreeNode> kvp in tnParent.Nodes)
{
tn = kvp.Value;
checkBoxKey = TreeList.GetCheckBoxID(tn);
if (htSelected.ContainsKey(checkBoxKey))
{
// this script is selected
scriptText = VwdCms.IO.ReadFile(tn.Key);
// get the name of the sproc (take off the version info)
qualifiedName = GetObjectNameQualified(tn);
objectName = GetObjectNameUnqualified(qualifiedName);
// execute the appropriate action
switch (action)
{
case InstallerActions.Install:
success = RunSqlScript("PROCEDURE", objectName, tn.CheckBoxValue, scriptText, out sbOutput);
sbResults.Append(sbOutput.ToString());
if (success && grantexecute && !string.IsNullOrEmpty(username))
{
sql = "GRANT EXECUTE ON [" + qualifiedName + "] TO [" + username + "] ";
success = RunSqlCommand("Grant Execute to '" + username + "'", sql, null, null, out sbOutput);
sbResults.Append(sbOutput.ToString());
}
break;
case InstallerActions.Uninstall:
sql = "DROP PROCEDURE " + qualifiedName;
RunSqlCommand("Uninstall " + qualifiedName, sql, null, null, out sbOutput);
sbResults.Append(sbOutput.ToString());
break;
}
}
}
// ************************************************
// execute the selected data scripts
// ************************************************
tnParent = tnRoot.Nodes["data"];
foreach (KeyValuePair<string, VwdCms.TreeNode> kvp in tnParent.Nodes)
{
tn = kvp.Value;
checkBoxKey = TreeList.GetCheckBoxID(tn);
if (htSelected.ContainsKey(checkBoxKey) && action == InstallerActions.Install)
{
// this script is selected and the Install button was clicked so,
// execute the script (data scripts do not support uninstall)
scriptText = VwdCms.IO.ReadFile(tn.Key);
success = RunSqlScript(null, null, tn.CheckBoxValue, scriptText, out sbOutput);
sbResults.Append(sbOutput.ToString());
}
}
// ************************************************
// execute the selected other scripts
// ************************************************
tnParent = tnRoot.Nodes["other"];
foreach (KeyValuePair<string, VwdCms.TreeNode> kvp in tnParent.Nodes)
{
tn = kvp.Value;
checkBoxKey = TreeList.GetCheckBoxID(tn);
if (htSelected.ContainsKey(checkBoxKey) && action == InstallerActions.Install)
{
// this script is selected and the Install button was clicked so,
// execute the script (other scripts do not support uninstall)
scriptText = VwdCms.IO.ReadFile(tn.Key);
success = RunSqlScript(null, null, tn.CheckBoxValue, scriptText, out sbOutput);
sbResults.Append(sbOutput.ToString());
}
}
this.divOutput.InnerHtml = sbResults.ToString();
LoadTables();
LoadSprocs();
bool missing = CheckForRequiredDatabaseObjects();
this.tlScripts.Clear();
LoadSqlScripts();
VwdCms.Configuration.CmsConfig cfg = VwdCms.Configuration.CmsConfig.Current;
if (cfg != null)
{
VwdCms.Configuration.CmsHost host = cfg.CurrentHost;
if (host != null )
{
VwdCms.Configuration.HostComponent comp = null;
if (host.Components.ContainsKey(_installerKey))
{
comp = host.Components[_installerKey];
}
else
{
comp = new VwdCms.Configuration.HostComponent(host, _installerKey);
host.Components.Add(_installerKey, comp);
}
if (comp != null)
{
if (_fullyInstalled)
{
comp.Status = "installed";
}
else
{
comp.Status = "notinstalled";
}
cfg.Save();
}
}
}
}
private static string GetObjectNameQualified(VwdCms.TreeNode node)
{
string name = node.Text;
// strip off the version info
if (name.IndexOf(' ') != -1)
{
name = name.Substring(0, name.IndexOf(' '));
}
return name;
}
private static bool RunSqlScript(string objectType, string objectName, string scriptFile, string commandText, out StringBuilder sbOutput)
{
return RunSqlScript(objectType, objectName, scriptFile, commandText, null, null, out sbOutput);
}
private static bool RunSqlScript(string objectType, string objectName, string scriptFile, string commandText, string username, out StringBuilder sbOutput)
{
return RunSqlScript(objectType, objectName, scriptFile, commandText, username, null, out sbOutput);
}
private static bool RunSqlScript(string objectType, string objectName, string scriptFile, string commandText, string username, string password, out StringBuilder sbOutput)
{
bool success = true;
sbOutput = new StringBuilder();
try
{
sbOutput.Append("span style=\"font-weight:bold;\">" + scriptFile + "</span><br/>");
IDbConnection cn = VwdCms.DbUtil.GetConnection();
StringBuilder sbSql = new StringBuilder(commandText);
string database = cn.Database;
if (database != null)
{
sbSql.Replace("{database}", database);
}
if (username != null)
{
sbSql.Replace("{username}", username);
}
if (password != null)
{
sbSql.Replace("{password}", password);
}
StringReader sr = new StringReader(sbSql.ToString());
ArrayList commands = new ArrayList();
string line = null;
StringBuilder sbCmd = new StringBuilder();
while (true)
{
line = sr.ReadLine();
if (line == null)
{
commands.Add(sbCmd.ToString());
break;
}
else if (line.Trim().ToLower() == "go")
{
commands.Add(sbCmd.ToString());
sbCmd = new StringBuilder();
}
else
{
sbCmd.Append(line);
sbCmd.Append("\r\n");
}
}
IDbCommand cmd = VwdCms.DbUtil.OpenCommand(cn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 300;
foreach (string cmdText in commands)
{
if (!string.IsNullOrEmpty(cmdText))
{
cmd.CommandText = cmdText;
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
success = false;
sbOutput.Append("<span style=\"color:firebrick;\">SQL Server Error:<br/>");
sbOutput.Append(HttpUtility.HtmlEncode(sqlex.Message));
sbOutput.Append("</span><br/>");
}
catch (Exception ex)
{
success = false;
sbOutput.Append("<span style=\"color:firebrick;\">ASP.NET Error:<br/>");
sbOutput.Append(HttpUtility.HtmlEncode(ex.Message));
sbOutput.Append("</span><br/>");
}
}
}
if (objectType != null)
{
// set the script version
int version = FindScriptVersion(commandText);
success = SetScriptVersion(cn, objectType, objectName, version, sbOutput);
}
}
catch (Exception ex1)
{
success = false;
sbOutput.Append("<span style=\"color:firebrick;\">ASP.NET Error:<br/>");
sbOutput.Append(HttpUtility.HtmlEncode(ex1.Message));
sbOutput.Append("</span><br/>");
}
sbOutput.Replace("\r\n", "<br/>");
if (success)
{
sbOutput.Append("<span style=\"color:darkgreen;\"> Completed Successfully</span><br/>");
}
return success;
}
private static bool RunSqlCommand(string commandName, string commandText, string username, string password, out StringBuilder sbOutput)
{
bool success = true;
sbOutput = new StringBuilder();
try
{
sbOutput.Append("<span style=\"font-weight:bold;\">" + commandName + "</span><br/>");
IDbConnection cn = VwdCms.DbUtil.GetConnection();
StringBuilder sbSql = new StringBuilder(commandText);
string database = cn.Database;
if (database != null)
{
sbSql.Replace("{database}", database);
}
if (username != null)
{
sbSql.Replace("{username}", username);
}
if (password != null)
{
sbSql.Replace("{password}", password);
}
StringReader sr = new StringReader(sbSql.ToString());
ArrayList commands = new ArrayList();
string line = null;
StringBuilder sbCmd = new StringBuilder();
while (true)
{
line = sr.ReadLine();
if (line == null)
{
commands.Add(sbCmd.ToString());
break;
}
else if (line.Trim().ToLower() == "go")
{
commands.Add(sbCmd.ToString());
sbCmd = new StringBuilder();
}
else
{
sbCmd.Append(line);
sbCmd.Append("\r\n");
}
}
IDbCommand cmd = VwdCms.DbUtil.OpenCommand(cn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 300;
foreach (string cmdText in commands)
{
if (!string.IsNullOrEmpty(cmdText))
{
cmd.CommandText = cmdText;
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException sqlex)
{
success = false;
sbOutput.Append("<span style=\"color:firebrick;\">SQL Server Error:<br/>");
sbOutput.Append(HttpUtility.HtmlEncode(sqlex.Message));
sbOutput.Append("</span><br/>");
}
catch (Exception ex)
{
success = false;
sbOutput.Append("<span style=\"color:firebrick;\">ASP.NET Error:<br/>");
sbOutput.Append(HttpUtility.HtmlEncode(ex.Message));
sbOutput.Append("</span><br/>");
}
}
}
}
catch (Exception ex1)
{
success = false;
sbOutput.Append("<span style=\"color:firebrick;\">ASP.NET Error:<br/>");
sbOutput.Append(HttpUtility.HtmlEncode(ex1.Message));
sbOutput.Append("</span><br/>");
}
if (success)
{
sbOutput.Append("<span style=\"color:darkgreen;\"> Completed Successfully</span><br/>");
}
return success;
}
private static bool SetScriptVersion(IDbConnection cn, string objectType, string objectName, int scriptVersion, StringBuilder sbOutput)
{
bool success = false;
if (!string.IsNullOrEmpty(objectType) !string.IsNullOrEmpty(objectName) scriptVersion 0)
{
IDbCommand cmd = VwdCms.DbUtil.OpenCommand(cn);
// set the ScriptVersion extended property
try
{
cmd.CommandText = "vwdcms_SetScriptVersion";
DbUtil.AddParameter(cmd, "@ObjectType", objectType);
DbUtil.AddParameter(cmd, "@ObjectName", objectName);
DbUtil.AddParameter(cmd, "@Value", scriptVersion.ToString());
cmd.ExecuteScalar();
success = true;
}
catch (Exception ex)
{
// vwdcms_GetTableScriptVersions probably does not exist
sbOutput.AppendLine("Exception in SetScriptVersion for '" + objectType + "', '" + objectName + "'.");
sbOutput.AppendLine(ex.Message);
}
}
return success;
}
private static int FindScriptVersion(string commandText)
{
int version = -1;
StringReader sr = new StringReader(commandText);
string line = sr.ReadLine();
string linelower = null;
const string versionIdentifier = "-- **** version:";
while (line != null)
{
linelower = line.ToLower();
if (linelower.StartsWith(versionIdentifier))
{
version = Convert.ToInt32(linelower.Substring(versionIdentifier.Length));
break;
}
line = sr.ReadLine();
}
return version;
}
private void LoadTables()
{
string qualifiedName = null;
IDataReader dr = null;
IDbConnection cn = DbUtil.GetConnection();
IDbCommand cmd = DbUtil.OpenCommand(cn);
cmd.Parameters.Clear();
cmd.CommandText = "sys.sp_tables";
dr = cmd.ExecuteReader();
_htTables = new Hashtable();
if (dr != null !dr.IsClosed)
{
while (dr.Read())
{
qualifiedName = Convert.ToString(dr["TABLE_OWNER"]) + "." + Convert.ToString(dr["TABLE_NAME"]);
qualifiedName = qualifiedName.ToLower();
if (!string.IsNullOrEmpty(qualifiedName) !_htTables.ContainsKey(qualifiedName))
{
_htTables.Add(qualifiedName, qualifiedName);
}
}
DbUtil.CloseDataReader(dr);
dr = null;
}
}
private void LoadSprocs()
{
string qualifiedName = null;
IDataReader dr = null;
IDbConnection cn = DbUtil.GetConnection();
IDbCommand cmd = DbUtil.OpenCommand(cn);
cmd.Parameters.Clear();
cmd.CommandText = "sys.sp_stored_procedures";
dr = cmd.ExecuteReader();
_htSprocs = new Hashtable();
if (dr != null !dr.IsClosed)
{
while (dr.Read())
{
qualifiedName = Convert.ToString(dr["PROCEDURE_OWNER"]) + "." + Convert.ToString(dr["PROCEDURE_NAME"]);
qualifiedName = qualifiedName.Substring(0, qualifiedName.IndexOf(';'));
qualifiedName = qualifiedName.ToLower();
if (!string.IsNullOrEmpty(qualifiedName) !_htSprocs.ContainsKey(qualifiedName))
{
_htSprocs.Add(qualifiedName, qualifiedName);
}
}
DbUtil.CloseDataReader(dr);
dr = null;
}
}
private void LoadSqlScripts()
{
_fullyInstalled = true;
string qualifiedName = null;
string objectName = null;
int objectVersion = 0;
int scriptVersion = 0;
string versionKey = null;
string versionValue = null;
IDataReader dr = null;
IDbConnection cn = DbUtil.GetConnection();
IDbCommand cmd = DbUtil.OpenCommand(cn);
// get the versions for the database objects
Hashtable htVersions = new Hashtable();
try
{
cmd.Parameters.Clear();
cmd.CommandText = "vwdcms_GetScriptVersions";
DbUtil.AddParameter(cmd, "@ObjectType", "TABLE");
dr = cmd.ExecuteReader();
if (dr != null !dr.IsClosed)
{
while (dr.Read())
{
versionKey = Convert.ToString(dr["objtype"]);
versionKey += ":" + Convert.ToString(dr["objname"]);
versionKey = versionKey.ToLower();
versionValue = Convert.ToString(dr["value"]);
if (!htVersions.ContainsKey(versionKey))
{
htVersions.Add(versionKey, versionValue);
}
}
DbUtil.CloseDataReader(dr);
dr = null;
}
cmd.Parameters.Clear();
cmd.CommandText = "vwdcms_GetScriptVersions";
DbUtil.AddParameter(cmd, "@ObjectType", "PROCEDURE");
dr = cmd.ExecuteReader();
if (dr != null !dr.IsClosed)
{
while (dr.Read())
{
versionKey = Convert.ToString(dr["objtype"]);
versionKey += ":" + Convert.ToString(dr["objname"]);
versionKey = versionKey.ToLower();
versionValue = Convert.ToString(dr["value"]);
if (!htVersions.ContainsKey(versionKey))
{
htVersions.Add(versionKey, versionValue);
}
}
DbUtil.CloseDataReader(dr);
dr = null;
}
}
catch(Exception ex)
{
// vwdcms_GetScriptVersions probably does not exist
System.Diagnostics.Debug.WriteLine("vwdcms_GetScriptVersions does not exist");
System.Diagnostics.Debug.WriteLine(ex.ToString());
}
// load the treelist
string imageUrl = VwdCms.Configuration.Utilities.AdminImageUrl;
DirectoryInfo di = null;
VwdCms.TreeNode tn = null;
VwdCms.TreeNode tnRoot = new VwdCms.TreeNode();
// ******************
// TABLES
// ******************
VwdCms.TreeNode tnTable = new VwdCms.TreeNode();
tnTable.Key = "tables";
tnTable.NodeType = TreeNodeType.Container;
tnTable.Parent = tnRoot;
tnTable.Text = "Tables";
tnTable.ToolTip = "Tables";
tnTable.ImageUrl = imageUrl + "folder.gif";
tnRoot.Nodes.Add(tnTable.Key, tnTable);
if(Directory.Exists(_lpTablesFolder))
{
di = new DirectoryInfo(_lpTablesFolder);
FileInfo[] createTableScripts = di.GetFiles("*.sql", SearchOption.TopDirectoryOnly);
if (createTableScripts != null createTableScripts.Length 0)
{
foreach (FileInfo fi in createTableScripts)
{
qualifiedName = GetObjectNameFromFileInfo(fi, true);
objectName = GetObjectNameFromFileInfo(fi, false);
versionKey = "table:" + objectName.ToLower();
if (htVersions.ContainsKey(versionKey))
{
objectVersion = Convert.ToInt32(htVersions[versionKey]);
}
else
{
objectVersion = 0;
}
scriptVersion = GetScriptVersion(fi.FullName);
tn = new VwdCms.TreeNode();
tn.Key = fi.FullName;
tn.Parent = tnTable;
tn.Text = qualifiedName + (objectVersion == 0 ? string.Empty : " v" + objectVersion.ToString());
tn.CheckBoxValue = fi.Name;
if (_htTables.ContainsKey(qualifiedName.ToLower()))
{
if (objectVersion == scriptVersion)
{
tn.ImageUrl = imageUrl + "table-exists.gif";
tn.ToolTip = qualifiedName + " exists in the database";
}
else
{
_fullyInstalled = false;
tn.ImageUrl = imageUrl + "table-version.gif";
if (scriptVersion objectVersion)
{
tn.ToolTip = "The script for '" + qualifiedName + "' is a newer version.";
}
else
{
tn.ToolTip = "The script for '" + qualifiedName + "' is an older version.";
}
}
}
else
{
_fullyInstalled = false;
tn.ImageUrl = imageUrl + "table-missing.gif";
tn.ToolTip = qualifiedName + " does not exist in the database";
}
tnTable.Nodes.Add(tn.Key, tn);
}
}
}
// ******************
// SPROCS
// ******************
VwdCms.TreeNode tnSproc = new VwdCms.TreeNode();
tnSproc.Key = "sprocs";
tnSproc.NodeType = TreeNodeType.Container;
tnSproc.Parent = tnRoot;
tnSproc.Text = "Stored Procedures";
tnSproc.ToolTip = "Stored Procedures";
tnSproc.ImageUrl = imageUrl + "folder.gif";
tnRoot.Nodes.Add(tnSproc.Key, tnSproc);
if (Directory.Exists(_lpSprocsFolder))
{
di = new DirectoryInfo(_lpSprocsFolder);
FileInfo[] createSprocScripts = di.GetFiles("*.sql", SearchOption.TopDirectoryOnly); ;
if (createSprocScripts != null createSprocScripts.Length 0)
{
foreach (FileInfo fi in createSprocScripts)
{
qualifiedName = GetObjectNameFromFileInfo(fi, true);
objectName = GetObjectNameFromFileInfo(fi, false);
versionKey = "procedure:" + objectName.ToLower();
if (htVersions.ContainsKey(versionKey))
{
objectVersion = Convert.ToInt32(htVersions[versionKey]);
}
else
{
objectVersion = 0;
}
scriptVersion = GetScriptVersion(fi.FullName);
tn = new VwdCms.TreeNode();
tn.Key = fi.FullName;
tn.Parent = tnSproc;
tn.Text = qualifiedName + (objectVersion == 0 ? string.Empty : " v" + objectVersion.ToString());
tn.CheckBoxValue = fi.Name;
if (_htSprocs.ContainsKey(qualifiedName.ToLower()))
{
if (objectVersion == scriptVersion)
{
tn.ImageUrl = imageUrl + "sproc-exists.gif";
tn.ToolTip = qualifiedName + " exists in the database";
}
else
{
_fullyInstalled = false;
tn.ImageUrl = imageUrl + "sproc-version.gif";
if (scriptVersion objectVersion)
{
tn.ToolTip = "The script for '" + qualifiedName + "' is a newer version.";
}
else
{
tn.ToolTip = "The script for '" + qualifiedName + "' is an older version.";
}
}
}
else
{
_fullyInstalled = false;
tn.ImageUrl = imageUrl + "sproc-missing.gif";
tn.ToolTip = qualifiedName + " does not exist in the database";
}
tnSproc.Nodes.Add(tn.Key, tn);
}
}
}
// ******************
// DATA (note: no database objects are associated with a data script)
// ******************
VwdCms.TreeNode tnData = new VwdCms.TreeNode();
tnData.Key = "data";
tnData.NodeType = TreeNodeType.Container;
tnData.Parent = tnRoot;
tnData.Text = "Data";
tnData.ToolTip = "Data Scripts";
tnData.ImageUrl = imageUrl + "folder.gif";
tnRoot.Nodes.Add(tnData.Key, tnData);
if (Directory.Exists(_lpDataFolder))
{
di = new DirectoryInfo(_lpDataFolder);
FileInfo[] dataScripts = di.GetFiles("*.sql", SearchOption.TopDirectoryOnly);
if (dataScripts != null dataScripts.Length 0)
{
foreach (FileInfo fi in dataScripts)
{
qualifiedName = GetObjectNameFromFileInfo(fi, true);
objectName = GetObjectNameFromFileInfo(fi, false);
tn = new VwdCms.TreeNode();
tn.Key = fi.FullName;
tn.Parent = tnData;
tn.Text = qualifiedName;
tn.CheckBoxValue = fi.Name;
tn.ImageUrl = imageUrl + "data.gif";
tn.ToolTip = fi.Name;
tnData.Nodes.Add(tn.Key, tn);
}
}
}
// ******************
// OTHER (note: no database objects are associated with a data script)
// this folder contains utility scripts and any other sql scripts
// ******************
VwdCms.TreeNode tnOther = new VwdCms.TreeNode();
tnOther.Key = "other";
tnOther.NodeType = TreeNodeType.Container;
tnOther.Parent = tnRoot;
tnOther.Text = "Other";
tnOther.ToolTip = "Other Scripts";
tnOther.ImageUrl = imageUrl + "folder.gif";
tnRoot.Nodes.Add(tnOther.Key, tnOther);
if (Directory.Exists(_lpOtherFolder))
{
di = new DirectoryInfo(_lpOtherFolder);
FileInfo[] otherScripts = di.GetFiles("*.sql", SearchOption.TopDirectoryOnly);
if (otherScripts != null otherScripts.Length 0)
{
foreach (FileInfo fi in otherScripts)
{
qualifiedName = GetObjectNameFromFileInfo(fi, true);
objectName = GetObjectNameFromFileInfo(fi, false);
tn = new VwdCms.TreeNode();
tn.Key = fi.FullName;
tn.Parent = tnOther;
tn.Text = qualifiedName;
tn.CheckBoxValue = fi.Name;
tn.ImageUrl = imageUrl + "data.gif";
tn.ToolTip = fi.Name;
tnOther.Nodes.Add(tn.Key, tn);
}
}
}
if (_fullyInstalled)
{
this.lblStatus.Text = "<b>Status:</b> All Database Objects are Installed and have the Correct Version.";
this.lblStatus.ForeColor = System.Drawing.Color.DarkGreen;
}
else
{
this.lblStatus.Text = "<b>Status:</b> Some Database Objects are Missing or the Version is Incorrect.";
this.lblStatus.ForeColor = System.Drawing.Color.Firebrick;
}
this.tlScripts.RootNode = tnRoot;
this.tlScripts.RenderNodes();
}
private string GetObjectNameFromFileInfo(FileInfo fi, bool qualified)
{
string name = null;
if (fi != null)
{
name = fi.Name;
name = name.Substring(0, name.Length - fi.Extension.Length);
if (!qualified)
{
name = GetObjectNameUnqualified(name);
}
}
return name;
}
private string GetObjectNameUnqualified(string name)
{
int index = name.LastIndexOf('.');
if (index != -1)
{
name = name.Substring(index + 1);
}
return name;
}
private static int GetScriptVersion(string lpScriptFile)
{
string commandText = VwdCms.IO.ReadFile(lpScriptFile);
int version = -1;
const string versionIdentifier = "-- **** version:";
string linelower = null;
StringReader sr = new StringReader(commandText);
string line = sr.ReadLine();
while (line != null)
{
linelower = line.ToLower();
if (linelower.StartsWith(versionIdentifier))
{
version = Convert.ToInt32(linelower.Substring(versionIdentifier.Length));
break;
}
line = sr.ReadLine();
}
return version;
}
}
}