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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 36 - (show annotations) (download)
Thu Jul 12 11:35:07 2012 UTC (7 years, 7 months ago) by william
File size: 16764 byte(s)
+ add logging library (to be shared across application)

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

  ViewVC Help
Powered by ViewVC 1.1.22