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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 10 - (hide annotations) (download)
Wed Jul 11 16:01:40 2012 UTC (8 years, 7 months ago) by william
File size: 13731 byte(s)
SetupDatabase():
sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;

+ this enables the user to set the connection string
(they could use SQLEXPRESS or a full Server install with named instances)
Data Source=.\SQLEXPRESS
-or-
Data Source=IPADDRES\INSTANCENAME

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

  ViewVC Help
Powered by ViewVC 1.1.22