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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 17 - (hide annotations) (download)
Wed Jul 11 16:48:23 2012 UTC (7 years, 11 months ago) by william
File size: 14072 byte(s)
+ fix more references to SQLEXPRESS

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

  ViewVC Help
Powered by ViewVC 1.1.22