WhereIn() public method

Applies a WHERE clause with IN operator to a query.
public WhereIn ( string query, string column, string parameters ) : string
query string The query.
column string The column subject of the WHERE clause.
parameters string The names of the parameters in the IN set.
return string
        /// <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);
        }
Esempio n. 2
0
        /// <summary>
        /// Reduces the size of the Log to the specified size (or less).
        /// </summary>
        /// <param name="size">The size to shrink the log to (in bytes).</param>
        private void CutLog(int size)
        {
            ICommandBuilder builder     = GetCommandBuilder();
            DbConnection    connection  = builder.GetConnection(connString);
            DbTransaction   transaction = BeginTransaction(connection);

            QueryBuilder queryBuilder = new QueryBuilder(builder);

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

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

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

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

            int estimatedSize = rows * EstimatedLogEntrySize;

            if (size < estimatedSize)
            {
                int difference      = estimatedSize - size;
                int entriesToDelete = difference / EstimatedLogEntrySize;
                // Add 10% to avoid 1-by-1 deletion when adding new entries
                entriesToDelete += entriesToDelete / 10;

                if (entriesToDelete > 0)
                {
                    // This code is not optimized, but it surely works in most DBMS
                    query = queryBuilder.SelectFrom("Log", 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 CutRecentChangesIfNecessary

                        for (int chunk = 0; chunk <= ids.Count / MaxParametersInQuery; chunk++)
                        {
                            query = queryBuilder.DeleteFrom("Log");
                            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);
                }
            }
        }