/// <summary>
        /// Create the initial database
        /// </summary>
        private void CreateDB()
        {
            var connection = new SqlCeConnection(this.path);

            try
            {
                var eng = new SqlCeEngine(this.path);
                var cleanup = new System.Threading.Tasks.Task(eng.Dispose);
                eng.CreateDatabase();
                cleanup.Start();
            }
            catch (Exception e)
            {
                EventLogging.WriteError(e);
            }

            connection.Open();
            var usersDB =
                new SqlCeCommand(
                    "CREATE TABLE Users_DB("
                    + "UserID int IDENTITY (100,1) NOT NULL UNIQUE, "
                    + "UserName nvarchar(128) NOT NULL UNIQUE, "
                    + "PassHash nvarchar(128) NOT NULL, "
                    + "Friends varbinary(5000), "
                    + "PRIMARY KEY (UserID));",
                    connection);
            usersDB.ExecuteNonQuery();
            usersDB.Dispose();
            connection.Dispose();
            connection.Close();
        }

 static void DropData(SqlCeConnection connection)
 {
     Console.Write("Dropping");
     Console.CursorLeft = 0;
     SqlCeCommand command = new SqlCeCommand("delete from entity", connection);
     command.ExecuteNonQuery();
 }

        private static void CreateInitialDatabaseObjects(string connString)
        {
            using (SqlCeConnection conn = new SqlCeConnection(connString))
            {
                string[] queries = Regex.Split(NetworkAssetManager.Properties.Resources.DBGenerateSql, "GO");
                SqlCeCommand command = new SqlCeCommand();
                command.Connection = conn;
                conn.Open();
                foreach (string query in queries)
                {
                    string tempQuery = string.Empty;
                    tempQuery = query.Replace("\r\n", "");

                    if (tempQuery.StartsWith("--") == true)
                    {
                        /*Comments in script so ignore*/
                        continue;
                    }

                    _logger.Info("Executing query: " + tempQuery);

                    command.CommandText = tempQuery;
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (System.Exception e)
                    {
                        _logger.Error(e.Message);
                    }
                }
                conn.Close();
            }
        }

        //Chamar sempre apos carregar os parametros
        public void Carregar()
        {
            SqlCeDataReader reader=null;
            try
            {
                string sql = "select * from funcionario where id=" + Id;
                SqlCeCommand cmd = new SqlCeCommand(sql, D.Bd.Con);
                reader = cmd.ExecuteReader();
                reader.Read();

                Nome = Convert.ToString(reader["nome"]);
                DescontoMaximo = Convert.ToDouble(reader["desconto_maximo"]);
                AcrescimoMaximo = Convert.ToDouble(reader["acrescimo_maximo"]);
            }
            catch (Exception ex)
            {
                throw new Exception("Não consegui obter os dados do funcionário, configure e sincronize antes de utilizar o dispositivo " + ex.Message);
            }
            finally
            {
                try
                {
                    reader.Close();
                }
                catch { }
            }
        }

        public void InitTestSchema()
        {
            var connStr = String.Format("Data Source = '{0}';", _testDb);
            using (var conn = new SqlCeConnection(connStr))
            {
                conn.Open();
                var command = new SqlCeCommand();
                command.Connection = conn;
                command.CommandText =
                    @"CREATE TABLE accel_data (
                                     id INT IDENTITY NOT NULL PRIMARY KEY,
                                     date DATETIME,
                                     Ax Float,Ay Float
                                     )";
                command.ExecuteNonQuery();

                command.CommandText = @"CREATE TABLE accel_params (
                                         id INT IDENTITY NOT NULL PRIMARY KEY,
                                         date DATETIME,
                                         sensorNumber smallint,
                                         offsetX Float,offsetY Float,
                                         gravityX Float,gravityY Float
                                     )";
                command.ExecuteNonQuery();

                command.CommandText = @"CREATE TABLE calibr_result (
                                         id INT IDENTITY NOT NULL PRIMARY KEY,
                                         accelDataId INT,
                                         accelParamsId INT
                                     )";
                command.ExecuteNonQuery();
            }
        }

        public ModuleMainForm()
        {
            InitializeComponent();
            try
            {
                // make data folder for this module
                Common.MakeAllSubFolders(Static.DataFolderPath);
                // check sdf database file, and copy new if dont exists
                Static.CheckDB_SDF();

                conn = new SqlCeConnection(Static.ConnectionString);
                command = new SqlCeCommand("", conn);
                dgwPanel.Columns.Add("tag", "Tag");
                dgwPanel.Columns.Add("info", "Info");
                DataGridViewButtonColumn btnColl1 = new DataGridViewButtonColumn();
                btnColl1.HeaderText = "Edit";
                btnColl1.Name = "Edit";
                dgwPanel.Columns.Add(btnColl1);
                keyEventsArgs = new KeyEventArgs(Keys.Oemtilde | Keys.Control);
                HookManager.KeyDown += new KeyEventHandler(HookManager_KeyDown);
            }
            catch (Exception exc)
            {
                Log.Write(exc, this.Name, "ModuleMainForm", Log.LogType.ERROR);
            }
        }

 public List<Appointment> getAppointmentList()
 {
     List<Appointment> AppointmentList = new List<Appointment>();
     SqlCeCommand cmd = new SqlCeCommand("select * from Appointment " +
                                         "inner join RoomInformation on Appointment.RoomID = RoomInformation.RoomID " +
                                         "inner join UserInformation on Appointment.UserID = UserInformation.User_ID " +
                                         "inner join UserInformation as RoomAccount on RoomInformation.UserID = RoomAccount.User_ID " +
                                         "where Appointment.RoomID = @RoomID and Appointment.UserID = @UserID", conn);
     cmd.Parameters.AddWithValue("@UserID", this._appointment.UserID);
     cmd.Parameters.AddWithValue("@RoomID", this._appointment.RoomID);
     SqlCeDataAdapter adapter = new SqlCeDataAdapter();
     adapter.SelectCommand = cmd;
     DataSet setdata = new DataSet();
     adapter.Fill(setdata, "Appointment");
     for (int i = 0; i < setdata.Tables[0].Rows.Count; i++)
     {
         Appointment model = new Appointment();
         model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[0].ToString());
         model.RoomID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[1].ToString());
         model.UserID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[2].ToString());
         model.AppointmentDate = DateTime.Parse(setdata.Tables[0].Rows[i].ItemArray[3].ToString());
         model.Status = setdata.Tables[0].Rows[i].ItemArray[4].ToString();
         model.Respond = setdata.Tables[0].Rows[i].ItemArray[5].ToString();
         model.RoomAcc.RoomID = Int64.Parse(setdata.Tables[0].Rows[i].ItemArray[1].ToString());
         model.RoomAcc.ApartmentName = setdata.Tables[0].Rows[i].ItemArray[7].ToString();
         model.RoomAcc.RoomForSale = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[8].ToString());
         model.RoomAcc.RoomForRent = bool.Parse(setdata.Tables[0].Rows[0].ItemArray[9].ToString());
         model.Appointee = setdata.Tables[0].Rows[i].ItemArray[49].ToString();
         model.RoomAcc.Username = setdata.Tables[0].Rows[i].ItemArray[62].ToString();
         AppointmentList.Add(model);
     }
     return AppointmentList;
 }

        public static int InsertCapturePointsForTextConversion(int RecommendationId, List<CustomTreeNode> customNodesList)
        {
            int returnCode = -1;
            List<int> capturePointsIds = new List<int>();
            SqlCeConnection conn = BackEndUtils.GetSqlConnection();
            try {
                conn.Open();
                for (int i = 0; i < customNodesList.Count; i++) {
                    SqlCeCommand command = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandInsertCapturePointTextConv, conn);
                    //@pointText, @pointUsedAttributes, @pointParentNode, @pointUsedAttribValues, @pointRecId
                    command.Parameters.Add("@pointText", customNodesList[i].Text);
                    command.Parameters.Add("@pointUsedAttributes", BackEndUtils.GetUsedAttributes(customNodesList[i].customizedAttributeCollection));
                    command.Parameters.Add("@pointParentNode", (customNodesList[i].Parent == null ? "" : customNodesList[i].Parent.Text));
                    command.Parameters.Add("@pointUsedAttribValues", BackEndUtils.GetUsedAttributesValues(customNodesList[i].customizedAttributeCollection));
                    command.Parameters.Add("@pointRecId", RecommendationId);
                    command.Parameters.Add("@Level", customNodesList[i].Level);
                    command.Parameters.Add("@ItemIndex", customNodesList[i].Index);
                    command.Parameters.Add("@parentLevel", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Level);
                    command.Parameters.Add("@parentIndex", customNodesList[i].Parent == null ? -1 : customNodesList[i].Parent.Index);

                    returnCode = Convert.ToInt32(command.ExecuteNonQuery());
                    SqlCeCommand commandMaxId = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandMaxCapturePointIdTextConv, conn);
                    capturePointsIds.Add(Convert.ToInt32(commandMaxId.ExecuteScalar()));
                }
            } finally {
                conn.Close();
            }
            return returnCode;
        }

        //AñadirPago
        public void AñadirNuevo(PagoEntrante pagEntr)
        {
            //Crear Conexion y Abrirla
            SqlCeConnection Con = CrearConexion();

            // Crear SQLCeCommand - Asignarle la conexion - Asignarle la instruccion SQL (consulta)
            SqlCeCommand Comando = new SqlCeCommand();
            Comando.Connection = Con;
            Comando.CommandType = CommandType.Text;

            Comando.CommandText = "INSERT INTO [PagosEntrantes] ([idTramite], [dniCuilCliente], [fecha], [valor], [detalle]) VALUES (@IDTRAMITE, @DNICUILCLI, @FECHA, @VALOR, @DETALLE)";
            Comando.Parameters.Add(new SqlCeParameter("@IDTRAMITE", SqlDbType.Int));
            Comando.Parameters["@IDTRAMITE"].Value = pagEntr.IdTramite;
            Comando.Parameters.Add(new SqlCeParameter("@DNICUILCLI", SqlDbType.NVarChar));
            Comando.Parameters["@DNICUILCLI"].Value = pagEntr.DniCuilCliente;
            Comando.Parameters.Add(new SqlCeParameter("@FECHA", SqlDbType.DateTime));
            Comando.Parameters["@FECHA"].Value = pagEntr.Fecha;
            Comando.Parameters.Add(new SqlCeParameter("@VALOR", SqlDbType.Money));
            Comando.Parameters["@VALOR"].Value = pagEntr.Valor;
            Comando.Parameters.Add(new SqlCeParameter("@DETALLE", SqlDbType.NVarChar));
            Comando.Parameters["@DETALLE"].Value = pagEntr.Detalle;

            //Ejecuta el comando INSERT
            Comando.Connection.Open();
            Comando.ExecuteNonQuery();
            Comando.Connection.Close();
        }

        public static User CreateUser(string username, string password)
        {
            SqlCeConnection con = new SqlCeConnection(CONNECTION_STRING);
            try
            {
                con.Open();
                SqlCeCommand comm = new SqlCeCommand("INSERT INTO users (username, password, salt, dateCreated) VALUES (@username, @password, @salt, @createdDate)", con);
                comm.Parameters.Add(new SqlCeParameter("@username", username));
                comm.Parameters.Add(new SqlCeParameter("@password", password));
                comm.Parameters.Add(new SqlCeParameter("@salt", String.Empty));
                comm.Parameters.Add(new SqlCeParameter("@createdDate", DateTime.UtcNow));

                int numberOfRows = comm.ExecuteNonQuery();
                if (numberOfRows > 0)
                {
                    return GetUser(username);
                }
            }
            catch (Exception ex)
            {
                Debug.Print("CreateUser Exception: " + ex);
            }
            finally
            {
                if (con != null && con.State == ConnectionState.Open)
                {
                    con.Close();
                }
            }

            return null;
        }

        private ApplicationState()
        {
            // read the application state from db
            SqlCeConnection _dataConn = null;
            try
            {
                _dataConn = new SqlCeConnection("Data Source=FlightPlannerDB.sdf;Persist Security Info=False;");
                _dataConn.Open();
                SqlCeCommand selectCmd = new SqlCeCommand();
                selectCmd.Connection = _dataConn;
                StringBuilder selectQuery = new StringBuilder();
                selectQuery.Append("SELECT cruiseSpeed,cruiseFuelFlow,minFuel,speed,unit,utcOffset,locationFormat,deckHoldFuel,registeredClientName FROM ApplicationState");
                selectCmd.CommandText = selectQuery.ToString();
                SqlCeResultSet results = selectCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
                if (results.HasRows)
                {
                    results.ReadFirst();
                    cruiseSpeed = results.GetInt64(0);
                    cruiseFuelFlow = results.GetInt64(1);
                    minFuel = results.GetInt64(2);
                    speed = results.GetSqlString(3).ToString();
                    unit = results.GetSqlString(4).ToString();
                    utcOffset = results.GetSqlString(5).ToString();
                    locationFormat = results.GetSqlString(6).ToString();
                    deckHoldFuel = results.IsDBNull(7) ? 0 : results.GetInt64(7);
                    registeredClientName = results.IsDBNull(8) ? string.Empty : results.GetString(8);
                }

            }

            finally
            {
                _dataConn.Close();
            }
        }

 public static void DeleteAllFileNames(SqlCeTransaction transaction, SqlCeConnection conn)
 {
     SqlCeCommand command = new SqlCeCommand(Folder_Names_SQL.commandDeleteAllFolderNames, conn);
     command.Transaction = transaction;
     command.Connection = conn;
     command.ExecuteNonQuery();
 }

        //Search button clicked
        private void button1_Click(object sender, EventArgs e)
        {
            SqlCeCommand cm = new SqlCeCommand();
            if (comboBox1.Text.Length < 1)
            {
                cm = new SqlCeCommand("SELECT ID, Marka as Brand, Tipus as Type, Kiadasi_ev as R_year, Berlesi_ar as Price  FROM Autok WHERE Allapot='raktaron' AND Kiadasi_ev>@yearfrom AND Kiadasi_ev<@yearuntil AND Eladasi_ar>@pricefrom AND Eladasi_ar<@priceuntil", Form1.con);
            }
            else if (comboBox2.Text.Length < 1)
            {
                cm = new SqlCeCommand("SELECT ID, Marka as Brand, Tipus as Type, Kiadasi_ev as R_year, Berlesi_ar as Price FROM Autok  WHERE Allapot='raktaron' AND [email protected]  AND Kiadasi_ev>@yearfrom AND Kiadasi_ev<@yearuntil AND Eladasi_ar>@pricefrom AND Eladasi_ar<@priceuntil ", Form1.con);
            }
            else
            {
                cm = new SqlCeCommand("SELECT ID, Marka as Brand, Tipus as Type, Kiadasi_ev as R_yer, Berlesi_ar as Price FROM Autok WHERE Allapot='raktaron' AND [email protected] AND [email protected]  AND Kiadasi_ev>@yearfrom AND Kiadasi_ev<@yearuntil AND Eladasi_ar>@pricefrom AND Eladasi_ar<@priceuntil ", Form1.con);
            }
            if (comboBox6.Text.Equals("100000+"))
            {
                cm.Parameters.AddWithValue("@priceuntil", int.MaxValue);
            }
            else
            {
                cm.Parameters.AddWithValue("@priceuntil", comboBox6.Text);
            }
            cm.Parameters.AddWithValue("@pricefrom", comboBox4.Text);
            cm.Parameters.AddWithValue("@yearuntil", comboBox5.Text);
            cm.Parameters.AddWithValue("@yearfrom", comboBox3.Text);
            cm.Parameters.AddWithValue("@tipus", comboBox2.Text);
            cm.Parameters.AddWithValue("@marka", comboBox1.Text);
            SqlCeDataAdapter a = new SqlCeDataAdapter(cm);
            DataTable dt = new DataTable();
            a.Fill(dt);

            dataGridView2.DataSource = dt;
            this.dataGridView2.Height = setAutomaticHeight(this.dataGridView2.Columns[0].HeaderCell.Size.Height, this.dataGridView2.Rows[0].Height, this.dataGridView2.Rows.Count, 340);
        }

		internal void Save()
		{
			const string updateSql = @"update Creating set
ExcludeFilesOfType = @ExcludeFilesOfType,
SortFiles = @SortFiles,
SFV32Compatibility = @SFV32Compatibility,
MD5SumCompatibility = @MD5SumCompatibility,
PromptForFileName = @PromptForFileName,
AutoCloseWhenDoneCreating = @AutoCloseWhenDoneCreating,
CreateForEachSubDir = @CreateForEachSubDir
";

			using (SqlCeCommand cmd = new SqlCeCommand(updateSql, Program.GetOpenSettingsConnection()))
			{
				cmd.Parameters.AddWithValue("@ExcludeFilesOfType", ExcludeFilesOfType);
				cmd.Parameters.AddWithValue("@SortFiles", SortFiles);
				cmd.Parameters.AddWithValue("@SFV32Compatibility", SFV32Compatibility);
				cmd.Parameters.AddWithValue("@MD5SumCompatibility", MD5SumCompatibility);
				cmd.Parameters.AddWithValue("@PromptForFileName", PromptForFileName);
				cmd.Parameters.AddWithValue("@AutoCloseWhenDoneCreating", AutoCloseWhenDoneCreating);
				cmd.Parameters.AddWithValue("@CreateForEachSubDir", CreateForEachSubDir);

				cmd.ExecuteNonQuery();
			}
		}

        public void connectToDatabase()
        {
            mySqlConnection = new SqlCeConnection(@"Data Source=C:\University\Adv Software Engineering\Bug Tracker\BugTracker\BugTracker\BugDatabase.mdf");
            String selcmd = "SELECT BugID, LineStart, LineEnd, ProgrammerName, ClassName, MethodName, TimeSubmitted, ProjectName, Description FROM dbo ORDER BY TimeSubmitted";
            SqlCeCommand mySqlCommand = new SqlCeCommand(selcmd, mySqlConnection);

        }

        public static DataTable ExecuteSqlQuery(string query, params SqlCeParameter[] sqlParams)
        {
            var dt = new DataTable();

            using (var conn = new SqlCeConnection(connStr))
            using (var cmd = new SqlCeCommand(query, conn))
            {
                try
                {
                    SqlCeEngine engine = new SqlCeEngine(conn.ConnectionString);
                    engine.Upgrade(conn.ConnectionString);
                }
                catch
                {
                }

                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddRange(sqlParams);

                conn.Open();
                dt.Load(cmd.ExecuteReader());
            }

            return dt;
        }

 public static long? ExecuteNonQuery(string query)
 {
     try
     {
         SqlCeConnection conn = CaseStudyDB.GetConnection();
         conn.Open();
         SqlCeCommand cmd = new SqlCeCommand(query, conn);
         cmd.ExecuteScalar();
         cmd = new SqlCeCommand("SELECT @@IDENTITY", conn);
         object queryReturn = cmd.ExecuteScalar();
         long value;
         long.TryParse(queryReturn.ToString(),out value);
         conn.Close();
         if(value != 0)
         {
             return value;
         }
         else
         {
             return null;
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(string.Format("Error exeuting query: {0}", ex.Message));
         return null;
     }
 }

        public Appointment getAppointment()
        {
            Appointment model = new Appointment();
            SqlCeCommand cmd = new SqlCeCommand("Select * from Appointment " +
                                                "inner join RoomInformation on Appointment.RoomID = RoomInformation.RoomID " +
                                                "inner join UserInformation on Appointment.UserID = UserInformation.User_ID where Appointment.AppointmentID = @AppointmentID", conn);
            cmd.Parameters.AddWithValue("@AppointmentID", this._appointment.AppointmentID);
            SqlCeDataAdapter adapter = new SqlCeDataAdapter();
            adapter.SelectCommand = cmd;

            DataSet setdata = new DataSet();
            adapter.Fill(setdata, "Appointment");
            model.AppointmentID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[0].ToString());
            model.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString());
            model.UserID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[2].ToString());
            model.AppointmentDate = DateTime.Parse(setdata.Tables[0].Rows[0].ItemArray[3].ToString());
            model.Status = setdata.Tables[0].Rows[0].ItemArray[4].ToString();
            model.Respond = setdata.Tables[0].Rows[0].ItemArray[5].ToString();
            model.RoomAcc.RoomID = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[1].ToString());
            model.RoomAcc.ApartmentName = setdata.Tables[0].Rows[0].ItemArray[7].ToString();
            model.RoomAcc.RoomForSale = bool.Parse (setdata.Tables[0].Rows[0].ItemArray[8].ToString());
            model.RoomAcc.RoomForRent = bool.Parse (setdata.Tables[0].Rows[0].ItemArray[9].ToString());
            model.RoomAcc.RoomPrice = Int64.Parse(setdata.Tables[0].Rows[0].ItemArray[12].ToString());
            model.RoomAcc.Username = setdata.Tables[0].Rows[0].ItemArray [49].ToString ();
            Account AccountModel = new Account();
            AccountModel.ID = model.UserID;
            AccountRepository _accountRepository = new AccountRepository(AccountModel);
            model.Appointee = _accountRepository.getDataByID().username;
            return model;
        }

        public static Guid GetApplicationId(string connectionString, string applicationName)
        {
            using (SqlCeConnection conn = new SqlCeConnection(connectionString))
                    {
                        using (SqlCeCommand cmd = new SqlCeCommand("SELECT ApplicationId FROM [aspnet_Applications] " +
                                            "WHERE ApplicationName = @ApplicationName", conn))
                        {
                            cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName;

                            conn.Open();
                            var applicationId = cmd.ExecuteScalar();
                            if (applicationId == null)
                            {
                                cmd.Parameters.Clear();
                                cmd.CommandText = "INSERT INTO [aspnet_Applications] (ApplicationId, ApplicationName, LoweredApplicationName, Description) VALUES (@ApplicationId, @ApplicationName, @LoweredApplicationName, @Description)";

                                applicationId = Guid.NewGuid();
                                cmd.Parameters.Add("@ApplicationId", SqlDbType.UniqueIdentifier).Value = applicationId;
                                cmd.Parameters.Add("@ApplicationName", SqlDbType.NVarChar, 256).Value = applicationName;
                                cmd.Parameters.Add("@LoweredApplicationName", SqlDbType.NVarChar, 256).Value = applicationName.ToLowerInvariant();
                                cmd.Parameters.Add("@Description", SqlDbType.NVarChar, 256).Value = String.Empty;

                                cmd.ExecuteNonQuery();
                            }
                            return (Guid)applicationId;

                        }
                    }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataConnectionDialog dcd = new DataConnectionDialog();
            DataConnectionConfiguration dcs = new DataConnectionConfiguration(null);
            dcs.LoadConfiguration(dcd);

            if (DataConnectionDialog.Show(dcd) == DialogResult.OK)
            {

                textBox2.Text = dcd.ConnectionString;
                connectionString = dcd.ConnectionString;
                comboBox1.Enabled = true;
                using (SqlCeConnection con = new SqlCeConnection(connectionString))
                {
                    comboBox1.Items.Clear();
                    con.Open();
                    using (SqlCeCommand command = new SqlCeCommand("SELECT table_name FROM INFORMATION_SCHEMA.Tables", con))
                    {
                        SqlCeDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            comboBox1.Items.Add(reader.GetString(0));

                        }
                    }
                }
                //textBox1.Text = dcd.SelectedDataSource.DisplayName;
            }
            dcs.SaveConfiguration(dcd);
        }