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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 44 - (show annotations) (download)
Thu Jul 12 12:59:45 2012 UTC (7 years, 10 months ago) by william
File size: 24243 byte(s)
+ make sure connection is not already opened before attempted to open it

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.Close();
268 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
269 if (sqlCon.State != ConnectionState.Open) 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 if (sqlCon.State != ConnectionState.Open) 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 if (sqlCon.State != ConnectionState.Open) 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