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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 44 - (hide annotations) (download)
Thu Jul 12 12:59:45 2012 UTC (7 years, 11 months ago) by william
File size: 24243 byte(s)
+ make sure connection is not already opened before attempted to open it

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

  ViewVC Help
Powered by ViewVC 1.1.22