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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 17 - (show annotations) (download)
Wed Jul 11 16:48:23 2012 UTC (7 years, 11 months ago) by william
File size: 14072 byte(s)
+ fix more references to SQLEXPRESS

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

  ViewVC Help
Powered by ViewVC 1.1.22