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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 19 - (hide annotations) (download)
Wed Jul 11 18:07:00 2012 UTC (8 years, 7 months ago) by william
File size: 15502 byte(s)
+ fix instancename parsing

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    
10     namespace AnywhereTS
11     {
12     class DatabaseSupport
13     {
14     enum VersionCheck
15     {
16     Failed = 0, Equal, DatabaseIsMoreNew,
17     DatabaseIsOlder, DatabaseNotFound
18     };
19    
20     private SqlConnection sqlCon = new SqlConnection();
21     private SqlCommand sqlCmd = new SqlCommand();
22    
23     const string SQL_BROWSER_SERVICE_NAME = "SQLBrowser";
24 william 17
25 william 19 public static string InstanceName
26     {
27     get
28     {
29     bool start = false;
30     bool end = false;
31     List<char> pChars = new List<char>();
32     foreach (char c in Properties.Settings.Default.atsConnectionString.ToCharArray())
33     {
34     if (c == '\\') { start = true; continue; }
35     if (c == ';') { end = true; }
36     if (end) break;
37     if (start) { pChars.Add(c); }
38     }
39     return new string(pChars.ToArray());
40     }
41     }
42 william 4 public bool SetupDatabase()
43     {
44     bool bContinue = false;
45     // Create a connection to SQL Server
46     try
47     {
48 william 10 sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
49 william 4 sqlCon.Open();
50     }
51 william 19 catch
52 william 4 {
53 william 18 //MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
54     //return bContinue;
55     //bCreateDB = true;
56     try
57     {
58 william 19 sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master");
59 william 18 sqlCon.Open();
60     RunScript(Resource1.CreateDatabase.ToString());
61     sqlCon.Close();
62 william 19 sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
63 william 18 sqlCon.Open();
64     }
65     catch (SqlException sql_ex1)
66     {
67 william 19 SqlException sql_ex = (sql_ex1.GetBaseException() as SqlException);
68     MessageBox.Show(string.Format("Failed to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
69 william 18 return bContinue;
70     }
71 william 4 }
72    
73     // Now that you are connected to Express, check the database versions
74     Version databaseVersion; // The current version of the database
75 william 18 int chkVer = CheckVersion(out databaseVersion);
76     VersionCheck verChk = (VersionCheck)chkVer;
77     //MessageBox.Show(string.Format("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString()));
78     switch (chkVer)
79 william 4 {
80     case (int)VersionCheck.Equal:
81     {
82     bContinue = true;
83     break;
84     }
85     case (int)VersionCheck.Failed:
86     {
87     bContinue = false;
88     break;
89     }
90     case (int)VersionCheck.DatabaseIsOlder:
91     {
92     switch (databaseVersion.ToString())
93     {
94     // Run the apropriate upgdrade script(s)
95     case "1.0.0.0":
96     { // Current database is version 1.0.0.0, update to 1.0.0.1
97     bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
98     goto case "1.0.0.1"; // Continue and upgrade one more step
99     }
100     case "1.0.0.1":
101     { // Current database is version 1.0.0.1, update to 1.0.0.2
102     bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
103     goto case "1.0.0.2"; // Continue and upgrade one more step
104     }
105     case "1.0.0.2":
106     { // Current database is version 1.0.0.2, update to 1.0.0.3
107     bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
108     break;
109     }
110    
111    
112     default:
113     {
114     MessageBox.Show("Error: Not able to upgrade database (51188)");
115     break;
116     }
117     }
118     break;
119     }
120     case (int)VersionCheck.DatabaseIsMoreNew:
121     {
122     bContinue = false;
123     break;
124     }
125     case (int)VersionCheck.DatabaseNotFound:
126     {
127     //Run the creation script
128     bContinue = RunScript(Resource1.CreateDatabase.ToString());
129     if (bContinue)
130     {
131     // Set up file access rights for remote desktop users
132     SetDatabaseRights();
133     }
134    
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    
197     if (strResult == "0")
198     {
199    
200     return (int)VersionCheck.DatabaseNotFound;
201     }
202    
203     try
204     {
205     sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
206     strResult = (string)sqlCmd.ExecuteScalar();
207     }
208     catch
209     { // Assume we could not open the physical file
210     //Drop the database
211     sqlCmd = new SqlCommand("IF EXISTS(SELECT * FROM sysdatabases WHERE name='AnywhereTS')DROP DATABASE AnywhereTS", sqlCon);
212     strResult = (string)sqlCmd.ExecuteScalar();
213 william 18 return (int)VersionCheck.DatabaseNotFound;
214 william 4 }
215     vDb = new Version(strResult);
216    
217     sqlCon.Close();
218    
219     if (vDb == v)
220     return (int)VersionCheck.Equal;
221    
222     if (vDb > v)
223     return (int)VersionCheck.DatabaseIsMoreNew;
224    
225     else
226     return (int)VersionCheck.DatabaseIsOlder;
227    
228     }
229     catch (SqlException sql_ex)
230     {
231     MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
232     return (int)VersionCheck.Failed;
233     }
234     catch (Exception system_ex)
235     {
236     MessageBox.Show(system_ex.Message.ToString());
237     return (int)VersionCheck.Failed;
238     }
239     }
240    
241     public string[] ParseScriptToCommands(string strScript)
242     {
243     string[] commands;
244     commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
245     return commands;
246     }
247    
248    
249     public static void SetDatabaseRights()
250     {
251     try
252     {
253     // Add an access control entry to the database file.
254     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
255     ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
256     }
257     catch (Exception e)
258     {
259     MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
260     Application.Exit();
261     return;
262     }
263     }
264    
265     public static void StartSQLbrowserService()
266     {
267     System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
268     try
269     {
270     // Check that the SQL browser service is not already running
271     if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
272     { // Service not running, start it.
273     srvController.Start();
274     srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
275     }
276     }
277    
278     catch (Exception e)
279     {
280     MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
281     }
282     }
283    
284     // Configure the SQL browser service to autostart
285     public static void AutostartSQLbrowserService()
286     {
287     try
288     {
289     //construct the management path
290     string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
291     using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
292     {
293     object[] parameters = new object[1];
294     parameters[0] = "Automatic";
295     service.InvokeMethod("ChangeStartMode", parameters);
296    
297     }
298     }
299     catch
300     {
301 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.");
302 william 4 }
303     }
304    
305     // Enable named pipes on the SQL Express server
306     public static bool EnableNamedPipes()
307     {
308     ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
309     ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
310     ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
311     bool restarted = false; // Indicating if restart of SQL server was performed
312    
313     sqlServicesMan.Get();
314     serverProtocolsMan.Get();
315    
316     foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
317     {
318     prot.Get();
319     if ((string)prot.GetPropertyValue("ProtocolName") == "Np" && //Named pipes
320 william 17 (string)prot.GetPropertyValue("InstanceName") == InstanceName)
321 william 4 { // We found the named pipes protocol
322     if (!(bool)prot.GetPropertyValue("Enabled"))
323     { // Named pipes not activated
324     prot.InvokeMethod("SetEnable", null); // Activate named pipes
325    
326     // Check if user wants to restart SQL server
327     DialogResult resultRights;
328 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);
329 william 4 if (resultRights == DialogResult.Yes)
330     {
331     // Restart the SQL server
332     const uint sqlServerService = 1;
333     const uint sqlServiceStopped = 1;
334     foreach (ManagementObject svc in sqlServicesMan.GetInstances())
335     {
336     if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
337 william 17 (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
338 william 4 {
339     svc.Get();
340     if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
341     {
342     svc.InvokeMethod("StopService", null);
343     }
344     svc.InvokeMethod("StartService", null);
345     restarted = true;
346     } // end if
347     }
348     }
349     } // end if 'named pipes protool'
350     }
351     } // foreach
352    
353     return restarted;
354     }
355     }
356     }

  ViewVC Help
Powered by ViewVC 1.1.22