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

Contents of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 18 - (show annotations) (download)
Wed Jul 11 17:45:48 2012 UTC (8 years, 7 months ago) by william
File size: 15141 byte(s)
+ add support to createdatabase is it does not exist

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

  ViewVC Help
Powered by ViewVC 1.1.22