C# QueryBuilder.SelectCountFrom方法代码示例

        /// <summary>
        /// Determines whether a directory exists.
        /// </summary>
        /// <param name="transaction">A database transaction.</param>
        /// <param name="directory">The directory, for example "/my/directory".</param>
        /// <returns><c>true</c> if the directory exists, <c>false</c> otherwise.</returns>
        /// <remarks>The root directory always exists.</remarks>
        private bool DirectoryExists(DbTransaction transaction, string directory)
        {
            directory = PrepareDirectory(directory);

            ICommandBuilder builder = GetCommandBuilder();
            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query = queryBuilder.SelectCountFrom("Directory");
            query = queryBuilder.Where(query, "FullPath", WhereOperator.Equals, "FullPath");

            List<Parameter> parameters = new List<Parameter>(1);
            parameters.Add(new Parameter(ParameterType.String, "FullPath", directory));

            DbCommand command = builder.GetCommand(transaction, query, parameters);

            int count = ExecuteScalar<int>(command, -1, false);

            return count == 1;
        }

        /// <summary>
        /// Determines whether a file exists.
        /// </summary>
        /// <param name="transaction">A database transaction.</param>
        /// <param name="fullName">The file full name, for example "/file.txt" or "/directory/file.txt".</param>
        /// <returns><c>true</c> if the file exists, <c>false</c> otherwise.</returns>
        private bool FileExists(DbTransaction transaction, string fullName)
        {
            string directory, file;
            SplitFileFullName(fullName, out directory, out file);

            ICommandBuilder builder = GetCommandBuilder();
            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query = queryBuilder.SelectCountFrom("File");
            query = queryBuilder.Where(query, "Name", WhereOperator.Equals, "Name");
            query = queryBuilder.AndWhere(query, "Directory", WhereOperator.Equals, "Directory");

            List<Parameter> parameters = new List<Parameter>(2);
            parameters.Add(new Parameter(ParameterType.String, "Name", file));
            parameters.Add(new Parameter(ParameterType.String, "Directory", directory));

            DbCommand command = builder.GetCommand(transaction, query, parameters);

            int count = ExecuteScalar<int>(command, -1, false);

            return count == 1;
        }

        /// <summary>
        /// Gets the total number of Messages in a Page Discussion.
        /// </summary>
        /// <param name="page">The Page.</param>
        /// <returns>The number of messages.</returns>
        /// <exception cref="ArgumentNullException">If <paramref name="page"/> is <c>null</c>.</exception>
        public int GetMessageCount(PageInfo page)
        {
            if(page == null) throw new ArgumentNullException("page");

            ICommandBuilder builder = GetCommandBuilder();
            DbConnection connection = builder.GetConnection(connString);

            if(GetPage(connection, page.FullName) == null) {
                CloseConnection(connection);
                return -1;
            }

            string nspace, name;
            NameTools.ExpandFullName(page.FullName, out nspace, out name);
            if(nspace == null) nspace = "";

            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query = queryBuilder.SelectCountFrom("Message");
            query = queryBuilder.Where(query, "Page", WhereOperator.Equals, "Page");
            query = queryBuilder.AndWhere(query, "Namespace", WhereOperator.Equals, "Namespace");

            List<Parameter> parameters = new List<Parameter>(2);
            parameters.Add(new Parameter(ParameterType.String, "Page", name));
            parameters.Add(new Parameter(ParameterType.String, "Namespace", nspace));

            DbCommand command = builder.GetCommand(connection, query, parameters);

            int count = ExecuteScalar<int>(command, 0);

            return count;
        }

        /// <summary>
        /// Determines whether a page attachment exists.
        /// </summary>
        /// <param name="transaction">A database transaction.</param>
        /// <param name="page">The page.</param>
        /// <param name="name">The attachment.</param>
        /// <returns><c>true</c> if the attachment exists, <c>false</c> otherwise.</returns>
        private bool AttachmentExists(DbTransaction transaction, PageInfo page, string name)
        {
            ICommandBuilder builder = GetCommandBuilder();
            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query = queryBuilder.SelectCountFrom("Attachment");
            query = queryBuilder.Where(query, "Name", WhereOperator.Equals, "Name");
            query = queryBuilder.AndWhere(query, "Page", WhereOperator.Equals, "Page");

            List<Parameter> parameters = new List<Parameter>(2);
            parameters.Add(new Parameter(ParameterType.String, "Name", name));
            parameters.Add(new Parameter(ParameterType.String, "Page", page.FullName));

            DbCommand command = builder.GetCommand(transaction, query, parameters);

            int count = ExecuteScalar<int>(command, -1, false);

            return count == 1;
        }

        /// <summary>
        /// Gets the approximate size, in bytes, of the search engine index.
        /// </summary>
        private long GetSize()
        {
            // 1. Size of documents: 8 + 2*20 + 2*30 + 2*1 + 8 = 118 bytes
            // 2. Size of words: 8 + 2*8 = 24 bytes
            // 3. Size of mappings: 8 + 8 + 2 + 2 + 1 = 21 bytes
            // 4. Size = Size * 2

            ICommandBuilder builder = GetCommandBuilder();
            DbConnection connection = builder.GetConnection(connString);

            QueryBuilder queryBuilder = new QueryBuilder(builder);

            long size = 0;

            string query = queryBuilder.SelectCountFrom("IndexDocument");
            DbCommand command = builder.GetCommand(connection, query, new List<Parameter>());
            int rows = ExecuteScalar<int>(command, -1, false);

            if(rows == -1) return 0;
            size += rows * 118;

            query = queryBuilder.SelectCountFrom("IndexWord");
            command = builder.GetCommand(connection, query, new List<Parameter>());
            rows = ExecuteScalar<int>(command, -1, false);

            if(rows == -1) return 0;
            size += rows * 24;

            query = queryBuilder.SelectCountFrom("IndexWordMapping");
            command = builder.GetCommand(connection, query, new List<Parameter>());
            rows = ExecuteScalar<int>(command, -1, false);

            if(rows == -1) return 0;
            size += rows * 21;

            CloseConnection(connection);

            return size * 2;
        }

        /// <summary>
        /// Gets the number of elements in the index.
        /// </summary>
        /// <param name="element">The type of elements.</param>
        /// <returns>The number of elements.</returns>
        private int GetCount(IndexElementType element)
        {
            ICommandBuilder builder = GetCommandBuilder();
            DbConnection connection = builder.GetConnection(connString);

            QueryBuilder queryBuilder = new QueryBuilder(builder);

            int count = 0;

            string elemName = "";
            if(element == IndexElementType.Documents) elemName = "IndexDocument";
            else if(element == IndexElementType.Words) elemName = "IndexWord";
            else if(element == IndexElementType.Occurrences) elemName = "IndexWordMapping";
            else throw new NotSupportedException("Unsupported element type");

            string query = queryBuilder.SelectCountFrom(elemName);

            DbCommand command = builder.GetCommand(connection, query, new List<Parameter>());
            count = ExecuteScalar<int>(command, -1, true);

            return count;
        }

        /// <summary>
        /// Prepares the plugin status row, if necessary.
        /// </summary>
        /// <param name="transaction">A database transaction.</param>
        /// <param name="typeName">The Type name of the plugin.</param>
        private void PreparePluginStatusRow(DbTransaction transaction, string typeName)
        {
            ICommandBuilder builder = GetCommandBuilder();
            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query = queryBuilder.SelectCountFrom("PluginStatus");
            query = queryBuilder.Where(query, "Name", WhereOperator.Equals, "Name");

            List<Parameter> parameters = new List<Parameter>(1);
            parameters.Add(new Parameter(ParameterType.String, "Name", typeName));

            DbCommand command = builder.GetCommand(transaction, query, parameters);

            int rows = ExecuteScalar<int>(command, -1, false);

            if(rows == -1) return;

            if(rows == 0) {
                // Insert a neutral row (enabled, empty config)

                query = queryBuilder.InsertInto("PluginStatus", new string[] { "Name", "Enabled", "Configuration" }, new string[] { "Name", "Enabled", "Configuration" });

                parameters = new List<Parameter>(3);
                parameters.Add(new Parameter(ParameterType.String, "Name", typeName));
                parameters.Add(new Parameter(ParameterType.Boolean, "Enabled", true));
                parameters.Add(new Parameter(ParameterType.String, "Configuration", ""));

                command = builder.GetCommand(transaction, query, parameters);

                ExecuteNonQuery(command, false);
            }
        }

        /// <summary>
        /// Cuts the recent changes if necessary.
        /// </summary>
        private void CutRecentChangesIfNecessary()
        {
            ICommandBuilder builder = GetCommandBuilder();
            DbConnection connection = builder.GetConnection(connString);
            DbTransaction transaction = BeginTransaction(connection);

            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query = queryBuilder.SelectCountFrom("RecentChange");

            DbCommand command = builder.GetCommand(transaction, query, new List<Parameter>());

            int rows = ExecuteScalar<int>(command, -1, false);

            int maxChanges = int.Parse(host.GetSettingValue(SettingName.MaxRecentChanges));

            if(rows > maxChanges) {
                // Remove 10% of old changes to avoid 1-by-1 deletion every time a change is made
                int entriesToDelete = maxChanges / 10;
                if(entriesToDelete > rows) entriesToDelete = rows;
                //entriesToDelete += entriesToDelete / 10;

                // This code is not optimized, but it surely works in most DBMS
                query = queryBuilder.SelectFrom("RecentChange", new string[] { "Id" });
                query = queryBuilder.OrderBy(query, new string[] { "Id" }, new Ordering[] { Ordering.Asc });

                command = builder.GetCommand(transaction, query, new List<Parameter>());

                DbDataReader reader = ExecuteReader(command);

                List<int> ids = new List<int>(entriesToDelete);

                if(reader != null) {
                    while(reader.Read() && ids.Count < entriesToDelete) {
                        ids.Add((int)reader["Id"]);
                    }

                    CloseReader(reader);
                }

                if(ids.Count > 0) {
                    // Given that the IDs to delete can be many, the query is split in many chunks, each one deleting 50 items
                    // This works-around the problem of too many parameters in a RPC call of SQL Server
                    // See also CutLog

                    for(int chunk = 0; chunk <= ids.Count / MaxParametersInQuery; chunk++) {
                        query = queryBuilder.DeleteFrom("RecentChange");
                        List<string> parms = new List<string>(MaxParametersInQuery);
                        List<Parameter> parameters = new List<Parameter>(MaxParametersInQuery);

                        for(int i = chunk * MaxParametersInQuery; i < Math.Min(ids.Count, (chunk + 1) * MaxParametersInQuery); i++) {
                            parms.Add("P" + i.ToString());
                            parameters.Add(new Parameter(ParameterType.Int32, parms[parms.Count - 1], ids[i]));
                        }

                        query = queryBuilder.WhereIn(query, "Id", parms.ToArray());

                        command = builder.GetCommand(transaction, query, parameters);

                        if(ExecuteNonQuery(command, false) < 0) {
                            RollbackTransaction(transaction);
                            return;
                        }
                    }
                }
            }

            CommitTransaction(transaction);
        }

        /// <summary>
        /// Verifies that a user exists.
        /// </summary>
        /// <param name="connection">A database connection.</param>
        /// <param name="username">The username.</param>
        /// <returns><c>true</c> if the user exists, <c>false</c> otherwise.</returns>
        private bool UserExists(DbConnection connection, string username)
        {
            ICommandBuilder builder = GetCommandBuilder();
            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query = queryBuilder.SelectCountFrom("User");
            query = queryBuilder.Where(query, "Username", WhereOperator.Equals, "Username");

            List<Parameter> parameters = new List<Parameter>(1);
            parameters.Add(new Parameter(ParameterType.String, "Username", username));

            DbCommand command = builder.GetCommand(connection, query, parameters);

            int count = ExecuteScalar<int>(command, -1, false);

            return count == 1;
        }