/[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 17:45:48	18
+++ trunk/TSAdminTool/Database.cs	2012/07/12 12:43:29	43
@@ -6,62 +6,105 @@
 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 = Properties.Settings.Default.atsConnectionString.Substring(Properties.Settings.Default.atsConnectionString.IndexOf(@"\"), Properties.Settings.Default.atsConnectionString.IndexOf(@";Database")).Replace(@"\", "").Replace(@";", "").Replace(@"Database", "");
+        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;
-            bool bCreateDB = 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(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
                 {
-                    sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS","master");
-                    sqlCon.Open();
-                    RunScript(Resource1.CreateDatabase.ToString());
-                    sqlCon.Close();
-                    sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
-                    sqlCon.Open();
+                    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)
                 {
-                    MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex1.Number.ToString() + " " + sql_ex1.Message.ToString());
+                    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
-
             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:
@@ -81,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;
                                 }
                         }
@@ -109,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;
@@ -178,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);
           
@@ -216,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;
             }
         }
@@ -232,7 +354,8 @@
             return commands;
         }
 
-     
+
+        [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
         public static void SetDatabaseRights()
         {
             try

 

  ViewVC Help
Powered by ViewVC 1.1.22