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;
using log4net;

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

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

        const string SQL_BROWSER_SERVICE_NAME = "SQLBrowser";
        const Version CURRENT_DB_VERSION = new Version(1, 0, 0, 3);
        public static string InstanceName
        {
            get
            {
                bool start = false;
                bool end = false;
                List<char> pChars = new List<char>();
                foreach (char c in Properties.Settings.Default.atsConnectionString.ToCharArray())
                {
                    if (c == '\\') { start = true; continue; }
                    if (c == ';') { end = true; }
                    if (end) break;
                    if (start) { pChars.Add(c); }
                }
                return new string(pChars.ToArray());
            }
        }
        public bool SetupDatabase()
        {
            bool bContinue = false;
            // Create a connection to SQL Server
            
            Logging.ATSAdminLog.Debug("SetupDatabase() called ");
            try
            {                
                sqlCon.ConnectionString = Properties.Settings.Default.atsConnectionString;                
                using (log4net.NDC.Push(string.Format("ConnectionString={0}", Properties.Settings.Default.atsConnectionString)))
                {
                    Logging.ATSAdminLog.Debug("Opening connection to AnywhereTS Database");
                }
                sqlCon.Open();
            }
            catch
            {
                //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
                {
                    using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateDatabase.ToString())))
                    {
                        Logging.ATSAdminLog.Debug("Creating Database AnywhereTS");
                        sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
                        sqlCon.Open();
                        RunScript(Resource1.CreateDatabase.ToString());
                        sqlCon.Close();
                    }
                    Logging.ATSAdminLog.Debug("Created Database AnywhereTS");

                    using (log4net.NDC.Push(string.Format("SQL Statment={0}", Resource1.CreateTables.ToString())))
                    {
                        Logging.ATSAdminLog.Debug("Creating Tables in AnywhereTS Database");
                        sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
                        sqlCon.Open();
                        RunScript(Resource1.CreateTables.ToString());
                        sqlCon.Close();
                    }
                    Logging.ATSAdminLog.Debug("Created Tables in AnywhereTS Database");
                }
                catch (SqlException sql_ex1)
                {
                    SqlException sql_ex = (sql_ex1.GetBaseException() as SqlException);
                    MessageBox.Show(string.Format("Failed to connect to SQL Server Instance: {0}\n", InstanceName) + sql_ex.Number.ToString() + " " + sql_ex.Message.ToString());

                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", sql_ex.Number.ToString(), sql_ex.Message, System.Environment.NewLine, sql_ex.ToString())))
                    {
                        Logging.ATSAdminLog.Error("Failed to open connection to AnywhereTS Database");
                    }
                    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()));
            Logging.ATSAdminLog.DebugFormat("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
                                    Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
                                    bContinue = RunScript(Resource1.UpdateDatabase1.ToString());
                                    Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.0", "1.0.0.1");
                                    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
                                    Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.1", "1.0.0.2");
                                    bContinue = RunScript(Resource1.UpdateDatabase2.ToString());
                                    Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.2");
                                    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
                                    Logging.ATSAdminLog.DebugFormat("Upgrading Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
                                    bContinue = RunScript(Resource1.UpdateDatabase3.ToString());
                                    Logging.ATSAdminLog.DebugFormat("Upgraded Database from version: {0} to version: {1}", "1.0.0.2", "1.0.0.3");
                                    break;
                                }

                            
                            default:
                                {
                                    MessageBox.Show("Error: Not able to upgrade database (51188)");
                                    Logging.ATSAdminLog.DebugFormat("Failed to upgrade Database from version: {0} to version: {1}", databaseVersion.ToString(), CURRENT_DB_VERSION.ToString());
                                    break;
                                }
                        }
                        break;
                    }
                case (int)VersionCheck.DatabaseIsMoreNew:
                    {
                        bContinue = false;
                        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")
                {
                    sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
                    sqlCon.Open();
                    RunScript(Resource1.CreateDatabase.ToString());
                    sqlCon.Close();
                    sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
                    sqlCon.Open();
                    RunScript(Resource1.CreateTables.ToString());
                    sqlCon.Close();
                }
                try
                {
                    sqlCon.Close();
                    sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
                    sqlCon.Open();
                    sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
                    strResult = (string)sqlCmd.ExecuteScalar();
                    sqlCon.Close();
                }
                catch
                {
                    // the database exists, but one or more tables are missing
                    try
                    {
                        sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString.Replace("AnywhereTS", "master"));
                        sqlCon.Open();
                        RunScript(Resource1.CreateTables.ToString());
                        sqlCon.Close();
                        sqlCon = new SqlConnection(Properties.Settings.Default.atsConnectionString);
                        sqlCon.Open();
                        sqlCmd = new SqlCommand("SELECT value from AnywhereTS..AppInfo where property='version'", sqlCon);
                        strResult = (string)sqlCmd.ExecuteScalar();
                    }
                    catch
                    {
                        return (int)VersionCheck.Failed;
                    }
                }
                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;
        }


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