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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 43 - (show annotations) (download)
Thu Jul 12 12:43:29 2012 UTC (7 years, 11 months ago) by william
File size: 23833 byte(s)
use ATSGlobals.strDatabaseVersion instead of duplicating the value in Database.cs

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 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 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 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 return false;
210 }
211
212 return true;
213 }
214
215
216 // Check the version of the datbase
217 public int CheckVersion(out Version vDb)
218 {
219 //Get Version information from application
220 Version v=new Version(ATSGlobals.strDatabaseVersion);
221 vDb = new Version("0.0.0.0"); // Assign a default value for version
222 try
223 {
224
225 string strResult;
226
227 //Verify that the AnywhereTS database exists
228
229 using (log4net.NDC.Push(string.Format("SQL STATMENT={0}", "select count(*) from master..sysdatabases where name='AnywhereTS'")))
230 {
231 Logging.ATSAdminLog.Debug("Getting Coount of AnywhereTS databases");
232 sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
233 strResult = sqlCmd.ExecuteScalar().ToString();
234 }
235 Logging.ATSAdminLog.DebugFormat("AnywhereTS databases Count={0}", strResult);
236
237 if (strResult == "0")
238 {
239
240
241 using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
242 {
243 Logging.ATSAdminLog.Debug("Creating Database AnywhereTS in CheckVersion()");
244 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
245 sqlCon.Open();
246 RunScript(Resource1.CreateDatabase.ToString());
247 sqlCon.Close();
248 }
249 Logging.ATSAdminLog.Debug("Created Database AnywhereTS in CheckVersion()");
250
251 using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
252 {
253 Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database in CheckVersion()");
254 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
255 sqlCon.Open();
256 RunScript(Resource1.CreateTables.ToString());
257 sqlCon.Close();
258 }
259 Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database in CheckVersion()");
260 }
261 try
262 {
263 using (log4net.NDC.Push(string.Format("SQL Statment={0}", "SELECT value from AnywhereTS..AppInfo where property='version'")))
264 {
265 Logging.ATSAdminLog.Debug("Gettting AnywhereTS Database Version");
266 sqlCon.Close();
267 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
268 sqlCon.Open();
269 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
270 strResult = (string)sqlCmd.ExecuteScalar();
271 sqlCon.Close();
272 }
273 Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
274 }
275 catch(SqlException ex)
276 {
277 // the database exists, but one or more tables are missing
278 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())))
279 {
280 Logging.ATSAdminLog.Error("Failed to get database version");
281 }
282 try
283 {
284
285 using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
286 {
287 Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database in CheckVersion()");
288 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
289 sqlCon.Open();
290 RunScript(Resource1.CreateTables.ToString());
291 sqlCon.Close();
292 }
293 Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database in CheckVersion()");
294
295 using (log4net.NDC.Push(string.Format("SQL Statment={0}", "SELECT value from AnywhereTS..AppInfo where property='version'")))
296 {
297 Logging.ATSAdminLog.Debug("Gettting AnywhereTS Database Version");
298 sqlCon.Close();
299 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
300 sqlCon.Open();
301 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
302 strResult = (string)sqlCmd.ExecuteScalar();
303 sqlCon.Close();
304 }
305 Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
306 }
307 catch(SqlException ex1)
308 {
309 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())))
310 {
311 Logging.ATSAdminLog.Error("Failed to get database version");
312 }
313 return (int)VersionCheck.Failed;
314 }
315 }
316 vDb = new Version(strResult);
317
318 sqlCon.Close();
319
320 if (vDb == v)
321 return (int)VersionCheck.Equal;
322
323 if (vDb > v)
324 return (int)VersionCheck.DatabaseIsMoreNew;
325
326 else
327 return (int)VersionCheck.DatabaseIsOlder;
328
329 }
330 catch (SqlException sql_ex)
331 {
332 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
333 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())))
334 {
335 Logging.ATSAdminLog.Error("Failed to check database version");
336 }
337 return (int)VersionCheck.Failed;
338 }
339 catch (Exception system_ex)
340 {
341 MessageBox.Show(system_ex.Message.ToString());
342 using (log4net.NDC.Push(string.Format("SqlException: MESSAGE={0}{1}Diagnostics:{1}{2}", system_ex.Message, System.Environment.NewLine, system_ex.ToString())))
343 {
344 Logging.ATSAdminLog.Error("Failed to check database version");
345 }
346 return (int)VersionCheck.Failed;
347 }
348 }
349
350 public string[] ParseScriptToCommands(string strScript)
351 {
352 string[] commands;
353 commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
354 return commands;
355 }
356
357
358 [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
359 public static void SetDatabaseRights()
360 {
361 try
362 {
363 // Add an access control entry to the database file.
364 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
365 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
366 }
367 catch (Exception e)
368 {
369 MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
370 Application.Exit();
371 return;
372 }
373 }
374
375 public static void StartSQLbrowserService()
376 {
377 System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
378 try
379 {
380 // Check that the SQL browser service is not already running
381 if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
382 { // Service not running, start it.
383 srvController.Start();
384 srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
385 }
386 }
387
388 catch (Exception e)
389 {
390 MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
391 }
392 }
393
394 // Configure the SQL browser service to autostart
395 public static void AutostartSQLbrowserService()
396 {
397 try
398 {
399 //construct the management path
400 string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
401 using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
402 {
403 object[] parameters = new object[1];
404 parameters[0] = "Automatic";
405 service.InvokeMethod("ChangeStartMode", parameters);
406
407 }
408 }
409 catch
410 {
411 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.");
412 }
413 }
414
415 // Enable named pipes on the SQL Express server
416 public static bool EnableNamedPipes()
417 {
418 ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
419 ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
420 ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
421 bool restarted = false; // Indicating if restart of SQL server was performed
422
423 sqlServicesMan.Get();
424 serverProtocolsMan.Get();
425
426 foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
427 {
428 prot.Get();
429 if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
430 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
431 { // We found the named pipes protocol
432 if (!(bool)prot.GetPropertyValue("Enabled"))
433 { // Named pipes not activated
434 prot.InvokeMethod("SetEnable", null); // Activate named pipes
435
436 // Check if user wants to restart SQL server
437 DialogResult resultRights;
438 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);
439 if (resultRights == DialogResult.Yes)
440 {
441 // Restart the SQL server
442 const uint sqlServerService = 1;
443 const uint sqlServiceStopped = 1;
444 foreach (ManagementObject svc in sqlServicesMan.GetInstances())
445 {
446 if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
447 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
448 {
449 svc.Get();
450 if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
451 {
452 svc.InvokeMethod("StopService", null);
453 }
454 svc.InvokeMethod("StartService", null);
455 restarted = true;
456 } // end if
457 }
458 }
459 } // end if 'named pipes protool'
460 }
461 } // foreach
462
463 return restarted;
464 }
465 }
466 }

  ViewVC Help
Powered by ViewVC 1.1.22