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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 33 - (show annotations) (download)
Thu Jul 12 10:52:05 2012 UTC (7 years, 11 months ago) by william
File size: 16819 byte(s)
+ attempt to add log4net support to ATSAdmin

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 private readonly ILog logger = LogManager.GetLogger("AnywhereTS.ATSAmdinLogger");
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
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 logger.Debug("SetupDatabase() called ");
53 try
54 {
55 sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
56 sqlCon.Open();
57 }
58 catch
59 {
60 //MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
61 //return bContinue;
62 //bCreateDB = true;
63 try
64 {
65 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
66 sqlCon.Open();
67 RunScript(Resource1.CreateDatabase.ToString());
68 sqlCon.Close();
69 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
70 sqlCon.Open();
71 RunScript(Resource1.CreateTables.ToString());
72 sqlCon.Close();
73 //sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
74 //sqlCon.Open();
75 }
76 catch (SqlException sql_ex1)
77 {
78 SqlException sql_ex = (sql_ex1.GetBaseException() as SqlException);
79 MessageBox.Show(string.Format("Failed to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
80 return bContinue;
81 }
82 }
83
84 // Now that you are connected to Express, check the database versions
85 Version databaseVersion; // The current version of the database
86 int chkVer = CheckVersion(out databaseVersion);
87 VersionCheck verChk = (VersionCheck)chkVer;
88 MessageBox.Show(string.Format("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString()));
89 switch (chkVer)
90 {
91 case (int)VersionCheck.Equal:
92 {
93 bContinue = true;
94 break;
95 }
96 case (int)VersionCheck.Failed:
97 {
98 bContinue = false;
99 break;
100 }
101 case (int)VersionCheck.DatabaseIsOlder:
102 {
103 switch (databaseVersion.ToString())
104 {
105 // Run the apropriate upgdrade script(s)
106 case "1.0.0.0":
107 { // Current database is version 1.0.0.0, update to 1.0.0.1
108 bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
109 goto case "1.0.0.1"; // Continue and upgrade one more step
110 }
111 case "1.0.0.1":
112 { // Current database is version 1.0.0.1, update to 1.0.0.2
113 bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
114 goto case "1.0.0.2"; // Continue and upgrade one more step
115 }
116 case "1.0.0.2":
117 { // Current database is version 1.0.0.2, update to 1.0.0.3
118 bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
119 break;
120 }
121
122
123 default:
124 {
125 MessageBox.Show("Error: Not able to upgrade database (51188)");
126 break;
127 }
128 }
129 break;
130 }
131 case (int)VersionCheck.DatabaseIsMoreNew:
132 {
133 bContinue = false;
134 break;
135 }
136 default:
137 {
138 bContinue = false;
139 break;
140 }
141
142 }
143 sqlCon.Close();
144 sqlCon.Dispose();
145 sqlCmd.Connection.Close();
146 sqlCmd.Connection.Dispose();
147 return bContinue;
148 }
149
150 // Run a SQL script (to create or update a database)
151 public bool RunScript(string strFile)
152 {
153 string[] strCommands;
154 strCommands = ParseScriptToCommands(strFile);
155 try
156 {
157 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
158
159 sqlCmd.Connection = sqlCon;
160
161 foreach (string strCmd in strCommands)
162 {
163 if (strCmd.Length > 0)
164 {
165 // Substitute database directory with the decided one.
166 sqlCmd.CommandText = strCmd.Replace("[DataDir]",ProSupport.strDatabasePath);
167 sqlCmd.ExecuteNonQuery();
168 }
169 }
170 }
171 catch (SqlException sql_ex)
172 {
173 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
174 return false;
175 }
176
177 return true;
178 }
179
180
181 // Check the version of the datbase
182 public int CheckVersion(out Version vDb)
183 {
184 //Get Version information from application
185 Version v=new Version(ATSGlobals.strDatabaseVersion);
186 vDb = new Version("0.0.0.0"); // Assign a default value for version
187 try
188 {
189
190 string strResult;
191
192 //Verify that the AnywhereTS database exists
193 sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
194 strResult = sqlCmd.ExecuteScalar().ToString();
195
196 if (strResult == "0")
197 {
198 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
199 sqlCon.Open();
200 RunScript(Resource1.CreateDatabase.ToString());
201 sqlCon.Close();
202 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
203 sqlCon.Open();
204 RunScript(Resource1.CreateTables.ToString());
205 sqlCon.Close();
206 }
207 try
208 {
209 sqlCon.Close();
210 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
211 sqlCon.Open();
212 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
213 strResult = (string)sqlCmd.ExecuteScalar();
214 sqlCon.Close();
215 }
216 catch
217 {
218 // the database exists, but one or more tables are missing
219 try
220 {
221 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
222 sqlCon.Open();
223 RunScript(Resource1.CreateTables.ToString());
224 sqlCon.Close();
225 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
226 sqlCon.Open();
227 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
228 strResult = (string)sqlCmd.ExecuteScalar();
229 }
230 catch
231 {
232 return (int)VersionCheck.Failed;
233 }
234 }
235 vDb = new Version(strResult);
236
237 sqlCon.Close();
238
239 if (vDb == v)
240 return (int)VersionCheck.Equal;
241
242 if (vDb > v)
243 return (int)VersionCheck.DatabaseIsMoreNew;
244
245 else
246 return (int)VersionCheck.DatabaseIsOlder;
247
248 }
249 catch (SqlException sql_ex)
250 {
251 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
252 return (int)VersionCheck.Failed;
253 }
254 catch (Exception system_ex)
255 {
256 MessageBox.Show(system_ex.Message.ToString());
257 return (int)VersionCheck.Failed;
258 }
259 }
260
261 public string[] ParseScriptToCommands(string strScript)
262 {
263 string[] commands;
264 commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
265 return commands;
266 }
267
268
269 [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
270 public static void SetDatabaseRights()
271 {
272 try
273 {
274 // Add an access control entry to the database file.
275 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
276 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
277 }
278 catch (Exception e)
279 {
280 MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
281 Application.Exit();
282 return;
283 }
284 }
285
286 public static void StartSQLbrowserService()
287 {
288 System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
289 try
290 {
291 // Check that the SQL browser service is not already running
292 if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
293 { // Service not running, start it.
294 srvController.Start();
295 srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
296 }
297 }
298
299 catch (Exception e)
300 {
301 MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
302 }
303 }
304
305 // Configure the SQL browser service to autostart
306 public static void AutostartSQLbrowserService()
307 {
308 try
309 {
310 //construct the management path
311 string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
312 using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
313 {
314 object[] parameters = new object[1];
315 parameters[0] = "Automatic";
316 service.InvokeMethod("ChangeStartMode", parameters);
317
318 }
319 }
320 catch
321 {
322 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.");
323 }
324 }
325
326 // Enable named pipes on the SQL Express server
327 public static bool EnableNamedPipes()
328 {
329 ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
330 ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
331 ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
332 bool restarted = false; // Indicating if restart of SQL server was performed
333
334 sqlServicesMan.Get();
335 serverProtocolsMan.Get();
336
337 foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
338 {
339 prot.Get();
340 if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
341 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
342 { // We found the named pipes protocol
343 if (!(bool)prot.GetPropertyValue("Enabled"))
344 { // Named pipes not activated
345 prot.InvokeMethod("SetEnable", null); // Activate named pipes
346
347 // Check if user wants to restart SQL server
348 DialogResult resultRights;
349 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);
350 if (resultRights == DialogResult.Yes)
351 {
352 // Restart the SQL server
353 const uint sqlServerService = 1;
354 const uint sqlServiceStopped = 1;
355 foreach (ManagementObject svc in sqlServicesMan.GetInstances())
356 {
357 if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
358 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
359 {
360 svc.Get();
361 if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
362 {
363 svc.InvokeMethod("StopService", null);
364 }
365 svc.InvokeMethod("StartService", null);
366 restarted = true;
367 } // end if
368 }
369 }
370 } // end if 'named pipes protool'
371 }
372 } // foreach
373
374 return restarted;
375 }
376 }
377 }

  ViewVC Help
Powered by ViewVC 1.1.22