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

Annotation of /trunk/TSAdminTool/Database.cs

Parent Directory Parent Directory | Revision Log Revision Log


Revision 25 - (hide annotations) (download)
Wed Jul 11 20:39:32 2012 UTC (7 years, 11 months ago) by william
File size: 16657 byte(s)
+ commit current wip for non-existing database

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

  ViewVC Help
Powered by ViewVC 1.1.22