executeReaderFirstDataRow() public static méthode

public static executeReaderFirstDataRow ( SQLiteCommand command ) : DataRow
command System.Data.SQLite.SQLiteCommand
Résultat System.Data.DataRow
Exemple #1
0
        public long getIdFromPath(string path)
        {
            SQLiteCommand command = new SQLiteCommand("SELECT id FROM `FileNodes` WHERE path = @path;", this.dbConnector.connection);

            command.Parameters.AddWithValue("@path", path);
            DataRow dr = DBConnector.executeReaderFirstDataRow(command);

            if (dr == null)
            {
                return(0);
            }
            return(Convert.ToInt32(dr["id"]));
        }
Exemple #2
0
        public int nrMetadataImagesToProcess()
        {
            SQLiteCommand command = new SQLiteCommand("SELECT COUNT(id) FROM `FileNodes` WHERE metainfoindexed = 0 LIMIT 1;", this.dbConnector.connection);
            DataRow       dr      = DBConnector.executeReaderFirstDataRow(command);

            if (dr != null)
            {
                return(Convert.ToInt32(dr[0]));
            }
            else
            {
                return(-1);
            }
        }
Exemple #3
0
        public int nrImagesFilter()
        {
            string        tableName = this.filterReady();
            SQLiteCommand command   = new SQLiteCommand("SELECT COUNT(id) FROM `" + tableName + "`;", this.dbConnector.connection);
            DataRow       dr        = DBConnector.executeReaderFirstDataRow(command);

            if (dr != null)
            {
                return(Convert.ToInt32(dr[0]));
            }
            else
            {
                return(-1);
            }
        }
Exemple #4
0
        public string getMetadataById(long id)
        {
            string        tableName = this.filterReady();
            SQLiteCommand command   = new SQLiteCommand("SELECT `all` FROM `Metadata` WHERE id = @id;", this.metaDataDbConnector.connection);

            command.Parameters.AddWithValue("@id", id);
            DataRow dr = DBConnector.executeReaderFirstDataRow(command);

            if (dr != null && dr.Table.Columns.Contains("all"))
            {
                return(Convert.ToString(dr["all"]));
            }
            else
            {
                return(null);
            }
        }
Exemple #5
0
        public DataRow getFirstImage(string orderBy, SortOrder direction, long offset)
        {
            string        tableName = this.filterReady();
            SQLiteCommand command;

            if (orderBy == null)
            {
                orderBy = "created";
            }
            if (direction == null)
            {
                direction = new SortOrder(SortOrder.Direction.ASC);
            }
            command = new SQLiteCommand("SELECT * FROM `" + tableName + "` ORDER BY " + orderBy + " " + direction.ToString() + ", id " + direction.ToString() + " LIMIT 1 OFFSET " + Math.Abs(offset) + ";", this.dbConnector.connection);
            command.Parameters.AddWithValue("@orderBy", orderBy);
            return(DBConnector.executeReaderFirstDataRow(command));
        }
Exemple #6
0
        public DataRow getRandomImage(long offset)
        {
            /***
             *  Single query "SELECT * FROM `Filter` ORDER BY RANDOM() LIMIT 1;" takes approximately 1000ms
             *  Two separate queries take approximately 100ms (combined)
             *
             * TODO optimise?
             * Test Select "SELECT id, path, ..., ... FROM `Filter` ORDER BY RANDOM() LIMIT 1;" ~ 450ms - 500ms
             ***/
            string        tableName = this.filterReady();
            SQLiteCommand command   = new SQLiteCommand("SELECT id FROM `" + tableName + "` ORDER BY RANDOM() LIMIT 1;", this.dbConnector.connection);
            DataRow       dr        = DBConnector.executeReaderFirstDataRow(command);

            if (dr == null)
            {
                return(null);
            }
            return(getImageById(Convert.ToInt32(dr["id"]), offset));
        }
Exemple #7
0
        public DataRow nextMetadataLessImage()
        {
            SQLiteCommand command = new SQLiteCommand("SELECT * FROM `FileNodes` WHERE metainfoindexed = 0 LIMIT 1;", this.dbConnector.connection);

            return(DBConnector.executeReaderFirstDataRow(command));
        }
Exemple #8
0
        public DataRow getImageById(long id, long offset, string sortByColumn, SortOrder sortByDirection)
        {
            string        tableName = this.filterReady();
            SQLiteCommand command;
            DataRow       dr;

            if (offset == 0)
            {
                command = new SQLiteCommand("SELECT * FROM `" + tableName + "` WHERE (id = @id) LIMIT 1;", this.dbConnector.connection);
                command.Parameters.AddWithValue("@id", id);
                dr = DBConnector.executeReaderFirstDataRow(command);
                if (dr == null)
                {
                    return(this.getFirstImage(sortByColumn, sortByDirection));
                }
            }
            else
            {
                if (sortByColumn == null)
                {
                    sortByColumn = "path";
                }
                if (sortByDirection == null)
                {
                    sortByDirection = new SortOrder(SortOrder.Direction.ASC);
                }
                string than = "<";

                if (sortByDirection.isDESC())
                {
                    offset *= -1;
                }
                if (offset < 0)
                {
                    than = "<";
                    sortByDirection.setDESC();
                }
                else
                {
                    than = ">";
                    sortByDirection.setASC();
                }

                string sqlValue = "(SELECT " + sortByColumn + " FROM `" + tableName + "` WHERE `" + tableName + "`.id = @id)";

                string sql = "FROM `" + tableName + "` WHERE (" + sortByColumn + " " + than + " " + sqlValue + ") " +
                             " OR ((" + sortByColumn + " = " + sqlValue + " AND id " + than + " @id)) " +
                             "ORDER BY " + sortByColumn + " " + sortByDirection.ToString() + ", id " + sortByDirection.ToString();

                command = new SQLiteCommand(@"SELECT * " + sql + " LIMIT 1 OFFSET " + (Math.Abs(offset) - 1) + ";", this.dbConnector.connection);
                command.Parameters.AddWithValue("@id", id);
                dr = DBConnector.executeReaderFirstDataRow(command);
                if (dr == null)
                {
                    command = new SQLiteCommand(@"SELECT COUNT(`" + tableName + "`.id) " + sql, this.dbConnector.connection);
                    command.Parameters.AddWithValue("@id", id);
                    dr = DBConnector.executeReaderFirstDataRow(command);
                    if (dr != null)
                    {
                        if (offset < 0)
                        {
                            offset += Convert.ToInt32(dr[0]) + 1;
                        }
                        else
                        {
                            offset -= Convert.ToInt32(dr[0]) + 1;
                        }
                        if (this.nrImagesFilter() > 0)
                        {
                            offset = offset % this.nrImagesFilter();
                        }
                        dr = getFirstImage(sortByColumn, sortByDirection, offset);
                    }
                }
            }
            return(dr);
        }