Provide auto-trim support to keep table size down

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
dhont / elmah

Provide auto-trim support to keep table size down #112
