C# QueryBuilder.AppendForBatch方法代码示例

        /// <summary>
        /// Binds a Page with one or more Categories.
        /// </summary>
        /// <param name="connection">A database connection.</param>
        /// <param name="page">The Page to bind.</param>
        /// <param name="categories">The Categories to bind the Page with.</param>
        /// <returns>True if the binding succeeded.</returns>
        /// <remarks>After a successful operation, the Page is bound with all and only the categories passed as argument.</remarks>
        private bool RebindPage(DbConnection connection, PageInfo page, string[] categories)
        {
            string nspace, name;
            NameTools.ExpandFullName(page.FullName, out nspace, out name);
            if(nspace == null) nspace = "";

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

            string query = queryBuilder.DeleteFrom("CategoryBinding");
            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 rows = ExecuteNonQuery(command, false);

            if(rows < 0) return false;

            if(categories.Length > 0) {
                string finalQuery = "";
                parameters = new List<Parameter>(categories.Length * 3);
                int count = 0;
                string countString;

                foreach(string cat in categories) {
                    countString = count.ToString();

                    query = queryBuilder.InsertInto("CategoryBinding", new string[] { "Namespace", "Category", "Page" },
                        new string[] { "Namespace" + countString, "Category" + countString, "Page" + countString });
                    finalQuery = queryBuilder.AppendForBatch(finalQuery, query);

                    parameters.Add(new Parameter(ParameterType.String, "Namespace" + countString, nspace));
                    parameters.Add(new Parameter(ParameterType.String, "Category" + countString, NameTools.GetLocalName(cat)));
                    parameters.Add(new Parameter(ParameterType.String, "Page" + countString, name));

                    count++;
                }

                command = builder.GetCommand(connection, finalQuery, parameters);

                rows = ExecuteNonQuery(command, false);

                return rows == categories.Length;
            }
            else return true;
        }

        /// <summary>
        /// Stores the content for a revision.
        /// </summary>
        /// <param name="transaction">A database transaction.</param>
        /// <param name="content">The content.</param>
        /// <param name="revision">The revision.</param>
        /// <returns><c>true</c> if the content is stored, <c>false</c> otherwise.</returns>
        private bool SetContent(DbTransaction transaction, PageContent content, int revision)
        {
            string name, nspace;
            NameTools.ExpandFullName(content.PageInfo.FullName, out nspace, out name);
            if(nspace == null) nspace = "";

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

            string query = queryBuilder.InsertInto("PageContent",
                new string[] { "Page", "Namespace", "Revision", "Title", "User", "LastModified", "Comment", "Content", "Description" },
                new string[] { "Page", "Namespace", "Revision", "Title", "User", "LastModified", "Comment", "Content", "Description" });

            List<Parameter> parameters = new List<Parameter>(9);
            parameters.Add(new Parameter(ParameterType.String, "Page", name));
            parameters.Add(new Parameter(ParameterType.String, "Namespace", nspace));
            parameters.Add(new Parameter(ParameterType.Int16, "Revision", revision));
            parameters.Add(new Parameter(ParameterType.String, "Title", content.Title));
            parameters.Add(new Parameter(ParameterType.String, "User", content.User));
            parameters.Add(new Parameter(ParameterType.DateTime, "LastModified", content.LastModified));
            if(!string.IsNullOrEmpty(content.Comment)) parameters.Add(new Parameter(ParameterType.String, "Comment", content.Comment));
            else parameters.Add(new Parameter(ParameterType.String, "Comment", DBNull.Value));
            parameters.Add(new Parameter(ParameterType.String, "Content", content.Content));
            if(!string.IsNullOrEmpty(content.Description)) parameters.Add(new Parameter(ParameterType.String, "Description", content.Description));
            else parameters.Add(new Parameter(ParameterType.String, "Description", DBNull.Value));

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

            int rows = ExecuteNonQuery(command, false);

            if(rows != 1) return false;

            if(content.Keywords.Length > 0) {
                parameters = new List<Parameter>(content.Keywords.Length * 4);
                string fullQuery = "";
                int count = 0;
                string countString;
                foreach(string kw in content.Keywords) {
                    countString = count.ToString();

                    query = queryBuilder.InsertInto("PageKeyword", new string[] { "Page", "Namespace", "Revision", "Keyword" },
                        new string[] { "Page" + countString, "Namespace" + countString, "Revision" + countString, "Keyword" + countString });
                    fullQuery = queryBuilder.AppendForBatch(fullQuery, query);

                    parameters.Add(new Parameter(ParameterType.String, "Page" + countString, name));
                    parameters.Add(new Parameter(ParameterType.String, "Namespace" + countString, nspace));
                    parameters.Add(new Parameter(ParameterType.Int16, "Revision" + countString, revision));
                    parameters.Add(new Parameter(ParameterType.String, "Keyword" + countString, kw));

                    count++;
                }

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

                rows = ExecuteNonQuery(command, false);

                return rows == content.Keywords.Length;
            }
            else return true;
        }

        /// <summary>
        /// Removes all messages for a page and stores the new messages.
        /// </summary>
        /// <param name="page">The page.</param>
        /// <param name="messages">The new messages to store.</param>
        /// <returns><c>true</c> if the messages are stored, <c>false</c> otherwise.</returns>
        /// <exception cref="ArgumentNullException">If <paramref name="page"/> or <paramref name="messages"/> are <c>null</c>.</exception>
        public bool BulkStoreMessages(PageInfo page, Message[] messages)
        {
            if(page == null) throw new ArgumentNullException("page");
            if(messages == null) throw new ArgumentNullException("messages");

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

            if(GetPage(transaction, page.FullName) == null) {
                RollbackTransaction(transaction);
                return false;
            }

            foreach(Message msg in GetMessages(transaction, page)) {
                UnindexMessageTree(page, msg, transaction);
            }

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

            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query = queryBuilder.DeleteFrom("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(transaction, query, parameters);

            ExecuteNonQuery(command, false);

            List<Message> allMessages;
            List<int> parents;

            UnTreeMessages(messages, out allMessages, out parents, -1);

            string finalQuery = "";
            int count = 1;
            string countString;
            parameters = new List<Parameter>(MaxStatementsInBatch * 8);

            int rowsDone = 0;

            for(int i = 0; i < allMessages.Count; i++) {
                // Execute the batch in smaller chunks

                Message msg = allMessages[i];
                int parent = parents[i];

                countString = count.ToString();

                query = queryBuilder.InsertInto("Message", new string[] { "Page", "Namespace", "Id", "Parent", "Username", "Subject", "DateTime", "Body" },
                    new string[] { "Page" + countString, "Namespace" + countString, "Id" + countString, "Parent" + countString, "Username" + countString, "Subject" + countString, "DateTime" + countString, "Body" + countString });

                parameters.Add(new Parameter(ParameterType.String, "Page" + countString, name));
                parameters.Add(new Parameter(ParameterType.String, "Namespace" + countString, nspace));
                parameters.Add(new Parameter(ParameterType.Int16, "Id" + countString, (short)msg.ID));
                if(parent != -1) parameters.Add(new Parameter(ParameterType.Int16, "Parent" + countString, parent));
                else parameters.Add(new Parameter(ParameterType.Int16, "Parent" + countString, DBNull.Value));
                parameters.Add(new Parameter(ParameterType.String, "Username" + countString, msg.Username));
                parameters.Add(new Parameter(ParameterType.String, "Subject" + countString, msg.Subject));
                parameters.Add(new Parameter(ParameterType.DateTime, "DateTime" + countString, msg.DateTime));
                parameters.Add(new Parameter(ParameterType.String, "Body" + countString, msg.Body));

                finalQuery = queryBuilder.AppendForBatch(finalQuery, query);

                count++;

                if(count == MaxStatementsInBatch) {
                    command = builder.GetCommand(transaction, finalQuery, parameters);

                    rowsDone += ExecuteNonQuery(command, false);

                    finalQuery = "";
                    count = 1;
                    parameters.Clear();
                }
            }

            if(finalQuery.Length > 0) {
                command = builder.GetCommand(transaction, finalQuery, parameters);

                rowsDone += ExecuteNonQuery(command, false);
            }

            if(rowsDone == allMessages.Count) {
                foreach(Message msg in messages) {
                    IndexMessageTree(page, msg, transaction);
//.........这里部分代码省略.........

        /// <summary>
        /// Deletes all data associated to a document.
        /// </summary>
        /// <param name="document">The document.</param>
        /// <param name="state">A state object passed from the index (can be <c>null</c> or a <see cref="T:DbTransaction" />).</param>
        private void DeleteDataForDocument(IDocument document, object state)
        {
            // 1. Delete all data related to a document
            // 2. Delete all words that have no more mappings

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

            string query = queryBuilder.DeleteFrom("IndexDocument");
            query = queryBuilder.Where(query, "Name", WhereOperator.Equals, "DocName");
            List<Parameter> parameters = new List<Parameter>(1);
            parameters.Add(new Parameter(ParameterType.String, "DocName", document.Name));

            string subQuery = queryBuilder.SelectFrom("IndexWordMapping", new string[] { "Word" });
            subQuery = queryBuilder.GroupBy(subQuery, new string[] { "Word" });
            string query2 = queryBuilder.DeleteFrom("IndexWord");
            query2 = queryBuilder.WhereNotInSubquery(query2, "IndexWord", "Id", subQuery);

            query = queryBuilder.AppendForBatch(query, query2);

            DbCommand command = null;
            if(state != null) command = builder.GetCommand((DbTransaction)state, query, parameters);
            else command = builder.GetCommand(connString, query, parameters);

            // Close only if state is null
            ExecuteNonQuery(command, state == null);
        }

        /// <summary>
        /// Clears the index.
        /// </summary>
        /// <param name="state">A state object passed from the index.</param>
        private void ClearIndex(object state)
        {
            // state can be null, depending on when the method is called

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

            string query = queryBuilder.DeleteFrom("IndexWordMapping");
            query = queryBuilder.AppendForBatch(query, queryBuilder.DeleteFrom("IndexWord"));
            query = queryBuilder.AppendForBatch(query, queryBuilder.DeleteFrom("IndexDocument"));

            DbCommand command = null;
            if(state == null) command = builder.GetCommand(connString, query, new List<Parameter>());
            else command = builder.GetCommand((DbTransaction)state, query, new List<Parameter>());

            ExecuteNonQuery(command, state == null);
        }

        /// <summary>
        /// Adds a new Navigation Path.
        /// </summary>
        /// <param name="transaction">A database transaction.</param>
        /// <param name="nspace">The target namespace (<c>null</c> for the root).</param>
        /// <param name="name">The Name of the Path.</param>
        /// <param name="pages">The Pages array.</param>
        /// <returns>The correct <see cref="T:NavigationPath"/> object.</returns>
        private NavigationPath AddNavigationPath(DbTransaction transaction, string nspace, string name, PageInfo[] pages)
        {
            ICommandBuilder builder = GetCommandBuilder();
            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query, finalQuery = "";
            List<Parameter> parameters = new List<Parameter>(3 * pages.Length);
            int count = 0;
            string countString;

            foreach(PageInfo page in pages) {
                countString = count.ToString();

                query = queryBuilder.InsertInto("NavigationPath", new string[] { "Name", "Namespace", "Page", "Number" },
                    new string[] { "Name" + countString, "Namespace" + countString, "Page" + countString, "Number" + countString });

                parameters.Add(new Parameter(ParameterType.String, "Name" + countString, name));
                parameters.Add(new Parameter(ParameterType.String, "Namespace" + countString, nspace));
                parameters.Add(new Parameter(ParameterType.String, "Page" + countString, NameTools.GetLocalName(page.FullName)));
                parameters.Add(new Parameter(ParameterType.Int32, "Number" + countString, (short)count));

                finalQuery = queryBuilder.AppendForBatch(finalQuery, query);

                count++;
            }

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

            int rows = ExecuteNonQuery(command, false);

            if(rows == pages.Length) {
                NavigationPath result = new NavigationPath(NameTools.GetFullName(nspace, name), this);
                result.Pages = Array.ConvertAll<PageInfo, string>(pages, (x) => { return x.FullName; });
                return result;
            }
            else return null;
        }

        /// <summary>
        /// Merges two Categories.
        /// </summary>
        /// <param name="source">The source Category.</param>
        /// <param name="destination">The destination Category.</param>
        /// <returns>The correct <see cref="T:CategoryInfo" /> object.</returns>
        /// <remarks>The destination Category remains, while the source Category is deleted, and all its Pages re-bound 
        /// in the destination Category.</remarks>
        /// <exception cref="ArgumentNullException">If <paramref name="source"/> or <paramref name="destination"/> are <c>null</c>.</exception>
        public CategoryInfo MergeCategories(CategoryInfo source, CategoryInfo destination)
        {
            if(source == null) throw new ArgumentNullException("source");
            if(destination == null) throw new ArgumentNullException("destination");

            // 1. Check for same namespace
            // 2. Load all pages in source
            // 3. Load all pages in destination
            // 4. Merge lists in memory
            // 5. Delete all destination bindings
            // 6. Delete source cat
            // 7. Insert new bindings stored in memory

            string sourceNs = NameTools.GetNamespace(source.FullName);
            string destinationNs = NameTools.GetNamespace(destination.FullName);

            // If one is null and the other not null, fail
            if(sourceNs == null && destinationNs != null || sourceNs != null && destinationNs == null) return null;
            else {
                // Both non-null or both null
                if(sourceNs != null) {
                    // Both non-null, check names
                    NamespaceInfo tempSource = new NamespaceInfo(sourceNs, this, null);
                    NamespaceInfo tempDest = new NamespaceInfo(destinationNs, this, null);
                    // Different names, fail
                    if(new NamespaceComparer().Compare(tempSource, tempDest) != 0) return null;
                }
                // else both null, OK
            }

            string nspace = sourceNs != null ? sourceNs : "";

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

            CategoryInfo actualSource = GetCategory(transaction, source.FullName);
            CategoryInfo actualDestination = GetCategory(transaction, destination.FullName);

            if(actualSource == null) {
                RollbackTransaction(transaction);
                return null;
            }
            if(actualDestination == null) {
                RollbackTransaction(transaction);
                return null;
            }

            string destinationName = NameTools.GetLocalName(actualDestination.FullName);

            string[] mergedPages = MergeArrays(actualSource.Pages, actualDestination.Pages);

            QueryBuilder queryBuilder = new QueryBuilder(builder);

            string query = queryBuilder.DeleteFrom("CategoryBinding");
            query = queryBuilder.Where(query, "Namespace", WhereOperator.Equals, "Namespace");
            query = queryBuilder.AndWhere(query, "Category", WhereOperator.Equals, "Category");

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

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

            int rows = ExecuteNonQuery(command, false);

            if(rows == -1) {
                RollbackTransaction(transaction);
                return null;
            }

            if(!RemoveCategory(transaction, source)) {
                RollbackTransaction(transaction);
                return null;
            }

            string finalQuery = "";
            parameters = new List<Parameter>(MaxStatementsInBatch * 3);
            rows = 0;
            int count = 1;
            string countString;

            foreach(string page in mergedPages) {
                // This batch is executed in small chunks (MaxStatementsInBatch) to avoid exceeding DB's max batch length/size

                countString = count.ToString();

                query = queryBuilder.InsertInto("CategoryBinding", new string[] { "Namespace", "Category", "Page" },
                    new string[] { "Namespace" + countString, "Category" + countString, "Page" + countString });
                finalQuery = queryBuilder.AppendForBatch(finalQuery, query);

//.........这里部分代码省略.........