/[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 14:19:13	4
+++ trunk/TSAdminTool/Database.cs	2012/07/12 12:43:29	43
@@ -6,43 +6,106 @@
 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";
-        
+        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 = "Server=.\\sqlexpress;Integrated Security=true";
+            {                
+                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 +124,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(), ATSGlobals.strDatabaseVersion);
+                                        Logging.ATSAdminLog.Fatal(format);
+                                        MessageBox.Show(format);
+                                    }
+                                    else
+                                    {
+                                        string format = string.Format("Failed to upgrade Database from version: {0} to version: {1}", databaseVersion.ToString(), ATSGlobals.strDatabaseVersion);
+                                        Logging.ATSAdminLog.Fatal(format);
+                                        MessageBox.Show(format);
+                                    }                                    
                                     break;
                                 }
                         }
@@ -89,18 +168,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;
@@ -158,26 +225,93 @@
                 string strResult;
 
                 //Verify that the AnywhereTS database exists
-                sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
-                strResult = sqlCmd.ExecuteScalar().ToString();
-                
+
+                using (log4net.NDC.Push(string.Format("SQL STATMENT={0}", "select count(*) from master..sysdatabases where name='AnywhereTS'")))
+                {
+                    Logging.ATSAdminLog.Debug("Getting Coount of AnywhereTS databases");
+                      sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
+                        strResult = sqlCmd.ExecuteScalar().ToString();
+                }
+               Logging.ATSAdminLog.DebugFormat("AnywhereTS databases Count={0}", strResult);
+
                 if (strResult == "0")
                 {
 
-                    return (int)VersionCheck.DatabaseNotFound;
-                }
 
+                    using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
+                    {
+                        Logging.ATSAdminLog.Debug("Creating Database AnywhereTS in CheckVersion()");
+                        sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
+                        sqlCon.Open();
+                        RunScript(Resource1.CreateDatabase.ToString());
+                        sqlCon.Close();
+                    }
+                    Logging.ATSAdminLog.Debug("Created Database AnywhereTS in CheckVersion()");
+
+                    using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
+                    {
+                        Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database in CheckVersion()");
+                        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 in CheckVersion()");
+                }
                 try
                 {
-                    sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
-                    strResult = (string)sqlCmd.ExecuteScalar();
+                    using (log4net.NDC.Push(string.Format("SQL Statment={0}", "SELECT value from AnywhereTS..AppInfo where property='version'")))
+                    {
+                        Logging.ATSAdminLog.Debug("Gettting AnywhereTS Database Version");
+                        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();
+                    }
+                    Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
                 }
-                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;                        
+                catch(SqlException ex)
+                {
+                    // the database exists, but one or more tables are missing
+                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
+                    {
+                        Logging.ATSAdminLog.Error("Failed to get database version");
+                    }
+                    try
+                    {
+
+                        using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
+                        {
+                            Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database in CheckVersion()");
+                            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 in CheckVersion()");
+
+                        using (log4net.NDC.Push(string.Format("SQL Statment={0}", "SELECT value from AnywhereTS..AppInfo where property='version'")))
+                        {
+                            Logging.ATSAdminLog.Debug("Gettting AnywhereTS Database Version");
+                            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();
+                        }
+                        Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
+                    }
+                    catch(SqlException ex1)
+                    {
+                        using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex1.Number.ToString(), ex1.Message, System.Environment.NewLine, ex1.ToString())))
+                        {
+                            Logging.ATSAdminLog.Error("Failed to get database version");
+                        }
+                        return (int)VersionCheck.Failed;
+                    }
                 }
                 vDb = new Version(strResult);
           
@@ -196,11 +330,19 @@
             catch (SqlException sql_ex)
             {
                 MessageBox.Show(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 check database version");
+                }
                 return (int)VersionCheck.Failed;
             }
             catch (Exception system_ex)
             {
                 MessageBox.Show(system_ex.Message.ToString());
+                using (log4net.NDC.Push(string.Format("SqlException: MESSAGE={0}{1}Diagnostics:{1}{2}", system_ex.Message, System.Environment.NewLine, system_ex.ToString())))
+                {
+                    Logging.ATSAdminLog.Error("Failed to check database version");
+                }
                 return (int)VersionCheck.Failed;
             }
         }
@@ -212,7 +354,8 @@
             return commands;
         }
 
-     
+
+        [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
         public static void SetDatabaseRights()
         {
             try
@@ -265,7 +408,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 +427,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 +435,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 +444,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