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 bool SetupDatabase()
        {
            bool bContinue = false;

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