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(@";Database")).Replace(@"\", "").Replace(@";", "").Replace(@"Database", "");
        public bool SetupDatabase()
        {
            bool bContinue = false;
            bool bCreateDB = 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;
                //bCreateDB = true;
                try
                {
                    sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS","master");
                    sqlCon.Open();
                    RunScript(Resource1.CreateDatabase.ToString());
                    sqlCon.Close();
                    sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;
                    sqlCon.Open();
                }
                catch (SqlException sql_ex1)
                {
                    MessageBox.Show(string.Format("Fail to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex1.Number.ToString() + " " + sql_ex1.Message.ToString());
                    return bContinue;
                }
            }
         
            // Now that you are connected to Express, check the database versions

            Version databaseVersion; // The current version of the database

            int chkVer = CheckVersion(out databaseVersion);
            VersionCheck verChk = (VersionCheck)chkVer;
            //MessageBox.Show(string.Format("Version Check: {0} Version: {1}", verChk.ToString(), databaseVersion.ToString()));

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