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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 19 - (show annotations) (download)
Wed Jul 11 18:07:00 2012 UTC (7 years, 11 months ago) by william
File size: 15502 byte(s)
+ fix instancename parsing

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 static string InstanceName
26 {
27 get
28 {
29 bool start = false;
30 bool end = false;
31 List<char> pChars = new List<char>();
32 foreach (char c in Properties.Settings.Default.atsConnectionString.ToCharArray())
33 {
34 if (c == '\\') { start = true; continue; }
35 if (c == ';') { end = true; }
36 if (end) break;
37 if (start) { pChars.Add(c); }
38 }
39 return new string(pChars.ToArray());
40 }
41 }
42 public bool SetupDatabase()
43 {
44 bool bContinue = false;
45 // Create a connection to SQL Server
46 try
47 {
48 sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
49 sqlCon.Open();
50 }
51 catch
52 {
53 //MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
54 //return bContinue;
55 //bCreateDB = true;
56 try
57 {
58 sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master");
59 sqlCon.Open();
60 RunScript(Resource1.CreateDatabase.ToString());
61 sqlCon.Close();
62 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
63 sqlCon.Open();
64 }
65 catch (SqlException sql_ex1)
66 {
67 SqlException sql_ex = (sql_ex1.GetBaseException() as SqlException);
68 MessageBox.Show(string.Format("Failed to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
69 return bContinue;
70 }
71 }
72
73 // Now that you are connected to Express, check the database versions
74 Version databaseVersion; // The current version of the database
75 int chkVer = CheckVersion(out databaseVersion);
76 VersionCheck verChk = (VersionCheck)chkVer;
77 //MessageBox.Show(string.Format("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString()));
78 switch (chkVer)
79 {
80 case (int)VersionCheck.Equal:
81 {
82 bContinue = true;
83 break;
84 }
85 case (int)VersionCheck.Failed:
86 {
87 bContinue = false;
88 break;
89 }
90 case (int)VersionCheck.DatabaseIsOlder:
91 {
92 switch (databaseVersion.ToString())
93 {
94 // Run the apropriate upgdrade script(s)
95 case "1.0.0.0":
96 { // Current database is version 1.0.0.0, update to 1.0.0.1
97 bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
98 goto case "1.0.0.1"; // Continue and upgrade one more step
99 }
100 case "1.0.0.1":
101 { // Current database is version 1.0.0.1, update to 1.0.0.2
102 bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
103 goto case "1.0.0.2"; // Continue and upgrade one more step
104 }
105 case "1.0.0.2":
106 { // Current database is version 1.0.0.2, update to 1.0.0.3
107 bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
108 break;
109 }
110
111
112 default:
113 {
114 MessageBox.Show("Error: Not able to upgrade database (51188)");
115 break;
116 }
117 }
118 break;
119 }
120 case (int)VersionCheck.DatabaseIsMoreNew:
121 {
122 bContinue = false;
123 break;
124 }
125 case (int)VersionCheck.DatabaseNotFound:
126 {
127 //Run the creation script
128 bContinue = RunScript(Resource1.CreateDatabase.ToString());
129 if (bContinue)
130 {
131 // Set up file access rights for remote desktop users
132 SetDatabaseRights();
133 }
134
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
200 return (int)VersionCheck.DatabaseNotFound;
201 }
202
203 try
204 {
205 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
206 strResult = (string)sqlCmd.ExecuteScalar();
207 }
208 catch
209 { // Assume we could not open the physical file
210 //Drop the database
211 sqlCmd = new SqlCommand("IF EXISTS(SELECT * FROM sysdatabases WHERE name='AnywhereTS')DROP DATABASE AnywhereTS", sqlCon);
212 strResult = (string)sqlCmd.ExecuteScalar();
213 return (int)VersionCheck.DatabaseNotFound;
214 }
215 vDb = new Version(strResult);
216
217 sqlCon.Close();
218
219 if (vDb == v)
220 return (int)VersionCheck.Equal;
221
222 if (vDb > v)
223 return (int)VersionCheck.DatabaseIsMoreNew;
224
225 else
226 return (int)VersionCheck.DatabaseIsOlder;
227
228 }
229 catch (SqlException sql_ex)
230 {
231 MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
232 return (int)VersionCheck.Failed;
233 }
234 catch (Exception system_ex)
235 {
236 MessageBox.Show(system_ex.Message.ToString());
237 return (int)VersionCheck.Failed;
238 }
239 }
240
241 public string[] ParseScriptToCommands(string strScript)
242 {
243 string[] commands;
244 commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
245 return commands;
246 }
247
248
249 public static void SetDatabaseRights()
250 {
251 try
252 {
253 // Add an access control entry to the database file.
254 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
255 ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
256 }
257 catch (Exception e)
258 {
259 MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
260 Application.Exit();
261 return;
262 }
263 }
264
265 public static void StartSQLbrowserService()
266 {
267 System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
268 try
269 {
270 // Check that the SQL browser service is not already running
271 if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
272 { // Service not running, start it.
273 srvController.Start();
274 srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
275 }
276 }
277
278 catch (Exception e)
279 {
280 MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
281 }
282 }
283
284 // Configure the SQL browser service to autostart
285 public static void AutostartSQLbrowserService()
286 {
287 try
288 {
289 //construct the management path
290 string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
291 using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
292 {
293 object[] parameters = new object[1];
294 parameters[0] = "Automatic";
295 service.InvokeMethod("ChangeStartMode", parameters);
296
297 }
298 }
299 catch
300 {
301 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.");
302 }
303 }
304
305 // Enable named pipes on the SQL Express server
306 public static bool EnableNamedPipes()
307 {
308 ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
309 ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
310 ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
311 bool restarted = false; // Indicating if restart of SQL server was performed
312
313 sqlServicesMan.Get();
314 serverProtocolsMan.Get();
315
316 foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
317 {
318 prot.Get();
319 if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
320 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
321 { // We found the named pipes protocol
322 if (!(bool)prot.GetPropertyValue("Enabled"))
323 { // Named pipes not activated
324 prot.InvokeMethod("SetEnable", null); // Activate named pipes
325
326 // Check if user wants to restart SQL server
327 DialogResult resultRights;
328 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);
329 if (resultRights == DialogResult.Yes)
330 {
331 // Restart the SQL server
332 const uint sqlServerService = 1;
333 const uint sqlServiceStopped = 1;
334 foreach (ManagementObject svc in sqlServicesMan.GetInstances())
335 {
336 if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
337 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
338 {
339 svc.Get();
340 if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
341 {
342 svc.InvokeMethod("StopService", null);
343 }
344 svc.InvokeMethod("StartService", null);
345 restarted = true;
346 } // end if
347 }
348 }
349 } // end if 'named pipes protool'
350 }
351 } // foreach
352
353 return restarted;
354 }
355 }
356 }

  ViewVC Help
Powered by ViewVC 1.1.22