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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 40 - (hide annotations) (download)
Thu Jul 12 12:25:58 2012 UTC (7 years, 11 months ago) by william
File size: 20064 byte(s)
+ more logging and changes

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

  ViewVC Help
Powered by ViewVC 1.1.22