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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 45 - (show annotations) (download)
Thu Jul 12 13:01:44 2012 UTC (8 years, 11 months ago) by william
File size: 24157 byte(s)
remove one sqlCon.close()

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 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 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
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 if (sqlCon.State != ConnectionState.Open) 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 if (sqlCon.State != ConnectionState.Open) 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 = new SqlConnection(Properties.Settings.Default.atsConnectionString);
268 if (sqlCon.State != ConnectionState.Open) 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 if (sqlCon.State != ConnectionState.Open) 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 = new SqlConnection(Properties.Settings.Default.atsConnectionString);
299 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
300 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
301 strResult = (string)sqlCmd.ExecuteScalar();
302 sqlCon.Close();
303 }
304 Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
305 }
306 catch(SqlException ex1)
307 {
308 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())))
309 {
310 Logging.ATSAdminLog.Error("Failed to get database version");
311 }
312 return (int)VersionCheck.Failed;
313 }
314 }
315 vDb = new Version(strResult);
316
317 sqlCon.Close();
318
319 if (vDb == v)
320 return (int)VersionCheck.Equal;
321
322 if (vDb > v)
323 return (int)VersionCheck.DatabaseIsMoreNew;
324
325 else
326 return (int)VersionCheck.DatabaseIsOlder;
327
328 }
329 catch (SqlException sql_ex)
330 {
331 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
332 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())))
333 {
334 Logging.ATSAdminLog.Error("Failed to check database version");
335 }
336 return (int)VersionCheck.Failed;
337 }
338 catch (Exception system_ex)
339 {
340 MessageBox.Show(system_ex.Message.ToString());
341 using (log4net.NDC.Push(string.Format("SqlException: MESSAGE={0}{1}Diagnostics:{1}{2}", system_ex.Message, System.Environment.NewLine, system_ex.ToString())))
342 {
343 Logging.ATSAdminLog.Error("Failed to check database version");
344 }
345 return (int)VersionCheck.Failed;
346 }
347 }
348
349 public string[] ParseScriptToCommands(string strScript)
350 {
351 string[] commands;
352 commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
353 return commands;
354 }
355
356
357 [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
358 public static void SetDatabaseRights()
359 {
360 try
361 {
362 // Add an access control entry to the database file.
363 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
364 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
365 }
366 catch (Exception e)
367 {
368 MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
369 Application.Exit();
370 return;
371 }
372 }
373
374 public static void StartSQLbrowserService()
375 {
376 System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
377 try
378 {
379 // Check that the SQL browser service is not already running
380 if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
381 { // Service not running, start it.
382 srvController.Start();
383 srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
384 }
385 }
386
387 catch (Exception e)
388 {
389 MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
390 }
391 }
392
393 // Configure the SQL browser service to autostart
394 public static void AutostartSQLbrowserService()
395 {
396 try
397 {
398 //construct the management path
399 string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
400 using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
401 {
402 object[] parameters = new object[1];
403 parameters[0] = "Automatic";
404 service.InvokeMethod("ChangeStartMode", parameters);
405
406 }
407 }
408 catch
409 {
410 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.");
411 }
412 }
413
414 // Enable named pipes on the SQL Express server
415 public static bool EnableNamedPipes()
416 {
417 ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
418 ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
419 ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
420 bool restarted = false; // Indicating if restart of SQL server was performed
421
422 sqlServicesMan.Get();
423 serverProtocolsMan.Get();
424
425 foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
426 {
427 prot.Get();
428 if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
429 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
430 { // We found the named pipes protocol
431 if (!(bool)prot.GetPropertyValue("Enabled"))
432 { // Named pipes not activated
433 prot.InvokeMethod("SetEnable", null); // Activate named pipes
434
435 // Check if user wants to restart SQL server
436 DialogResult resultRights;
437 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);
438 if (resultRights == DialogResult.Yes)
439 {
440 // Restart the SQL server
441 const uint sqlServerService = 1;
442 const uint sqlServiceStopped = 1;
443 foreach (ManagementObject svc in sqlServicesMan.GetInstances())
444 {
445 if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
446 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
447 {
448 svc.Get();
449 if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
450 {
451 svc.InvokeMethod("StopService", null);
452 }
453 svc.InvokeMethod("StartService", null);
454 restarted = true;
455 } // end if
456 }
457 }
458 } // end if 'named pipes protool'
459 }
460 } // foreach
461
462 return restarted;
463 }
464 }
465 }

  ViewVC Help
Powered by ViewVC 1.1.22