trunk/TSAdminTool/Database.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Management;

namespace AnywhereTS
{
    class DatabaseSupport
    {
        enum VersionCheck
        {
            Failed = 0, Equal, DatabaseIsMoreNew,
            DatabaseIsOlder, DatabaseNotFound
        };

        private SqlConnection sqlCon = new SqlConnection();
        private SqlCommand sqlCmd = new SqlCommand();

        const string SQL_BROWSER_SERVICE_NAME = "SQLBrowser";

        public static string InstanceName = Properties.Settings.Default.atsConnectionString.Substring(Properties.Settings.Default.atsConnectionString.IndexOf(@"\"), Properties.Settings.Default.atsConnectionString.IndexOf(@";")).Replace(@"\", "").Replace(@";", "");
        public bool SetupDatabase()
        {
            bool bContinue = false;

            // Create a connection to SQL Server
            try
            {
                sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
                sqlCon.Open();
            }
            catch (SqlException sql_ex)
            {
                MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
                return bContinue;
            }
         
            // Now that you are connected to Express, check the database versions

            Version databaseVersion; // The current version of the database

            switch (CheckVersion(out databaseVersion))
            {
                case (int)VersionCheck.Equal:
                    {
                        bContinue = true;
                        break;
                    }
                case (int)VersionCheck.Failed:
                    {
                        bContinue = false;
                        break;
                    }
                case (int)VersionCheck.DatabaseIsOlder:
                    {
                        switch (databaseVersion.ToString())
                        {
                            // Run the apropriate upgdrade script(s)
                            case "1.0.0.0":
                                {   // Current database is version 1.0.0.0, update to 1.0.0.1
                                    bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
                                    goto case "1.0.0.1"; // Continue and upgrade one more step
                                }
                            case "1.0.0.1":
                                {   // Current database is version 1.0.0.1, update to 1.0.0.2
                                    bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
                                    goto case "1.0.0.2"; // Continue and upgrade one more step
                                }
                            case "1.0.0.2":
                                {   // Current database is version 1.0.0.2, update to 1.0.0.3
                                    bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
                                    break;
                                }

                            
                            default:
                                {
                                    MessageBox.Show("Error: Not able to upgrade database (51188)");
                                    break;
                                }
                        }
                        break;
                    }
                case (int)VersionCheck.DatabaseIsMoreNew:
                    {
                        bContinue = false;
                        break;
                    }
                case (int)VersionCheck.DatabaseNotFound:
                    {
                        //Run the creation script
                        bContinue = RunScript(Resource1.CreateDatabase.ToString());
                        if (bContinue)
                        {
                            // Set up file access rights for remote desktop users
                            SetDatabaseRights(); 
                        }

                        break;
                    }
                default:
                    {
                        bContinue = false;
                        break; 
                    }

            }
            sqlCon.Close();
            sqlCon.Dispose();
            sqlCmd.Connection.Close();
            sqlCmd.Connection.Dispose(); 
            return bContinue;
        }
        
        // Run a SQL script (to create or update a database)
        public bool RunScript(string strFile)
        {
            string[] strCommands;
            strCommands = ParseScriptToCommands(strFile);
            try
            {
                if (sqlCon.State != ConnectionState.Open) sqlCon.Open();
                
                sqlCmd.Connection = sqlCon;

                foreach (string strCmd in strCommands)
                {
                    if (strCmd.Length > 0)
                    {
                        // Substitute database directory with the decided one.
                        sqlCmd.CommandText = strCmd.Replace("[DataDir]",ProSupport.strDatabasePath);
                        sqlCmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException sql_ex)
            {
                MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
                return false;
            }

            return true;
        }
        
        
        // Check the version of the datbase
        public int CheckVersion(out Version vDb)
        {
            //Get Version information from application
            Version v=new Version(ATSGlobals.strDatabaseVersion);
            vDb = new Version("0.0.0.0"); // Assign a default value for version
            try
            {

                string strResult;

                //Verify that the AnywhereTS database exists
                sqlCmd = new SqlCommand("select count(*) from master..sysdatabases where name='AnywhereTS'", sqlCon);
                strResult = sqlCmd.ExecuteScalar().ToString();
                
                if (strResult == "0")
                {

                    return (int)VersionCheck.DatabaseNotFound;
                }

                try
                {
                    sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
                    strResult = (string)sqlCmd.ExecuteScalar();
                }
                catch
                {   // Assume we could not open the physical file 
                    //Drop the database
                    sqlCmd = new SqlCommand("IF EXISTS(SELECT * FROM sysdatabases WHERE name='AnywhereTS')DROP DATABASE AnywhereTS", sqlCon);
                    strResult = (string)sqlCmd.ExecuteScalar();
                    return (int)VersionCheck.DatabaseNotFound;                        
                }
                vDb = new Version(strResult);
          
                sqlCon.Close();

                if (vDb == v)
                    return (int)VersionCheck.Equal;

                if (vDb > v)
                    return (int)VersionCheck.DatabaseIsMoreNew;

                else 
                    return (int)VersionCheck.DatabaseIsOlder;
                
            }
            catch (SqlException sql_ex)
            {
                MessageBox.Show(sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());
                return (int)VersionCheck.Failed;
            }
            catch (Exception system_ex)
            {
                MessageBox.Show(system_ex.Message.ToString());
                return (int)VersionCheck.Failed;
            }
        }

        public string[] ParseScriptToCommands(string strScript)
        {
            string[] commands;
            commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
            return commands;
        }

     
        public static void SetDatabaseRights()
        {
            try
            {
                // Add an access control entry to the database file.
                ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename);
                ProSupport.GrantRWaccessForRemoteDesktopUsers(ProSupport.strDatabasePath + @"\" + ProSupport.strDatabaseFilename2);
            }
            catch (Exception e)
            {
                MessageBox.Show("Cannot set access rights for users to the database. Do you have the sufficient rights? Application will abort. Error: " + e.Message);
                Application.Exit();
                return;
            }
        }

        public static void StartSQLbrowserService()
        {
            System.ServiceProcess.ServiceController srvController = new System.ServiceProcess.ServiceController(SQL_BROWSER_SERVICE_NAME);
            try
            {
                // Check that the SQL browser service is not already running
                if (srvController.Status != System.ServiceProcess.ServiceControllerStatus.Running)
                {   // Service not running, start it.
                    srvController.Start();
                    srvController.WaitForStatus(System.ServiceProcess.ServiceControllerStatus.Running, TimeSpan.FromSeconds(50));
                }
            }

            catch (Exception e)
            {
                MessageBox.Show("Could not start the SQL Browser service (13078). Error:" + e.Message);
            }
        }

        // Configure the SQL browser service to autostart
        public static void AutostartSQLbrowserService()
        {
            try
            {
                //construct the management path
                string path = "Win32_Service.Name='" + SQL_BROWSER_SERVICE_NAME + "'";
                using (ManagementObject service = new ManagementObject(new ManagementPath(path)))
                {
                    object[] parameters = new object[1];
                    parameters[0] = "Automatic";
                    service.InvokeMethod("ChangeStartMode", parameters);

                }
            }
            catch
            {
                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.");
            }
        }

        // Enable named pipes on the SQL Express server
        public static bool EnableNamedPipes()
        { 
            ManagementScope manScope = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement");
            ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
            ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);
            bool restarted = false; // Indicating if restart of SQL server was performed

            sqlServicesMan.Get();
            serverProtocolsMan.Get();

            foreach (ManagementObject prot in serverProtocolsMan.GetInstances())
            {
                prot.Get();
                if ((string)prot.GetPropertyValue("ProtocolName") == "Np" &&  //Named pipes
                    (string)prot.GetPropertyValue("InstanceName") == InstanceName)
                {   // We found the named pipes protocol
                    if (!(bool)prot.GetPropertyValue("Enabled"))
                    {   // Named pipes not activated
                        prot.InvokeMethod("SetEnable", null);  // Activate named pipes
              
                        // Check if user wants to restart SQL server
                        DialogResult resultRights;
                        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);
                        if (resultRights == DialogResult.Yes)
                        {
                            // Restart the SQL server
                            const uint sqlServerService = 1;
                            const uint sqlServiceStopped = 1;
                            foreach (ManagementObject svc in sqlServicesMan.GetInstances())
                            {
                                if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
                                    (string)svc.GetPropertyValue("ServiceName") == string.Format("MSSQL${0}", InstanceName))
                                {
                                    svc.Get();
                                    if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
                                    {
                                        svc.InvokeMethod("StopService", null);
                                    }
                                    svc.InvokeMethod("StartService", null);
                                    restarted = true;
                                }   // end if
                            }
                        }
                    } // end if 'named pipes protool'
                }
            } // foreach

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