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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 22 - (show annotations) (download)
Wed Jul 11 20:13:17 2012 UTC (7 years, 11 months ago) by william
File size: 16995 byte(s)
+ finish support for creating tables in an empty database (incase the database are pre-created, but contains no tables)
- still have AD login issues when creating the database from scratch (ie. it did not exist to begin with)

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

  ViewVC Help
Powered by ViewVC 1.1.22