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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 43 - (hide annotations) (download)
Thu Jul 12 12:43:29 2012 UTC (7 years, 11 months ago) by william
File size: 23833 byte(s)
use ATSGlobals.strDatabaseVersion instead of duplicating the value in Database.cs

1 william 4 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 william 33 using log4net;
10 william 4
11     namespace AnywhereTS
12     {
13     class DatabaseSupport
14     {
15 william 36
16 william 4 enum VersionCheck
17     {
18 william 21 Failed = 0,
19     Equal,
20     DatabaseIsMoreNew,
21     DatabaseIsOlder,
22     //DatabaseNotFound,
23     DatabaseCreated,
24 william 4 };
25    
26     private SqlConnection sqlCon = new SqlConnection();
27     private SqlCommand sqlCmd = new SqlCommand();
28    
29     const string SQL_BROWSER_SERVICE_NAME = "SQLBrowser";
30 william 19 public static string InstanceName
31     {
32     get
33     {
34     bool start = false;
35     bool end = false;
36     List<char> pChars = new List<char>();
37     foreach (char c in Properties.Settings.Default.atsConnectionString.ToCharArray())
38     {
39     if (c == '\\') { start = true; continue; }
40     if (c == ';') { end = true; }
41     if (end) break;
42     if (start) { pChars.Add(c); }
43     }
44     return new string(pChars.ToArray());
45     }
46     }
47 william 4 public bool SetupDatabase()
48     {
49     bool bContinue = false;
50     // Create a connection to SQL Server
51 william 36
52     Logging.ATSAdminLog.Debug("SetupDatabase() called ");
53 william 4 try
54 william 39 {
55     sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
56     using (log4net.NDC.Push(string.Format("ConnectionString={0}", Properties.Settings.Default.atsConnectionString)))
57     {
58     Logging.ATSAdminLog.Debug("Opening connection to AnywhereTS Database");
59     }
60 william 4 sqlCon.Open();
61     }
62 william 19 catch
63 william 4 {
64 william 18 //MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
65     //return bContinue;
66     //bCreateDB = true;
67     try
68     {
69 william 39 using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
70     {
71     Logging.ATSAdminLog.Debug("Creating Database AnywhereTS");
72     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
73     sqlCon.Open();
74     RunScript(Resource1.CreateDatabase.ToString());
75     sqlCon.Close();
76     }
77     Logging.ATSAdminLog.Debug("Created Database AnywhereTS");
78    
79     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
80     {
81     Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database");
82     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
83     sqlCon.Open();
84     RunScript(Resource1.CreateTables.ToString());
85     sqlCon.Close();
86     }
87     Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database");
88 william 18 }
89     catch (SqlException sql_ex1)
90     {
91 william 19 SqlException sql_ex = (sql_ex1.GetBaseException() as SqlException);
92     MessageBox.Show(string.Format("Failed to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
93 william 39
94     using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", sql_ex.Number.ToString(), sql_ex.Message, System.Environment.NewLine, sql_ex.ToString())))
95     {
96     Logging.ATSAdminLog.Error("Failed to open connection to AnywhereTS Database");
97     }
98 william 18 return bContinue;
99     }
100 william 4 }
101    
102     // Now that you are connected to Express, check the database versions
103     Version databaseVersion; // The current version of the database
104 william 18 int chkVer = CheckVersion(out databaseVersion);
105     VersionCheck verChk = (VersionCheck)chkVer;
106 william 39 //MessageBox.Show(string.Format("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString()));
107     Logging.ATSAdminLog.DebugFormat("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString());
108 william 18 switch (chkVer)
109 william 4 {
110     case (int)VersionCheck.Equal:
111     {
112     bContinue = true;
113     break;
114     }
115     case (int)VersionCheck.Failed:
116     {
117     bContinue = false;
118     break;
119     }
120     case (int)VersionCheck.DatabaseIsOlder:
121     {
122     switch (databaseVersion.ToString())
123     {
124     // Run the apropriate upgdrade script(s)
125     case "1.0.0.0":
126     { // Current database is version 1.0.0.0, update to 1.0.0.1
127 william 39 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
128 william 4 bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
129 william 39 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
130 william 4 goto case "1.0.0.1"; // Continue and upgrade one more step
131     }
132     case "1.0.0.1":
133     { // Current database is version 1.0.0.1, update to 1.0.0.2
134 william 39 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.1", "1.0.0.2");
135 william 4 bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
136 william 39 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.2");
137 william 4 goto case "1.0.0.2"; // Continue and upgrade one more step
138     }
139     case "1.0.0.2":
140     { // Current database is version 1.0.0.2, update to 1.0.0.3
141 william 39 Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
142 william 4 bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
143 william 39 Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
144 william 4 break;
145 william 40 }
146 william 4 default:
147     {
148 william 40 //MessageBox.Show("Error: Not able to upgrade database (51188)");
149     if (databaseVersion == new Version(0, 0, 0, 0))
150     {
151 william 43 string format = string.Format("Database version is {0}, this should have been auto upgraded to {1}", databaseVersion.ToString(), ATSGlobals.strDatabaseVersion);
152 william 40 Logging.ATSAdminLog.Fatal(format);
153     MessageBox.Show(format);
154     }
155     else
156     {
157 william 43 string format = string.Format("Failed to upgrade Database from version: {0} to version: {1}", databaseVersion.ToString(), ATSGlobals.strDatabaseVersion);
158 william 40 Logging.ATSAdminLog.Fatal(format);
159     MessageBox.Show(format);
160     }
161 william 4 break;
162     }
163     }
164     break;
165     }
166     case (int)VersionCheck.DatabaseIsMoreNew:
167     {
168     bContinue = false;
169     break;
170     }
171     default:
172     {
173     bContinue = false;
174     break;
175     }
176    
177     }
178     sqlCon.Close();
179     sqlCon.Dispose();
180     sqlCmd.Connection.Close();
181     sqlCmd.Connection.Dispose();
182     return bContinue;
183     }
184    
185     // Run a SQL script (to create or update a database)
186     public bool RunScript(string strFile)
187     {
188     string[] strCommands;
189     strCommands = ParseScriptToCommands(strFile);
190     try
191     {
192     if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
193    
194     sqlCmd.Connection = sqlCon;
195    
196     foreach (string strCmd in strCommands)
197     {
198     if (strCmd.Length > 0)
199     {
200     // Substitute database directory with the decided one.
201     sqlCmd.CommandText = strCmd.Replace("[DataDir]",ProSupport.strDatabasePath);
202     sqlCmd.ExecuteNonQuery();
203     }
204     }
205     }
206     catch (SqlException sql_ex)
207     {
208     MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
209     return false;
210     }
211    
212     return true;
213     }
214    
215    
216     // Check the version of the datbase
217     public int CheckVersion(out Version vDb)
218     {
219     //Get Version information from application
220     Version v=new Version(ATSGlobals.strDatabaseVersion);
221     vDb = new Version("0.0.0.0"); // Assign a default value for version
222     try
223     {
224    
225     string strResult;
226    
227     //Verify that the AnywhereTS database exists
228 william 21
229 william 41 using (log4net.NDC.Push(string.Format("SQL STATMENT={0}", "select count(*) from master..sysdatabases where name='AnywhereTS'")))
230 william 4 {
231 william 41 Logging.ATSAdminLog.Debug("Getting Coount of AnywhereTS databases");
232     sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
233     strResult = sqlCmd.ExecuteScalar().ToString();
234 william 21 }
235 william 41 Logging.ATSAdminLog.DebugFormat("AnywhereTS databases Count={0}", strResult);
236    
237     if (strResult == "0")
238 william 4 {
239 william 41
240    
241     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
242 william 21 {
243 william 41 Logging.ATSAdminLog.Debug("Creating Database AnywhereTS in CheckVersion()");
244 william 21 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
245     sqlCon.Open();
246 william 41 RunScript(Resource1.CreateDatabase.ToString());
247     sqlCon.Close();
248     }
249     Logging.ATSAdminLog.Debug("Created Database AnywhereTS in CheckVersion()");
250    
251     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
252     {
253     Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database in CheckVersion()");
254     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
255     sqlCon.Open();
256 william 22 RunScript(Resource1.CreateTables.ToString());
257 william 21 sqlCon.Close();
258 william 41 }
259     Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database in CheckVersion()");
260     }
261     try
262     {
263     using (log4net.NDC.Push(string.Format("SQL Statment={0}", "SELECT value from AnywhereTS..AppInfo where property='version'")))
264     {
265     Logging.ATSAdminLog.Debug("Gettting AnywhereTS Database Version");
266     sqlCon.Close();
267 william 21 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
268 william 22 sqlCon.Open();
269 william 21 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
270     strResult = (string)sqlCmd.ExecuteScalar();
271 william 41 sqlCon.Close();
272 william 21 }
273 william 41 Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
274     }
275     catch(SqlException ex)
276     {
277     // the database exists, but one or more tables are missing
278     using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
279 william 22 {
280 william 41 Logging.ATSAdminLog.Error("Failed to get database version");
281     }
282     try
283     {
284    
285     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
286     {
287     Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database in CheckVersion()");
288     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
289     sqlCon.Open();
290     RunScript(Resource1.CreateTables.ToString());
291     sqlCon.Close();
292     }
293     Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database in CheckVersion()");
294    
295     using (log4net.NDC.Push(string.Format("SQL Statment={0}", "SELECT value from AnywhereTS..AppInfo where property='version'")))
296     {
297     Logging.ATSAdminLog.Debug("Gettting AnywhereTS Database Version");
298     sqlCon.Close();
299     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
300     sqlCon.Open();
301     sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
302     strResult = (string)sqlCmd.ExecuteScalar();
303     sqlCon.Close();
304     }
305     Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
306     }
307     catch(SqlException ex1)
308     {
309     using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex1.Number.ToString(), ex1.Message, System.Environment.NewLine, ex1.ToString())))
310     {
311     Logging.ATSAdminLog.Error("Failed to get database version");
312     }
313 william 22 return (int)VersionCheck.Failed;
314     }
315 william 4 }
316     vDb = new Version(strResult);
317    
318     sqlCon.Close();
319    
320     if (vDb == v)
321     return (int)VersionCheck.Equal;
322    
323     if (vDb > v)
324     return (int)VersionCheck.DatabaseIsMoreNew;
325    
326     else
327     return (int)VersionCheck.DatabaseIsOlder;
328    
329     }
330     catch (SqlException sql_ex)
331     {
332     MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
333 william 41 using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", sql_ex.Number.ToString(), sql_ex.Message, System.Environment.NewLine, sql_ex.ToString())))
334     {
335     Logging.ATSAdminLog.Error("Failed to check database version");
336     }
337 william 4 return (int)VersionCheck.Failed;
338     }
339     catch (Exception system_ex)
340     {
341     MessageBox.Show(system_ex.Message.ToString());
342 william 41 using (log4net.NDC.Push(string.Format("SqlException: MESSAGE={0}{1}Diagnostics:{1}{2}", system_ex.Message, System.Environment.NewLine, system_ex.ToString())))
343     {
344     Logging.ATSAdminLog.Error("Failed to check database version");
345     }
346 william 4 return (int)VersionCheck.Failed;
347     }
348     }
349    
350     public string[] ParseScriptToCommands(string strScript)
351     {
352     string[] commands;
353     commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
354     return commands;
355     }
356    
357 william 21
358     [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
359 william 4 public static void SetDatabaseRights()
360     {
361     try
362     {
363     // Add an access control entry to the database file.
364     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
365     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
366     }
367     catch (Exception e)
368     {
369     MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
370     Application.Exit();
371     return;
372     }
373     }
374    
375     public static void StartSQLbrowserService()
376     {
377     System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
378     try
379     {
380     // Check that the SQL browser service is not already running
381     if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
382     { // Service not running, start it.
383     srvController.Start();
384     srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
385     }
386     }
387    
388     catch (Exception e)
389     {
390     MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
391     }
392     }
393    
394     // Configure the SQL browser service to autostart
395     public static void AutostartSQLbrowserService()
396     {
397     try
398     {
399     //construct the management path
400     string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
401     using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
402     {
403     object[] parameters = new object[1];
404     parameters[0] = "Automatic";
405     service.InvokeMethod("ChangeStartMode", parameters);
406    
407     }
408     }
409     catch
410     {
411 william 17 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.");
412 william 4 }
413     }
414    
415     // Enable named pipes on the SQL Express server
416     public static bool EnableNamedPipes()
417     {
418     ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
419     ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
420     ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
421     bool restarted = false; // Indicating if restart of SQL server was performed
422    
423     sqlServicesMan.Get();
424     serverProtocolsMan.Get();
425    
426     foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
427     {
428     prot.Get();
429     if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
430 william 17 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
431 william 4 { // We found the named pipes protocol
432     if (!(bool)prot.GetPropertyValue("Enabled"))
433     { // Named pipes not activated
434     prot.InvokeMethod("SetEnable", null); // Activate named pipes
435    
436     // Check if user wants to restart SQL server
437     DialogResult resultRights;
438 william 17 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);
439 william 4 if (resultRights == DialogResult.Yes)
440     {
441     // Restart the SQL server
442     const uint sqlServerService = 1;
443     const uint sqlServiceStopped = 1;
444     foreach (ManagementObject svc in sqlServicesMan.GetInstances())
445     {
446     if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
447 william 17 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
448 william 4 {
449     svc.Get();
450     if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
451     {
452     svc.InvokeMethod("StopService", null);
453     }
454     svc.InvokeMethod("StartService", null);
455     restarted = true;
456     } // end if
457     }
458     }
459     } // end if 'named pipes protool'
460     }
461     } // foreach
462    
463     return restarted;
464     }
465     }
466     }

  ViewVC Help
Powered by ViewVC 1.1.22