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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 33 - (hide annotations) (download)
Thu Jul 12 10:52:05 2012 UTC (7 years, 11 months ago) by william
File size: 16819 byte(s)
+ attempt to add log4net support to ATSAdmin

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

  ViewVC Help
Powered by ViewVC 1.1.22