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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 45 - (hide annotations) (download)
Thu Jul 12 13:01:44 2012 UTC (8 years, 11 months ago) by william
File size: 24157 byte(s)
remove one sqlCon.close()

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 44 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
61 william 4 }
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 william 44 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
74 william 39 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 william 44 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
84 william 39 RunScript(Resource1.CreateTables.ToString());
85 william 44 //sqlCon.Close();
86 william 39 }
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 william 44 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
233     sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
234     strResult = sqlCmd.ExecuteScalar().ToString();
235 william 21 }
236 william 41 Logging.ATSAdminLog.DebugFormat("AnywhereTS databases Count={0}", strResult);
237    
238     if (strResult == "0")
239 william 4 {
240 william 41
241    
242     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
243 william 21 {
244 william 41 Logging.ATSAdminLog.Debug("Creating Database AnywhereTS in CheckVersion()");
245 william 21 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
246 william 44 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
247 william 41 RunScript(Resource1.CreateDatabase.ToString());
248     sqlCon.Close();
249     }
250     Logging.ATSAdminLog.Debug("Created Database AnywhereTS in CheckVersion()");
251    
252     using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
253     {
254     Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database in CheckVersion()");
255     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
256 william 44 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
257 william 22 RunScript(Resource1.CreateTables.ToString());
258 william 21 sqlCon.Close();
259 william 41 }
260     Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database in CheckVersion()");
261     }
262     try
263     {
264     using (log4net.NDC.Push(string.Format("SQL Statment={0}", "SELECT value from AnywhereTS..AppInfo where property='version'")))
265     {
266     Logging.ATSAdminLog.Debug("Gettting AnywhereTS Database Version");
267 william 21 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
268 william 44 if (sqlCon.State != ConnectionState.Open) 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 william 44 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
290 william 41 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 = new SqlConnection(Properties.Settings.Default.atsConnectionString);
299 william 44 if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
300 william 41 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
301     strResult = (string)sqlCmd.ExecuteScalar();
302     sqlCon.Close();
303     }
304     Logging.ATSAdminLog.DebugFormat("AnywhereTS database version={0}", strResult);
305     }
306     catch(SqlException ex1)
307     {
308     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())))
309     {
310     Logging.ATSAdminLog.Error("Failed to get database version");
311     }
312 william 22 return (int)VersionCheck.Failed;
313     }
314 william 4 }
315     vDb = new Version(strResult);
316    
317     sqlCon.Close();
318    
319     if (vDb == v)
320     return (int)VersionCheck.Equal;
321    
322     if (vDb > v)
323     return (int)VersionCheck.DatabaseIsMoreNew;
324    
325     else
326     return (int)VersionCheck.DatabaseIsOlder;
327    
328     }
329     catch (SqlException sql_ex)
330     {
331     MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
332 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())))
333     {
334     Logging.ATSAdminLog.Error("Failed to check database version");
335     }
336 william 4 return (int)VersionCheck.Failed;
337     }
338     catch (Exception system_ex)
339     {
340     MessageBox.Show(system_ex.Message.ToString());
341 william 41 using (log4net.NDC.Push(string.Format("SqlException: MESSAGE={0}{1}Diagnostics:{1}{2}", system_ex.Message, System.Environment.NewLine, system_ex.ToString())))
342     {
343     Logging.ATSAdminLog.Error("Failed to check database version");
344     }
345 william 4 return (int)VersionCheck.Failed;
346     }
347     }
348    
349     public string[] ParseScriptToCommands(string strScript)
350     {
351     string[] commands;
352     commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
353     return commands;
354     }
355    
356 william 21
357     [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
358 william 4 public static void SetDatabaseRights()
359     {
360     try
361     {
362     // Add an access control entry to the database file.
363     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
364     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
365     }
366     catch (Exception e)
367     {
368     MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
369     Application.Exit();
370     return;
371     }
372     }
373    
374     public static void StartSQLbrowserService()
375     {
376     System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
377     try
378     {
379     // Check that the SQL browser service is not already running
380     if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
381     { // Service not running, start it.
382     srvController.Start();
383     srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
384     }
385     }
386    
387     catch (Exception e)
388     {
389     MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
390     }
391     }
392    
393     // Configure the SQL browser service to autostart
394     public static void AutostartSQLbrowserService()
395     {
396     try
397     {
398     //construct the management path
399     string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
400     using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
401     {
402     object[] parameters = new object[1];
403     parameters[0] = "Automatic";
404     service.InvokeMethod("ChangeStartMode", parameters);
405    
406     }
407     }
408     catch
409     {
410 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.");
411 william 4 }
412     }
413    
414     // Enable named pipes on the SQL Express server
415     public static bool EnableNamedPipes()
416     {
417     ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
418     ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
419     ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
420     bool restarted = false; // Indicating if restart of SQL server was performed
421    
422     sqlServicesMan.Get();
423     serverProtocolsMan.Get();
424    
425     foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
426     {
427     prot.Get();
428     if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
429 william 17 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
430 william 4 { // We found the named pipes protocol
431     if (!(bool)prot.GetPropertyValue("Enabled"))
432     { // Named pipes not activated
433     prot.InvokeMethod("SetEnable", null); // Activate named pipes
434    
435     // Check if user wants to restart SQL server
436     DialogResult resultRights;
437 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);
438 william 4 if (resultRights == DialogResult.Yes)
439     {
440     // Restart the SQL server
441     const uint sqlServerService = 1;
442     const uint sqlServiceStopped = 1;
443     foreach (ManagementObject svc in sqlServicesMan.GetInstances())
444     {
445     if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
446 william 17 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
447 william 4 {
448     svc.Get();
449     if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
450     {
451     svc.InvokeMethod("StopService", null);
452     }
453     svc.InvokeMethod("StartService", null);
454     restarted = true;
455     } // end if
456     }
457     }
458     } // end if 'named pipes protool'
459     }
460     } // foreach
461    
462     return restarted;
463     }
464     }
465     }

  ViewVC Help
Powered by ViewVC 1.1.22