public SqlCeCommand CreateAddProductCommand(SqlCeConnection conn, SqlCeTransaction transaction)
        {
            var cmd = new SqlCeCommand(_sql, conn, transaction);
            CreateParameters(cmd);

            return cmd;
        }

 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();
 }

 public static void DeletAllRespectiveCapturePointsForTextConversion(int captureEventId, SqlCeConnection conn, SqlCeTransaction transaction)
 {
     //pointRecId
     int value = 0;
     SqlCeCommand command = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandRemoveAllCapturePointsByRecIdTextConv, conn);
     command.Transaction = transaction;
     command.Parameters.Add("@pointRecId", captureEventId);
     value = Convert.ToInt32(command.ExecuteNonQuery());
 }

        /// <summary>
        ///     Starts a Transaction using the Connection instance
        /// </summary>
        /// <returns>
        ///     The <see cref="SqlCeTransaction" />.
        /// </returns>
        public SqlCeTransaction BeginTransaction()
        {
            if (this.transaction != null)
            {
                throw new InvalidOperationException(
                    "A transaction has already been started. Only one transaction is allowed");
            }

            this.transaction = EntityBase.Connection.BeginTransaction();
            this.UsersDB.Transaction = this.transaction;
            return this.transaction;
        }

 public static void SaveReplacementEventForTextConversion(ReplacementEvent replacementEvent, SqlCeConnection conn, SqlCeTransaction transaction)
 {
     SqlCeCommand command = new SqlCeCommand(Advanced_Replacements_TextConv_SQL.commandUpdateReplacementByIdTextConv, conn);
     command.Transaction = transaction;
     command.Parameters.Add("@name", replacementEvent.name);
     command.Parameters.Add("@description", replacementEvent.description);
     command.Parameters.Add("@value", replacementEvent.Value);
     command.Parameters.Add("@typeId", replacementEvent.typeId);
     command.Parameters.Add("@capturePointId", replacementEvent.capturePointId);
     command.Parameters.Add("@userId", replacementEvent.userId);
     command.Parameters.Add("@usageCount", replacementEvent.usageCount);
     command.Parameters.Add("@id", replacementEvent.id);
     command.ExecuteNonQuery();
 }

 public static void InsertFolderName(int index, string folderName, object parentId, string generatedID, SqlCeTransaction transaction, SqlCeConnection conn)
 {
     try {
         SqlCeCommand command = new SqlCeCommand(Folder_Names_SQL.commandInsertFolderName, conn);
         command.Parameters.Add("@id", index);
         command.Parameters.Add("@folderName", folderName);
         command.Parameters.Add("@parentId", parentId);
         command.Parameters.Add("@generatedID", generatedID);
         command.Transaction = transaction;
         command.Connection = conn;
         command.ExecuteNonQuery();
     } catch (Exception ex) {
         CommonUtils.LogError(ex.Message, ex);
     }
 }

 public static void InsertUpdatedFileNames(List<TreeNode> treeNodes, SqlCeTransaction transaction, SqlCeConnection conn)
 {
     try {
         for (int i = 0; i < treeNodes.Count; i++) {
             int parentId = treeNodes[i].Parent == null ? -1 : treeNodes.IndexOf(treeNodes[i].Parent);
             if (parentId != -1) {
                 InsertFolderName(i, treeNodes[i].Text, parentId, treeNodes[i].Tag.ToString(), transaction, conn);
             } else {
                 InsertFolderName(i, treeNodes[i].Text, DBNull.Value, treeNodes[i].Tag.ToString(), transaction, conn);
             }
         }
     } catch (Exception ex) {
         CommonUtils.LogError(ex.Message,ex);
     }
 }

    /// <summary>
    ///     A SqlCeConnection extension method that executes the non query operation.
    /// </summary>
    /// <param name="this">The @this to act on.</param>
    /// <param name="cmdText">The command text.</param>
    /// <param name="parameters">Options for controlling the operation.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="transaction">The transaction.</param>
    public static void ExecuteNonQuery(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
    {
        using (SqlCeCommand command = @this.CreateCommand())
        {
            command.CommandText = cmdText;
            command.CommandType = commandType;
            command.Transaction = transaction;

            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }

            command.ExecuteNonQuery();
        }
    }

    /// <summary>
    ///     A SqlCeConnection extension method that executes the reader operation.
    /// </summary>
    /// <param name="this">The @this to act on.</param>
    /// <param name="cmdText">The command text.</param>
    /// <param name="parameters">Options for controlling the operation.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="transaction">The transaction.</param>
    /// <returns>A SqlCeDataReader.</returns>
    public static SqlCeDataReader SqlCeExecuteReader(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
    {
        using (SqlCeCommand command = @this.CreateCommand())
        {
            command.CommandText = cmdText;
            command.CommandType = commandType;
            command.Transaction = transaction;

            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }

            return command.ExecuteReader();
        }
    }

 private void OpenConnection(SqlCeConnection aSqlCeConnection, SqlCeTransaction aSqlCeTransaction)
 {
     using (SqlCeCommand sqlCommand = new SqlCeCommand("SELECT s.Id_Session, s.Session, s.[DateTime] FROM Session AS s", aSqlCeConnection, aSqlCeTransaction))
     {
         sqlCommand.CommandType = CommandType.Text;
         using (SqlCeDataReader rdr = sqlCommand.ExecuteReader())
         {
             if (rdr.Read())
             {
                 FId_Session = (Int32)rdr["Id_Session"];
                 FSession = (Guid)rdr["Session"];
                 FDateTime = (DateTime)rdr["DateTime"];
             }
         }
     }
 }

 public static int InserNewCategory(ComparisonCategory comparisonCategory, SqlCeTransaction transaction, SqlCeConnection conn)
 {
     SqlCeCommand command = new SqlCeCommand(Env_Comparison_Categories_SQL.commandInsertNewEnvComparisonCategory, conn);
        command.Transaction = transaction;
        command.Parameters.Add("@id", comparisonCategory.categoryId);
        command.Parameters.Add("@name", comparisonCategory.categoryName);
        command.Parameters.Add("@description", comparisonCategory.categoryDescription);
        command.Parameters.Add("@path", comparisonCategory.categoryPath);
        if (comparisonCategory.categoryParentId == -1) {
        //DBNull.Value
        command.Parameters.Add("@parentId", DBNull.Value);
        } else {
        command.Parameters.Add("@parentId", comparisonCategory.categoryParentId);
        }
        int numberAffectedRows = Convert.ToInt32(command.ExecuteNonQuery());
        return numberAffectedRows;
 }

        public int Excluir(SqlCeTransaction bdTrans)
        {
            //string sqlUpdateEstoque = "";
            //Código temporariamente comentado por estar com erro 19/03/2009

            //            foreach (GradeItem gi in LstGradeItem)
            //            {
            //                //Recupera a quantidade do estoque
            //                string sqlPedidoOriginalQuantidade = @"
            //                select
            //                     quantidade
            //                from
            //                     item_pedido_grade
            //                where
            //                     id_pedido=" + Globals.Pedido.Id + " and id_produto=" + Produto.Id +
            //                         @" and id_grade = " + Produto.IdGrade.V +
            //                         @" and id_item_grade = " + gi.IdItemGrade +
            //                         @" and id_atributo = " + gi.IdAtributo +
            //                         @" and id_item_atributo = " + gi.IdItemAtributo;

            //                IntN quantidadeOriginal = Globals.Bd.IntN(sqlPedidoOriginalQuantidade, bdTrans);
            //                if (quantidadeOriginal.Iniciada)
            //                {
            //                    sqlUpdateEstoque = @"
            //                    update
            //                        saldo_grade
            //                    set
            //                        estoque=estoque + " + quantidadeOriginal.V + @"
            //                    where
            //                        id_produto=" + Produto.Id +
            //                                @" and id_grade = " + Produto.IdGrade.V +
            //                                @" and id_item_grade = " + gi.IdItemGrade +
            //                                @" and id_atributo = " + gi.IdAtributo +
            //                                @" and id_item_atributo = " + gi.IdItemAtributo;
            //                    Globals.Bd.ExecuteNonQuery(sqlUpdateEstoque, bdTrans);
            //                }
            //            }

            int rf;
            string sqlDeleteItemGrade = @"
            delete from
                item_pedido_grade
            where id_produto=" + Produto.Id + " and id_pedido='" + Produto.Pedido.Id + "'";
            rf = D.Bd.ExecuteNonQuery(sqlDeleteItemGrade, bdTrans);
            return rf;
        }

        public static void SetPrimaryKey(SqlCeTransaction trans, SqlCeRowUpdatedEventArgs e)
        {
            // If this is an INSERT operation...
            if (e.Status == UpdateStatus.Continue && e.StatementType == StatementType.Insert)
            {
                System.Data.DataColumn[] pk = e.Row.Table.PrimaryKey;
                // and a primary key PK column exists...
                if (pk != null && pk.Length == 1)
                {
                    //Set up the post-update query to fetch new @@Identity
                    SqlCeCommand cmdGetIdentity = new SqlCeCommand("SELECT @@IDENTITY", (SqlCeConnection)trans.Connection, trans);

                    //Execute the command and set the result identity value to the PK
                    e.Row[pk[0]] = Convert.ToInt32(cmdGetIdentity.ExecuteScalar());
                    e.Row.AcceptChanges();
                }
            }
        }

    /// <summary>
    ///     Enumerates execute expando objects in this collection.
    /// </summary>
    /// <param name="this">The @this to act on.</param>
    /// <param name="cmdText">The command text.</param>
    /// <param name="parameters">Options for controlling the operation.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="transaction">The transaction.</param>
    /// <returns>
    ///     An enumerator that allows foreach to be used to process execute expando objects in this collection.
    /// </returns>
    public static IEnumerable<dynamic> ExecuteExpandoObjects(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
    {
        using (SqlCeCommand command = @this.CreateCommand())
        {
            command.CommandText = cmdText;
            command.CommandType = commandType;
            command.Transaction = transaction;

            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }

            using (IDataReader reader = command.ExecuteReader())
            {
                return reader.ToExpandoObjects();
            }
        }
    }

		public override bool Save(SqlCeTransaction tr)
		{
			bool res = true;

			try
			{
				//save this
				switch (Status)
				{
					case Status.Added:
						if (string.IsNullOrWhiteSpace(Id))
							this.Id = Generator.GenerateID(CConstants.COLL);
						res = AddValue(this, tr);
						Status = Common.Interfaces.Status.Normal;
						break;
					case Status.Normal:
						break;
					case Status.Updated:
						res = UpdateValue(this);
						Status = Common.Interfaces.Status.Normal;
						break;
					case Status.Deleted:
						res = DeleteValue(this.Id);
						break;
					default:
						Status = Common.Interfaces.Status.Normal;
						break;
				}
			}
			catch (Exception ex)
			{
				res = false;
			}

			if (res)
			{
				Status = Common.Interfaces.Status.Normal;
			}

			return res;
		}

    /// <summary>
    ///     A SqlCeConnection extension method that executes the data set operation.
    /// </summary>
    /// <param name="this">The @this to act on.</param>
    /// <param name="cmdText">The command text.</param>
    /// <param name="parameters">Options for controlling the operation.</param>
    /// <param name="commandType">Type of the command.</param>
    /// <param name="transaction">The transaction.</param>
    /// <returns>A DataSet.</returns>
    public static DataSet ExecuteDataSet(this SqlCeConnection @this, string cmdText, SqlCeParameter[] parameters, CommandType commandType, SqlCeTransaction transaction)
    {
        using (SqlCeCommand command = @this.CreateCommand())
        {
            command.CommandText = cmdText;
            command.CommandType = commandType;
            command.Transaction = transaction;

            if (parameters != null)
            {
                command.Parameters.AddRange(parameters);
            }

            var ds = new DataSet();
            using (var dataAdapter = new SqlCeDataAdapter(command))
            {
                dataAdapter.Fill(ds);
            }

            return ds;
        }
    }

        public static int InsertCapturePointsAsTransactionForTextConversion(int RecommendationId, List<CustomTreeNode> customNodesList, SqlCeConnection conn, SqlCeTransaction transaction)
        {
            int returnCode = -1;
            List<int> capturePointsIds = new List<int>();
            for (int i = 0; i < customNodesList.Count; i++) {
                SqlCeCommand command = new SqlCeCommand(Rec_CapturePoints_TextConv_SQL.commandInsertCapturePointTextConv, conn);
                command.Transaction = transaction;
                //@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()));
            }
            return returnCode;
        }

 private static void InsertUpdatedCategories(List<ComparisonCategoryTreeNode> treeNodes, SqlCeTransaction transaction, SqlCeConnection conn)
 {
     for (int i = 0; i < treeNodes.Count; i++) {
        int parentId = treeNodes[i].Parent == null ? -1 : treeNodes.IndexOf(treeNodes[i].Parent as ComparisonCategoryTreeNode);
        if (parentId != -1) {
            ComparisonCategory comparisonCategory = treeNodes[i].comparisonCategory;
            comparisonCategory.categoryParentId = parentId;
            comparisonCategory.categoryId = i;
            InserNewCategory(comparisonCategory, transaction, conn);
            //  BackEndUtils.InsertFolderName(i, treeNodes[i].Text, parentId, transaction, conn);
        } else {
            ComparisonCategory comparisonCategory = treeNodes[i].comparisonCategory;
            comparisonCategory.categoryParentId = parentId;
            comparisonCategory.categoryId = i;
            InserNewCategory(comparisonCategory, transaction, conn);
        }
        }
 }

 private static void DeleteEnvResultsCategories(SqlCeTransaction transaction, SqlCeConnection conn)
 {
     SqlCeCommand command = new SqlCeCommand(Env_Comparison_Categories_SQL.commandDeleteAllEnvCategories, conn);
        command.Transaction = transaction;
        command.Connection = conn;
        command.ExecuteNonQuery();
 }

        /// <summary>
        /// Create a SqlCeCommand instance using the global SQL CE Connection instance and associate this with a transaction
        /// </summary>
        /// <param name="transaction">
        /// SqlCeTransaction to be used for the SqlCeCommand
        /// </param>
        /// <returns>
        /// The <see cref="SqlCeCommand"/>.
        /// </returns>
        public static SqlCeCommand CreateCommand(SqlCeTransaction transaction)
        {
            // TODO come up with a better way to check for the database file
            SqlCeCommand command;

                if (!File.Exists(ConfigurationSettings.AppSettings["DB_File"]))
                {
                    DatabaseFile.CreateDatabase();
                }
                command = Connection.CreateCommand();
            //try
            //{
            //    command = Connection.CreateCommand();
            //}
            //catch (SqlCeException notFound)
            //{

            //    if (notFound.Message.Contains("be found"))
            //    {

            //    }

            //    command = Connection.CreateCommand();
            //}

            command.Transaction = transaction;
            return command;
        }