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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 25 - (show annotations) (download)
Wed Jul 11 20:39:32 2012 UTC (7 years, 11 months ago) by william
File size: 16657 byte(s)
+ commit current wip for non-existing database

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

  ViewVC Help
Powered by ViewVC 1.1.22