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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 21 - (hide annotations) (download)
Wed Jul 11 19:34:51 2012 UTC (8 years, 11 months ago) by william
File size: 17316 byte(s)
+ commit wip for when database exists, but is empty

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

  ViewVC Help
Powered by ViewVC 1.1.22