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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 39 - (hide annotations) (download)
Thu Jul 12 12:21:43 2012 UTC (7 years, 11 months ago) by william
File size: 19259 byte(s)
+ add logging messages

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     }
147    
148    
149     default:
150     {
151     MessageBox.Show("Error: Not able to upgrade database (51188)");
152 william 39 Logging.ATSAdminLog.DebugFormat("Failed to upgrade Database from version: {0} to version: {1}", databaseVersion.ToString(), CURRENT_DB_VERSION.ToString());
153 william 4 break;
154     }
155     }
156     break;
157     }
158     case (int)VersionCheck.DatabaseIsMoreNew:
159     {
160     bContinue = false;
161     break;
162     }
163     default:
164     {
165     bContinue = false;
166     break;
167     }
168    
169     }
170     sqlCon.Close();
171     sqlCon.Dispose();
172     sqlCmd.Connection.Close();
173     sqlCmd.Connection.Dispose();
174     return bContinue;
175     }
176    
177     // Run a SQL script (to create or update a database)
178     public bool RunScript(string strFile)
179     {
180     string[] strCommands;
181     strCommands = ParseScriptToCommands(strFile);
182     try
183     {
184     if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
185    
186     sqlCmd.Connection = sqlCon;
187    
188     foreach (string strCmd in strCommands)
189     {
190     if (strCmd.Length > 0)
191     {
192     // Substitute database directory with the decided one.
193     sqlCmd.CommandText = strCmd.Replace("[DataDir]",ProSupport.strDatabasePath);
194     sqlCmd.ExecuteNonQuery();
195     }
196     }
197     }
198     catch (SqlException sql_ex)
199     {
200     MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
201     return false;
202     }
203    
204     return true;
205     }
206    
207    
208     // Check the version of the datbase
209     public int CheckVersion(out Version vDb)
210     {
211     //Get Version information from application
212     Version v=new Version(ATSGlobals.strDatabaseVersion);
213     vDb = new Version("0.0.0.0"); // Assign a default value for version
214     try
215     {
216    
217     string strResult;
218    
219     //Verify that the AnywhereTS database exists
220     sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
221     strResult = sqlCmd.ExecuteScalar().ToString();
222 william 21
223 william 4 if (strResult == "0")
224     {
225 william 21 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
226     sqlCon.Open();
227     RunScript(Resource1.CreateDatabase.ToString());
228     sqlCon.Close();
229     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
230     sqlCon.Open();
231 william 22 RunScript(Resource1.CreateTables.ToString());
232 william 21 sqlCon.Close();
233     }
234 william 4 try
235     {
236 william 21 sqlCon.Close();
237     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
238 william 22 sqlCon.Open();
239 william 4 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
240     strResult = (string)sqlCmd.ExecuteScalar();
241 william 22 sqlCon.Close();
242 william 4 }
243     catch
244 william 21 {
245 william 22 // the database exists, but one or more tables are missing
246 william 21 try
247     {
248     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
249     sqlCon.Open();
250 william 22 RunScript(Resource1.CreateTables.ToString());
251 william 21 sqlCon.Close();
252     sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
253 william 22 sqlCon.Open();
254 william 21 sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
255     strResult = (string)sqlCmd.ExecuteScalar();
256     }
257 william 22 catch
258     {
259     return (int)VersionCheck.Failed;
260     }
261 william 4 }
262     vDb = new Version(strResult);
263    
264     sqlCon.Close();
265    
266     if (vDb == v)
267     return (int)VersionCheck.Equal;
268    
269     if (vDb > v)
270     return (int)VersionCheck.DatabaseIsMoreNew;
271    
272     else
273     return (int)VersionCheck.DatabaseIsOlder;
274    
275     }
276     catch (SqlException sql_ex)
277     {
278     MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
279     return (int)VersionCheck.Failed;
280     }
281     catch (Exception system_ex)
282     {
283     MessageBox.Show(system_ex.Message.ToString());
284     return (int)VersionCheck.Failed;
285     }
286     }
287    
288     public string[] ParseScriptToCommands(string strScript)
289     {
290     string[] commands;
291     commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
292     return commands;
293     }
294    
295 william 21
296     [Obsolete("SetDatabaseRights() has been deperecated - user rights are assigned via SQL Server")]
297 william 4 public static void SetDatabaseRights()
298     {
299     try
300     {
301     // Add an access control entry to the database file.
302     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
303     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
304     }
305     catch (Exception e)
306     {
307     MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
308     Application.Exit();
309     return;
310     }
311     }
312    
313     public static void StartSQLbrowserService()
314     {
315     System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
316     try
317     {
318     // Check that the SQL browser service is not already running
319     if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
320     { // Service not running, start it.
321     srvController.Start();
322     srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
323     }
324     }
325    
326     catch (Exception e)
327     {
328     MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
329     }
330     }
331    
332     // Configure the SQL browser service to autostart
333     public static void AutostartSQLbrowserService()
334     {
335     try
336     {
337     //construct the management path
338     string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
339     using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
340     {
341     object[] parameters = new object[1];
342     parameters[0] = "Automatic";
343     service.InvokeMethod("ChangeStartMode", parameters);
344    
345     }
346     }
347     catch
348     {
349 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.");
350 william 4 }
351     }
352    
353     // Enable named pipes on the SQL Express server
354     public static bool EnableNamedPipes()
355     {
356     ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
357     ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
358     ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
359     bool restarted = false; // Indicating if restart of SQL server was performed
360    
361     sqlServicesMan.Get();
362     serverProtocolsMan.Get();
363    
364     foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
365     {
366     prot.Get();
367     if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
368 william 17 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
369 william 4 { // We found the named pipes protocol
370     if (!(bool)prot.GetPropertyValue("Enabled"))
371     { // Named pipes not activated
372     prot.InvokeMethod("SetEnable", null); // Activate named pipes
373    
374     // Check if user wants to restart SQL server
375     DialogResult resultRights;
376 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);
377 william 4 if (resultRights == DialogResult.Yes)
378     {
379     // Restart the SQL server
380     const uint sqlServerService = 1;
381     const uint sqlServiceStopped = 1;
382     foreach (ManagementObject svc in sqlServicesMan.GetInstances())
383     {
384     if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
385 william 17 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
386 william 4 {
387     svc.Get();
388     if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
389     {
390     svc.InvokeMethod("StopService", null);
391     }
392     svc.InvokeMethod("StartService", null);
393     restarted = true;
394     } // end if
395     }
396     }
397     } // end if 'named pipes protool'
398     }
399     } // foreach
400    
401     return restarted;
402     }
403     }
404     }

  ViewVC Help
Powered by ViewVC 1.1.22