/[AnywhereTS-MSSQL]/trunk/TSAdminTool/Database.cs
ViewVC logotype

Diff of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

--- trunk/TSAdminTool/Database.cs	2012/07/11 16:01:40	10
+++ trunk/TSAdminTool/Database.cs	2012/07/12 12:25:58	40
@@ -6,43 +6,107 @@
 using System.Windows.Forms;
 using System.Text.RegularExpressions;
 using System.Management;
+using log4net;
 
 namespace AnywhereTS
 {
     class DatabaseSupport
     {
+        
         enum VersionCheck
         {
-            Failed = 0, Equal, DatabaseIsMoreNew,
-            DatabaseIsOlder, DatabaseNotFound
+            Failed = 0, 
+            Equal, 
+            DatabaseIsMoreNew,
+            DatabaseIsOlder, 
+            //DatabaseNotFound,
+            DatabaseCreated,
         };
 
         private SqlConnection sqlCon = new SqlConnection();
         private SqlCommand sqlCmd = new SqlCommand();
 
         const string SQL_BROWSER_SERVICE_NAME = "SQLBrowser";
-        
+        const Version CURRENT_DB_VERSION = new Version(1, 0, 0, 3);
+        public static string InstanceName
+        {
+            get
+            {
+                bool start = false;
+                bool end = false;
+                List<char> pChars = new List<char>();
+                foreach (char c in Properties.Settings.Default.atsConnectionString.ToCharArray())
+                {
+                    if (c == '\\') { start = true; continue; }
+                    if (c == ';') { end = true; }
+                    if (end) break;
+                    if (start) { pChars.Add(c); }
+                }
+                return new string(pChars.ToArray());
+            }
+        }
         public bool SetupDatabase()
         {
             bool bContinue = false;
-
             // Create a connection to SQL Server
+            
+            Logging.ATSAdminLog.Debug("SetupDatabase() called ");
             try
-            {
-                sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
+            {                
+                sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;                
+                using (log4net.NDC.Push(string.Format("ConnectionString={0}", Properties.Settings.Default.atsConnectionString)))
+                {
+                    Logging.ATSAdminLog.Debug("Opening connection to AnywhereTS Database");
+                }
                 sqlCon.Open();
             }
-            catch (SqlException sql_ex)
+            catch
             {
-                MessageBox.Show("Fail to connect to SQL Server Express\n" + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
-                return bContinue;
+                //MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
+                //return bContinue;
+                //bCreateDB = true;
+                try
+                {
+                    using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
+                    {
+                        Logging.ATSAdminLog.Debug("Creating Database AnywhereTS");
+                        sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
+                        sqlCon.Open();
+                        RunScript(Resource1.CreateDatabase.ToString());
+                        sqlCon.Close();
+                    }
+                    Logging.ATSAdminLog.Debug("Created Database AnywhereTS");
+
+                    using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
+                    {
+                        Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database");
+                        sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
+                        sqlCon.Open();
+                        RunScript(Resource1.CreateTables.ToString());
+                        sqlCon.Close();
+                    }
+                    Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database");
+                }
+                catch (SqlException sql_ex1)
+                {
+                    SqlException sql_ex = (sql_ex1.GetBaseException() as SqlException);
+                    MessageBox.Show(string.Format("Failed to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
+
+                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", sql_ex.Number.ToString(), sql_ex.Message, System.Environment.NewLine, sql_ex.ToString())))
+                    {
+                        Logging.ATSAdminLog.Error("Failed to open connection to AnywhereTS Database");
+                    }
+                    return bContinue;
+                }
             }
          
             // Now that you are connected to Express, check the database versions
-
             Version databaseVersion; // The current version of the database
-
-            switch (CheckVersion(out databaseVersion))
+            int chkVer = CheckVersion(out databaseVersion);
+            VersionCheck verChk = (VersionCheck)chkVer;
+            //MessageBox.Show(string.Format("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString()));
+            Logging.ATSAdminLog.DebugFormat("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString());
+            switch (chkVer)
             {
                 case (int)VersionCheck.Equal:
                     {
@@ -61,24 +125,40 @@
                             // Run the apropriate upgdrade script(s)
                             case "1.0.0.0":
                                 {   // Current database is version 1.0.0.0, update to 1.0.0.1
+                                    Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
                                     bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
+                                    Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
                                     goto case "1.0.0.1"; // Continue and upgrade one more step
                                 }
                             case "1.0.0.1":
                                 {   // Current database is version 1.0.0.1, update to 1.0.0.2
+                                    Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.1", "1.0.0.2");
                                     bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
+                                    Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.2");
                                     goto case "1.0.0.2"; // Continue and upgrade one more step
                                 }
                             case "1.0.0.2":
                                 {   // Current database is version 1.0.0.2, update to 1.0.0.3
+                                    Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
                                     bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
+                                    Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
                                     break;
-                                }
-
-                            
+                                }                            
                             default:
                                 {
-                                    MessageBox.Show("Error: Not able to upgrade database (51188)");
+                                    //MessageBox.Show("Error: Not able to upgrade database (51188)");
+                                    if (databaseVersion == new Version(0, 0, 0, 0))
+                                    {
+                                        string format = string.Format("Database version is {0}, this should have been auto upgraded to {1}", databaseVersion.ToString(), CURRENT_DB_VERSION.ToString());
+                                        Logging.ATSAdminLog.Fatal(format);
+                                        MessageBox.Show(format);
+                                    }
+                                    else
+                                    {
+                                        string format = string.Format("Failed to upgrade Database from version: {0} to version: {1}", databaseVersion.ToString(), CURRENT_DB_VERSION.ToString());
+                                        Logging.ATSAdminLog.Fatal(format);
+                                        MessageBox.Show(format);
+                                    }                                    
                                     break;
                                 }
                         }
@@ -89,18 +169,6 @@
                         bContinue = false;
                         break;
                     }
-                case (int)VersionCheck.DatabaseNotFound:
-                    {
-                        //Run the creation script
-                        bContinue = RunScript(Resource1.CreateDatabase.ToString());
-                        if (bContinue)
-                        {
-                            // Set up file access rights for remote desktop users
-                            SetDatabaseRights(); 
-                        }
-
-                        break;
-                    }
                 default:
                     {
                         bContinue = false;
@@ -160,24 +228,45 @@
                 //Verify that the AnywhereTS database exists
                 sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
                 strResult = sqlCmd.ExecuteScalar().ToString();
-                
+
                 if (strResult == "0")
                 {
-
-                    return (int)VersionCheck.DatabaseNotFound;
+                    sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
+                    sqlCon.Open();
+                    RunScript(Resource1.CreateDatabase.ToString());
+                    sqlCon.Close();
+                    sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
+                    sqlCon.Open();
+                    RunScript(Resource1.CreateTables.ToString());
+                    sqlCon.Close();
                 }
-
                 try
                 {
+                    sqlCon.Close();
+                    sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
+                    sqlCon.Open();
                     sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
                     strResult = (string)sqlCmd.ExecuteScalar();
+                    sqlCon.Close();
                 }
                 catch
-                {   // Assume we could not open the physical file 
-                    //Drop the database
-                    sqlCmd = new SqlCommand("IF EXISTS(SELECT * FROM sysdatabases WHERE name='AnywhereTS')DROP DATABASE AnywhereTS", sqlCon);
-                    strResult = (string)sqlCmd.ExecuteScalar();
-                    return (int)VersionCheck.DatabaseNotFound;                        
+                {
+                    // the database exists, but one or more tables are missing
+                    try
+                    {
+                        sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
+                        sqlCon.Open();
+                        RunScript(Resource1.CreateTables.ToString());
+                        sqlCon.Close();
+                        sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
+                        sqlCon.Open();
+                        sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
+                        strResult = (string)sqlCmd.ExecuteScalar();
+                    }
+                    catch
+                    {
+                        return (int)VersionCheck.Failed;
+                    }
                 }
                 vDb = new Version(strResult);
           
@@ -212,7 +301,8 @@
             return commands;
         }
 
-     
+
+        [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
         public static void SetDatabaseRights()
         {
             try
@@ -265,7 +355,7 @@
             }
             catch
             {
-                MessageBox.Show("Error, could not configure SQL Browser service (24888). Please check that SQL Server Express is intalled and that you are logged in with sufficient rights to configure services. Then retry the operation.");
+                MessageBox.Show("Error, could not configure SQL Browser service (24888). Please check that SQL Server is intalled and that you are logged in with sufficient rights to configure services. Then retry the operation.");
             }
         }
 
@@ -284,7 +374,7 @@
             {
                 prot.Get();
                 if ((string)prot.GetPropertyValue("ProtocolName") == "Np" &&  //Named pipes
-                    (string)prot.GetPropertyValue("InstanceName") == "SQLEXPRESS")
+                    (string)prot.GetPropertyValue("InstanceName") == InstanceName)
                 {   // We found the named pipes protocol
                     if (!(bool)prot.GetPropertyValue("Enabled"))
                     {   // Named pipes not activated
@@ -292,7 +382,7 @@
               
                         // Check if user wants to restart SQL server
                         DialogResult resultRights;
-                        resultRights = MessageBox.Show("In order for users to use the AnywhereTS control panel, the SQL Express service on this computer need to be restarted. This operation might take up to 60 seconds. Do you want restart the SQL Express service now?", "AnywhereTS - Restart SQL Express (This operation might take up to 60 seconds!)", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
+                        resultRights = MessageBox.Show(string.Format("In order for users to use the AnywhereTS control panel, the {0} service on this computer need to be restarted. This operation might take up to 60 seconds. Do you want restart the {0} service now?", InstanceName), string.Format("AnywhereTS - Restart {0} (This operation might take up to 60 seconds!)", InstanceName), MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
                         if (resultRights == DialogResult.Yes)
                         {
                             // Restart the SQL server
@@ -301,7 +391,7 @@
                             foreach (ManagementObject svc in sqlServicesMan.GetInstances())
                             {
                                 if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
-                                    (string)svc.GetPropertyValue("ServiceName") == "MSSQL$SQLEXPRESS")
+                                    (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
                                 {
                                     svc.Get();
                                     if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)

 

  ViewVC Help
Powered by ViewVC 1.1.22