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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 4 - (show annotations) (download)
Wed Jul 11 14:19:13 2012 UTC (7 years, 10 months ago) by william
File size: 13731 byte(s)
imported from https://anywherets.svn.sourceforge.net/svnroot/anywherets/trunk/
Revision: 1
Author: beartown
Date: 3:10:40 AM, Monday, June 21, 2010
Message:
First upload. Still todo in order to get a fully working product: Replace Thinstation images with vanilla Thinstation, modify ImageDesigntimeConfig.cs, ImageRuntimeConfig.cs in order to support the Thinstation image
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, Equal, DatabaseIsMoreNew,
17 DatabaseIsOlder, DatabaseNotFound
18 };
19
20 private SqlConnection sqlCon = new SqlConnection();
21 private SqlCommand sqlCmd = new SqlCommand();
22
23 const string SQL_BROWSER_SERVICE_NAME = "SQLBrowser";
24
25 public bool SetupDatabase()
26 {
27 bool bContinue = false;
28
29 // Create a connection to SQL Server
30 try
31 {
32 sqlCon.ConnectionString = "Server=.\\sqlexpress;Integrated Security=true";
33 sqlCon.Open();
34 }
35 catch (SqlException sql_ex)
36 {
37 MessageBox.Show("Fail to connect to SQL Server Express\n" + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
38 return bContinue;
39 }
40
41 // Now that you are connected to Express, check the database versions
42
43 Version databaseVersion; // The current version of the database
44
45 switch (CheckVersion(out databaseVersion))
46 {
47 case (int)VersionCheck.Equal:
48 {
49 bContinue = true;
50 break;
51 }
52 case (int)VersionCheck.Failed:
53 {
54 bContinue = false;
55 break;
56 }
57 case (int)VersionCheck.DatabaseIsOlder:
58 {
59 switch (databaseVersion.ToString())
60 {
61 // Run the apropriate upgdrade script(s)
62 case "1.0.0.0":
63 { // Current database is version 1.0.0.0, update to 1.0.0.1
64 bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
65 goto case "1.0.0.1"; // Continue and upgrade one more step
66 }
67 case "1.0.0.1":
68 { // Current database is version 1.0.0.1, update to 1.0.0.2
69 bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
70 goto case "1.0.0.2"; // Continue and upgrade one more step
71 }
72 case "1.0.0.2":
73 { // Current database is version 1.0.0.2, update to 1.0.0.3
74 bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
75 break;
76 }
77
78
79 default:
80 {
81 MessageBox.Show("Error: Not able to upgrade database (51188)");
82 break;
83 }
84 }
85 break;
86 }
87 case (int)VersionCheck.DatabaseIsMoreNew:
88 {
89 bContinue = false;
90 break;
91 }
92 case (int)VersionCheck.DatabaseNotFound:
93 {
94 //Run the creation script
95 bContinue = RunScript(Resource1.CreateDatabase.ToString());
96 if (bContinue)
97 {
98 // Set up file access rights for remote desktop users
99 SetDatabaseRights();
100 }
101
102 break;
103 }
104 default:
105 {
106 bContinue = false;
107 break;
108 }
109
110 }
111 sqlCon.Close();
112 sqlCon.Dispose();
113 sqlCmd.Connection.Close();
114 sqlCmd.Connection.Dispose();
115 return bContinue;
116 }
117
118 // Run a SQL script (to create or update a database)
119 public bool RunScript(string strFile)
120 {
121 string[] strCommands;
122 strCommands = ParseScriptToCommands(strFile);
123 try
124 {
125 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
126
127 sqlCmd.Connection = sqlCon;
128
129 foreach (string strCmd in strCommands)
130 {
131 if (strCmd.Length > 0)
132 {
133 // Substitute database directory with the decided one.
134 sqlCmd.CommandText = strCmd.Replace("[DataDir]",ProSupport.strDatabasePath);
135 sqlCmd.ExecuteNonQuery();
136 }
137 }
138 }
139 catch (SqlException sql_ex)
140 {
141 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
142 return false;
143 }
144
145 return true;
146 }
147
148
149 // Check the version of the datbase
150 public int CheckVersion(out Version vDb)
151 {
152 //Get Version information from application
153 Version v=new Version(ATSGlobals.strDatabaseVersion);
154 vDb = new Version("0.0.0.0"); // Assign a default value for version
155 try
156 {
157
158 string strResult;
159
160 //Verify that the AnywhereTS database exists
161 sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
162 strResult = sqlCmd.ExecuteScalar().ToString();
163
164 if (strResult == "0")
165 {
166
167 return (int)VersionCheck.DatabaseNotFound;
168 }
169
170 try
171 {
172 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
173 strResult = (string)sqlCmd.ExecuteScalar();
174 }
175 catch
176 { // Assume we could not open the physical file
177 //Drop the database
178 sqlCmd = new SqlCommand("IF EXISTS(SELECT * FROM sysdatabases WHERE name='AnywhereTS')DROP DATABASE AnywhereTS", sqlCon);
179 strResult = (string)sqlCmd.ExecuteScalar();
180 return (int)VersionCheck.DatabaseNotFound;
181 }
182 vDb = new Version(strResult);
183
184 sqlCon.Close();
185
186 if (vDb == v)
187 return (int)VersionCheck.Equal;
188
189 if (vDb > v)
190 return (int)VersionCheck.DatabaseIsMoreNew;
191
192 else
193 return (int)VersionCheck.DatabaseIsOlder;
194
195 }
196 catch (SqlException sql_ex)
197 {
198 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
199 return (int)VersionCheck.Failed;
200 }
201 catch (Exception system_ex)
202 {
203 MessageBox.Show(system_ex.Message.ToString());
204 return (int)VersionCheck.Failed;
205 }
206 }
207
208 public string[] ParseScriptToCommands(string strScript)
209 {
210 string[] commands;
211 commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
212 return commands;
213 }
214
215
216 public static void SetDatabaseRights()
217 {
218 try
219 {
220 // Add an access control entry to the database file.
221 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
222 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
223 }
224 catch (Exception e)
225 {
226 MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
227 Application.Exit();
228 return;
229 }
230 }
231
232 public static void StartSQLbrowserService()
233 {
234 System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
235 try
236 {
237 // Check that the SQL browser service is not already running
238 if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
239 { // Service not running, start it.
240 srvController.Start();
241 srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
242 }
243 }
244
245 catch (Exception e)
246 {
247 MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
248 }
249 }
250
251 // Configure the SQL browser service to autostart
252 public static void AutostartSQLbrowserService()
253 {
254 try
255 {
256 //construct the management path
257 string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
258 using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
259 {
260 object[] parameters = new object[1];
261 parameters[0] = "Automatic";
262 service.InvokeMethod("ChangeStartMode", parameters);
263
264 }
265 }
266 catch
267 {
268 MessageBox.Show("Error, could not configure SQL Browser service (24888). Please check that SQL Server Express is intalled and that you are logged in with sufficient rights to configure services. Then retry the operation.");
269 }
270 }
271
272 // Enable named pipes on the SQL Express server
273 public static bool EnableNamedPipes()
274 {
275 ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
276 ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
277 ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
278 bool restarted = false; // Indicating if restart of SQL server was performed
279
280 sqlServicesMan.Get();
281 serverProtocolsMan.Get();
282
283 foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
284 {
285 prot.Get();
286 if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
287 (string)prot.GetPropertyValue("InstanceName") == "SQLEXPRESS")
288 { // We found the named pipes protocol
289 if (!(bool)prot.GetPropertyValue("Enabled"))
290 { // Named pipes not activated
291 prot.InvokeMethod("SetEnable", null); // Activate named pipes
292
293 // Check if user wants to restart SQL server
294 DialogResult resultRights;
295 resultRights = MessageBox.Show("In order for users to use the AnywhereTS control panel, the SQL Express service on this computer need to be restarted. This operation might take up to 60 seconds. Do you want restart the SQL Express service now?", "AnywhereTS - Restart SQL Express (This operation might take up to 60 seconds!)", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1);
296 if (resultRights == DialogResult.Yes)
297 {
298 // Restart the SQL server
299 const uint sqlServerService = 1;
300 const uint sqlServiceStopped = 1;
301 foreach (ManagementObject svc in sqlServicesMan.GetInstances())
302 {
303 if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
304 (string)svc.GetPropertyValue("ServiceName") == "MSSQL$SQLEXPRESS")
305 {
306 svc.Get();
307 if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
308 {
309 svc.InvokeMethod("StopService", null);
310 }
311 svc.InvokeMethod("StartService", null);
312 restarted = true;
313 } // end if
314 }
315 }
316 } // end if 'named pipes protool'
317 }
318 } // foreach
319
320 return restarted;
321 }
322 }
323 }

  ViewVC Help
Powered by ViewVC 1.1.22