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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 21 - (show annotations) (download)
Wed Jul 11 19:34:51 2012 UTC (9 years, 2 months ago) by william
File size: 17316 byte(s)
+ commit wip for when database exists, but is empty

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

  ViewVC Help
Powered by ViewVC 1.1.22