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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 40 - (show annotations) (download)
Thu Jul 12 12:25:58 2012 UTC (7 years, 7 months ago) by william
File size: 20064 byte(s)
+ more logging and changes

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 const 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 sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
230 strResult = sqlCmd.ExecuteScalar().ToString();
231
232 if (strResult == "0")
233 {
234 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
235 sqlCon.Open();
236 RunScript(Resource1.CreateDatabase.ToString());
237 sqlCon.Close();
238 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
239 sqlCon.Open();
240 RunScript(Resource1.CreateTables.ToString());
241 sqlCon.Close();
242 }
243 try
244 {
245 sqlCon.Close();
246 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
247 sqlCon.Open();
248 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
249 strResult = (string)sqlCmd.ExecuteScalar();
250 sqlCon.Close();
251 }
252 catch
253 {
254 // the database exists, but one or more tables are missing
255 try
256 {
257 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
258 sqlCon.Open();
259 RunScript(Resource1.CreateTables.ToString());
260 sqlCon.Close();
261 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
262 sqlCon.Open();
263 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
264 strResult = (string)sqlCmd.ExecuteScalar();
265 }
266 catch
267 {
268 return (int)VersionCheck.Failed;
269 }
270 }
271 vDb = new Version(strResult);
272
273 sqlCon.Close();
274
275 if (vDb == v)
276 return (int)VersionCheck.Equal;
277
278 if (vDb > v)
279 return (int)VersionCheck.DatabaseIsMoreNew;
280
281 else
282 return (int)VersionCheck.DatabaseIsOlder;
283
284 }
285 catch (SqlException sql_ex)
286 {
287 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
288 return (int)VersionCheck.Failed;
289 }
290 catch (Exception system_ex)
291 {
292 MessageBox.Show(system_ex.Message.ToString());
293 return (int)VersionCheck.Failed;
294 }
295 }
296
297 public string[] ParseScriptToCommands(string strScript)
298 {
299 string[] commands;
300 commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
301 return commands;
302 }
303
304
305 [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
306 public static void SetDatabaseRights()
307 {
308 try
309 {
310 // Add an access control entry to the database file.
311 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
312 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
313 }
314 catch (Exception e)
315 {
316 MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
317 Application.Exit();
318 return;
319 }
320 }
321
322 public static void StartSQLbrowserService()
323 {
324 System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
325 try
326 {
327 // Check that the SQL browser service is not already running
328 if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
329 { // Service not running, start it.
330 srvController.Start();
331 srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
332 }
333 }
334
335 catch (Exception e)
336 {
337 MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
338 }
339 }
340
341 // Configure the SQL browser service to autostart
342 public static void AutostartSQLbrowserService()
343 {
344 try
345 {
346 //construct the management path
347 string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
348 using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
349 {
350 object[] parameters = new object[1];
351 parameters[0] = "Automatic";
352 service.InvokeMethod("ChangeStartMode", parameters);
353
354 }
355 }
356 catch
357 {
358 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.");
359 }
360 }
361
362 // Enable named pipes on the SQL Express server
363 public static bool EnableNamedPipes()
364 {
365 ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
366 ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
367 ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
368 bool restarted = false; // Indicating if restart of SQL server was performed
369
370 sqlServicesMan.Get();
371 serverProtocolsMan.Get();
372
373 foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
374 {
375 prot.Get();
376 if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
377 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
378 { // We found the named pipes protocol
379 if (!(bool)prot.GetPropertyValue("Enabled"))
380 { // Named pipes not activated
381 prot.InvokeMethod("SetEnable", null); // Activate named pipes
382
383 // Check if user wants to restart SQL server
384 DialogResult resultRights;
385 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);
386 if (resultRights == DialogResult.Yes)
387 {
388 // Restart the SQL server
389 const uint sqlServerService = 1;
390 const uint sqlServiceStopped = 1;
391 foreach (ManagementObject svc in sqlServicesMan.GetInstances())
392 {
393 if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
394 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
395 {
396 svc.Get();
397 if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
398 {
399 svc.InvokeMethod("StopService", null);
400 }
401 svc.InvokeMethod("StartService", null);
402 restarted = true;
403 } // end if
404 }
405 }
406 } // end if 'named pipes protool'
407 }
408 } // foreach
409
410 return restarted;
411 }
412 }
413 }

  ViewVC Help
Powered by ViewVC 1.1.22