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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 22 - (hide annotations) (download)
Wed Jul 11 20:13:17 2012 UTC (7 years, 11 months ago) by william
File size: 16995 byte(s)
+ finish support for creating tables in an empty database (incase the database are pre-created, but contains no tables)
- still have AD login issues when creating the database from scratch (ie. it did not exist to begin with)

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

  ViewVC Help
Powered by ViewVC 1.1.22