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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 18 - (hide annotations) (download)
Wed Jul 11 17:45:48 2012 UTC (9 years ago) by william
File size: 15141 byte(s)
+ add support to createdatabase is it does not exist

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

  ViewVC Help
Powered by ViewVC 1.1.22