Sunday, October 14, 2012

Loop-safe List

I recently worked on a problem (of while I will probably write about later in more detail) that required looping over the elements in a list, and ran into this classic mistake:

List<int> list = new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
List<int> toRemove = new List<int>() { 2, 4, 6, 8 };
foreach(int item in list)
{
    if(toRemove.Contains(item))
    {
        list.Remove(item);
    }
}


Of course when this code runs, you immediately get the "Collection was modified; enumeration may not execute." error. Easily fixed by looping over a copy of the original list:

List<int> list = new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
List<int> toRemove = new List<int>() { 2, 4, 6, 8 };
List<int> listCopy = new List<int>(list);
foreach(int item in listCopy)
{
    if(toRemove.Contains(item))
    {
        list.Remove(item);
    }
}


So, I created my own "loop-safe" list class, called LoopList, as so:

/// <summary>
/// List that allows modification inside a foreach loop.
/// </summary>
/// <typeparam name="T">The type of object contained in the list.</typeparam>
public class LoopList<T> : IList<T>
{
    /// <summary>
    /// The items in the LoopList.
    /// </summary>
    List<T> _items;

    /// <summary>
    /// Default LoopList constructor, takes an optional isReadOnly flag.
    /// </summary>
    /// <param name="isReadOnly">Whether this will be a read-only collection.</param>
    public LoopList(bool isReadOnly = false)
    {
        _items = new List<T>();
        IsReadOnly = isReadOnly;
    }

    /// <summary>
    /// Constructor that takes an input collection to copy, also takes an optional isReadOnly flag.
    /// </summary>
    /// <param name="collection">The input collection to copy.</param>
    /// <param name="isReadOnly">Whether this will be a read-only collection.</param>
    public LoopList(IEnumerable<T> collection, bool isReadOnly = false)
    {
        _items = new List<T>(collection);
        IsReadOnly = isReadOnly;
    }

    /// <summary>
    /// Constructor that takes an initial capacity to allocate, also takes an optional isReadOnly flag.
    /// </summary>
    /// <param name="capacity">The initial capacity to allocate.</param>
    /// <param name="isReadOnly">Whether this will be a read-only collection.</param>
    public LoopList(int capacity, bool isReadOnly = false)
    {
        _items = new List<T>(capacity);
        IsReadOnly = isReadOnly;
    }

    /// <summary>
    /// Get the Index of an item in the LoopList.
    /// </summary>
    /// <param name="item">The item to find the index of.</param>
    /// <<returns>The index of the specified item.</returns>
    public int IndexOf(T item)
    {
        return _items.IndexOf(item);
    }

    /// <summary>
    /// Insert an item at the specified index.
    /// </summary>
    /// <param name="index">The index at which to insert the item.</param>
    /// <param name="item">The item to insert.</param>
    public void Insert(int index, T item)
    {
        if (IsReadOnly)
        {
            throw new InvalidOperationException("Attempt to Insert item into ReadOnly LoopList");
        }
        _items.Insert(index, item);
    }

    /// <summary>
    /// Remove the item at the specified index.
    /// </summary>
    /// <param name="index">The index of the item to remove.</param>
    public void RemoveAt(int index)
    {
        if (IsReadOnly)
        {
            throw new InvalidOperationException("Attempt to Remove item from ReadOnly LoopList");
        }
        _items.RemoveAt(index);
    }

    /// <summary>
    /// Get or set the item at a specified index.
    /// </summary>
    /// <param name="index">The index of the item to get or set.</param>
    /// <returns>The item at the specified index.</returns>
    public T this[int index]
    {
        get
        {
            return _items[index];
        }
        set
        {
            if (IsReadOnly)
            {
                throw new InvalidOperationException("Attempt to set item value in ReadOnly LoopList");
            }
            _items[index] = value;
        }
    }

    /// <summary>
    /// Add an item to the LoopList.
    /// </summary>
    /// <param name="item">The item to add.</param>
    public void Add(T item)
    {
        if (IsReadOnly)
        {
            throw new InvalidOperationException("Attempt to Add item to ReadOnly LoopList");
        }
        _items.Add(item);
    }

    /// <summary>
    /// Remove all items from the LoopList.
    /// </summary>
    public void Clear()
    {
        if (IsReadOnly)
        {
            throw new InvalidOperationException("Attempt to Clear ReadOnly LoopList");
        }
        _items.Clear();
    }

    /// <summary>
    /// Check if the LoopList contains an item.
    /// </summary>
    /// <param name="item">The item to look for.</param>
    /// <<returns>True if the LoopList contains the item, false otherwise.</returns>
    public bool Contains(T item)
    {
        return _items.Contains(item);
    }

    /// <summary>
    /// Copy the items in the LoopList to an array.
    /// </summary>
    /// <param name="array">The array to copy items into.</param>
    /// <param name="arrayIndex">The index in the array to start copying items to.</param>
    public void CopyTo(T[] array, int arrayIndex)
    {
        _items.CopyTo(array, arrayIndex);
    }
        
    /// <summary>
    /// The number of items in the LoopList.
    /// </summary>
    public int Count
    {
        get { return _items.Count; }
    }

    /// <summary>
    /// A flag indicating whether the LoopList is read-only.
    /// </summary>
    public bool IsReadOnly
    {
        get;
        private set;
    }

    /// <summary>
    /// Remove the specified item from the LoopList.
    /// </summary>
    /// <param name="item">The item to remove.</param>
    /// <returns>True if the item was successfully removed, false otherwise.</returns>
    public bool Remove(T item)
    {
        if (IsReadOnly)
        {
            throw new InvalidOperationException("Attempt to Remove item from ReadOnly LoopList");
        }
        return _items.Remove(item);
    }

    /// <summary>
    /// Get an enumerator to loop over the items in the LoopList.
    /// </summary>
    /// <returns>An Enumerator over items of the type contained in the LoopList.</returns>
    public IEnumerator<T> GetEnumerator()
    {
        List<T> tempItems = new List<T>(_items);
        return tempItems.GetEnumerator();
    }

    /// <summary>
    /// Get an enumerator to loop over the items in the LoopList.
    /// </summary>
    /// <returns>An Enumerator over items of the type contained in the LoopList.</returns>
    System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    {
        return GetEnumerator();
    }
}


And, voila! It works:

LoopList<int> list = new LoopList<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
List<int> toRemove = new List<int>() { 2, 4, 6, 8 };
foreach (int item in list)
{
    if (toRemove.Contains(item))
    {
        list.Remove(item);
    }
}

Friday, October 12, 2012

SQL Server inserting to two tables linked via an auto-increment ID column

I recently ran into an interesting SQL problem...thought I would share what I came up with and see if anyone has an other ideas.

So, the basic layout of the problem is this:

I have a record set (in a temp table) with some columns that need to be inserted into one table, and some columns that will be inserted into another. Only some of the records are going to be inserted. The first table that I'm inserting to has an auto-incremented ID column, and the value from this column will need to be copied to the corresponding rows in the second table. The values that will be inserted into the first table are not necessarily unique in and of themselves, so I can't just insert into the first table and then select out the matching IDs that were generated.

My solution was to generate an "insert index" value for each record to be inserted in the input record set, insert these records (in order), grab the last ID allocated, and then use this to update the input temp table so it can then be inserted into the second output table.

Luckily, if you use an order by when inserting into a table with an auto-increment ID column, SQL Server guarantees that the new IDs will be allocated in the order you specify (although the records may by physically inserted in a different order).

Anyway, without further ado, here is some example code...feel free to let me know what you think:


-- @TempA is the first output table
DECLARE @TempA TABLE ( id INT IDENTITY(320, 1), something VARCHAR(100) )

-- @TempB is the temp table that contains the data to be inserted
DECLARE @TempB TABLE (
      
rowindex INT IDENTITY(1, 1),
      
insertindex INT NULL,
      
doinsert bit,
      
id INT NULL,
      
something VARCHAR(100),
      
somethingelse VARCHAR(100 )
)

-- some variables...
DECLARE @id INT, @insertindex INT

-- populate the "insert" data set along with flags determining
-- whether the record will be inserted
INSERT INTO @TempB
( something, somethingelse, doinsert )
VALUES
( 'something1', 'else1', 0 ),
(
'something2', 'else2', 1 ),
(
'something1', 'else3', 1 ),
(
'something2', 'else4', 0 ),
(
'something1', 'else5', 1 ),
(
'something2', 'else6', 1 ),
(
'something1', 'else7', 1 )

-- initialize the counter variable
SET @insertindex = 0

-- generate ordering values for the records to be inserted
UPDATE @TempB
SET @insertindex = insertindex = @insertindex + 1
WHERE doinsert = 1

-- insert the records that are to be inserted
INSERT INTO @TempA
( something )
SELECT something
FROM @TempB
WHERE doinsert = 1
ORDER BY rowindex

-- grab the last identity that was auto-generated by the insert
SET @id = @@identity

-- update the temp table with the input record set with the
-- auto-generated ID so it can be inserted into the other table
UPDATE @TempB SET id = (@id - @insertindex) + insertindex
WHERE doinsert = 1

-- check to make sure the correct IDs were copied
SELECT * FROM @TempB