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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 36 - (hide annotations) (download)
Thu Jul 12 11:35:07 2012 UTC (7 years, 11 months ago) by william
File size: 16764 byte(s)
+ add logging library (to be shared across application)

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

  ViewVC Help
Powered by ViewVC 1.1.22