Provide auto-trim support to keep table size down

[GoogleCodeExporter]

What new or enhanced feature are you proposing?

Provide auto-trim support.

What goal would this enhancement help you achieve?

It would keep the table from getting too big.

Below is a sample.

        public int TrimTable(string targetConnectionString, int 
targetRowCountMax)
        {
            int myRowsAffected = int.MinValue;

            targetConnectionString = 
(targetConnectionString + "").Trim();

            const string myTargetTableName = "ELMAH_Error";
            const string myTargetColumnNameForSort 
= "Sequence";
            System.Data.SqlClient.SortOrder 
myTargetSortOrder = System.Data.SqlClient.SortOrder.Ascending;
            const string myTargetColumnNameForKey 
= "ErrorId";
            const int myTargetTrimDenominator = 2;

            //Call an overload.
            myRowsAffected = this.TrimTable
(targetConnectionString, myTargetTableName, myTargetColumnNameForSort, 
myTargetSortOrder, myTargetColumnNameForKey, targetRowCountMax, 
myTargetTrimDenominator);

            return myRowsAffected;
        }

        /// <summary>
        /// This will trim the given table to the specified 
size.
        /// </summary>
        /// <param name="targetConnectionString">This is the 
connection string to use.</param>
        /// <param name="targetTableName">This is the table to 
truncate.</param>
        /// <param name="targetColumnNameForSort">This is the 
column to use for sorting before truncating.</param>
        /// <param name="targetSortOrder">Note 
Ascending=TrimFromTop and Descending=TrimFromBottom.</param>
        /// <param name="targetColumnNameForKey">This is the 
single-column primary-key column name.</param>
        /// <param name="targetRowCountMax">This is the max 
size allowed in the table.</param>
        /// <param name="targetTrimDenominator">Note 
(targetRowCountMax/targetTrimDenominator)=(RowCountToTrim).</param>
        /// <returns>This is the number of rows 
affected.</returns>
        public int TrimTable(
                string targetConnectionString,
                string targetTableName,
                string targetColumnNameForSort,
                System.Data.SqlClient.SortOrder 
targetSortOrder,
                string targetColumnNameForKey,
                int targetRowCountMax,
                int targetTrimDenominator)
        {
            int myRowsAffected = int.MinValue;

            targetConnectionString = 
(targetConnectionString + "").Trim();
            targetTableName = (targetTableName + "").Trim
();

            //TODO. 20090525. Remove after 1 month.
            //
            //int myRowCountCurrent = this.GetCount
(targetConnectionString, targetTableName);

            int myRowCountCurrent = this.GetCount();

            if (myRowCountCurrent <= targetRowCountMax)
            {
                //Continue.
            }
            else
            {
                targetColumnNameForSort = 
(targetColumnNameForSort + "").Trim();
                targetColumnNameForKey = 
(targetColumnNameForKey + "").Trim();
                string mySortDirection = "";

                if (targetSortOrder == 
System.Data.SqlClient.SortOrder.Ascending)
                {
                    mySortDirection = "ASC";
                }
                else
                {
                    mySortDirection = "DESC";
                }

                decimal myTargetSize = 
(targetRowCountMax / targetTrimDenominator);
                myTargetSize = (Math.Round
(myTargetSize, 0));
                int myTrimCount = (myRowCountCurrent -
 (int)myTargetSize);

                string myCommandText = " DELETE " + 
targetTableName +
                        " FROM " +
                        " (SELECT TOP " + 
myTrimCount + " * " +
                        " FROM " + 
targetTableName +
                        " ORDER BY " + 
targetColumnNameForSort + " " + mySortDirection + ") AS T1 " +
                        " WHERE " + 
targetTableName + "." + targetColumnNameForKey + " = T1." +

    targetColumnNameForKey;

                using (SqlConnection myConnection = 
new SqlConnection(targetConnectionString))
                {
                    SqlCommand myCommand = new 
SqlCommand(myCommandText, myConnection);
                    myCommand.Connection.Open();
                    myRowsAffected = 
myCommand.ExecuteNonQuery();
                }
            }

            if (myRowsAffected >= 0)
            {
                //Continue.
            }
            else
            {
                myRowsAffected = 0;
            }

            return myRowsAffected;
        }

Original issue reported on code.google.com by mkamo...@gmail.com on 1 Jun 2009 at 8:50

[GoogleCodeExporter]

I agree that this would be a nice intrinsic feature of the SQL-based logger (as 
well
as for the other database-specific loggers). The good news is that this sort of
functionality can easily be implemented in a number of ways, as I detailed in 
this
blog post:
http://scottonwriting.net/sowblog/posts/13882.aspx

Original comment by scott.k....@gmail.com on 4 Aug 2009 at 9:29

[GoogleCodeExporter]

This issue has been migrated to:
https://github.com/elmah/Elmah/issues/112
The conversation continues there.
DO NOT post any further comments to the issue tracker on Google Code as it is 
shutting down.
You can also just subscribe to the issue on GitHub to receive notifications of 
any further development.

Original comment by azizatif on 25 Aug 2015 at 8:17