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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 46 - (hide annotations) (download)
Thu Jul 12 14:17:14 2012 UTC (7 years, 11 months ago) by william
File size: 25533 byte(s)
+ add logging across ATSAdmin Tool project

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

  ViewVC Help
Powered by ViewVC 1.1.22