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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 39 - (show annotations) (download)
Thu Jul 12 12:21:43 2012 UTC (9 years, 2 months ago) by william
File size: 19259 byte(s)
+ add logging messages

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
148
149 default:
150 {
151 MessageBox.Show("Error: Not able to upgrade database (51188)");
152 Logging.ATSAdminLog.DebugFormat("Failed to upgrade Database from version: {0} to version: {1}", databaseVersion.ToString(), CURRENT_DB_VERSION.ToString());
153 break;
154 }
155 }
156 break;
157 }
158 case (int)VersionCheck.DatabaseIsMoreNew:
159 {
160 bContinue = false;
161 break;
162 }
163 default:
164 {
165 bContinue = false;
166 break;
167 }
168
169 }
170 sqlCon.Close();
171 sqlCon.Dispose();
172 sqlCmd.Connection.Close();
173 sqlCmd.Connection.Dispose();
174 return bContinue;
175 }
176
177 // Run a SQL script (to create or update a database)
178 public bool RunScript(string strFile)
179 {
180 string[] strCommands;
181 strCommands = ParseScriptToCommands(strFile);
182 try
183 {
184 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
185
186 sqlCmd.Connection = sqlCon;
187
188 foreach (string strCmd in strCommands)
189 {
190 if (strCmd.Length > 0)
191 {
192 // Substitute database directory with the decided one.
193 sqlCmd.CommandText = strCmd.Replace("[DataDir]",ProSupport.strDatabasePath);
194 sqlCmd.ExecuteNonQuery();
195 }
196 }
197 }
198 catch (SqlException sql_ex)
199 {
200 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
201 return false;
202 }
203
204 return true;
205 }
206
207
208 // Check the version of the datbase
209 public int CheckVersion(out Version vDb)
210 {
211 //Get Version information from application
212 Version v=new Version(ATSGlobals.strDatabaseVersion);
213 vDb = new Version("0.0.0.0"); // Assign a default value for version
214 try
215 {
216
217 string strResult;
218
219 //Verify that the AnywhereTS database exists
220 sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
221 strResult = sqlCmd.ExecuteScalar().ToString();
222
223 if (strResult == "0")
224 {
225 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
226 sqlCon.Open();
227 RunScript(Resource1.CreateDatabase.ToString());
228 sqlCon.Close();
229 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
230 sqlCon.Open();
231 RunScript(Resource1.CreateTables.ToString());
232 sqlCon.Close();
233 }
234 try
235 {
236 sqlCon.Close();
237 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
238 sqlCon.Open();
239 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
240 strResult = (string)sqlCmd.ExecuteScalar();
241 sqlCon.Close();
242 }
243 catch
244 {
245 // the database exists, but one or more tables are missing
246 try
247 {
248 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
249 sqlCon.Open();
250 RunScript(Resource1.CreateTables.ToString());
251 sqlCon.Close();
252 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
253 sqlCon.Open();
254 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
255 strResult = (string)sqlCmd.ExecuteScalar();
256 }
257 catch
258 {
259 return (int)VersionCheck.Failed;
260 }
261 }
262 vDb = new Version(strResult);
263
264 sqlCon.Close();
265
266 if (vDb == v)
267 return (int)VersionCheck.Equal;
268
269 if (vDb > v)
270 return (int)VersionCheck.DatabaseIsMoreNew;
271
272 else
273 return (int)VersionCheck.DatabaseIsOlder;
274
275 }
276 catch (SqlException sql_ex)
277 {
278 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
279 return (int)VersionCheck.Failed;
280 }
281 catch (Exception system_ex)
282 {
283 MessageBox.Show(system_ex.Message.ToString());
284 return (int)VersionCheck.Failed;
285 }
286 }
287
288 public string[] ParseScriptToCommands(string strScript)
289 {
290 string[] commands;
291 commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
292 return commands;
293 }
294
295
296 [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
297 public static void SetDatabaseRights()
298 {
299 try
300 {
301 // Add an access control entry to the database file.
302 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
303 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
304 }
305 catch (Exception e)
306 {
307 MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
308 Application.Exit();
309 return;
310 }
311 }
312
313 public static void StartSQLbrowserService()
314 {
315 System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
316 try
317 {
318 // Check that the SQL browser service is not already running
319 if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
320 { // Service not running, start it.
321 srvController.Start();
322 srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
323 }
324 }
325
326 catch (Exception e)
327 {
328 MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
329 }
330 }
331
332 // Configure the SQL browser service to autostart
333 public static void AutostartSQLbrowserService()
334 {
335 try
336 {
337 //construct the management path
338 string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
339 using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
340 {
341 object[] parameters = new object[1];
342 parameters[0] = "Automatic";
343 service.InvokeMethod("ChangeStartMode", parameters);
344
345 }
346 }
347 catch
348 {
349 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.");
350 }
351 }
352
353 // Enable named pipes on the SQL Express server
354 public static bool EnableNamedPipes()
355 {
356 ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
357 ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
358 ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
359 bool restarted = false; // Indicating if restart of SQL server was performed
360
361 sqlServicesMan.Get();
362 serverProtocolsMan.Get();
363
364 foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
365 {
366 prot.Get();
367 if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
368 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
369 { // We found the named pipes protocol
370 if (!(bool)prot.GetPropertyValue("Enabled"))
371 { // Named pipes not activated
372 prot.InvokeMethod("SetEnable", null); // Activate named pipes
373
374 // Check if user wants to restart SQL server
375 DialogResult resultRights;
376 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);
377 if (resultRights == DialogResult.Yes)
378 {
379 // Restart the SQL server
380 const uint sqlServerService = 1;
381 const uint sqlServiceStopped = 1;
382 foreach (ManagementObject svc in sqlServicesMan.GetInstances())
383 {
384 if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
385 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
386 {
387 svc.Get();
388 if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
389 {
390 svc.InvokeMethod("StopService", null);
391 }
392 svc.InvokeMethod("StartService", null);
393 restarted = true;
394 } // end if
395 }
396 }
397 } // end if 'named pipes protool'
398 }
399 } // foreach
400
401 return restarted;
402 }
403 }
404 }

  ViewVC Help
Powered by ViewVC 1.1.22