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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 41 - (show 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 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 using log4net;
10
11 namespace AnywhereTS
12 {
13 class DatabaseSupport
14 {
15
16 enum VersionCheck
17 {
18 Failed = 0,
19 Equal,
20 DatabaseIsMoreNew,
21 DatabaseIsOlder,
22 //DatabaseNotFound,
23 DatabaseCreated,
24 };
25
26 private SqlConnection sqlCon = new SqlConnection();
27 private SqlCommand sqlCmd = new SqlCommand();
28
29 const string SQL_BROWSER_SERVICE_NAME = "SQLBrowser";
30 static Version CURRENT_DB_VERSION = new Version(1, 0, 0, 3);
31 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 public bool SetupDatabase()
49 {
50 bool bContinue = false;
51 // Create a connection to SQL Server
52
53 Logging.ATSAdminLog.Debug("SetupDatabase() called ");
54 try
55 {
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 sqlCon.Open();
62 }
63 catch
64 {
65 //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 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 }
90 catch (SqlException sql_ex1)
91 {
92 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
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 return bContinue;
100 }
101 }
102
103 // Now that you are connected to Express, check the database versions
104 Version databaseVersion; // The current version of the database
105 int chkVer = CheckVersion(out databaseVersion);
106 VersionCheck verChk = (VersionCheck)chkVer;
107 //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 switch (chkVer)
110 {
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 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
129 bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
130 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
131 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 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.1", "1.0.0.2");
136 bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
137 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.2");
138 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 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
143 bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
144 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
145 break;
146 }
147 default:
148 {
149 //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 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
230 using (log4net.NDC.Push(string.Format("SQL STATMENT={0}", "select count(*) from master..sysdatabases where name='AnywhereTS'")))
231 {
232 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 }
236 Logging.ATSAdminLog.DebugFormat("AnywhereTS databases Count={0}", strResult);
237
238 if (strResult == "0")
239 {
240
241
242 using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
243 {
244 Logging.ATSAdminLog.Debug("Creating Database AnywhereTS in CheckVersion()");
245 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
246 sqlCon.Open();
247 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 RunScript(Resource1.CreateTables.ToString());
258 sqlCon.Close();
259 }
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 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
269 sqlCon.Open();
270 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
271 strResult = (string)sqlCmd.ExecuteScalar();
272 sqlCon.Close();
273 }
274 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 {
281 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 return (int)VersionCheck.Failed;
315 }
316 }
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 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 return (int)VersionCheck.Failed;
339 }
340 catch (Exception system_ex)
341 {
342 MessageBox.Show(system_ex.Message.ToString());
343 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 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
359 [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
360 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 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 }
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 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
432 { // 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 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 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 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
449 {
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