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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 41 - (hide annotations) (download)
Thu Jul 12 12:41:04 2012 UTC (7 years, 11 months ago) by william
File size: 23903 byte(s)
+ add logging in CheckVersion

1 william 4 using System;
2     using System.Collections.Generic;
3     using System.Text;
4     using System.Data;
5     using System.Data.SqlClient;
6     using System.Windows.Forms;
7     using System.Text.RegularExpressions;
8     using System.Management;
9 william 33 using log4net;
10 william 4
11     namespace AnywhereTS
12     {
13     class DatabaseSupport
14     {
15 william 36
16 william 4 enum VersionCheck
17     {
18 william 21 Failed = 0,
19     Equal,
20     DatabaseIsMoreNew,
21     DatabaseIsOlder,
22     //DatabaseNotFound,
23     DatabaseCreated,
24 william 4 };
25    
26     private SqlConnection sqlCon = new SqlConnection();
27     private SqlCommand sqlCmd = new SqlCommand();
28    
29     const string SQL_BROWSER_SERVICE_NAME = "SQLBrowser";
30 william 41 static Version CURRENT_DB_VERSION = new Version(1, 0, 0, 3);
31 william 19 public static string InstanceName
32     {
33     get
34     {
35     bool start = false;
36     bool end = false;
37     List<char> pChars = new List<char>();
38     foreach (char c in Properties.Settings.Default.atsConnectionString.ToCharArray())
39     {
40     if (c == '\\') { start = true; continue; }
41     if (c == ';') { end = true; }
42     if (end) break;
43     if (start) { pChars.Add(c); }
44     }
45     return new string(pChars.ToArray());
46     }
47     }
48 william 4 public bool SetupDatabase()
49     {
50     bool bContinue = false;
51     // Create a connection to SQL Server
52 william 36
53     Logging.ATSAdminLog.Debug("SetupDatabase() called ");
54 william 4 try
55 william 39 {
56     sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
57     using (log4net.NDC.Push(string.Format("ConnectionString={0}", Properties.Settings.Default.atsConnectionString)))
58     {
59     Logging.ATSAdminLog.Debug("Opening connection to AnywhereTS Database");
60     }
61 william 4 sqlCon.Open();
62     }
63 william 19 catch
64 william 4 {
65 william 18 //MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
66     //return bContinue;
67     //bCreateDB = true;
68     try
69     {
70 william 39 using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
71     {
72     Logging.ATSAdminLog.Debug("Creating Database AnywhereTS");
73     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
74     sqlCon.Open();
75     RunScript(Resource1.CreateDatabase.ToString());
76     sqlCon.Close();
77     }
78     Logging.ATSAdminLog.Debug("Created Database AnywhereTS");
79    
80     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
81     {
82     Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database");
83     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
84     sqlCon.Open();
85     RunScript(Resource1.CreateTables.ToString());
86     sqlCon.Close();
87     }
88     Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database");
89 william 18 }
90     catch (SqlException sql_ex1)
91     {
92 william 19 SqlException sql_ex = (sql_ex1.GetBaseException() as SqlException);
93     MessageBox.Show(string.Format("Failed to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
94 william 39
95     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())))
96     {
97     Logging.ATSAdminLog.Error("Failed to open connection to AnywhereTS Database");
98     }
99 william 18 return bContinue;
100     }
101 william 4 }
102    
103     // Now that you are connected to Express, check the database versions
104     Version databaseVersion; // The current version of the database
105 william 18 int chkVer = CheckVersion(out databaseVersion);
106     VersionCheck verChk = (VersionCheck)chkVer;
107 william 39 //MessageBox.Show(string.Format("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString()));
108     Logging.ATSAdminLog.DebugFormat("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString());
109 william 18 switch (chkVer)
110 william 4 {
111     case (int)VersionCheck.Equal:
112     {
113     bContinue = true;
114     break;
115     }
116     case (int)VersionCheck.Failed:
117     {
118     bContinue = false;
119     break;
120     }
121     case (int)VersionCheck.DatabaseIsOlder:
122     {
123     switch (databaseVersion.ToString())
124     {
125     // Run the apropriate upgdrade script(s)
126     case "1.0.0.0":
127     { // Current database is version 1.0.0.0, update to 1.0.0.1
128 william 39 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
129 william 4 bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
130 william 39 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
131 william 4 goto case "1.0.0.1"; // Continue and upgrade one more step
132     }
133     case "1.0.0.1":
134     { // Current database is version 1.0.0.1, update to 1.0.0.2
135 william 39 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.1", "1.0.0.2");
136 william 4 bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
137 william 39 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.2");
138 william 4 goto case "1.0.0.2"; // Continue and upgrade one more step
139     }
140     case "1.0.0.2":
141     { // Current database is version 1.0.0.2, update to 1.0.0.3
142 william 39 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
143 william 4 bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
144 william 39 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
145 william 4 break;
146 william 40 }
147 william 4 default:
148     {
149 william 40 //MessageBox.Show("Error: Not able to upgrade database (51188)");
150     if (databaseVersion == new Version(0, 0, 0, 0))
151     {
152     string format = string.Format("Database version is {0}, this should have been auto upgraded to {1}", databaseVersion.ToString(), CURRENT_DB_VERSION.ToString());
153     Logging.ATSAdminLog.Fatal(format);
154     MessageBox.Show(format);
155     }
156     else
157     {
158     string format = string.Format("Failed to upgrade Database from version: {0} to version: {1}", databaseVersion.ToString(), CURRENT_DB_VERSION.ToString());
159     Logging.ATSAdminLog.Fatal(format);
160     MessageBox.Show(format);
161     }
162 william 4 break;
163     }
164     }
165     break;
166     }
167     case (int)VersionCheck.DatabaseIsMoreNew:
168     {
169     bContinue = false;
170     break;
171     }
172     default:
173     {
174     bContinue = false;
175     break;
176     }
177    
178     }
179     sqlCon.Close();
180     sqlCon.Dispose();
181     sqlCmd.Connection.Close();
182     sqlCmd.Connection.Dispose();
183     return bContinue;
184     }
185    
186     // Run a SQL script (to create or update a database)
187     public bool RunScript(string strFile)
188     {
189     string[] strCommands;
190     strCommands = ParseScriptToCommands(strFile);
191     try
192     {
193     if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
194    
195     sqlCmd.Connection = sqlCon;
196    
197     foreach (string strCmd in strCommands)
198     {
199     if (strCmd.Length > 0)
200     {
201     // Substitute database directory with the decided one.
202     sqlCmd.CommandText = strCmd.Replace("[DataDir]",ProSupport.strDatabasePath);
203     sqlCmd.ExecuteNonQuery();
204     }
205     }
206     }
207     catch (SqlException sql_ex)
208     {
209     MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
210     return false;
211     }
212    
213     return true;
214     }
215    
216    
217     // Check the version of the datbase
218     public int CheckVersion(out Version vDb)
219     {
220     //Get Version information from application
221     Version v=new Version(ATSGlobals.strDatabaseVersion);
222     vDb = new Version("0.0.0.0"); // Assign a default value for version
223     try
224     {
225    
226     string strResult;
227    
228     //Verify that the AnywhereTS database exists
229 william 21
230 william 41 using (log4net.NDC.Push(string.Format("SQL STATMENT={0}", "select count(*) from master..sysdatabases where name='AnywhereTS'")))
231 william 4 {
232 william 41 Logging.ATSAdminLog.Debug("Getting Coount of AnywhereTS databases");
233     sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
234     strResult = sqlCmd.ExecuteScalar().ToString();
235 william 21 }
236 william 41 Logging.ATSAdminLog.DebugFormat("AnywhereTS databases Count={0}", strResult);
237    
238     if (strResult == "0")
239 william 4 {
240 william 41
241    
242     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
243 william 21 {
244 william 41 Logging.ATSAdminLog.Debug("Creating Database AnywhereTS in CheckVersion()");
245 william 21 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
246     sqlCon.Open();
247 william 41 RunScript(Resource1.CreateDatabase.ToString());
248     sqlCon.Close();
249     }
250     Logging.ATSAdminLog.Debug("Created Database AnywhereTS in CheckVersion()");
251    
252     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
253     {
254     Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database in CheckVersion()");
255     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
256     sqlCon.Open();
257 william 22 RunScript(Resource1.CreateTables.ToString());
258 william 21 sqlCon.Close();
259 william 41 }
260     Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database in CheckVersion()");
261     }
262     try
263     {
264     using (log4net.NDC.Push(string.Format("SQL Statment={0}", "SELECT value from AnywhereTS..AppInfo where property='version'")))
265     {
266     Logging.ATSAdminLog.Debug("Gettting AnywhereTS Database Version");
267     sqlCon.Close();
268 william 21 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
269 william 22 sqlCon.Open();
270 william 21 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
271     strResult = (string)sqlCmd.ExecuteScalar();
272 william 41 sqlCon.Close();
273 william 21 }
274 william 41 Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
275     }
276     catch(SqlException ex)
277     {
278     // the database exists, but one or more tables are missing
279     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())))
280 william 22 {
281 william 41 Logging.ATSAdminLog.Error("Failed to get database version");
282     }
283     try
284     {
285    
286     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
287     {
288     Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database in CheckVersion()");
289     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
290     sqlCon.Open();
291     RunScript(Resource1.CreateTables.ToString());
292     sqlCon.Close();
293     }
294     Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database in CheckVersion()");
295    
296     using (log4net.NDC.Push(string.Format("SQL Statment={0}", "SELECT value from AnywhereTS..AppInfo where property='version'")))
297     {
298     Logging.ATSAdminLog.Debug("Gettting AnywhereTS Database Version");
299     sqlCon.Close();
300     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
301     sqlCon.Open();
302     sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
303     strResult = (string)sqlCmd.ExecuteScalar();
304     sqlCon.Close();
305     }
306     Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
307     }
308     catch(SqlException ex1)
309     {
310     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())))
311     {
312     Logging.ATSAdminLog.Error("Failed to get database version");
313     }
314 william 22 return (int)VersionCheck.Failed;
315     }
316 william 4 }
317     vDb = new Version(strResult);
318    
319     sqlCon.Close();
320    
321     if (vDb == v)
322     return (int)VersionCheck.Equal;
323    
324     if (vDb > v)
325     return (int)VersionCheck.DatabaseIsMoreNew;
326    
327     else
328     return (int)VersionCheck.DatabaseIsOlder;
329    
330     }
331     catch (SqlException sql_ex)
332     {
333     MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
334 william 41 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())))
335     {
336     Logging.ATSAdminLog.Error("Failed to check database version");
337     }
338 william 4 return (int)VersionCheck.Failed;
339     }
340     catch (Exception system_ex)
341     {
342     MessageBox.Show(system_ex.Message.ToString());
343 william 41 using (log4net.NDC.Push(string.Format("SqlException: MESSAGE={0}{1}Diagnostics:{1}{2}", system_ex.Message, System.Environment.NewLine, system_ex.ToString())))
344     {
345     Logging.ATSAdminLog.Error("Failed to check database version");
346     }
347 william 4 return (int)VersionCheck.Failed;
348     }
349     }
350    
351     public string[] ParseScriptToCommands(string strScript)
352     {
353     string[] commands;
354     commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
355     return commands;
356     }
357    
358 william 21
359     [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
360 william 4 public static void SetDatabaseRights()
361     {
362     try
363     {
364     // Add an access control entry to the database file.
365     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
366     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
367     }
368     catch (Exception e)
369     {
370     MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
371     Application.Exit();
372     return;
373     }
374     }
375    
376     public static void StartSQLbrowserService()
377     {
378     System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
379     try
380     {
381     // Check that the SQL browser service is not already running
382     if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
383     { // Service not running, start it.
384     srvController.Start();
385     srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
386     }
387     }
388    
389     catch (Exception e)
390     {
391     MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
392     }
393     }
394    
395     // Configure the SQL browser service to autostart
396     public static void AutostartSQLbrowserService()
397     {
398     try
399     {
400     //construct the management path
401     string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
402     using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
403     {
404     object[] parameters = new object[1];
405     parameters[0] = "Automatic";
406     service.InvokeMethod("ChangeStartMode", parameters);
407    
408     }
409     }
410     catch
411     {
412 william 17 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.");
413 william 4 }
414     }
415    
416     // Enable named pipes on the SQL Express server
417     public static bool EnableNamedPipes()
418     {
419     ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
420     ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
421     ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
422     bool restarted = false; // Indicating if restart of SQL server was performed
423    
424     sqlServicesMan.Get();
425     serverProtocolsMan.Get();
426    
427     foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
428     {
429     prot.Get();
430     if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
431 william 17 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
432 william 4 { // We found the named pipes protocol
433     if (!(bool)prot.GetPropertyValue("Enabled"))
434     { // Named pipes not activated
435     prot.InvokeMethod("SetEnable", null); // Activate named pipes
436    
437     // Check if user wants to restart SQL server
438     DialogResult resultRights;
439 william 17 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);
440 william 4 if (resultRights == DialogResult.Yes)
441     {
442     // Restart the SQL server
443     const uint sqlServerService = 1;
444     const uint sqlServiceStopped = 1;
445     foreach (ManagementObject svc in sqlServicesMan.GetInstances())
446     {
447     if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
448 william 17 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
449 william 4 {
450     svc.Get();
451     if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
452     {
453     svc.InvokeMethod("StopService", null);
454     }
455     svc.InvokeMethod("StartService", null);
456     restarted = true;
457     } // end if
458     }
459     }
460     } // end if 'named pipes protool'
461     }
462     } // foreach
463    
464     return restarted;
465     }
466     }
467     }

  ViewVC Help
Powered by ViewVC 1.1.22