Wednesday, March 25, 2015

SQL Server 2008 - Get primary key in INSERT SQL statement

This tutorial is about to receive the primary key of a new inserted dataset.

declare @CreatedID bigint
exec sp_MyStoredProcedure parameter1, parameter2, @CreatedID OUTPUT
select @CreatedID

The stored procedure looks like this:
ALTER PROCEDURE [dbo].[sp_MyStoredProcedure]
@parameter1 int,
@parameter2 int,
@CreatedID BIGINT OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

insert into 
tbl_MyTable
(
field1,
field2
)
values
(
@parameter1,
@parameter2
);
SELECT @CreatedID = CAST(SCOPE_IDENTITY() as bigint)
END

Wednesday, March 4, 2015

C# - How to implement the IList Interface into a collection class

Yesterday I learned how to implement interfaces into my classes. 
I will show you, how to implement the IList interface into my custom collection class.
It's very simple. :-)

First we have to add 2 references to your class:

using System.Collections.Generic;
using System.Collections;
Then you have to add the interface behind your class name:

namespace MyProject.Classes
{
    class CollectionBase<T> : Connection, IList<T>
...

My example code is a universal collection class. Therefore, there is the type not defined of the IList not defined. You could write instead IList<MyClass>.

After added the interface name to the class, we can use the Visual Studio feature "Implement Interface":






The following code will be added automatically:

public int IndexOf(T item)
{
throw new NotImplementedException();
}

public void Insert(int index, T item)
{
throw new NotImplementedException();
}

public void RemoveAt(int index)
{
throw new NotImplementedException();
}

public T this[int index]
{
get
{
throw new NotImplementedException();
}
set
{
throw new NotImplementedException();
}
}

public void Add(T item)
{
throw new NotImplementedException();
}

public void Clear()
{
throw new NotImplementedException();
}

public bool Contains(T item)
{
throw new NotImplementedException();
}

public void CopyTo(T[] array, int arrayIndex)
{
throw new NotImplementedException();
}

public int Count
{
get { throw new NotImplementedException(); }
}

public bool IsReadOnly
{
get { throw new NotImplementedException(); }
}

public bool Remove(T item)
{
throw new NotImplementedException();
}

public IEnumerator<T> GetEnumerator()
{
throw new NotImplementedException();
}

IEnumerator IEnumerable.GetEnumerator()
{
throw new NotImplementedException();
}

Now we have all methods declared, but not defined yet.
We add now a list of our class to store to code:

#region Variables
private List<T> _InternalList = new List<T>();
#endregion

This list we can use to store all objects from the given class.

Now we have to modify the methods:

public int IndexOf(T item)
{
return _InternalList.IndexOf(item);
}

public void Insert(int index, T item)
{
_InternalList.Insert(index, item);
}

public void RemoveAt(int index)
{
_InternalList.RemoveAt(index);
}

public T this[int index]
{
get
{
        return _InternalList[index];
}
set
{
_InternalList[index] = value;
}
}

public void Add(T item)
{
_InternalList.Add(item);
}

public void Clear()
{
_InternalList.Clear();
}

public bool Contains(T item)
{
return _InternalList.Contains(item);
}

public void CopyTo(T[] array, int arrayIndex)
{
_InternalList.CopyTo(array, arrayIndex);
}

public int Count
{
get 
{
return _InternalList.Count;
}
}

public bool IsReadOnly
{
get 
{
return false;
}
}

public bool Remove(T item)
{
return _InternalList.Remove(item);
}

public IEnumerator<T> GetEnumerator()
{
return _InternalList.GetEnumerator();
}

System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
return (IEnumerator)this;
}

Now we have the interface implemented and are able to use the collection.

Here is the complete code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace MyProject.Classes
{
    class CollectionBase<T> : Connection, IList<T>
    {
        #region Variables
        private List<T> _InternalList = new List<T>();
        #endregion

        #region Properties
        #endregion

        #region Constructors
        public CollectionBase()
        {
        }
        #endregion

        #region Methods
        #endregion

        #region IList Interface
        public int IndexOf(T item)
        {
            return _InternalList.IndexOf(item);
        }

        public void Insert(int index, T item)
        {
            _InternalList.Insert(index, item);
        }

        public void RemoveAt(int index)
        {
            _InternalList.RemoveAt(index);
        }

        public T this[int index]
        {
            get
            {
                return _InternalList[index];
            }
            set
            {
                _InternalList[index] = value;
            }
        }

        public void Add(T item)
        {
            _InternalList.Add(item);
        }

        public void Clear()
        {
            _InternalList.Clear();
        }

        public bool Contains(T item)
        {
            return _InternalList.Contains(item);
        }

        public void CopyTo(T[] array, int arrayIndex)
        {
            _InternalList.CopyTo(array, arrayIndex);
        }

        public int Count
        {
            get 
            {
                return _InternalList.Count;
            }
        }

        public bool IsReadOnly
        {
            get 
            {
                return false;
            }
        }

        public bool Remove(T item)
        {
            return _InternalList.Remove(item);
        }

        public IEnumerator<T> GetEnumerator()
        {
            return _InternalList.GetEnumerator();
        }

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            return (IEnumerator)this;
        }
        #endregion
    }
}