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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 51 - (show annotations) (download)
Thu Jul 12 16:21:35 2012 UTC (8 years ago) by william
File size: 25599 byte(s)
fix an issue that was resolved by connecting to master database instead of AnywhereTS database

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 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 public bool SetupDatabase()
48 {
49 bool bContinue = false;
50 // Create a connection to SQL Server
51
52 Logging.ATSAdminLog.Debug("SetupDatabase() called ");
53 try
54 {
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 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
61 }
62 catch
63 {
64 //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 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 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
74 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 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
84 RunScript(Resource1.CreateTables.ToString());
85 //sqlCon.Close();
86 }
87 Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database");
88 }
89 catch (SqlException sql_ex1)
90 {
91 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
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 return bContinue;
99 }
100 }
101
102 // Now that you are connected to Express, check the database versions
103 Version databaseVersion; // The current version of the database
104 int chkVer = CheckVersion(out databaseVersion);
105 VersionCheck verChk = (VersionCheck)chkVer;
106 //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 switch (chkVer)
109 {
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 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
128 bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
129 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
130 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 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.1", "1.0.0.2");
135 bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
136 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.2");
137 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 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
142 bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
143 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
144 break;
145 }
146 default:
147 {
148 //MessageBox.Show("Error: Not able to upgrade database (51188)");
149 if (databaseVersion == new Version(0, 0, 0, 0))
150 {
151 string format = string.Format("Database version is {0}, this should have been auto upgraded to {1}", databaseVersion.ToString(), ATSGlobals.strDatabaseVersion);
152 Logging.ATSAdminLog.Fatal(format);
153 MessageBox.Show(format);
154 }
155 else
156 {
157 string format = string.Format("Failed to upgrade Database from version: {0} to version: {1}", databaseVersion.ToString(), ATSGlobals.strDatabaseVersion);
158 Logging.ATSAdminLog.Fatal(format);
159 MessageBox.Show(format);
160 }
161 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 {
208 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
209 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 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
233 using (log4net.NDC.Push(string.Format("SQL STATMENT={0}", "select count(*) from master..sysdatabases where name='AnywhereTS'")))
234 {
235 Logging.ATSAdminLog.Debug("Getting Coount of AnywhereTS databases");
236 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 }
240 Logging.ATSAdminLog.DebugFormat("AnywhereTS databases Count={0}", strResult);
241
242 if (strResult == "0")
243 {
244
245
246 using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
247 {
248 Logging.ATSAdminLog.Debug("Creating Database AnywhereTS in CheckVersion()");
249 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
250 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
251 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 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
261 RunScript(Resource1.CreateTables.ToString());
262 sqlCon.Close();
263 }
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("Getting AnywhereTS Database Version#1");
271 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
272 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
273 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
274 strResult = (string)sqlCmd.ExecuteScalar();
275 sqlCon.Close();
276 }
277 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 {
284 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 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
294 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("Getting AnywhereTS Database Version#2");
302 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
303 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
304 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 return (int)VersionCheck.Failed;
317 }
318 }
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 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 return (int)VersionCheck.Failed;
341 }
342 catch (Exception system_ex)
343 {
344 MessageBox.Show(system_ex.Message.ToString());
345 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 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
361 [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
362 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 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 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 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 }
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 catch(Exception e)
421 {
422 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 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 }
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 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
446 { // 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 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 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 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
463 {
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