Home

 Business Logic Toolkit for .NET
www.bltoolkit.net

Business Logic Toolkit is a set of components to simplify .NET application development. BLToolkit is provided as source code that you can use "as is" or customize for your applications. It is written in C# and compatible with .NET Framework 2.0, 3.0, and 3.5.

Version 3.1
• BLToolkit 3.1 | Source code
• BLToolkit 3.1 Dev | Full dev version
Current SVN working copy
• BLToolkit | SVN checkout source code
• BLToolkit Dev | SVN checkout full dev version
Current SVN snapshot
• bltoolkit.zip | Source code
• bltoolkit_dev.zip | Full dev version
• bltoolkit_bin.zip | Binaries

ChangeLog.txt
v 3.2 (working)

Demo (see the dev version of the project)

- Linq.Demo.
- WebServices powered by BLToolkit.
- Partial.Trust demo.

Bug fixes

- Extension assemblies are ignored in TypeFactory.GetType(type).
- Default parameter values were ignored by OverloadAspectBuilder.
- [Parameter(null)] wasn't setting Nullable<> and EditableValue<Nullable<>> values to null
- DefaultTypeBuilder throwing exception if [Parameter(value)] was used on
  EditableValue<Nullable<>> generated properties when Nullable<> did have appropriate
  constructor accepting value type
- DateTimeOffset wasn't handled in DataReaderMapper correctly.

Enhancement

- Linq provider for supported databases.
- PostgreSQL Data Provider added.
- Informix Data Provider added.
- DB2 Data Provider added.
- New SqlIgnore attribute.
- New SqlBuilder class.
- Partial trust support.
- Mapping to/from private classes (FW 3.x only).
- New ParamTypeName attribute for SqlServer2008 table-type parameter support (FW 3.x only).
- EditableArrayList & BindingListImpl implements INotifyCollectionChanged interface.

v 3.1

Bug fixes

- MapValueAttribute ignored for nullable enums.
- MappingSchema.ConvertChangeType does not handle nullable enums properly.
- EditableArrayList indexer add/remove bug.
- ParameterAttribute doesn't work with Nullabe<T> and EditableValue<T>
  when 'T' constructor is not trivial.
- BindingListImpl.AddNew does not fire ListChanged event.
- Some inner types are missing in ObjectMappes, some included w/o reason.
- Setter for ObjectBinder.ItemType fires OnListChanged event before the list changes.
- Convert<To,From> does not handle `Nullable<To>' from scalar `From' case.
- EditableXmlDocument fires PropertyChanged event before an xml node changes.

Enhancement

- CHM help file provided. See the Doc folder.
- New InstanceCache attribute.
- DataAccessor support for IEnumerable<T>.
- Multiple duck. See DuckTyping.Aggregate methods.
- New Overload aspect.
- Own configuration section.
- TypeFactory.GetType() is public now.
- Simplified GenerateAttributeAttribute usage.

v 3.0

Breaking changes

- No more support for .Net 1.1 Framework.

Bug fixes

- EmitHelper.ldc_i4_ emits wrong opcode for numbers from 128 till 255 (bug in msdn)
- Some opcodes were not implemented.
- MappingSchema.MapResultSets fails with NullReferenceException when no data returned.
- Nullable enums were not handled properly.
- BindingList.GetItemSortedPosition() returns wrong index for an item at end of the list.
- Fixed AsyncAspect and other type builders compatibility.
- DbMananger.DiscoverSpParameters fires both OpenConnection and CloseConnection events.

Enhancement

- DateTimeOffset type support.
- MemberAccessor.CloneValue to avoid boxing.
- Named arguments support for GenerateAttributeTypeBuilder.
- Extended support for IsNull & Nullable types in MemberAccessor.
- Support for enum values in EmitHelper.LoadWellKnownValue/LoadInitValue and so on.
- Generic GetValue/SetValue methods for ValueHolders.
- Different projects to support FW 2.0 & FW 3.5.
- LinqMetadataProvider to support Linq To SQL attributes.
- Relocated MetadataProvider's to BLToolkit.Reflection namespace.
- New DbManager ctors to support manual/custom configuration.
- Ability to configure SqlQueryAttribute dynamically.
- Assembly is signed.
- New ActualTypeAttribute to associate DataAccessor's object type and its implementation.
- New AutoImplementInterfaceAttribute to automatically generate interface implementation.
- New TypeAccessor.AssociateType method and TypeAccessor.AssociatedTypeHandler event
  to control interface/type implementation.
- DataException now has the 'Number' property.
- New ClearCache aspect.

v 2.0.7

Bug fixes

- Oracle/Odp does not handle colon (':') in ConvertType.ParameterToName.
- Property change builder will use op_Inequality operation if it is available to compare
  property values.
- DbManager.ExecuteList<T>(params object[]) add an extra typeof(T)
- EditableObject.Clone does reference cloning of its inner objects.
- EditableObject.Clone does not track dirty state of its members.
- Generated assembly name ends with '.dll'.
  As a result, System.Reflection.Assembly.Load() tries to load 'SomeAssembly.dll.DLL'
  and fails.
- Oracle treat '\r' as an invalid character.
- DataAccessorBuilder calls DbProvider.Convert(NameToParameter) for text query
  parameters.
- SqlQueryBase.GetKeyFieldList was incorrectly marked as public.
- TypeAccessorBuilder fails to build a getter/setter if base method is protected.
- EditableXmlDocument fails to reject changes if there was an attribute insertion or
  deletion.

Enhancement

- FdpDataProvider.QuoteIdentifiers option for FB data provier.
- GenerateAtribute attribute to specify any custom attribute for a generated type or
  member.
- SQLite data provider.
- DeriveParameters implemented for MsAccess data provider.
- XmlIncludeAbstractAttribute to simplify the xml serialization of a BLToolkit generated
  type.
- Better support for internal types/members. The behavior is configurable via
  Configuration.EditableObjectUsesMemberwiseEquals property.
- VS 2008 support.

v 2.0.6

Demo

- MS PetShop 4.0 powered by BLToolkit (see the dev version of the project).

Bug fixes

- DbManager.OutputParameter lacks overload with 'size' parameters.
- CacheAspect hungs a desktop application on exit.
- ExecuteResultSet fails on many-to-one relations.
- TypeBuilder fails to override a method with generic parameters or generic return type.

Enhancement

- ParamSize & ParamDbType attributes to tweak DataAccessor parameters. Especially output
  ones.
- All 'emit.stloc' immediately followed by 'emit.ldloc' were replaced with simple
  'emit.dup' command.
  Lots of redundant local variables were removed.
- UnitTests for Oracle, Firebird & Access (Jet)
- DataSetTableAttribute is handled by all DataSet/DataTable related methods of
  DataAccessBuilder.
- ClearCache method for CacheAspect.
- Metadata provider for mapping.
- Ability to specify global assembly version & strong name key pair.
- AsyncAspect to execute any method asynchronously.
- Added support for ConnectionStrings config settings.
- Editable XmlDocuments.
- Added support for IDictionary, IDictionary<,>, IList, and IList<> abstract data
  accessor return types.

v 2.0.5

Breaking changes

- DbManager.InitCommand renamed to OnInitCommand.

Bug fixes

- System.Reflection.Emit.AssemblyBuilder assemblies are processed properly.
- RegExAttribute - now properly returns valid==true only when whole value is equal to
  match.
- EditableObject serialization issue
- StandardPropertyDescriptor.CheckNull was always returning DBNull.Value if _isNull
  handler was returning null. Now configurable via Configuration.CheckNullReturnIfNull.
- PropertyChangedBuilder was crashing when property was not public
- ObjectBinder.IsNull was serialized to resources
- Oracle provider now handles arrays of nullable types correctly.
- DataAccessor.PrepareParameters was invoked only to ref parameters.
- DataAccessor.PrepareParameters lacks DbManager which may be external.
- DbManager.InitParameters did hot handle decimal type.
- MappingSchema.ValueToEnum fails with underlying type other then Int32.
- CounterAspect catches the OnCatch event now to count exceptions.
- DbManager.ExecuteForEach does not call InitParameters in the loop.

Enhancement

- Ability to specify RegexOptions for RegExAttribute. Regex not being created on every
  call to IsValid
- Destination attribute works with scalar types.
- DataAccessorBuilder generates getters/setters for abstract properties & indexers.
- Ability to specify globaly whether strings should be trimed when mapping
- Ability to specify whether PropertyChange notification should only be fired when
  underlying value is changed and not when setter is called
- XmlDocument type support.
- DbManager.SetParameters() accepts arrays of IDbDataParameter.
- DataSetTableAttribute for DataAccessorBuilder to explicitly specify DataSet table name.
- Default CacheAspect.IsWeak value changed to false.
- Cleanup thread for CacheAspect.
- MemberMapper type can be specified directly in the target type declaration.
- Events for DbManager.OnBeforeOperation, OnAfterOperation, OnOperationException &
  OnInitCommand.

v 2.0.4

Bug fixes

- By default, the mapper included const fields into map list (???).
- If enum does not have explicit mapping, data accessor builder
  does not convert it to underlying type.
- MemberMapper fails to convert an object to its base type.
- BindingListImpl.RemoveSort was not firing ListChange event.
- TypeAccessor.CreateExtendedPropertyDescriptors did not work properly
  with 3rd+ level nested properties.
- Object holder ignored NoInstanceAttribute.
- ValidatorContext caches NullValue.
- EditableObject.RejectChanges/RejectMemberChanges were not firing PropertyChanged event
  for reverted fields.
- Fixed the generating IsValueType && !IsPrimitive ref/out parameters.

Enhancement

- Operator<T> is extensible now.
- ObjectBinder can now control lifetime (Disposal) of underlying collection.
- DuckTyping.
- ParamNullValue attribute.
- Virtual method DataProviderBase.PrepareCommand to let a data provider modify SQL
  statement befory query.
- Virtual method CacheAspect.GetCache. Can be used to provide a custom hashtable.

v 2.0.3

Bug fixes

- Race condition in MappingSchema.GetObjectMapper.
- DbManager will not try to close the connection in the finalizer anymore.
- MinValue/MaxValue attributes now determine value type by object property/field instead
  of test value.
- Fixed obscure case when object is removed from EditableArrayList/BindingListImpl while
  ItemPropertyChanged delegate is in process of being executed and collection handler was
  not yet invoked.

Enhancement

- HttpReader SOAP support.
- Convert<T,P> uses type cast operators when available.
- Third party DataProvider registration via appsettings.
- More specification adherent sorting behavior implementation of
  IBindingList/IBindingListView in BindingListImpl.
- EditableArrayList (potentially breaking changes) heavily relies on BindingListImpl for
  IBindingList/IBindingListView and their base interfaces implementation.
- BindingListImpl is changed to be fully specification adherent binding wrapper for other
  object lists/collection types.
- 2.0.1 version obsolete methods have been removed.

v 2.0.2

Bug fixes

- Abstract class builder overrode virtual final methods.
- Append records to a log file.
- ObjectBinder's AllowNew, AllowEdit, AllowRemove properties serialization.

Enhancement

- New DataAccessor<T,A> class.
- Direction.ReturnValueAttribute takes the Member parameter in '@fieldName' format.
- New property Object of the InterceptCallInfo class, which is an intercepted object
  reference.
© 2010 www.bltoolkit.net
support@bltoolkit.net


License

BLToolkit is licensed under MIT License:

Copyright © 2010 www.bltoolkit.net

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.


Documentation

• Aspects
• ComponentModel
• Data
• DataAccess
• EditableObjects
• Mapping
• Reflection
• TypeBuilder
• Validation


Aspects

All BLToolkit aspects are implemented as attributes and have to be applied to abstract or virtual members of an abstract class. You need to use TypeAccessor<T>.CreateInstance() method to create an instance of the class. BLToolkit class builder creates an actual class and applies aspects to abstract and virtual members by overriding the members and emitting (see System.Reflection.Emit namespace) additional code around them.

• Async aspect
Cache aspect
• Cache aspect
• NoCache attribute
• ClearCache aspect
• Counter aspect
• Logging aspect
• Mixin aspect
• NotNull attribute
• Overload aspect


Async aspect

This aspect simplifies asynchronous operations. Note the AsyncAspect is not compatible with other type builders that generate a method body. If you apply them along with the AsyncAspect to an abstract method, they will be igored.

AsyncAspect.cs
using System;
using System.Threading;

using NUnit.Framework;

using BLToolkit.Aspects;
using BLToolkit.Reflection;

namespace HowTo.Aspects
{
    public abstract class AsyncTestObject
    {
        // This is a member we will call asynchronously.
        //
        public int Test(int intVal, string strVal)
        {
            Thread.Sleep(200);
            return intVal;
        }

        // Begin async method should take the same parameter list as the Test method and return IAsyncResult.
        // Two additional parameters can be provided: AsyncCallback and state object.
        // 'Begin' prefix is a part of the default naming convention.
        //
        [Async] public abstract IAsyncResult BeginTest(int intVal, string strVal);
        [Async] public abstract IAsyncResult BeginTest(int intVal, string strVal, AsyncCallback callback);
        [Async] public abstract IAsyncResult BeginTest(int intVal, string strVal, AsyncCallback callback, object state);

        // End async method should take IAsyncResult and return the same type as the Test method.
        // 'End' prefix is a part of the default naming convention.
        //
        [Async] public abstract int          EndTest  (IAsyncResult asyncResult);

        // Begin/End naming convention is not required. You can use any name
        // if you provide the target method name as a parameter of the Async attribute.
        //
        [Async("Test")]
        public abstract IAsyncResult AnyName(int intVal, string strVal, AsyncCallback callback, object state);

        // Here we provide the parameter list along with the method name.
        //
        [Async("Test", typeof(int), typeof(string))]
        public abstract int         AnyName(IAsyncResult asyncResult);
    }

    [TestFixture]
    public class AsyncAspectTest
    {
        [Test]
        public void AsyncTest()
        {
            AsyncTestObject o = TypeAccessor<AsyncTestObject>.CreateInstance();

            IAsyncResult ar = o.BeginTest(1, "10");
            Assert.AreEqual(1, o.EndTest(ar));
        }

        private static void CallBack(IAsyncResult ar)
        {
            Console.WriteLine("Callback");

            AsyncTestObject o = (AsyncTestObject)ar.AsyncState;
            o.EndTest(ar);
        }

        [Test]
        public void CallbackTest()
        {
            AsyncTestObject o = TypeAccessor<AsyncTestObject>.CreateInstance();

            o.BeginTest(2, null, CallBack, o);
        }

        [Test]
        public void AnyNameTest()
        {
            AsyncTestObject o = TypeAccessor<AsyncTestObject>.CreateInstance();

            IAsyncResult ar = o.AnyName(2, null, null, null);
            Assert.AreEqual(2, o.AnyName(ar));
        }
    }
}
BLToolkit type builder will generate the following for the class above:
[BLToolkitGenerated]
internal delegate int TestObject$Test$Delegate(int, string);

[BLToolkitGenerated]
public sealed class AsyncTestObject : HowTo.Aspects.AsyncAspectTest.AsyncTestObject
{
    public override IAsyncResult BeginTest(int intVal, string strVal)
    {
        AsyncAspectBuilder.InternalAsyncResult r = new AsyncAspectBuilder.InternalAsyncResult();

        r.Delegate    = new TestObject$Test$Delegate(base.Test);
        r.InnerResult = r.Delegate.BeginInvoke(intVal, strVal, null, null);

        return r;
    }

    public override IAsyncResult BeginTest(int intVal, string strVal, AsyncCallback callback)
    {
        AsyncAspectBuilder.InternalAsyncResult r = new AsyncAspectBuilder.InternalAsyncResult();

        r.Delegate      = new TestObject$Test$Delegate(base.Test);
        r.AsyncCallback = callback;
        r.InnerResult   = r.Delegate.BeginInvoke(intVal, strVal, new AsyncCallback(r.CallBack), null);

        return r;
    }

    public override IAsyncResult BeginTest(int intVal, string strVal, AsyncCallback callback, object state)
    {
        AsyncAspectBuilder.InternalAsyncResult r = new AsyncAspectBuilder.InternalAsyncResult();

        r.Delegate      = new TestObject$Test$Delegate(base.Test);
        r.AsyncCallback = callback;
        r.InnerResult   = r.Delegate.BeginInvoke(intVal, strVal, new AsyncCallback(r.CallBack), state);

        return r;
    }

    public override int EndTest(IAsyncResult asyncResult)
    {
        AsyncAspectBuilder.InternalAsyncResult r = (AsyncAspectBuilder.InternalAsyncResult)asyncResult;

        return ((TestObject$Test$Delegate)r.Delegate).EndInvoke(r.InnerResult);
    }
}


Async aspect

This aspect simplifies method overloading. Note the OverloadAspect is not compatible with other type builders that generate a method body. If you apply them along with the OverloadAspect to an abstract method, they will be igored.

OverloadAspect.cs
using NUnit.Framework;

using BLToolkit.Aspects;
using BLToolkit.Reflection;

namespace HowTo.Aspects
{
    public abstract class OverloadTestObject
    {
        // This is a member we will overload.
        //
        public int Test(int intVal, string strVal)
        {
            return intVal;
        }

        // Overloaded methods simply calls a base method with same name
        // and has a few parameters less or more.
        //
        [Overload] public abstract int Test(int intVal);
        [Overload] public abstract int Test(string strVal);
        [Overload] public abstract int Test(int intVal, string strVal, bool boolVal);
    }

    [TestFixture]
    public class OverloadAspectTest
    {
        [Test]
        public void OverloadTest()
        {
            OverloadTestObject o = TypeAccessor<OverloadTestObject>.CreateInstance();

            Assert.AreEqual(1, o.Test(1));
            Assert.AreEqual(0, o.Test("str"));
        }
    }
}
BLToolkit type builder will generate the following for the class above:
[BLToolkitGenerated]
public sealed class OverloadTestObject : OverloadTestObject
{
    [BLToolkitGenerated]
    public override int Test(int intVal)
    {
        return this.Test(intVal, string.Empty);
    }

    [BLToolkitGenerated]
    public override int Test(string strVal)
    {
        return this.Test(0x0, strVal);
    }

    [BLToolkitGenerated]
    public override int Test(int intVal, string strVal, bool boolVal)
    {
        return this.Test(intVal, strVal);
    }
}


Cache aspect

This aspect helps to cache method calls. The aspect uses input method parameters to create a cache key and caches return value and all output (both ref and out) parameters. By default only value types and string type of the method parameters are used to create a cache key. Any other types are ignored. This behavior can be changed by assigning the CacheAspect.IsCacheableParameterType property to a delegate providing custom logic.

CacheAspect.cs
using System;
using System.Reflection;

using NUnit.Framework;

using BLToolkit.Aspects;
using BLToolkit.Reflection;

namespace HowTo.Aspects
{
    public abstract class TestClass
    {
        public static int Value;

        // This is a method we will cache. Cached return value depends on input parameters.
        // We will change the 'Value' field outside of the class and see how it affects the result.
        //
        [Cache(MaxCacheTime=500, IsWeak=false)]
        public virtual int CachedMethod(int p1, int p2)
        {
            return Value;
        }

        public static TestClass CreateInstance()
        {
            // Use TypeAccessor to create an instance of an abstract class.
            //
            return TypeAccessor<TestClass>.CreateInstance();
        }
    }

    [TestFixture]
    public class CacheAspectTest
    {
        [Test]
        public void Test1()
        {
            TestClass tc = TestClass.CreateInstance();

            DateTime begin = DateTime.Now;

            // Initial setup for the test static variable.
            //
            TestClass.Value = 777;

            while (tc.CachedMethod(2, 2) == 777)
            {
                // This change will not affect the Test method return value for 500 ms.
                //
                TestClass.Value++;
            }

            double totalMilliseconds = (DateTime.Now - begin).TotalMilliseconds;

            Assert.GreaterOrEqual(totalMilliseconds, 500);
        }

        [Test]
        public void Test2()
        {
            TestClass tc = TestClass.CreateInstance();

            // Return value depends on parameter values.
            //
            TestClass.Value = 1; Assert.AreEqual(1, tc.CachedMethod(1, 1));
            TestClass.Value = 2; Assert.AreEqual(1, tc.CachedMethod(1, 1)); // no change
            TestClass.Value = 3; Assert.AreEqual(3, tc.CachedMethod(2, 1));

            // However we can clear cache manually.
            // For particular method:
            //
            CacheAspect.ClearCache(typeof(TestClass), "CachedMethod", typeof(int), typeof(int));
            TestClass.Value = 4; Assert.AreEqual(4, tc.CachedMethod(2, 1));

            // By MethodInfo:
            //
            MethodInfo methodInfo = tc.GetType().GetMethod("CachedMethod", new Type[] { typeof(int), typeof(int) });
            CacheAspect.ClearCache(methodInfo);
            TestClass.Value = 5; Assert.AreEqual(5, tc.CachedMethod(2, 1));

            // For the all cached methods.
            //
            CacheAspect.ClearCache();
            TestClass.Value = 6; Assert.AreEqual(6, tc.CachedMethod(2, 1));
        }
    }
}
If we decompile the actual emitted TestClass class, we may see something like the following:
[BLToolkitGenerated]
public sealed class TestClass : HowTo.Aspects.TestClass
{
    private static CallMethodInfo _methodInfo;
    private static IInterceptor   _interceptor;

    public override int CachedMethod(int p1, int p2)
    {
        int returnValue = 0;

        if (_methodInfo == null)
        {
            _methodInfo = new CallMethodInfo((MethodInfo)MethodBase.GetCurrentMethod());
        }

        InterceptCallInfo info = new InterceptCallInfo();

        info.Object             = this;
        info.CallMethodInfo     = _methodInfo;
        info.ParameterValues[0] = p1;
        info.ParameterValues[1] = p2;
        info.ReturnValue        = returnValue;
        info.InterceptResult    = InterceptResult.Continue;
        info.InterceptType      = InterceptType.BeforeCall;

        if (_interceptor == null)
        {
            _interceptor = new CacheAspect();
            _interceptor.Init(_methodInfo, "MaxCacheTime=500;IsWeak=False");
        }

        // 'BeforeCall' step checks if the method is cached.
        // If it is and the cache is not expired, the Intercept method populates
        // return value and output parameters with the cached values and
        // sets info.InterceptResult to InterceptResult.Return.
        // See the CacheAspect.BeforeCall method for details.
        //
        _interceptor.Intercept(info);

        returnValue = (int)info.ReturnValue;

        if (info.InterceptResult != InterceptResult.Return)
        {
            // If the method call is not cached, target method is called.
            //
            returnValue = base.CachedMethod(p1, p2);

            info.ReturnValue     = returnValue;
            info.InterceptResult = InterceptResult.Continue;
            info.InterceptType   = InterceptType.AfterCall;

            // 'AfterCall' step stores parameters and return values in the cache.
            // See the CacheAspect.AfterCall method for details.
            //
            _interceptor.Intercept(info);

            returnValue = (int)info.ReturnValue;
        }

        return returnValue;
    }
}


ClearCache aspect

The ClearCache attribute clears cached data for the provided method.

ClearCacheAspect.cs
using System;

using NUnit.Framework;

using BLToolkit.Aspects;
using BLToolkit.Reflection;

namespace HowTo.Aspects
{
    [TestFixture]
    public class ClearCacheAspect
    {
        public abstract class TestClass
        {
            public static int Value;

            // This is a method we will cache. Cached return value depends on input parameters.
            // We will change the 'Value' field outside of the class and see how it affects the result.
            //
            [Cache(MaxCacheTime=500, IsWeak=false)]
            public virtual int CachedMethod(int p1, int p2)
            {
                return Value;
            }

            // This method clears the CachedMethod cache.
            //
            [ClearCache("CachedMethod")]
            public abstract void ClearCache();

            // The CachedMethod is specified by name and parameters.
            // Also you can use declaring method type.
            //
            [ClearCache("CachedMethod", typeof(int), typeof(int))]
            public abstract void ClearCache2();

            // This method clears all caches for provided type.
            //
            [ClearCache(typeof(TestClass))]
            public abstract void ClearAll();

            // This method clears all caches for current type.
            //
            [ClearCache]
            public abstract void ClearAll2();

            public static TestClass CreateInstance()
            {
                // Use TypeAccessor to create an instance of an abstract class.
                //
                return TypeAccessor<TestClass>.CreateInstance();
            }
        }

        [Test]
        public void Test()
        {
            TestClass tc = TypeAccessor<TestClass>.CreateInstance();

            TestClass.Value = 1;

            int value1 = tc.CachedMethod(1, 2);

            TestClass.Value = 2;

            int value2 = tc.CachedMethod(1, 2);

            // The cached values are equal.
            //
            Assert.AreEqual(value1, value2);

            tc.ClearCache();

            TestClass.Value = 3;

            // Previous and returned values are not equal.
            //
            Assert.AreNotEqual(value1, tc.CachedMethod(1, 2));

            tc.ClearCache2();
        }
    }
}
BLToolkit type builder will generate the following for the class above:
[BLToolkitGenerated]
public sealed class TestClass : ClearCacheAspect.TestClass
{
    private static MethodInfo _methodInfo1;
    private static MethodInfo _methodInfo2;
    private static Type       _type3;
    private static Type       _type4;

    public override int CachedMethod(int p1, int p2)
    {
        // Method implementation.
    }

    public override void ClearCache()
    {
        try
        {
            // Here should be main method implementation.
            // It is empty as this method does nothing.
        }
        finally
        {
            if (_methodInfo1 == null)
            {
                _methodInfo1 =
                    ClearCacheAspect.GetMethodInfo(this, null, "CachedMethod", null);
            }

            CacheAspect.ClearCache(_methodInfo1);
        }
    }

    public override void ClearCache2()
    {
        try
        {
        }
        finally
        {
            if (_methodInfo2 == null)
            {
                _methodInfo2 = ClearCacheAspect.GetMethodInfo(
                    this,
                    null,
                    "CachedMethod",
                    new Type[] { typeof(int), typeof(int) });
            }

            CacheAspect.ClearCache(_methodInfo2);
        }
    }

    public override void ClearAll()
    {
        try
        {
        }
        finally
        {
            if (_type3 == null)
            {
                _type3 = ClearCacheAspect.GetType(this, typeof(TestClass));
            }

            CacheAspect.ClearCache(_type3);
        }
    }

    public override void ClearAll2()
    {
        try
        {
        }
        finally
        {
            if (_type4 == null)
            {
                _type4 = ClearCacheAspect.GetType(this, null);
            }

            CacheAspect.ClearCache(_type4);
        }
    }
}


NoCache attribute

Cache aspect can be applied to entire class. In this case all virtual and abstract member calls will be cached. However you can use the NoCache attribute to exclude particular members from caching.

NoCache.cs
using System;

using NUnit.Framework;

using BLToolkit.Aspects;
using BLToolkit.Reflection;

namespace HowTo.Aspects
{
    [Cache]
    public abstract class NoCacheTestClass
    {
        public static int Value;

        public virtual int CachedMethod(int p1, int p2)
        {
            return Value;
        }

        [NoCache]
        public virtual int NoCacheMethod(int p1, int p2)
        {
            return Value;
        }

        public static NoCacheTestClass CreateInstance()
        {
            // Use TypeAccessor to create an instance of an abstract class.
            //
            return TypeAccessor<NoCacheTestClass>.CreateInstance();
        }
    }

    [TestFixture]
    public class NoCacheAttributeTest
    {
        [Test]
        public void Test()
        {
            NoCacheTestClass t = TypeAccessor<NoCacheTestClass>.CreateInstance();

            NoCacheTestClass.Value = 1; Assert.AreEqual(1, t.CachedMethod(1, 1));
            NoCacheTestClass.Value = 2; Assert.AreEqual(1, t.CachedMethod(1, 1)); // no change

            NoCacheTestClass.Value = 3; Assert.AreEqual(3, t.NoCacheMethod(2, 1));
            NoCacheTestClass.Value = 4; Assert.AreEqual(4, t.NoCacheMethod(2, 1));
        }
    }
}


Counter aspect

This aspect helps to collect statistical information for the members it is applied to.

CounterAspect.cs
using System;
using System.Reflection;

using NUnit.Framework;

using BLToolkit.Aspects;
using BLToolkit.Reflection;

namespace HowTo.Aspects
{
    [TestFixture]
    public class CounterAspectTest
    {
        public abstract class TestClass
        {
            // This is a method we collect statistic for.
            // Actually the entire class or even a base class
            // can be decorated with the attribute.
            //
            [Counter]
            public virtual void TestMethod()
            {
            }
        }

        [Test]
        public void Test()
        {
            TestClass t = TypeAccessor<TestClass>.CreateInstance();

            for (int i = 0; i < 10; i++)
                t.TestMethod();

            MethodInfo     methodInfo = typeof(TestClass).GetMethod("TestMethod");
            MethodCallCounter counter = CounterAspect.GetCounter(methodInfo);

            Assert.AreEqual(10, counter.TotalCount);

            Console.WriteLine(@"
Method         : {0}.{1}
TotalCount     : {2}
ExceptionCount : {3}
CachedCount    : {4}
CurrentCalls   : {5}
TotalTime      : {6}
MinTime        : {7}
MaxTime        : {8}
AverageTime    : {9}
",
                counter.MethodInfo.DeclaringType.Name,
                counter.MethodInfo.Name,
                counter.TotalCount,              // total actual calls (no cached calls)
                counter.ExceptionCount,          // calls with exceptions
                counter.CachedCount,             // cached calls
                counter.CurrentCalls.Count,      // current calls (make sense for multithreading)
                counter.TotalTime,               // total work time
                counter.MinTime,                 // min call time
                counter.MaxTime,                 // max call time
                counter.AverageTime);            // average call time
        }
    }
}
BLToolkit type builder will generate the following for the class above:
[BLToolkitGenerated]
public sealed class TestClass : CounterAspectTest.TestClass
{
    private static CallMethodInfo _methodInfo;
    private static IInterceptor   _interceptor;

    public override void TestMethod()
    {
        if (_methodInfo == null)
        {
            _methodInfo = new CallMethodInfo((MethodInfo)MethodBase.GetCurrentMethod());
        }

        InterceptCallInfo info = new InterceptCallInfo();

        try
        {
            info.Object          = this;
            info.CallMethodInfo  = _methodInfo;
            info.InterceptResult = InterceptResult.Continue;
            info.InterceptType   = InterceptType.BeforeCall;

            if (_interceptor == null)
            {
                _interceptor = new CounterAspect();
                _interceptor.Init(_methodInfo, null);
            }

            // 'BeforeCall' creates or gets a counter for the method and
            // registers the current call.
            // See the CounterAspect.BeforeCall method for details.
            //
            _interceptor.Intercept(info);

            if (info.InterceptResult != InterceptResult.Return)
            {
                // Target method call.
                //
                base.TestMethod();
            }
        }
        catch (Exception exception)
        {
            info.Exception       = exception;
            info.InterceptResult = InterceptResult.Continue;
            info.InterceptType   = InterceptType.OnCatch;

            // 'OnCatch' is required to count calls with exceptions.
            //
            _interceptor.Intercept(info);

            if (info.InterceptResult != InterceptResult.Return)
            {
                throw;
            }
        }
        finally
        {
            info.InterceptResult = InterceptResult.Continue;
            info.InterceptType   = InterceptType.OnFinally;

            // 'OnFinally' step adds statistic to the method counter.
            // See the CounterAspect.OnFinally method for details.
            //
            _interceptor.Intercept(info);
        }
    }
}

The following picture shows the information collected for data accessors of the BLToolkit.Demo.Asp.Net project.


Logging aspect

This aspect allows logging some diagnostic information with minimum efforts. All you need to do is to decorate your class with the Log attribute. If you have a class hierarchy you can decorate only your base class. Diagnostic information will be logged for all virtual and abstract members of your abstract class.

LoggingAspect.cs
using System;
using System.Threading;

using NUnit.Framework;

using BLToolkit.Aspects;
using BLToolkit.Reflection;

namespace HowTo.Aspects
{
    [TestFixture]
    public class LoggingAspectTest
    {
        [Log]
        public abstract class TestClass
        {
            // Here we customize the logging settings.
            // This call will be logged in spite of default settings.
            //
            [Log(MinCallTime=50)]
            public virtual void Test1(int i)
            {
                Thread.Sleep(100);
            }

            // This call is not going to be logged (see default settings below).
            //
            public virtual void Test2(DateTime dt)
            {
                Thread.Sleep(100);
            }

            // By default exception calls are logged.
            //
            public virtual void Test3(string s)
            {
                throw new ApplicationException("Test exception.");
            }
        }

        [Test]
        public void Test()
        {
            // By setting MinCallTime to some value, we prevent logging any call
            // which is shorter than the provided value.
            //
            LoggingAspect.MinCallTime = 1000;

            TestClass t = TypeAccessor<TestClass>.CreateInstance();

            t.Test1(1);
            t.Test2(DateTime.Now);

            try
            {
                t.Test3("3");
            }
            catch
            {
            }
        }
    }
}

Here is the logging output.

4/20/2008 11:19:44 PM: HowTo.Aspects.LoggingAspectTest.BLToolkitExtension.TestClass.Test1(1) - 105 ms.

4/20/2008 11:19:46 PM: HowTo.Aspects.LoggingAspectTest.BLToolkitExtension.TestClass.Test3("3") - 1507 ms
                       with exception 'System.ApplicationException' - "Test exception.".


Mixin aspect

A mixin is a... well, Wiki describes it much better.

MixinAspect.cs
using System;

using NUnit.Framework;

using BLToolkit.Aspects;
using BLToolkit.Reflection;

namespace HowTo.Aspects
{
    [TestFixture]
    public class MixinAspectTest
    {
        public interface ITestInterface1
        {
            int TestMethod(int value);
        }

        public class TestInterface1Impl : ITestInterface1
        {
            public int TestMethod(int value) { return value; }
        }

        public interface ITestInterface2
        {
            int TestMethod1(int value);
            int TestMethod2(int value);
        }

        public class TestInterface2Impl : ITestInterface2
        {
            public int TestMethod1(int value) { return value; }
            public int TestMethod2(int value) { return value; }
        }

        [Mixin(typeof(ITestInterface1), "_testInterface1")]
        [Mixin(typeof(ITestInterface2), "TestInterface2", "'{0}.{1}' is null.")]
        public abstract class TestClass
        {
            public TestClass()
            {
                _testInterface1 = new TestInterface1Impl();
            }

            protected object          _testInterface1;

            private   ITestInterface2 _testInterface2;
            public    ITestInterface2  TestInterface2
            {
                get { return _testInterface2 ?? (_testInterface2 = new TestInterface2Impl()); }
            }

            [MixinOverride(typeof(ITestInterface2))]
            protected int TestMethod1(int value) { return 15; }
        }

        [Test]
        public void Test()
        {
            TestClass       tc = TypeAccessor<TestClass>.CreateInstance();
            ITestInterface1 i1 = (ITestInterface1)tc;
            ITestInterface2 i2 = (ITestInterface2)tc;

            Assert.AreEqual(10, i1.TestMethod (10));
            Assert.AreEqual(15, i2.TestMethod1(20));
            Assert.AreEqual(30, i2.TestMethod2(30));
        }
    }
}
BLToolkit type builder will generate the following for the class above:
[BLToolkitGenerated]
public sealed class TestClass : MixinAspectTest.TestClass, MixinAspectTest.ITestInterface1, MixinAspectTest.ITestInterface2
{
    int MixinAspectTest.ITestInterface1.TestMethod(int value)
    {
        if (base._testInterface1 == null)
            throw new InvalidOperationException("'ITestInterface1._testInterface1' is not initialized.");

        return base._testInterface1.TestMethod(value);
    }

    int MixinAspectTest.ITestInterface2.TestMethod1(int value)
    {
        // The MixinOverride attribute enforces direct method call.
        //
        return base.TestMethod1(value);
    }

    int MixinAspectTest.ITestInterface2.TestMethod2(int value)
    {
        if (base.TestInterface2 == null)
            throw new InvalidOperationException("'ITestInterface2.TestInterface2' is null.");

        return base.TestInterface2.TestMethod2(value);
    }
}


NotNull attribute

This attribute allows checking a virtual or abstract method parameter if it is null at runtime. BLToolkit type builder will override methods with NotNull parameters and emit code for checking null value at the beginning of the method.

NotNull.cs
using System;

using NUnit.Framework;

using BLToolkit.Aspects;
using BLToolkit.Reflection;

namespace HowTo.Aspects
{
    [TestFixture]
    public class NotNullTest
    {
        public abstract class TestObject
        {
            public virtual void Foo1(string str1, [NotNull]              string str2, string str3) {}
            public virtual void Foo2(string str1, [NotNull("Null")]      string str2, string str3) {}
            public virtual void Foo3(string str1, [NotNull("Null: {0}")] string str2, string str3) {}

            public static TestObject CreateInstance() { return TypeAccessor<TestObject>.CreateInstance(); }
        }

        [Test, ExpectedException(typeof(ArgumentNullException))] // Error message is localized by framework.
        public void Test1()
        {
            TestObject o = TestObject.CreateInstance();

            o.Foo1("str1", null, "str3");
        }

        [Test]
        [ExpectedException(typeof(ArgumentNullException), ExpectedMessage="Null")]
        public void Test2()
        {
            TestObject o = TestObject.CreateInstance();

            o.Foo2("str1", null, "str3");
        }

        [Test]
        [ExpectedException(typeof(ArgumentNullException), ExpectedMessage="Null: str2")]
        public void Test3()
        {
            TestObject o = TestObject.CreateInstance();

            o.Foo3("str1", null, "str3");
        }
    }
}
BLToolkit type builder will generate the following for the class above:
[BLToolkitGenerated]
public sealed class TestObject : NotNullTest.TestObject
{
    public override void Foo1(string str1, string str2, string str3)
    {
        if (str2 == null) throw new ArgumentNullException("str2");

        base.Foo1(str1, str2, str3);
    }

    public override void Foo2(string str1, string str2, string str3)
    {
        if (str2 == null) throw new ArgumentNullException("str2", "Null");

        base.Foo2(str1, str2, str3);
    }

    public override void Foo3(string str1, string str2, string str3)
    {
        if (str2 == null) throw new ArgumentNullException("str2", "Null: str2");

        base.Foo3(str1, str2, str3);
    }
}


ComponentModel

• ObjectBinder


ObjectBinder

The ObjectBinder component has been designed to simplify binding controls on a form to objects for FW 1.x. Since Microsoft has released FW 2.0 we can use the BindingSource component to bind controls to objects. However the ObjectBinder still can be useful as it supports a few features which are not available for the BindingSource. Those features are:

  • Support for field binding along with property binding.

  • Support for inner class field and property binding such as Order.Address.Line1.

  • Support for the ObjectView feature which is available by assigning an object view type to the ObjectBinder.ObjectViewType property. An object view is an object that implements the IObjectView interface. This interface includes only one property - object Object { get; set; }. An object view can implement additional properties based on the assosiated object. The ObjectBinder will combine all of these properties with main object properties and create a single PropertyDescriptor collection. This feature can be used to separate UI presentation logic from business model objects and to keep them clean. ObjectView should be a stateless, lightweight object as its single instance can be assigned to many assosiated objects.

  • The ObjectBinder is optimized for high performance applications such real-time multithreaded message processing and distribution banking systems. So it does not use reflection to access objects. The standard way (which is used by the BindingSource) is to call the TypeDescriptor.GetProperties method to get a PropertyDescriptor collection. This method creates property descriptors that access object properties by reflection. The ObjectBinder has its own mechanism to avoid unnessasy reflection and boxing/unboxing operations.

The dev version of BLToolkit contains a demo project (Demo\WinForms) showing the use of the ObjectBinder.


Data

The DbManager class is a high-level, data provider independent wrapper for ADO.NET. It has been designed to simplify working with database and encapsulates most of ADO.NET objects such as Connection, Transaction, Command, and Parameter in one single object.

• Compare DbManager with ADO.NET
How to configure
• Method 1 | using app.config.
• Method 2 | programmatically.
• Method 3 | by passing DataProvider, Connection or Transaction object.
• Data Providers
• SetCommand | Using SQL statements
• SetSpCommand | Using stored procedures
• Parameters
• Prepare
• Transactions
• Close
• ExecuteDataSet
• ExecuteDataTable
• ExecuteDictionary
• ExecuteForEach
• ExecuteList
• ExecuteNonQuery
• ExecuteObject
• ExecuteReader
• ExecuteResultSet | Complex mapping
• ExecuteScalar
• ExecuteScalarDictionary
• ExecuteScalarList


Compare DbManager with ADO.NET

This example contains two demos to demonstrate the difference between ADO.NET and BLToolkit. The AdoDemo represents a typical data access method which takes one parameter and returns a list of objects. All routine mapping work is done manually inside the method.

AdoDemo.cs
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;

using NUnit.Framework;

namespace HowTo.Data
{
    [TestFixture]
    public class AdoDemo
    {
        // Typified definition of the Gender database field.
        //
        public enum Gender
        {
            Female,
            Male,
            Unknown,
            Other
        }

        // Business object.
        //
        public class Person
        {
            public int    ID         { get; set; }
            public string FirstName  { get; set; }
            public string MiddleName { get; set; }
            public string LastName   { get; set; }
            public Gender Gender     { get; set; }
        }

        // ADO.NET data access method.
        //
        public List<Person> GetList(Gender gender)
        {
            // Map the typified parameter value to its database representation.
            //
            string paramValue = "";

            switch (gender)
            {
                case Gender.Female:  paramValue = "F"; break;
                case Gender.Male:    paramValue = "M"; break;
                case Gender.Unknown: paramValue = "U"; break;
                case Gender.Other:   paramValue = "O"; break;
            }

            // Read a database configuration string.
            //
            string cs = ConfigurationManager.ConnectionStrings["DemoConnection"].ConnectionString;

            // Create and open a database connection.
            //
            using (SqlConnection con = new SqlConnection(cs))
            {
                con.Open();

                // Create and initialize a Command object.
                //
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT * FROM Person WHERE Gender = @gender";
                    cmd.Parameters.AddWithValue("@gender", paramValue);

                    // Execute query.
                    //
                    using (SqlDataReader rd = cmd.ExecuteReader())
                    {
                        List<Person> list = new List<Person>();

                        while (rd.Read())
                        {
                            Person person = new Person();

                            // Map a data reader row to a business object.
                            //
                            person.ID         = Convert.ToInt32 (rd["PersonID"]);
                            person.FirstName  = Convert.ToString(rd["FirstName"]);
                            person.MiddleName = Convert.ToString(rd["MiddleName"]);
                            person.LastName   = Convert.ToString(rd["LastName"]);

                            switch (rd["Gender"].ToString())
                            {
                                case "F": person.Gender = Gender.Female;  break;
                                case "M": person.Gender = Gender.Male;    break;
                                case "U": person.Gender = Gender.Unknown; break;
                                case "O": person.Gender = Gender.Other;   break;
                            }

                            list.Add(person);
                        }

                        return list;
                    }
                }
            }
        }

        [Test]
        public void Test()
        {
            List<Person> list = GetList(Gender.Male);
            Assert.Greater(list.Count, 0);
        }
    }
}

The DbManagerDemo does the same work performed by BLToolkit.DbManager.

DbManagerDemo.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.Mapping;

namespace HowTo.Data
{
    [TestFixture]
    public class DbManagerDemo
    {
        // The MapValue attribute is used by BLToolkit.
        //
        public enum Gender
        {
            [MapValue("F")] Female,
            [MapValue("M")] Male,
            [MapValue("U")] Unknown,
            [MapValue("O")] Other
        }

        // Business object. Here we use C# 3.0 automatic properties,
        // however it can be public fields, regular or abstract properties.
        // The MapField attribute is used by BLToolkit to associate a database field
        // with a business object property if they have different names.
        //
        public class Person
        {
            [MapField("PersonID")]
            public int    ID         { get; set; }
            public string FirstName  { get; set; }
            public string MiddleName { get; set; }
            public string LastName   { get; set; }
            public Gender Gender     { get; set; }
        }

        // BLToolkit data access method.
        //
        public List<Person> GetList(Gender gender)
        {
            using (DbManager db = new DbManager("DemoConnection"))
            {
                return db
                    .SetCommand(
                        "SELECT * FROM Person WHERE Gender = @gender",
                        db.Parameter("@gender", Map.EnumToValue(gender)))
                    .ExecuteList<Person>();
            }
        }

        [Test]
        public void Test()
        {
            List<Person> list = GetList(Gender.Male);
            Assert.Greater(list.Count, 0);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add
            name             = "DemoConnection"
            connectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"
            providerName     = "System.Data.SqlClient" />
    </connectionStrings>
</configuration>
Create.sql script


Configure using app.config

Since Microsoft has released FW 2.0 and the <connectionStrings> section of configuration file is available, it should be used. This configuration method is preferable. The following method demonstrates this ability.

OpenConfig1FW2.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class OpenConfig1FW2
    {
        [Test]
        public void FW2Configuration()
        {
            // <connectionString> section configuration supported in FW 2.0+.
            //
            using (DbManager db = new DbManager("DemoConnection"))
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add
            name             = "DemoConnection"
            connectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"
            providerName     = "System.Data.SqlClient" />
    </connectionStrings>
</configuration>

In addition BLToolkit supports an alternative way which uses the <appSettings> section. At the bottom of this page you can find a demo App.config file with a few examples.

BLToolkit recognizes configuration strings in the <appSettings> section by looking for a ConnectionString key prefix. Actual key value can be the following:
key value | Provider | Configuration
key="ConnectionString" | default provider | default configuration
key="ConnectionString.Foo.Bar" | 'Foo' provider | 'Bar' configuration
key="ConnectionString.Foo." | 'Foo' provider | default configuration
key="ConnectionString.Foo" | default provider | 'Foo' configuration
or | 'Foo' provider | default configuration
key="ConnectionString..Foo" | default provider | 'Foo' configuration
key="ConnectionString..Foo.Bar" | default provider | 'Foo.Bar' configuration
Default provider is SqlDataProvider. See also Data Providers.

OpenConfig1.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class OpenConfig1
    {
        [Test]
        public void DefaultConfiguration()
        {
            // Default configuration and default data provider.
            //
            using (DbManager db = new DbManager())
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }

        [Test]
        public void DevelopmentConfiguration()
        {
            // Development configuration and default data provider.
            //
            using (DbManager db = new DbManager("Development"))
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }

        [Test]
        public void ProductionConfiguration()
        {
            // Production configuration and default data provider.
            //
            using (DbManager db = new DbManager("Production"))
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }

        [Test]
        public void OleDbDefaultConfiguration()
        {
            // Default configuration and OleDb data provider.
            //
            using (DbManager db = new DbManager("OleDb"))
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }

        [Test]
        public void OleDbDevelopmentConfiguration()
        {
            // Development configuration and OleDb data provider.
            //
            using (DbManager db = new DbManager("OleDb", "Development"))
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }

        [Test]
        public void OleDbProductionConfiguration()
        {
            // Production configuration and OleDb data provider.
            //
            using (DbManager db = new DbManager("OleDb", "Production"))
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }
    }
}
App.config
<?xml version="1.0"?>
<configuration>
    <connectionStrings>
        <add
            name             = "DemoConnection"
            connectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"
            providerName     = "System.Data.SqlClient" />
    </connectionStrings>
    <appSettings>
        <!-- Default configuration -->
        <add key="ConnectionString"                   value="Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
        <!-- SQL Server Development configuration -->
        <add key="ConnectionString.Development"       value="Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
        <!-- SQL Server Production configuration -->
        <add key="ConnectionString.Production"        value="Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
        <!-- SQL Server configuration -->
        <add key="ConnectionString.Sql"               value="Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
        <!-- OLEDB configuration -->
        <add key="ConnectionString.OleDb"             value="Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;Initial Catalog=BLToolkitData"/>
        <!-- OLEDB Development configuration -->
        <add key="ConnectionString.OleDb.Development" value="Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;Initial Catalog=BLToolkitData"/>
        <!-- OLEDB Production configuration -->
        <add key="ConnectionString.OleDb.Production"  value="Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;Initial Catalog=BLToolkitData"/>
    </appSettings>
</configuration>

The default configuration can be set by a configuration file:

App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <!-- MySql configuration -->
        <add
            key   = "ConnectionString.MySql"
            value = "..."/>
        <add
            key   = "BLToolkit.DefaultConfiguration"
            value = "MySql"/>
    </appSettings>
</configuration>


Configure programmatically

Connection string can be also assigned programmatically. The DbManager.AddConnectionString method can be used for this. You should do it once when your application starts.

OpenConfig2.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class OpenConfig2
    {
        const string sqlConnectionString =
            "Server=.;Database=BLToolkitData;Integrated Security=SSPI";
        const string oleDbConnectionString =
            "Provider=SQLOLEDB;Data Source=.;Integrated Security=SSPI;Initial Catalog=BLToolkitData";

        [Test]
        public void Test1()
        {
            string defaultConfiguration = DbManager.DefaultConfiguration;
            DbManager.DefaultConfiguration = ""; //to reset possible previous changes

            try
            {
                DbManager.AddConnectionString(
                    sqlConnectionString);   // connection string

                using (DbManager db = new DbManager())
                {
                    Assert.AreEqual(ConnectionState.Open, db.Connection.State);
                }
            }
            finally
            {
                DbManager.DefaultConfiguration = defaultConfiguration; // to restore previous settings
            }

        }

        [Test]
        public void Test2()
        {
            DbManager.AddConnectionString(
                "NewConfig",            // configuration string
                sqlConnectionString);   // connection string

            using (DbManager db = new DbManager("NewConfig"))
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }

        [Test]
        public void Test3()
        {
            DbManager.AddConnectionString(
                "OleDb",                // provider name
                "NewConfig",            // configuration string
                oleDbConnectionString); // connection string

            using (DbManager db = new DbManager("OleDb", "NewConfig"))
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }
    }
}


Configure by passing objects

OpenConfig3.cs
using System;
using System.Data;
using System.Data.SqlClient;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.Data.DataProvider;

namespace HowTo.Data
{
    [TestFixture]
    public class OpenConfig3
    {
        const string connectionString =
            "Server=.;Database=BLToolkitData;Integrated Security=SSPI";

        [Test]
        public void DbConnectionConfiguration()
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                using (DbManager db = new DbManager(con))
                {
                    Assert.AreEqual(ConnectionState.Open, db.Connection.State);
                }
            }
        }

        [Test]
        public void DbTransactionConfiguration()
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                SqlTransaction tran = con.BeginTransaction();

                using (DbManager db = new DbManager(tran))
                {
                    Assert.AreEqual(ConnectionState.Open, db.Connection.State);
                }

                tran.Commit();
            }
        }

        [Test]
        public void DataProviderConfiguration()
        {
            SqlDataProvider dp = new SqlDataProvider();

            using (DbManager db = new DbManager(dp, connectionString))
            {
                Assert.AreEqual(ConnectionState.Open, db.Connection.State);
            }
        }
    }
}


DataProvider

The BLToolkit.DbManager component is a data provider independent wrapper for ADO.NET. It means that the DbManager does not use any specific data source classes such as SqlConnection, SqlCommand, etc. It uses common classes and interfaces instead. All data source specific logic is encapsulated in special classes called DataProviders.

The following table lists providers available with BLToolkit:

Class | Provider Name | Namespace | Vendor
AccessDataProvider | Access | System.Data.OleDb | Microsoft
OdbcDataProvider | Odbc | System.Data.Odbc | Microsoft
OleDbDataProvider | OleDb | System.Data.OleDb | Microsoft
OracleDataProvider | Oracle | System.Data.OracleClient | Microsoft
SqlDataProvider | Sql | System.Data.SqlClient | Microsoft
DB2DataProvider | DB2 | IBM.Data.DB2 | IBM
FdpDataProvider | Fdp | FirebirdSql.Data.FirebirdClient | Firebird
InformixDataProvider | Informix | IBM.Data.Informix | IBM
MySqlDataProvider | MySql | MySql.Data.MySqlClient | Sun Microsystems
OdpDataProvider | ODP | Oracle.DataAccess.Client | Oracle
SqlCeDataProvider | SqlCe | System.Data.SqlServerCe | Microsoft
SQLiteDataProvider | SQLite | System.Data.SQLite | SQLite.org
SybaseAdoDataProvider | SybaseAdo | System.Data.OleDb | Support for DataDirect Sybase ADO Provider
SybaseDataProvider | Sybase | Sybase.Data.AseClient | Sybase

The first five providers are preregistered in the library and are ready to use. The remaining providers need additional configuration as they require references to 3rd party components.

You can add a listed above or your own data provider into your project and register it as shown below:

AddDataProvider.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.Data.DataProvider;

namespace HowTo.Data.DataProvider
{
    [TestFixture]
    public class AddDataProvider
    {
        const string connectionString =
            "Provider=ASEOLEDB;Data Source=server;Catalog=database;User Id=user;Password=pwd;";

        [Test]
        public void Test()
        {
            // 3rd party data provider registration.
            //
            DbManager.AddDataProvider(new SybaseAdoDataProvider());

            // It can be configured by App.config.
            // We use this way for the demo purpose only.
            //
            DbManager.AddConnectionString(
                "SybaseAdo",       // Provider name
                "Default",         // Configuration
                connectionString); // Connection string

            using (DbManager db = new DbManager("SybaseAdo", "Default"))
            {
            }
        }
    }
}

Also a data provider can be registered by configuration file:

App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "BLToolkit.DataProviders"
            value = "BLToolkit.Data.DataProvider.OracleDataProvider, SomeAssembly"/>
    </appSettings>
</configuration>
Also you can use BLToolkit config section.
<?xml version="1.0"?>
<configuration>
    <configSections>
        <section name="bltoolkit" type="BLToolkit.Configuration.BLToolkitSection, BLToolkit.3"/>
    </configSections>
    <bltoolkit>
        <dataProviders>
            <add type="BLToolkit.Data.DataProvider.DB2DataProvider,        UnitTests.Linq" />
            <add type="BLToolkit.Data.DataProvider.FdpDataProvider,        UnitTests.Linq" />
            <add type="BLToolkit.Data.DataProvider.InformixDataProvider,   UnitTests.Linq" />
            <add type="BLToolkit.Data.DataProvider.MySqlDataProvider,      UnitTests.Linq" />
            <add type="BLToolkit.Data.DataProvider.OdpDataProvider,        UnitTests.Linq" />
            <add type="BLToolkit.Data.DataProvider.PostgreSQLDataProvider, UnitTests.Linq" />
            <add type="BLToolkit.Data.DataProvider.SqlCeDataProvider,      UnitTests.Linq" />
            <add type="BLToolkit.Data.DataProvider.SQLiteDataProvider,     UnitTests.Linq" />
            <add type="BLToolkit.Data.DataProvider.SybaseDataProvider,     UnitTests.Linq" />
        </dataProviders>
    </bltoolkit>
    <connectionStrings>
        <add name="Access"     connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\..\..\Data\BLToolkitData.mdb;Locale Identifier=1033;Jet OLEDB:Engine Type=5;Persist Security Info=True"/>
        <add name="DB2"        connectionString="Server=DBHost:50000;Database=BLTD;UID=TestUser;PWD=TestPassword;" providerName="IBM.Data.DB2"/>
        <add name="Fdp"        connectionString="DataSource=DBHost;Database=C:\Data\BLToolkitData.fb2;User Id=SYSDBA;Password=masterkey"/>
        <add name="Informix"   connectionString="Host=DBHost;Service=9090;Server=ol_ids_1150_1;Protocol=onsoctcp;Database=BLToolkitData;UID=sysmaster;PWD=TestPassword;" providerName="IBM.Data.Informix"/>
        <add name="MySql"      connectionString="Server=DBHost;Port=3306;Database=bltoolkitdata;Uid=root;Pwd=TestPassword;" providerName="MySql.Data.MySqlClient"/>
        <add name="Oracle"     connectionString="..." providerName="Oracle.DataAccess.Client"/>
        <add name="PostgreSQL" connectionString="Server=DBHost;Port=5432;Database=BLToolkitData;User Id=postgres;Password=TestPassword;" providerName="Npgsql"/>
        <add name="Sql2008"    connectionString="Server=.;Database=BLToolkitData;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
        <add name="Sql2005"    connectionString="Server=DBHost;Database=BLToolkitData;User Id=TestUser;Password=TestPassword;" providerName="System.Data.SqlClient"/>
        <add name="SqlCe"      connectionString="Data Source=..\..\..\..\Data\BLToolkitData.sdf"/>
        <add name="SQLite"     connectionString="Data Source=..\..\..\..\Data\BLToolkitData.sqlite"/>
        <add name="Sybase"     connectionString="Data Source=DBHost;Port=5000;Database=BLToolkitData;Uid=sa" providerName="Sybase.Data.AseClient"/>
        <add name="Northwind"  connectionString="Data Source=.;Database=Northwind;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>
Here UnitTests.Linq assembly name should be replaces by your own.


SetCommand

Typical scenario of using DbManager includes the following steps:

  • Create a DbManager class instance.
  • Set an SQL command or a stored procedure name.
  • Call an ExecuteXXX method.
The SetCommand method is used to set an SQL statement and to provide command parameters, if any.

SetCommand.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.Mapping;

namespace HowTo.Data
{
    using DataAccess;

    [TestFixture]
    public class SetCommand
    {
        // Select a person list.
        //
        public IList<Person> GetPersonList()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT * FROM Person")
                    .ExecuteList<Person>();
            }
        }

        [Test]
        public void Test1()
        {
            IList<Person> list = GetPersonList();

            Assert.AreNotEqual(0, list.Count);
        }

        // Select a person.
        //
        public Person GetPersonByID(int id)
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT * FROM Person WHERE PersonID = @id",
                        db.Parameter("@id", id))
                    .ExecuteObject<Person>();
            }
        }

        [Test]
        public void Test2()
        {
            Person person = GetPersonByID(1);

            Assert.IsNotNull(person);
        }

        // Insert, Update, and Delete a person.
        //
        public Person GetPersonByID(DbManager db, int id)
        {
            return db
                .SetCommand("SELECT * FROM Person WHERE PersonID = @id",
                    db.Parameter("@id", id))
                .ExecuteObject<Person>();
        }

        public Person CreatePerson(DbManager db)
        {
            int id = db
                .SetCommand(@"
                    INSERT INTO Person ( LastName,  FirstName,  Gender)
                    VALUES             (@LastName, @FirstName, @Gender)

                    SELECT Cast(SCOPE_IDENTITY() as int) PersonID",
                    db.Parameter("@LastName",  "Frog"),
                    db.Parameter("@FirstName", "Crazy"),
                    db.Parameter("@Gender",    Map.EnumToValue(Gender.Male)))
                .ExecuteScalar<int>();

            return GetPersonByID(db, id);
        }

        public Person UpdatePerson(DbManager db, Person person)
        {
            db
                .SetCommand(@"
                    UPDATE
                        Person
                    SET
                        LastName   = @LastName,
                        FirstName  = @FirstName,
                        Gender     = @Gender
                    WHERE
                        PersonID = @PersonID",
                    db.CreateParameters(person))
                .ExecuteNonQuery();

            return GetPersonByID(db, person.ID);
        }

        public Person DeletePerson(DbManager db, Person person)
        {
            db
                .SetCommand("DELETE FROM Person WHERE PersonID = @id",
                    db.Parameter("@id", person.ID))
                .ExecuteNonQuery();

            return GetPersonByID(db, person.ID);
        }

        [Test]
        public void Test3()
        {
            using (DbManager db = new DbManager())
            {
                db.BeginTransaction();

                // Insert.
                //
                Person person = CreatePerson(db);

                Assert.IsNotNull(person);

                // Update.
                //
                Assert.AreEqual(Gender.Male, person.Gender);

                person.Gender = Gender.Female;

                person = UpdatePerson(db, person);

                Assert.AreEqual(Gender.Female, person.Gender);

                // Delete.
                //
                person = DeletePerson(db, person);

                Assert.IsNull(person);

                db.CommitTransaction();
            }
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


SetSpCommand

Typical scenario of using DbManager includes the following steps:

  • Create a DbManager class instance.
  • Set an SQL command or a stored procedure name.
  • Call an ExecuteXXX method.
The SetSpCommand method is used to set a stored procedure name and to provide command parameters, if any.

SetSpCommand.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.Mapping;

namespace HowTo.Data
{
    using DataAccess;

    [TestFixture]
    public class SetSpCommand
    {
        // Select a person list.
        //
        public IList<Person> GetPersonList()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetSpCommand("Person_SelectAll")
                    .ExecuteList<Person>();
            }
        }

        [Test]
        public void Test1()
        {
            IList<Person> list = GetPersonList();

            Assert.AreNotEqual(0, list.Count);
        }

        // Select a person.
        //
        public Person GetPersonByID1(int id)
        {
            using (DbManager db = new DbManager())
            {
                // Pass a parameter using the Parameter method.
                //
                return db
                    .SetSpCommand("Person_SelectByKey",
                        db.Parameter("@id", id))
                    .ExecuteObject<Person>();
            }
        }

        public Person GetPersonByID2(int id)
        {
            using (DbManager db = new DbManager())
            {
                // Pass a parameter using the params parameter of the SetSpCommand method.
                //
                return db
                    .SetSpCommand("Person_SelectByKey", id)
                    .ExecuteObject<Person>();
            }
        }

        [Test]
        public void Test2()
        {
            Person person = GetPersonByID1(1);
            Assert.IsNotNull(person);

            person = GetPersonByID2(1);
            Assert.IsNotNull(person);
        }

        // Insert, Update, and Delete a person.
        //
        public Person GetPersonByID(DbManager db, int id)
        {
            return db
                .SetSpCommand("Person_SelectByKey", id)
                .ExecuteObject<Person>();
        }

        public Person CreatePerson(DbManager db)
        {
            int id = db
                .SetSpCommand("Person_Insert",
                    db.Parameter("@LastName",   "Frog"),
                    db.Parameter("@MiddleName", null),
                    db.Parameter("@FirstName",  "Crazy"),
                    db.Parameter("@Gender",     Map.EnumToValue(Gender.Male)))
                .ExecuteScalar<int>();

            return GetPersonByID(db, id);
        }

        public Person UpdatePerson(DbManager db, Person person)
        {
            db
                .SetSpCommand("Person_Update", db.CreateParameters(person))
                .ExecuteNonQuery();

            return GetPersonByID(db, person.ID);
        }

        public Person DeletePerson(DbManager db, Person person)
        {
            db
                .SetSpCommand("Person_Delete", person.ID)
                .ExecuteNonQuery();

            return GetPersonByID(db, person.ID);
        }

        [Test]
        public void Test3()
        {
            using (DbManager db = new DbManager())
            {
                db.BeginTransaction();

                // Insert.
                //
                Person person = CreatePerson(db);

                Assert.IsNotNull(person);

                // Update.
                //
                Assert.AreEqual(Gender.Male, person.Gender);

                person.Gender = Gender.Female;

                person = UpdatePerson(db, person);

                Assert.AreEqual(Gender.Female, person.Gender);

                // Delete.
                //
                person = DeletePerson(db, person);

                Assert.IsNull(person);

                db.CommitTransaction();
            }
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Parameter

The DbManager.Parameter method allows assigning parameters to an SQL query, setting parameter values before query, and getting return/out values after.

Parameter.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    using DataAccess;

    [TestFixture]
    public class Parameter
    {
        [Test]
        public void AssignParameterTest()
        {
            using (DbManager db = new DbManager())
            {
                int n = db
                    .SetCommand("SELECT @par1 + @par2",
                        db.Parameter("@par1", 2),
                        db.Parameter("@par2", 2))
                    .ExecuteScalar<int>();

                Assert.AreEqual(4, n);
            }
        }

        [Test]
        public void SetValueTest()
        {
            using (DbManager db = new DbManager())
            {
                db.SetCommand("SELECT @par * 2",
                    db.Parameter("@par", DbType.Int32));

                db.Parameter("@par").Value = 2;

                Assert.AreEqual(4, db.ExecuteScalar<int>());
            }
        }

        [Test]
        public void ReturnValueTest()
        {
            using (DbManager db = new DbManager())
            {
                /*
                 * CREATE Function Scalar_ReturnParameter()
                 * RETURNS int
                 * AS
                 * BEGIN
                 *     RETURN 12345
                 * END
                 */
                db
                    .SetSpCommand("Scalar_ReturnParameter")
                    .ExecuteNonQuery();

                int n = (int)db.Parameter("@RETURN_VALUE").Value;

                Assert.AreEqual(12345, n);
            }
        }

        [Test]
        public void ReturnValueTest2()
        {
            using (DbManager db = new DbManager())
            {
                int n = db
                    .SetSpCommand("Scalar_ReturnParameter")
                    .ExecuteScalar<int>(ScalarSourceType.ReturnValue);

                Assert.AreEqual(12345, n);
            }
        }

        [Test]
        public void OutputParameterTest()
        {
            using (DbManager db = new DbManager())
            {
                /*
                 * CREATE Procedure Scalar_OutputParameter
                 *     @outputInt    int         = 0  output,
                 *     @outputString varchar(50) = '' output
                 * AS
                 * BEGIN
                 *     SET @outputInt = 12345
                 *     SET @outputString = '54321'
                 * END
                 */

                db
                    .SetSpCommand("Scalar_OutputParameter",
                        db.OutputParameter("@outputInt",    1),
                        db.OutputParameter("@outputString", "1"))
                    .ExecuteNonQuery();

                Assert.AreEqual(12345,   (int)   db.Parameter("@outputInt").   Value);
                Assert.AreEqual("54321", (string)db.Parameter("@outputString").Value);
            }
        }

        [Test]
        public void OutputParameterAsReturnValueTest()
        {
            using (DbManager db = new DbManager())
            {
                string returnValue = db
                    .SetSpCommand("Scalar_OutputParameter")
                    .ExecuteScalar<string>(ScalarSourceType.OutputParameter, "outputString");

                Assert.AreEqual("54321", returnValue);
            }
        }

        [Test]
        public void CreateParametersTest()
        {
            Person person = new Person();

            person.FirstName = "John";
            person.LastName  = "Smith";
            person.Gender    = Gender.Male;

            using (DbManager db = new DbManager())
            {
                db.BeginTransaction();

                // Prepare command.
                //
                int id = db
                    .SetSpCommand("Person_Insert",
                        db.CreateParameters(person))
                    .ExecuteScalar<int>();

                // Check the result.
                //
                person = db
                    .SetCommand(
                        "SELECT * FROM Person WHERE PersonID = @id",
                        db.Parameter("@id", id))
                    .ExecuteObject<Person>();

                Assert.IsNotNull(person);

                // Cleanup.
                //
                db
                    .SetCommand(
                        "DELETE FROM Person WHERE PersonID = @id",
                        db.Parameter("@id", id))
                    .ExecuteNonQuery();

                db.CommitTransaction();
            }
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Prepare

The Prepare method can be useful if you need to execute the same command multiple times.

Prepare.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    using DataAccess;

    [TestFixture]
    public class Prepare
    {
        [Test]
        public void Test()
        {
            List<Person> list = new List<Person>
            {
                new Person { FirstName = "John", LastName = "Smith", Gender = Gender.Male   },
                new Person { FirstName = "Jane", LastName = "Smith", Gender = Gender.Female }
            };

            using (DbManager db = new DbManager())
            {
                db.BeginTransaction();

                // Prepare command.
                //
                db
                    .SetSpCommand("Person_Insert",
                        db.CreateParameters(list[0]))
                    .Prepare();

                // Insert.
                //
                foreach (Person person in list)
                {
                    db.AssignParameterValues(person);
                    db.ExecuteNonQuery();
                }

                // Check the result.
                //
                list = db
                    .SetCommand(
                        "SELECT * FROM Person WHERE LastName = @lastName",
                        db.Parameter("@lastName", "Smith"))
                    .ExecuteList<Person>();

                Assert.GreaterOrEqual(2, list.Count);

                // Cleanup.
                //
                db
                    .SetCommand(
                        "DELETE FROM Person WHERE LastName = @lastName",
                        db.Parameter("@lastName", "Smith"))
                    .ExecuteNonQuery();

                db.CommitTransaction();
            }
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Transaction

Transaction.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class Transaction
    {
        [Test]
        public void Test1()
        {
            using (DbManager db = new DbManager())
            {
                db.BeginTransaction();

                // ...

                db.CommitTransaction();
            }
        }

        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                db.BeginTransaction(IsolationLevel.ReadCommitted);

                // ...

                db.CommitTransaction();
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Close

Close.cs
using System;
using NUnit.Framework;
using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class Close
    {
        [Test]
        public void Test1()
        {
            DbManager db = new DbManager();

            try
            {
                // ...
            }
            finally
            {
                if (db != null)
                    db.Close();
            }
        }

        // Consider using the C# using statement instead.
        //
        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                // ...
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteDataSet

ExecuteDataSet.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class ExecuteDataSet
    {
        [Test]
        public void Test()
        {
            using (DbManager db = new DbManager())
            {
                DataSet ds = db
                    .SetCommand("SELECT * FROM Person")
                    .ExecuteDataSet();

                Assert.AreNotEqual(0, ds.Tables[0].Rows.Count);
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteDataTable

ExecuteDataTable.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class ExecuteDataTable
    {
        [Test]
        public void Test()
        {
            using (DbManager db = new DbManager())
            {
                DataTable dt = db
                    .SetCommand("SELECT * FROM Person")
                    .ExecuteDataTable();

                Assert.AreNotEqual(0, dt.Rows.Count);
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteDictionary

ExecuteDictionary.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Common;
using BLToolkit.Data;
using BLToolkit.Mapping;

namespace HowTo.Data
{
    using DataAccess;

    [TestFixture]
    public class ExecuteDictionary
    {
        // The dictionary key is built from an object field/property.
        //
        Dictionary<int, Person> GetPersonDictionary1()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT * FROM Person")
                    .ExecuteDictionary<int, Person>("ID");
            }
        }

        [Test]
        public void Test1()
        {
            Dictionary<int, Person> dic = GetPersonDictionary1();

            Assert.AreNotEqual(0, dic.Count);
        }

        // The dictionary key is built from a recordset field value ('@' prefix).
        //
        Dictionary<int, Person> GetPersonDictionary2()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT * FROM Person")
                    .ExecuteDictionary<int, Person>("@PersonID");
            }
        }

        [Test]
        public void Test2()
        {
            Dictionary<int, Person> dic = GetPersonDictionary2();

            Assert.AreNotEqual(0, dic.Count);
        }

        // Complex dictionary key.
        //
        Dictionary<CompoundValue, Person> GetPersonDictionary3()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT * FROM Person")
                    .ExecuteDictionary<Person>(new MapIndex("FirstName", "LastName"));
            }
        }

        [Test]
        public void Test3()
        {
            Dictionary<CompoundValue, Person> dic = GetPersonDictionary3();

            Assert.AreNotEqual(0, dic.Count);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteForEach

The ExecuteForEach method executes an SQL statement for every item in the provided collection and returns the number of rows affected.

ExecuteForEach.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    using DataAccess;

    [TestFixture]
    public class ExecuteForEach
    {
        [Test]
        public void Test()
        {
            List<Person> list = new List<Person>
            {
                new Person { FirstName = "John", LastName = "Smith", Gender = Gender.Male   },
                new Person { FirstName = "Jane", LastName = "Smith", Gender = Gender.Female }
            };

            using (DbManager db = new DbManager())
            {
                db.BeginTransaction();

                // Execute.
                //
                db
                    .SetSpCommand("Person_Insert")
                    .ExecuteForEach<Person>(list);

                // Check the result.
                //
                list = db
                    .SetCommand(
                        "SELECT * FROM Person WHERE LastName = @lastName",
                        db.Parameter("@lastName", "Smith"))
                    .ExecuteList<Person>();

                Assert.GreaterOrEqual(2, list.Count);

                // Cleanup.
                //
                db
                    .SetCommand(
                        "DELETE FROM Person WHERE LastName = @lastName",
                        db.Parameter("@lastName", "Smith"))
                    .ExecuteNonQuery();

                db.CommitTransaction();
            }
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteList

ExecuteList.cs
using System;
using System.Collections;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.Mapping;
using BLToolkit.Reflection;

namespace HowTo.Data
{
    [TestFixture]
    public class ExecuteList
    {
        [MapValue(Gender.Female,  "F")]
        [MapValue(Gender.Male,    "M")]
        [MapValue(Gender.Unknown, "U")]
        [MapValue(Gender.Other,   "O")]
        public enum Gender
        {
            Female,
            Male,
            Unknown,
            Other
        }

        [MapField("PersonID", "ID")]
        public class Person
        {
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;
            public Gender Gender;
        }

        IList<Person> GetPersonListSqlText()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT * FROM Person")
                    .ExecuteList<Person>();
            }
        }

        [Test]
        public void SqlText()
        {
            IList<Person> list = GetPersonListSqlText();

            foreach (Person p in list)
                TypeAccessor.WriteDebug(p);
        }

        IList<Person> GetPersonListSproc()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetSpCommand("Person_SelectAll")
                    .ExecuteList<Person>();
            }
        }

        [Test]
        public void Sproc()
        {
            IList<Person> list = GetPersonListSproc();

            foreach (Person p in list)
                TypeAccessor.WriteDebug(p);
        }

        void GetCustomPersonList(IList list)
        {
            using (DbManager db = new DbManager())
            {
                db
                    .SetSpCommand("Person_SelectAll")
                    .ExecuteList(list, typeof(Person));
            }
        }

        [Test]
        public void CustomList()
        {
            ArrayList list = new ArrayList(10);

            GetCustomPersonList(list);

            foreach (Person p in list)
                TypeAccessor.WriteDebug(p);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteNonQuery

The ExecuteNonQuery executes an SQL statement and returns the number of rows affected.

ExecuteNonQuery.cs
using System;
using NUnit.Framework;
using BLToolkit.Data;

namespace HowTo.Data
{
    using DataAccess;

    [TestFixture]
    public class ExecuteNonQuery
    {
        [Test]
        public void Test()
        {
            Person person = new Person();

            person.FirstName = "John";
            person.LastName  = "Smith";
            person.Gender    = Gender.Male;

            using (DbManager db = new DbManager())
            {
                db.BeginTransaction();

                // Execute.
                //
                db
                    .SetSpCommand("Person_Insert", db.CreateParameters(person))
                    .ExecuteNonQuery();

                // Check the result.
                //
                person = db
                    .SetCommand(
                        "SELECT * FROM Person WHERE LastName = @lastName",
                        db.Parameter("@lastName", "Smith"))
                    .ExecuteObject<Person>();

                Assert.IsNotNull(person);

                // Cleanup.
                //
                db
                    .SetCommand(
                        "DELETE FROM Person WHERE LastName = @lastName",
                        db.Parameter("@lastName", "Smith"))
                    .ExecuteNonQuery();

                db.CommitTransaction();
            }
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteObject

ExecuteObject.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.Mapping;
using BLToolkit.Reflection;

namespace HowTo.Data
{
    [TestFixture]
    public class ExecuteObject
    {
        public enum Gender
        {
            [MapValue("F")] Female,
            [MapValue("M")] Male,
            [MapValue("U")] Unknown,
            [MapValue("O")] Other
        }

        public abstract class Person
        {
            [MapField("PersonID")]
            public abstract int    ID         { get; }

            public abstract string LastName   { get; set; }
            public abstract string FirstName  { get; set; }
            public abstract string MiddleName { get; set; }
            public abstract Gender Gender     { get; set; }
        }

        Person GetPersonSqlText(int id)
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT * FROM Person WHERE PersonID = @id",
                        db.Parameter("@id", id))
                    .ExecuteObject<Person>();
            }
        }

        [Test]
        public void SqlText()
        {
            Person person = GetPersonSqlText(1);

            TypeAccessor.WriteConsole(person);
        }

        Person GetPersonSproc1(int id)
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetSpCommand("Person_SelectByKey",
                        db.Parameter("@id", id))
                    .ExecuteObject<Person>();
            }
        }

        [Test]
        public void Sproc1()
        {
            Person person = GetPersonSproc1(1);

            TypeAccessor.WriteConsole(person);
        }

        Person GetPersonSproc2(int id)
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetSpCommand("Person_SelectByKey", id)
                    .ExecuteObject<Person>();
            }
        }

        [Test]
        public void Sproc2()
        {
            Person person = GetPersonSproc2(1);

            TypeAccessor.WriteConsole(person);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteReader

ExecuteReader.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class ExecuteReader
    {
        [Test]
        public void Test()
        {
            using (DbManager db = new DbManager())
            {
                db.SetCommand("SELECT * FROM Person");

                using (IDataReader rd = db.ExecuteReader())
                {
                    while (rd.Read())
                    {
                        // ...
                    }
                }
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteResultSet

ComplexMapping.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Mapping;
using BLToolkit.Data;
using BLToolkit.DataAccess;
using BLToolkit.Reflection.Extension;

namespace HowTo.Data
{
    [TestFixture]
    public class ComplexMapping
    {
        const string TestQuery = @"
            -- Parent Data
            SELECT       1 as ParentID
            UNION SELECT 2 as ParentID

            -- Child Data
            SELECT       4 ChildID, 1 as ParentID
            UNION SELECT 5 ChildID, 2 as ParentID
            UNION SELECT 6 ChildID, 2 as ParentID
            UNION SELECT 7 ChildID, 1 as ParentID

            -- Grandchild Data
            SELECT       1 GrandchildID, 4 as ChildID
            UNION SELECT 2 GrandchildID, 4 as ChildID
            UNION SELECT 3 GrandchildID, 5 as ChildID
            UNION SELECT 4 GrandchildID, 5 as ChildID
            UNION SELECT 5 GrandchildID, 6 as ChildID
            UNION SELECT 6 GrandchildID, 6 as ChildID
            UNION SELECT 7 GrandchildID, 7 as ChildID
            UNION SELECT 8 GrandchildID, 7 as ChildID
";

        public class Parent
        {
            [MapField("ParentID"), PrimaryKey]
            public int ID;

            [Relation(typeof(Child))]
            public List<Child> Children = new List<Child>();
        }

        [MapField("ParentID", "Parent.ID")]
        public class Child
        {
            [MapField("ChildID"), PrimaryKey]
            public int ID;

            [Relation]
            public Parent Parent = new Parent();

            [Relation(typeof(Grandchild))]
            public List<Grandchild> Grandchildren = new List<Grandchild>();
        }

        [MapField("ChildID", "Child.ID")]
        public class Grandchild
        {
            [MapField("GrandchildID"), PrimaryKey]
            public int ID;

            [Relation]
            public Child Child = new Child();
        }

        [Test]
        public void Test()
        {
            List<Parent>   parents = new List<Parent>();
            MapResultSet[] sets    = new MapResultSet[3];

            sets[0] = new MapResultSet(typeof(Parent), parents);
            sets[1] = new MapResultSet(typeof(Child));
            sets[2] = new MapResultSet(typeof(Grandchild));

            sets[0].AddRelation(sets[1], "ParentID", "ParentID", "Children");
            sets[1].AddRelation(sets[0], "ParentID", "ParentID", "Parent");

            sets[1].AddRelation(sets[2], "ChildID", "ChildID", "Grandchildren");
            sets[2].AddRelation(sets[1], "ChildID", "ChildID", "Child");

            using (DbManager db = new DbManager())
            {
                db
                    .SetCommand      (TestQuery)
                    .ExecuteResultSet(sets);
            }

            Assert.IsNotEmpty(parents);

            foreach (Parent parent in parents)
            {
                Assert.IsNotNull(parent);
                Assert.IsNotEmpty(parent.Children);

                foreach (Child child in parent.Children)
                {
                    Assert.AreEqual(parent, child.Parent);
                    Assert.IsNotEmpty(child.Grandchildren);

                    foreach (Grandchild grandchild in child.Grandchildren)
                    {
                        Assert.AreEqual(child,  grandchild.Child);
                        Assert.AreEqual(parent, grandchild.Child.Parent);
                    }
                }
            }
        }

        [Test]
        public void Test2()
        {
            List<Parent> parents = new List<Parent>();
            MapResultSet[] sets = new MapResultSet[3];

            sets[0] = new MapResultSet(typeof(Parent), parents);
            sets[1] = new MapResultSet(typeof(Child));
            sets[2] = new MapResultSet(typeof(Grandchild));

            using (DbManager db = new DbManager())
            {
                db
                    .SetCommand(TestQuery)
                    .ExecuteResultSet(sets);
            }

            Assert.IsNotEmpty(parents);

            foreach (Parent parent in parents)
            {
                Assert.IsNotNull(parent);
                Assert.IsNotEmpty(parent.Children);

                foreach (Child child in parent.Children)
                {
                    Assert.AreEqual(parent, child.Parent);
                    Assert.IsNotEmpty(child.Grandchildren);

                    foreach (Grandchild grandchild in child.Grandchildren)
                    {
                        Assert.AreEqual(child, grandchild.Child);
                        Assert.AreEqual(parent, grandchild.Child.Parent);
                    }
                }
            }
        }

        public class ParentEx
        {
            public int           ID;
            public List<ChildEx> Children = new List<ChildEx>();
        }

        public class ChildEx
        {
            public int                ID;
            public ParentEx           Parent        = new ParentEx();
            public List<GrandchildEx> Grandchildren = new List<GrandchildEx>();
        }

        public class GrandchildEx
        {
            public int     ID;
            public ChildEx Child = new ChildEx();
        }

        static readonly MappingSchema _mappingSchema = new MappingSchema
        {
            Extensions = TypeExtension.GetExtensions("RelationExtension.xml")
        };

        [Test]
        public void Test3()
        {
            List<ParentEx> parents = new List<ParentEx>();
            MapResultSet[] sets = new MapResultSet[3];

            sets[0] = new MapResultSet(typeof(ParentEx), parents);
            sets[1] = new MapResultSet(typeof(ChildEx));
            sets[2] = new MapResultSet(typeof(GrandchildEx));

            using (DbManager db = new DbManager())
            {
                db.MappingSchema = _mappingSchema;

                db
                    .SetCommand(TestQuery)
                    .ExecuteResultSet(sets);
            }

            Assert.IsNotEmpty(parents);

            foreach (ParentEx parent in parents)
            {
                Assert.IsNotNull(parent);
                Assert.IsNotEmpty(parent.Children);

                foreach (ChildEx child in parent.Children)
                {
                    Assert.AreEqual(parent, child.Parent);
                    Assert.IsNotEmpty(child.Grandchildren);

                    foreach (GrandchildEx grandchild in child.Grandchildren)
                    {
                        Assert.AreEqual(child, grandchild.Child);
                        Assert.AreEqual(parent, grandchild.Child.Parent);
                    }
                }
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteScalar

ExecuteScalar.cs
using System;
using NUnit.Framework;
using BLToolkit.Data;

namespace HowTo.Data
{
    [TestFixture]
    public class ExecuteScalar
    {
        string GetFirstName(int id)
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT FirstName FROM Person WHERE PersonID = @id",
                        db.Parameter("@id", id))
                    .ExecuteScalar<string>();
            }
        }

        [Test]
        public void ReaderTest()
        {
            string firstName = GetFirstName(1);

            Assert.IsNotNull(firstName);
        }

        [Test]
        public void ReturnValueTest()
        {
            using (DbManager db = new DbManager())
            {
                /*
                 * CREATE Function Scalar_ReturnParameter()
                 * RETURNS int
                 * AS
                 * BEGIN
                 *     RETURN 12345
                 * END
                 */
                int n = db
                    .SetSpCommand("Scalar_ReturnParameter")
                    .ExecuteScalar<int>(ScalarSourceType.ReturnValue);

                Assert.AreEqual(12345, n);
            }
        }

        [Test]
        public void OutputParameterAsReturnValueTest()
        {
            using (DbManager db = new DbManager())
            {
                /*
                 * CREATE Procedure Scalar_OutputParameter
                 *     @outputInt    int         = 0  output,
                 *     @outputString varchar(50) = '' output
                 * AS
                 * BEGIN
                 *     SET @outputInt = 12345
                 *     SET @outputString = '54321'
                 * END
                 */
                string returnValue = db
                    .SetSpCommand("Scalar_OutputParameter")
                    .ExecuteScalar<string>(ScalarSourceType.OutputParameter, "outputString");

                Assert.AreEqual("54321", returnValue);
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteScalarDictionary

The ExecuteScalarDictionary method executes the query, and returns the dictionary. The keys are loaded from a column specified by the keyField parameter and values are loaded from a column specified by the valueField. Other columns are ignored.

ExecuteScalarDictionary.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    using DataAccess;

    [TestFixture]
    public class ExecuteScalarDictionary
    {
        Dictionary<int, string> GetNameDictionary()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT * FROM Person")
                    .ExecuteScalarDictionary<int, string>("PersonID", "FirstName");
            }
        }

        [Test]
        public void Test()
        {
            Dictionary<int, string> dic = GetNameDictionary();

            Assert.AreNotEqual(0, dic.Count);
            Assert.IsNotNull(dic[1]);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteScalarList

The ExecuteScalarList method executes the query and returns a list of values of the specified column of the every row in the resultset returned by the query. Other columns are ignored.

ExecuteScalarList.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;

namespace HowTo.Data
{
    using DataAccess;

    [TestFixture]
    public class ExecuteScalarList
    {
        List<string> GetNameList1()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT FirstName FROM Person")
                    .ExecuteScalarList<string>();
            }
        }

        [Test]
        public void Test1()
        {
            List<string> list = GetNameList1();

            Assert.AreNotEqual(0, list.Count);
            Assert.IsNotNull(list[0]);
        }

        List<string> GetNameList2()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand("SELECT * FROM Person")
                    .ExecuteScalarList<string>("FirstName");
            }
        }

        [Test]
        public void Test2()
        {
            List<string> list = GetNameList2();

            Assert.AreNotEqual(0, list.Count);
            Assert.IsNotNull(list[0]);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


CreateSql

Create.sql
--CREATE DATABASE BLToolkitData ON PRIMARY
--(NAME=N'BLToolkitTest',     FILENAME=N'C:\Data\MSSQL.1\MSSQL\DATA\BLToolkitData.mdf',     SIZE=3072KB, FILEGROWTH=1024KB)
--LOG ON
--(NAME=N'BLToolkitTest_log', FILENAME=N'C:\Data\MSSQL.1\MSSQL\DATA\BLToolkitData_log.ldf', SIZE=1024KB, FILEGROWTH=10%)
--GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Doctor') AND type in (N'U'))
BEGIN DROP TABLE Doctor END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Patient') AND type in (N'U'))
BEGIN DROP TABLE Patient END

-- Person Table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('Person') AND type in (N'U'))
BEGIN DROP TABLE Person END

CREATE TABLE Person
(
    PersonID   int          NOT NULL IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY CLUSTERED,
    FirstName  nvarchar(50) NOT NULL,
    LastName   nvarchar(50) NOT NULL,
    MiddleName nvarchar(50)     NULL,
    Gender     char(1)      NOT NULL CONSTRAINT CK_Person_Gender CHECK (Gender in ('M', 'F', 'U', 'O'))
)
ON [PRIMARY]
GO

INSERT INTO Person (FirstName, LastName, Gender) VALUES ('John',   'Pupkin',    'M')
GO
INSERT INTO Person (FirstName, LastName, Gender) VALUES ('Tester', 'Testerson', 'M')
GO

-- Doctor Table Extension

CREATE TABLE Doctor
(
    PersonID int          NOT NULL
        CONSTRAINT PK_Doctor        PRIMARY KEY CLUSTERED
        CONSTRAINT FK_Doctor_Person FOREIGN KEY
            REFERENCES Person ([PersonID])
            ON UPDATE CASCADE
            ON DELETE CASCADE,
    Taxonomy nvarchar(50) NOT NULL
)
ON [PRIMARY]
GO

INSERT INTO Doctor (PersonID, Taxonomy) VALUES (1, 'Psychiatry')
GO

-- Patient Table Extension

CREATE TABLE Patient
(
    PersonID  int           NOT NULL
        CONSTRAINT PK_Patient        PRIMARY KEY CLUSTERED
        CONSTRAINT FK_Patient_Person FOREIGN KEY
            REFERENCES Person ([PersonID])
            ON UPDATE CASCADE
            ON DELETE CASCADE,
    Diagnosis nvarchar(256) NOT NULL
)
ON [PRIMARY]
GO

INSERT INTO Patient (PersonID, Diagnosis) VALUES (2, 'Hallucination with Paranoid Bugs'' Delirium of Persecution')
GO

-- Person_SelectByKey

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectByKey')
BEGIN DROP Procedure Person_SelectByKey
END
GO

CREATE Procedure Person_SelectByKey
    @id int
AS

SELECT * FROM Person WHERE PersonID = @id

GO

GRANT EXEC ON Person_SelectByKey TO PUBLIC
GO

-- Person_SelectAll

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectAll')
BEGIN DROP Procedure Person_SelectAll END
GO

CREATE Procedure Person_SelectAll
AS

SELECT * FROM Person

GO

GRANT EXEC ON Person_SelectAll TO PUBLIC
GO

-- Person_SelectByName

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectByName')
BEGIN DROP Procedure Person_SelectByName END
GO

CREATE Procedure Person_SelectByName
    @firstName nvarchar(50),
    @lastName  nvarchar(50)
AS

SELECT
    *
FROM
    Person
WHERE
    FirstName = @firstName AND LastName = @lastName

GO

GRANT EXEC ON Person_SelectByName TO PUBLIC
GO

-- Person_SelectListByName

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_SelectListByName')
BEGIN DROP Procedure Person_SelectListByName
END
GO

CREATE Procedure Person_SelectListByName
    @firstName nvarchar(50),
    @lastName  nvarchar(50)
AS

SELECT
    *
FROM
    Person
WHERE
    FirstName like @firstName AND LastName like @lastName

GO

GRANT EXEC ON Person_SelectByName TO PUBLIC
GO

-- Person_Insert

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Insert')
BEGIN DROP Procedure Person_Insert END
GO

CREATE Procedure Person_Insert
    @FirstName  nvarchar(50),
    @LastName   nvarchar(50),
    @MiddleName nvarchar(50),
    @Gender     char(1)
AS

INSERT INTO Person
    ( LastName,  FirstName,  MiddleName,  Gender)
VALUES
    (@LastName, @FirstName, @MiddleName, @Gender)

SELECT Cast(SCOPE_IDENTITY() as int) PersonID

GO

GRANT EXEC ON Person_Insert TO PUBLIC
GO

-- Person_Insert_OutputParameter

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Insert_OutputParameter')
BEGIN DROP Procedure Person_Insert_OutputParameter END
GO

CREATE Procedure Person_Insert_OutputParameter
    @FirstName  nvarchar(50),
    @LastName   nvarchar(50),
    @MiddleName nvarchar(50),
    @Gender     char(1),
    @PersonID   int output
AS

INSERT INTO Person
    ( LastName,  FirstName,  MiddleName,  Gender)
VALUES
    (@LastName, @FirstName, @MiddleName, @Gender)

SET @PersonID = Cast(SCOPE_IDENTITY() as int)

GO

GRANT EXEC ON Person_Insert_OutputParameter TO PUBLIC
GO

-- Person_Update

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Update')
BEGIN DROP Procedure Person_Update END
GO

CREATE Procedure Person_Update
    @PersonID   int,
    @FirstName  nvarchar(50),
    @LastName   nvarchar(50),
    @MiddleName nvarchar(50),
    @Gender     char(1)
AS

UPDATE
    Person
SET
    LastName   = @LastName,
    FirstName  = @FirstName,
    MiddleName = @MiddleName,
    Gender     = @Gender
WHERE
    PersonID = @PersonID

GO

GRANT EXEC ON Person_Update TO PUBLIC
GO

-- Person_Delete

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Person_Delete')
BEGIN DROP Procedure Person_Delete END
GO

CREATE Procedure Person_Delete
    @PersonID int
AS

DELETE FROM Person WHERE PersonID = @PersonID

GO

GRANT EXEC ON Person_Delete TO PUBLIC
GO

-- Patient_SelectAll

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Patient_SelectAll')
BEGIN DROP Procedure Patient_SelectAll END
GO

CREATE Procedure Patient_SelectAll
AS

SELECT
    Person.*, Patient.Diagnosis
FROM
    Patient, Person
WHERE
    Patient.PersonID = Person.PersonID

GO

GRANT EXEC ON Patient_SelectAll TO PUBLIC
GO

-- Patient_SelectByName

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Patient_SelectByName')
BEGIN DROP Procedure Patient_SelectByName END
GO

CREATE Procedure Patient_SelectByName
    @firstName nvarchar(50),
    @lastName  nvarchar(50)
AS

SELECT
    Person.*, Patient.Diagnosis
FROM
    Patient, Person
WHERE
    Patient.PersonID = Person.PersonID
    AND FirstName = @firstName AND LastName = @lastName

GO

GRANT EXEC ON Person_SelectByName TO PUBLIC
GO

-- BinaryData Table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('BinaryData') AND type in (N'U'))
BEGIN DROP TABLE BinaryData END

CREATE TABLE BinaryData
(
    BinaryDataID int             NOT NULL IDENTITY(1,1) CONSTRAINT PK_BinaryData PRIMARY KEY CLUSTERED,
    Stamp        timestamp       NOT NULL,
    Data         varbinary(1024) NOT NULL)
ON [PRIMARY]
GO

-- OutRefTest

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'OutRefTest')
BEGIN DROP Procedure OutRefTest END
GO

CREATE Procedure OutRefTest
    @ID             int,
    @outputID       int output,
    @inputOutputID  int output,
    @str            varchar(50),
    @outputStr      varchar(50) output,
    @inputOutputStr varchar(50) output
AS

SET @outputID       = @ID
SET @inputOutputID  = @ID + @inputOutputID
SET @outputStr      = @str
SET @inputOutputStr = @str + @inputOutputStr

GO

-- OutRefEnumTest

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'OutRefEnumTest')
BEGIN DROP Procedure OutRefEnumTest END
GO

CREATE Procedure OutRefEnumTest
    @str            varchar(50),
    @outputStr      varchar(50) output,
    @inputOutputStr varchar(50) output
AS

SET @outputStr      = @str
SET @inputOutputStr = @str + @inputOutputStr

GO

-- ExecuteScalarTest

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Scalar_DataReader')
BEGIN DROP Procedure Scalar_DataReader END
GO

CREATE Procedure Scalar_DataReader
AS
SELECT Cast(12345 as int) AS intField, Cast('54321' as varchar(50)) AS stringField

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Scalar_OutputParameter')
BEGIN DROP Procedure Scalar_OutputParameter END
GO

CREATE Procedure Scalar_OutputParameter
    @outputInt    int = 0 output,
    @outputString varchar(50) = '' output
AS
BEGIN
    SET @outputInt    = 12345
    SET @outputString = '54321'
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type in (N'FN', N'IF', N'TF', N'FS', N'FT') AND name = 'Scalar_ReturnParameter')
BEGIN DROP Function Scalar_ReturnParameter END
GO

CREATE Function Scalar_ReturnParameter()
RETURNS int
AS
BEGIN
    RETURN 12345
END

GO

IF EXISTS (SELECT * FROM sys.objects WHERE type ='P' AND name = 'Scalar_ReturnParameterWithObject')
BEGIN DROP Procedure Scalar_ReturnParameterWithObject END
GO

CREATE Procedure Scalar_ReturnParameterWithObject
    @id int
AS
BEGIN
    SELECT * FROM Person WHERE PersonID = @id
    RETURN @id
END

GO

-- Data Types test

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('DataTypeTest') AND type in (N'U'))
BEGIN DROP TABLE DataTypeTest END
GO

CREATE TABLE DataTypeTest
(
    DataTypeID      int          NOT NULL IDENTITY(1,1) CONSTRAINT PK_DataType PRIMARY KEY CLUSTERED,
    Binary_         binary(50)       NULL,
    Boolean_        bit              NULL,
    Byte_           tinyint          NULL,
    Bytes_          varbinary(50)    NULL,
    Char_           char(1)          NULL,
    DateTime_       datetime         NULL,
    Decimal_        decimal(20,2)    NULL,
    Double_         float            NULL,
    Guid_           uniqueidentifier NULL,
    Int16_          smallint         NULL,
    Int32_          int              NULL,
    Int64_          bigint           NULL,
    Money_          money            NULL,
    SByte_          tinyint          NULL,
    Single_         real             NULL,
    Stream_         varbinary(50)    NULL,
    String_         nvarchar(50)     NULL,
    UInt16_         smallint         NULL,
    UInt32_         int              NULL,
    UInt64_         bigint           NULL,
    Xml_            xml              NULL
) ON [PRIMARY]
GO

INSERT INTO DataTypeTest
    (Binary_, Boolean_,   Byte_,  Bytes_,  Char_,  DateTime_, Decimal_,
     Double_,    Guid_,  Int16_,  Int32_,  Int64_,    Money_,   SByte_,
     Single_,  Stream_, String_, UInt16_, UInt32_,   UInt64_,     Xml_)
VALUES
    (   NULL,     NULL,    NULL,    NULL,    NULL,      NULL,     NULL,
        NULL,     NULL,    NULL,    NULL,    NULL,      NULL,     NULL,
        NULL,     NULL,    NULL,    NULL,    NULL,      NULL,     NULL)
GO

INSERT INTO DataTypeTest
    (Binary_, Boolean_,   Byte_,  Bytes_,  Char_,  DateTime_, Decimal_,
     Double_,    Guid_,  Int16_,  Int32_,  Int64_,    Money_,   SByte_,
     Single_,  Stream_, String_, UInt16_, UInt32_,   UInt64_,
     Xml_)
VALUES
    (NewID(),        1,     255, NewID(),     'B', GetDate(), 12345.67,
    1234.567,  NewID(),   32767,   32768, 1000000,   12.3456,      127,
    1234.123,  NewID(), 'string',  32767,   32768, 200000000,
    '<root><element strattr="strvalue" intattr="12345"/></root>')
GO

-- SKIP Sql2005 BEGIN
--
-- Arrays
--
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'ArrayTest')
BEGIN DROP PROCEDURE ArrayTest END
GO

--IF EXISTS (SELECT * FROM sys.objects WHERE type = 'T' AND name = 'IntArray')
--BEGIN
    DROP TYPE IntArray
--END
GO

CREATE TYPE IntArray AS TABLE
(
    Num int NULL
)
GO

CREATE PROCEDURE ArrayTest
    @InputIntArray IntArray READONLY
AS
BEGIN
    SELECT Num * 2 FROM @InputIntArray;
END
GO
-- SKIP Sql2005 END

DROP TABLE Parent
GO
DROP TABLE Child
GO
DROP TABLE GrandChild
GO

CREATE TABLE Parent      (ParentID int, Value1 int)
GO
CREATE TABLE Child       (ParentID int, ChildID int)
GO
CREATE TABLE GrandChild  (ParentID int, ChildID int, GrandChildID int)
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('LinqDataTypes') AND type in (N'U'))
BEGIN DROP TABLE LinqDataTypes END
GO

CREATE TABLE LinqDataTypes
(
    ID            int,
    MoneyValue    decimal(10,4),
    DateTimeValue datetime,
    BoolValue     bit,
    GuidValue     uniqueidentifier,
    BinaryValue   varbinary(5000)
)
GO


Linq

Data providers supported.

Data Provider | Provider Name | Namespace | Vendor
AccessDataProvider | Access | System.Data.OleDb | Microsoft
DB2DataProvider | DB2 | IBM.Data.DB2 | IBM
InformixDataProvider | Informix | IBM.Data.Informix | IBM
FdpDataProvider | Fdp | FirebirdSql.Data.FirebirdClient | Firebird
MySqlDataProvider | MySql | MySql.Data.MySqlClient | Sun Microsystems
OdpDataProvider | ODP | Oracle.DataAccess.Client | Oracle
PostgreSQLProvider | PostgreSQL | Npgsql | PostgreSQL.org
SqlCeDataProvider | SqlCe | System.Data.SqlServerCe | Microsoft
SQLiteDataProvider | SQLite | System.Data.SQLite | SQLite.org
SqlDataProvider | Sql | System.Data.SqlClient | Microsoft
SybaseDataProvider | Sybase | Sybase.Data.AseClient | Sybase

Operators.

 | Linq | DB2 | Informix | Oracle | Firebird | PostgreSQL | MySql | MS SQL | SqlCe | Sybase | SQLite | Access
Bitwise AND | a & b | BitAnd(a, b) |  |  | Bin_And(a, b) | a & b |  |  |  |  |  | X
Bitwise OR | a || b | BitOr(a, b) |  | (a + b) - BitAnd(a, b) | Bin_Or(a, b) | a || b |  |  |  |  |  | X
Bitwise XOR | a ^ b | BitXor(a, b) |  | (a + b) - BitAnd(a, b) * 2 | Bin_Xor(a, b) | a # b | a ^ b |  |  |  | (a + b) - (a & b) * 2 | X
Coalesce | a ?? b | Coalesce(a, b) | Nvl(a, b) |  | Coalesce(a, b) |  |  |  |  |  |  | Iif(a IS NULL, b, a)
 | a ?? b ?? c | Coalesce(a, b, c) | Nvl(a, b, c) |  | Coalesce(a, b, c) |  |  |  |  |  |  | Iif(a IS NULL, Iif(b IS NULL, c, b), a)
Concatenation | a + b + c | a || b || c |  |  |  |  | Concat(a, b, c) | a + b + c |  |  | a || b || c | a + b + c
Conditional | a ? b : c | CASE WHEN a THEN b ELSE c END |  |  |  |  |  |  |  |  |  | Iif(a, b, c)
 | a ? b : c ? d : e | CASE WHEN a THEN b WHEN c THEN d ELSE e END |  |  |  |  |  |  |  |  |  | Iif(a, b, Iif(c, d, e))
Modulo | a % b | Mod(a, b) |  |  |  | a % b |  |  |  |  |  | a MOD b

Core string functions.
 | Linq | DB2 | Informix | Oracle | Firebird | PostgreSQL | MySql | MS SQL | SqlCe | Sybase | SQLite | Access
Case | Sql.Lower(s) | Lower(s) |  |  |  |  |  |  |  |  |  | LCase(s)
 | Sql.Upper(s) | Upper(s) |  |  |  |  |  |  |  |  |  | UCase(s)
CharIndex | Sql.CharIndex(a, s) | Locate(a, s) | X | InStr(s, a) | X | Position(a in s) | Locate(a, s) | CharIndex(a, s) |  |  |  | InStr(1, s, a, 1)
 | Sql.CharIndex(a, s, b) | Locate(a, s, b) | X | InStr(s, a, b) | X | Position(a in Substring(s, b, Length(s)-b)) + b-1 | Locate(a, s, b) | CharIndex(a, s, b) |  | CharIndex(a,Substring( s, b, Len(s)-b)) + b - 1 | CharIndex(a, s, b) | InStr(b, s, a, 1)
Length | Sql.Length(s) | Length(s) |  |  | Char_Length(s) | Length(s) |  | Len(s) |  |  | Length(s) | Len(s)
LIKE | !s.Contains(a) | s NOT LIKE a | NOT s LIKE a | s NOT LIKE a
 | s.Contains("a%b") | s LIKE '%a~%b%' ESCAPE '~' |  |  |  |  |  |  |  |  |  | s LIKE "%a[%]b%"
 | s.Contains("abc") | s LIKE '%abc%'
 | s.Contains(str) | s LIKE @str ESCAPE '~' |  |  |  |  |  |  |  |  |  | s LIKE @str
 | s.EndsWith("abc") | s LIKE '%abc'
 | s.StartsWith("abc") | s LIKE 'abc%'
 | Sql.Like(s, "%abc%") | s LIKE '%abc%'
 | Sql.Like(s, "a~%b", '~') | s LIKE "a~%b" ESCAPE '~' |  |  |  |  |  |  |  |  |  | s LIKE "a[%]b"
 | SqlMethods.Like(s, "%abc%") | s LIKE '%abc%'
 | SqlMethods.Like(s, "a~%b", '~') | s LIKE "a~%b" ESCAPE '~' |  |  |  |  |  |  |  |  |  | s LIKE "a[%]b"
Pad | Sql.PadLeft(s, a, b) | VarChar(Repeat(b, a-Length(s)), 1000) || s | LPad(s, a, b) |  |  |  |  | Replicate(b, a - Len(s)) + s |  |  |  | String(a-Len(s), b)) + s
 | Sql.PadRight(s, a, b) | s || VarChar(Repeat( b, a-Length(s)), 1000) | RPad(s, a, b) |  |  |  |  | s + Replicate(b, a - Len(s)) |  |  |  | s + String(a-Len(s), b))
Replace | Sql.Replace(s, a, b) | Replace(s, a, b) |  |  |  |  |  |  |  | Str_Replace(s, a, b) | Replace(s, a, b) | X
Reverse | Sql.Reverse(s) | X |  | Reverse(s) |  | Reverse(s) -- implemented as UDF | Reverse(s) |  | X | Reverse(s) |  | X
Space | Sql.Space(a) | VarChar( Repeat(' ', a), 1000) | RPad(' ', a, ' ') |  |  | Repeat(' ', a) | Space(a) |  |  |  | PadR(' ', a) | Space(a)
Stuff | Sql.Stuff(s, a, b, c) | Substring(s, 1, a-1) + c + Substring(s, a+b, Length(s)-a-b+1)) |  |  |  |  |  | Stuff(s, a, b, c) |  |  | Substring(s, 1, a-1) + c + Substring(s, a+b, Length(s)-a-b+1))
Substring | Sql.Left(s, a) | Left(s, a) | Substr(s, 1, a) |  | Left(s, a) | Substring(s, 1, a) | Left(s, a) |  | Substring(s, 1, a) | Left(s, a) | LeftStr(s, a) | Left(s, a)
 | Sql.Right(s, a) | Right(s, a) | Substr(s, Length(s)-a+1, a) |  | Right(s, a) | Substring(s, Length(s)-a+1, a) | Right(s, a) |  | Substring(s, Len(s)-a+1, a) | Right(s, a) | RigthStr(s, a) | Right(s, a)
 | Sql.Substring(s, a, b) | Substr(s, a, b) |  |  | Substring( s from a for b) | Substring(s, a, b) |  |  |  |  | Substr(s, a, b) | Mid(s, a, b)
Trim | Sql.Trim(s) | Trim(s) |  |  |  |  |  | LTrim(RTrim(s)) |  |  | Trim(s)
 | Sql.TrimLeft(s) | LTrim(s)
 | Sql.TrimRight(s) | RTrim(s)

Extended Linq string functions.

To support methods listed below provider must implement all used functions.

Linq | All data providers
Case | s.ToLower () | Sql.Lower(s)
s.ToUpper () | Sql.Upper(s)
Compare | s.CompareTo (a) | s > a ? 1 : s == 0 ? 0 : -1
IndexOf | s.IndexOf (a) | Sql.Length(a) == 0 ? 0 : (Sql.CharIndex(a, s) ?? 0) - 1
s.IndexOf (a, b) | Sql.Length(a) == 0 && Sql.Length(s) > b ? b : (Sql.CharIndex(a, s, b + 1) ?? 0) - 1
s.IndexOf (a, b, c) | Sql.Length(a) == 0 && Sql.Length(s) > b ? b : (Sql.CharIndex(a, Sql.Left(s, c), b) ?? 0) - 1
Insert | s.Insert (a, b) | Sql.Length(s) == a ? s + b : Sql.Stuff(s, a + 1, 0, b)
LastIndexOf s.LastIndexOf(a) Sql.Length(a) == 0 ? Sql.Length(s) - 1 :
(Sql.CharIndex(a, s) ?? 0) == 0 ? -1 :
Sql.Length(s) - (Sql.CharIndex(Sql.Reverse(a), Sql.Reverse(s)) ?? 0) - Sql.Length(a) + 1
s.LastIndexOf(a, b) Sql.Length(a) == 0 ? b :
(Sql.CharIndex(a, s, b + 1) ?? 0) == 0 ? -1 :
Sql.Length(s) - (Sql.CharIndex(Sql.Reverse(a), Sql.Reverse(Sql.Substring(s, b + 1, Sql.Length(s) - b))) ?? 0) - Sql.Length(a) + 1
s.LastIndexOf(a, b, c) Sql.Length(a) == 0 ? b :
(Sql.CharIndex(a, Sql.Left(s, b + c), b + 1) ?? 0) == 0 ? -1 :
b + c - (Sql.CharIndex(Sql.Reverse(a), Sql.Reverse(Sql.Substring(s, b + 1, c))) ?? 0) - Sql.Length(a) + 1
Length | s.Length | Sql.Length(s)
Pad | s.PadLeft (a) | Sql.PadLeft(s, a, ' ')
s.PadLeft (a, b) | Sql.PadLeft(s, a, b)
s.PadRight (a) | Sql.PadRight(s, a, ' ')
s.PadRight (a, b) | Sql.PadRight(s, a, b)
Remove | s.Remove (a) | Sql.Left(s, a)
s.Remove (a, b) | Sql.Stuff(s, a + 1, b, "")
Replace | s.Replace (a, b) | Sql.Replace(s, a, b)
Substring | s.Substring (a) | Sql.Substring(s, a + 1, Sql.Length(s) - a)
s.Substring (a, b) | Sql.Substring(s, a + 1, b)
Trim | s.Trim () | Sql.Trim(s)
s.TrimEnd () | Sql.TrimRight(s)
s.TrimStart () | Sql.TrimLeft(s)

Core datetime functions.
 | Linq | DB2 | Informix | Oracle | Firebird | PostgreSQL | MySql | MS SQL | SqlCe | Sybase | SQLite | Access
Current Timestamp | Sql.CurrentTimestamp | CURRENT_TIMESTAMP | CURRENT | CURRENT_TIMESTAMP |  |  |  |  | GetDate() |  | CURRENT_TIMESTAMP | Now
 | Sql.GetDate() | CURRENT_TIMESTAMP | CURRENT | CURRENT_TIMESTAMP |  |  |  |  | GetDate() |  | CURRENT_TIMESTAMP | Now

Extended Linq datetime functions.

 | Linq | All data providers
Current Timestamp | DateTime.Now | Sql.CurrentTimestamp

SQL transformations.

Linq DB2 Informix Oracle Firebird PostgreSQL MySql MS SQL SqlCe Sybase SQLite Access
SELECT x db.Select(() => x)
SELECT x
FROM SYSIBM.SYSDUMMY1
FETCH FIRST 1 ROW ONLY
SELECT FIRST 1 x
FROM SYSTABLES
SELECT x
FROM SYS.DUAL
SELECT x
FROM rdb$database
SELECT x
TAKE x query.Take(x)
... FETCH FIRST x ROWS ONLY
SELECT FIRST x
... WHERE rownum <= x
SELECT FIRST x
... LIMIT x
SELECT TOP x
X
SELECT TOP x
... LIMIT x
SELECT TOP x


DataAccess

• Introduction
Abstract accessors.
• Open and Configure | How to open and configure data accessors.
• Transactions
• AbstractAccessor | Abstract accessor example.
• ExecuteList | DataAccessor + List.
• ExecuteDictionary | DataAccessor + Dictionary.
• ExecuteObject | DataAccessor + business objects.
• ExecuteScalar | DataAccessor + scalar values.
Abstract accessor attributes.
• ActionName | • ActionSprocName | • ActualType
• CommandBehavior | • DataSetTable | • Destination
• Direction | • DiscoverParameters | • Format
• Index | • ObjectType | • ParamDbType
• ParamName | • ParamNullValue | • ParamSize
• ScalarFieldName | • ScalarSource | • SprocName
• SqlQuery | • Custom SqlQuery 1 | • Custom SqlQuery 2
SqlQuery & SprocQuery
• Open and Configure | How to open and configure SqlQuery & SprocQuery.
Basic CRUDL operations and SQL text generation.
• SelectByKeySql | Reads a record by Primary Key.
• SelectAllSql | Reads all records from specified table.
• InsertSql | Inserts a new record.
• UpdateSql | Updates a record.
• DeleteSql | Deletes a record either by Primary Key or by provided object.
Basic CRUDL operations and stored procedures.
• SelectByKey | Reads a record by Primary Key.
• SelectAll | Reads all records from specified table.
• Insert | Inserts a new record.
• Update | Updates a record.
• Delete | Deletes a record either by Primary Key or by provided object.
SQL text generation control attributes.
• TableName
• PrimaryKey
• NonUpdatable
• XmlExtension


Introduction to abstract data accessors

Introduction

Before we start talking about the data accessors, let us create a few examples of typical data accessor methods.

The following table contains three stored procedures and three data access methods implementing the stored procedure calls.

Stored procedure Data access method
The first stored procedure takes filter and page parameters and returns recordset from the Person table.
CREATE Procedure GetPersonListByName(
    @firstName  varchar(50),
    @lastName   varchar(50),
    @pageNumber int,
    @pageSize   int)
AS
    -- stored procedure implementation
    --

public List<Person> GetPersonListByName(
    string firstName,
    string lastName,
    int    pageNumber,
    int    pageSize)
{
    // method implementation.
}
Second example will return single Person record by id.
CREATE Procedure GetPersonByID(@id int)
AS
    -- stored procedure implementation
    --

public Person GetPersonByID(int id)
{
    // method implementation.
}
The last example will delete a record from the database by id.
CREATE Procedure DeletePersonByID(@id int)
AS
    -- stored procedure implementation
    --

public void DeletePersonByID(int id)
{
    // method implementation.
}

Now lets see what we can say if we compare the stored procedure and C# method signatures.

  1. Stored procedure and method names match up.
  2. Sequential order, method parameter types and names correspond to stored procedure parameters.
  3. Methods' return values can give us an idea what Execute method we should utilize and what object type has to be used to map data from recordset if needed.

As demonstrated above method definition contains all the information we need to implement the method body. Actually, by defining the method signatures, we completed the most intelligent part of data accessor development. The rest of work is definitely a monkey's job. Honestly, I got bored of being just a coding machine writing the same data access code over and over again, especially understanding that this process can be automated.

This introduction shows how to avoid the implementation step of data access development and how to reduce this routine process to the method declaration.

Abstract classes

Unfortunately, mainstream .NET languages still do not have a compile-time transformation system like some functional or hybrid languages do. All we have today is pre-compile- and run-time code generation.

This introduction concentrates on run-time code generation and its support by Business Logic Toolkit for .NET.

Let us step back and bring the methods from the previous examples together in one class. Ideally, this data accessor class could look like the following:

using System;
using System.Collections.Generic;

public class PersonAccessor
{
    public List<Person> GetPersonListByName(
        string firstName, string lastName, int pageNumber, int pageSize);

    public Person GetPersonByID   (int id);
    public void   DeletePersonByID(int id);
}

The bad news about this sample is that we cannot use such syntax as the compiler expects the method's body implementation.

The good news is we can use abstract classes and methods that give us quite similar, compilable source code.

using System;
using System.Collections.Generic;

public abstract class PersonAccessor
{
    public abstract List<Person> GetPersonListByName(
        string firstName, string lastName, int pageNumber, int pageSize);

    public abstract Person GetPersonByID   (int id);
    public abstract void   DeletePersonByID(int id);
}

This code is 100% valid and our next step is to make it workable.

Abstract DataAccessor

Business Logic Toolkit provides the DataAccessor class, which is used as a base class to develop data accessor classes. If we add DataAccessor to our previous example, it will look like the following:

using System;
using System.Collections.Generic;

public abstract class PersonAccessor : DataAccessor
{
    public abstract List<Person> GetPersonListByName(
        string firstName, string lastName, int pageNumber, int pageSize);

    public abstract Person GetPersonByID   (int id);
    public abstract void   DeletePersonByID(int id);
}

That's it! Now this class is complete and fully functional. The code below shows how to use it:

using System;
using System.Collections.Generic;

using BLToolkit.Reflection;

namespace DataAccess
{
    class Program
    {
        static void Main(string[] args)
        {
            PersonAccessor pa = TypeAccessor<PersonAccessor>.CreateInstance();

            List<Person> list = pa.GetPersonListByName("Crazy", "Frog", 0, 20);

            foreach (Person p in list)
                Console.Write("{0} {1}", p.FirstName, p.LastName);
        }
    }
}

The only magic here is the TypeAccessor.CreateInstance method. First of all this method creates a new class inherited from the PersonAccessor class and then generates abstract method bodies depending on each method declaration. If we wrote those methods manually, we could get something like the following:

using System;
using System.Collections.Generic;

using BLToolkit.Data;

namespace Example.BLToolkitExtension
{
    public sealed class PersonAccessor : Example.PersonAccessor
    {
        public override List<Person> GetPersonListByName(
            string firstName,
            string lastName,
            int    pageNumber,
            int    pageSize)
        {
            using (DbManager db = GetDbManager())
            {
                return db
                    .SetSpCommand("GetPersonListByName",
                        db.Parameter("@firstName",  firstName),
                        db.Parameter("@lastName",   lastName),
                        db.Parameter("@pageNumber", pageNumber),
                        db.Parameter("@pageSize",   pageSize))
                    .ExecuteList<Person>();
            }
        }

        public override Person GetPersonByID(int id)
        {
            using (DbManager db = GetDbManager())
            {
                return db
                    .SetSpCommand("GetPersonByID", db.Parameter("@id", id))
                    .ExecuteObject<Person>();
            }
        }

        public override void DeletePersonByID(int id)
        {
            using (DbManager db = GetDbManager())
            {
                db
                    .SetSpCommand("DeletePersonByID", db.Parameter("@id", id))
                    .ExecuteNonQuery();
            }
        }
    }
}

(The DbManager class is another BLToolkit class used for 'low-level' database access).

Every part of the method declaration is important. Method's return value specifies one of the Execute methods in the following way:
Return Type | Execute Method
IDataReader interface | ExecuteReader
Subclass of DataSet | ExecuteDataSet
Subclass of DataTable | ExecuteDataTable
Class implementing the IList interface | ExecuteList or ExecuteScalarList
Class implementing the IDictionary interface | ExecuteDictionary or ExecuteScalarDictionary
void | ExecuteNonQuery
string, byte[] or value type | ExecuteScalar
In any other case | ExecuteObject
The method name explicitly defines the action name, which is converted to the stored procedure name. Type, sequential order, and name of the method parameters are mapped to the command parameters. Exceptions from this rule are:

  • a parameter of DbManager type. In this case generator uses provided DbManager to call the command.
  • parameters decorated with attribute FormatAttribute, DestinationAttribute.

Generating process control

The PersonAccessor class above is a very simple example and, of course, it seems too ideal to be real. In real life, we need more flexibility and more control over the generated code. BLToolkit contains a bunch of attributes to control DataAccessor generation in addition to DataAccessor virtual members.

Method CreateDbManager
protected virtual DbManager CreateDbManager()
{
    return new DbManager();
}

By default, this method creates a new instance of DbManager that uses default database configuration. You can change this behavior by overriding this method. For example:

public abstract class OracleDataAccessor : DataAccessor
{
    protected override DbManager CreateDbManager()
    {
        return new DbManager("Oracle", "Production");
    }
}

This code will use the Oracle data provider and Production configuration.

Method GetDefaultSpName
protected virtual string GetDefaultSpName(string typeName, string actionName)
{
    return typeName == null?
        actionName:
        string.Format("{0}_{1}", typeName, actionName);
}

As I mentioned, the method name explicitly defines the so-called action name. The final stored procedure name is created by the GetDefaultSpName method. The default implementation uses the following naming convention:

  • If type name is provided, the method constructs the stored proc name by concatenating the type and action names. Thus, if the type name is "Person" and the action name is "GetAll", the resulting sproc name will be "Person_GetAll".
  • If the type name is NOT provided, the stored procedure name will equal the action name.

You can easily change this behavior. For example, for the naming convention "p_Person_GetAll", the method implementation can be the following:

public abstract class MyBaseDataAccessor<T,A> : DataAccessor<T,A>
    where A : DataAccessor<T,A>
{
    protected override string GetDefaultSpName(string typeName, string actionName)
    {
        return string.Format("p_{0}_{1}", typeName, actionName);
    }
}
Method GetTableName
protected virtual string GetTableName(Type type)
{
    // ...
    return type.Name;
}

By default, the table name is the associated object type name (Person in our examples). There are two ways to associate an object type with an accessor. By providing generic parameter:

public abstract class PersonAccessor : DataAccessor<Person>
{
}

And by the ObjectType attribute:

[ObjectType(typeof(Person))]
public abstract class PersonAccessor : DataAccessor
{
}

If you want to have different table and type names in your application, you may override the GetTableName method:

public abstract class OracleDataAccessor<T,A> : DataAccessor<T,A>
    where A : DataAccessor<T,A>
{
    protected override string GetTableName(Type type)
    {
        return base.GetTableName(type).ToUpper();
    }
}
TableNameAttribute

Also, you can change the table name for a particular object type by decorating this object with the TableNameAttribute attribute:

[TableName("PERSON")]
public class Person
{
    public int    ID;
    public string FirstName;
    public string LastName;
}
ActionNameAttribute

This attribute allows changing the action name.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [ActionName("GetByID")]
    protected abstract IDataReader GetByIDInternal(DbManager db, int id);

    public Person GetByID(int id)
    {
        using (DbManager   db = GetDbManager())
        using (IDataReader rd = GetByIDInternal(db, id))
        {
            Person p = new Person();

            // do something complicated.

            return p;
        }
    }
}
ActionSprocNameAttribute

This attribute associates the action name with a stored procedure name:

[ActionSprocName("Insert", �sp_Person_Insert")]
public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    public abstract void Insert(Person p);
}

This attribute can be useful when you need to reassign a stored procedure name for a method defined in your base class.

SprocNameAttribute

The regular way to assign deferent from default sproc name for a method is the SprocName attribute.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SprocName("sp_Person_Insert")]
    public abstract void Insert(Person p);
}
DestinationAttribute

By default, the DataAccessor generator uses method's return value to determine which Execute method should be used to perform the current operation. The DestinationAttribute indicates that target object is a parameter decorated with this attribute:

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    public abstract void GetAll([Destination] List<Person> list);
}
DirectionAttributes

DataAccessor generator can map provided business object to stored procedure parameters. Direction attributes allow controlling this process more precisely.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    public abstract void Insert(
        [Direction.Output("ID"), Direction.Ignore("LastName")] Person person);
}

In addition, BLToolkit provides two more direction attributes: Direction.InputOutputAttribute and Direction.ReturnValueAttribute.

DiscoverParametersAttribute

By default, BLToolkit expects method parameter names to match stored procedure parameter names. The sequential order of parameters is not important in this case. This attribute enforces BLToolkit to retrieve parameter information from the sproc and to assign method parameters in the order they go. Parameter names are ignored.

FormatAttribute

This attribute indicates that the specified parameter is used to construct the stored procedure name or SQL statement:

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SqlQuery("SELECT {0} FROM {1} WHERE {2}")]
    public abstract List<string> GetStrings(
        [Format(0)] string fieldName,
        [Format(1)] string tableName,
        [Format(2)] string whereClause);
}
IndexAttribute

If you want your method to return a dictionary, you will have to specify fields to build the dictionary key. The Index attribute allows you to do that:

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SqlQuery("SELECT * FROM Person")]
    [Index("ID")]
    public abstract Dictionary<int, Person>           SelectAll1();

    [SqlQuery("SELECT * FROM Person")]
    [Index("@PersonID", "LastName")]
    public abstract Dictionary<CompoundValue, Person> SelectAll2();
}

Note: if your key has more than one field, the type of this key should be CompoundValue.

If the field name starts from '@' symbol, BLToolkit reads the field value from data source, otherwise from an object property/field.

ParamNameAttribute

By default, the method parameter name should match the stored procedure parameter name. This attribute specifies the sproc parameter name explicitly.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    public abstract Person SelectByName(
        [ParamName("FirstName")] string name1,
        [ParamName("@LastName")] string name2);
}
ScalarFieldNameAttribute

If your method returns a dictionary of scalar values, you will have to specify the name or index of the field used to populate the scalar list. The ScalarFieldName attribute allows you to do that:

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SqlQuery("SELECT * FROM Person")]
    [Index("@PersonID")]
    [ScalarFieldName("FirstName")]
    public abstract Dictionary<int, string>           SelectAll1();

    [SqlQuery("SELECT * FROM Person")]
    [Index("PersonID", "LastName")]
    [ScalarFieldName("FirstName")]
    public abstract Dictionary<CompoundValue, string> SelectAll2();
}
ScalarSourceAttribute

If a method returns a scalar value, this attribute can be used to specify how database returns this value. The ScalarSource attribute take a parameter of the ScalarSourceType type:

ScalarSourceType | Description
DataReader | Calls the DbManager.ExecuteReader method, and then calls IDataReader.GetValue method to read the value.
OutputParameter | Calls the DbManager.ExecuteNonQuery method, and then reads value from the IDbDataParameter.Value property.
ReturnValue | Calls the DbManager.ExecuteNonQuery method, and then reads return value from command parameter collection.
AffectedRows | Calls the DbManager.ExecuteNonQuery method, and then returns its return value.
SqlQueryAttribute

This attribute allows specifying SQL statement.

public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
{
    [SqlQuery("SELECT * FROM Person WHERE PersonID = @id")]
    public abstract Person GetByID(int @id);
}

Conclusion

I hope this brief tutorial demonstrates one of the simplest, quickest and most low-maintenance ways to develop your data access layer. In addition, you will get one more benefit, which is incredible object mapping performance. But that is a topic we will discuss later.


AbstractAccessor

The following example demonstrates how to create and use an abstract data accessor class. All abstract methods of the class are generated at run-time depending on each method declaration. Every part of the method declaration is important. Method's return value specifies one of the Execute methods in the following way:
Return Type | Execute Method
IDataReader interface | ExecuteReader
Subclass of DataSet | ExecuteDataSet
Subclass of DataTable | ExecuteDataTable
Class implementing the IList interface | ExecuteList or ExecuteScalarList
Class implementing the IDictionary interface | ExecuteDictionary or ExecuteScalarDictionary
void | ExecuteNonQuery
string, byte[] or value type | ExecuteScalar
In any other case | ExecuteObject
Method name explicitly defines action name which is converted to stored procedure name.
Type, sequential order, and name of the method parameters is mapped to the command parameters. Exceptions from this rule are:

  • a parameter of DbManager type. In this case generator uses provided DbManager to call the command.
  • parameters decorated with attribute FormatAttribute.

AbstractAccessor.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class AbstractAccessor
    {
        public abstract class PersonAccessor : DataAccessor<Person>
        {
            public abstract Person  SelectByName(Person person);
            public abstract Person  SelectByName(string firstName, string lastName);

            public abstract int     Insert      (Person person);

            [SqlQuery("SELECT Top {0} * FROM Person ORDER BY PersonID")]
            [Index("ID")]
            public abstract Dictionary<int,Person> SelectTop([Format(0)] int top);

            private SprocQuery<Person> _query;
            public  SprocQuery<Person>  Query
            {
                get
                {
                    if (_query == null)
                        _query = new SprocQuery<Person>(DbManager);
                    return _query;
                }
            }
        }

        [Test]
        public void Test()
        {
            using (DbManager db = new DbManager())
            {
                PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>(db);

                pa.BeginTransaction();

                // Insert and get id.
                //
                Person person = new Person();

                person.FirstName = "Crazy";
                person.LastName  = "Frog";
                person.Gender    = Gender.Unknown;

                int id = pa.Insert(person);

                // SelectByName.
                //
                person = pa.SelectByName("Crazy", "Frog");

                Assert.IsNotNull(person);

                // Select top.
                //
                Dictionary<int,Person> dic = pa.SelectTop(10);

                Assert.IsTrue(dic.Count <= 10);

                // Delete.
                //
                pa.Query.Delete(person);

                pa.CommitTransaction();
            }
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteDictionary

ExecuteDictionary.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Common;
using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ExecuteDictionary1
    {
        public class Person
        {
            [MapField("PersonID"), PrimaryKey]
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;
        }

        public abstract class PersonAccessor : DataAccessor<Person>
        {
            // This method uses Person class primary key information.
            //
            [ActionName("SelectAll")]
            public abstract Dictionary<int,Person> GetPersonDictionary1();

            // Define index field explicitly. "ID" is a field name of the Person class.
            //
            [ActionName("SelectAll")]
            [Index("ID")]
            public abstract Dictionary<int,Person> GetPersonDictionary2();

            // Define index field explicitly. "@PersonID" is a recordset field.
            // Note that the '@' symbol enforces the library to read index value
            // from recordset (not from object).
            //
            [ActionName("SelectAll")]
            [Index("@PersonID")]
            public abstract Dictionary<int,Person> GetPersonDictionary3();

            // This method reads a dictionary containing scalar values.
            //
            [SqlQuery("SELECT PersonID, FirstName FROM Person")]
            [Index("PersonID")]
            [ScalarFieldName("FirstName")]
            public abstract Dictionary<int,string> GetPersonNameDictionary();
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

            // ExecuteDictionary.
            //
            Dictionary<int,Person> dic;

            dic = pa.GetPersonDictionary1();
            dic = pa.GetPersonDictionary2();
            dic = pa.GetPersonDictionary3();

            foreach (int id in dic.Keys)
                Console.WriteLine("{0}: {1} {2}", id, dic[id].FirstName, dic[id].LastName);

            // ExecuteScalarDictionary.
            //
            Dictionary<int,string> sdic = pa.GetPersonNameDictionary();

            foreach (int id in dic.Keys)
                Console.WriteLine("{0}: {1}", id, sdic[id]);
        }
    }

    [TestFixture]
    public class ExecuteDictionary2
    {
        // This example demonstrates how to use a multiple field key.
        //
        public class Person
        {
            [PrimaryKey(1), MapField("PersonID")]
            public int    ID;
            [PrimaryKey(2)]
            public string LastName;

            public string FirstName;
            public string MiddleName;
        }

        public abstract class PersonAccessor : DataAccessor<Person>
        {
            // This method uses Person class primary key information.
            // Note that the key type of the dictionary must be of IndexValue type.
            // It is required if the index consists of more than one element.
            //
            [ActionName("SelectAll")]
            public abstract Dictionary<CompoundValue,Person> GetPersonDictionary();

            // This method reads a dictionary containing scalar values.
            //
            [SqlQuery("SELECT PersonID, LastName, FirstName FROM Person")]
            [Index("PersonID", "LastName")]
            [ScalarFieldName("FirstName")]
            public abstract Dictionary<CompoundValue,string> GetPersonNameDictionary();
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

            // ExecuteDictionary.
            //
            Dictionary<CompoundValue,Person> dic = pa.GetPersonDictionary();

            foreach (CompoundValue idx in dic.Keys)
                Console.WriteLine("{0}: {1} {2}", dic[idx].ID, dic[idx].FirstName, dic[idx].LastName);

            // ExecuteScalarDictionary.
            //
            Dictionary<CompoundValue,string> sdic = pa.GetPersonNameDictionary();

            string firstName = sdic[new CompoundValue(2, "Testerson")];

            Assert.AreEqual("Tester", firstName);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteList

ExecuteList.cs
using System;
using System.Collections;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ExecuteList
    {
        public abstract class PersonAccessor : DataAccessor
        {
            // This method reads a list of Person objects.
            //
            [ActionName("SelectAll")]
            public abstract List<Person> GetPersonList1();

            // Here we help the method to get object type information.
            // ObjectTypeAttribute can be applied to the class itself.
            // In this case there is no need to specify object type for each method.
            // Another way to specify object type is a generic parameter
            // of the DataAccessor<T> class.
            //
            [SqlQuery("SELECT * FROM Person")]
            [ObjectType(typeof(Person))]
            public abstract ArrayList GetPersonList2();

            // This method reads a list of scalar values.
            //
            [SqlQuery("SELECT PersonID FROM Person")]
            public abstract List<int> GetPersonIDList();
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

            // ExecuteList.
            //
            IList list;

            list = pa.GetPersonList1();
            list = pa.GetPersonList2();

            foreach (Person p in list)
                Console.WriteLine("{0}: {1} {2}", p.ID, p.FirstName, p.LastName);

            // ExecuteScalarList.
            //
            List<int> slist = pa.GetPersonIDList();

            foreach (int id in slist)
                Console.WriteLine("{0}", id);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteObject

ExecuteObject.cs
using System;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ExecuteObject
    {
        public abstract class PersonAccessor : DataAccessor<Person>
        {
            // Here we explicitly specify a stored procedure name.
            //
            [SprocName("Person_SelectByKey")]
            public abstract Person GetByID(int @id);

            // SQL query text.
            //
            [SqlQuery("SELECT * FROM Person WHERE PersonID = @id")]
            public abstract Person GetPersonByID(int @id);

            // Specify action name.
            // Stored procedure name is generated based on convention
            // defined by DataAccessor.GetDefaultSpName method.
            //
            [ActionName("SelectByName")]
            public abstract Person GetPersonByName(string @firstName, string @lastName);

            // By default method name defines an action name
            // which is converted to a stored procedure name.
            // Default conversion rule is ObjectName_MethodName.
            // This method calls the Person_SelectByName stored procedure.
            //
            public abstract Person SelectByName(string @firstName, string @lastName);
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

            // ExecuteObject.
            //
            Assert.IsNotNull(pa.GetByID        (1));
            Assert.IsNotNull(pa.GetPersonByID  (2));
            Assert.IsNotNull(pa.GetPersonByName("Tester", "Testerson"));
            Assert.IsNotNull(pa.SelectByName   ("Tester", "Testerson"));
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ExecuteScalar

ExecuteScalar.cs
using System;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ExecuteScalar
    {
        public abstract class PersonAccessor : DataAccessor<Person>
        {
            [SqlQuery("SELECT Count(*) FROM Person")]
            public abstract int GetCount();

            // The Person_Insert sproc returns an id of the created record.
            //
            [SprocName("Person_Insert")]
            public abstract int Insert(Person person);
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

            // ExecuteScalar.
            //
            Assert.IsTrue(pa.GetCount() > 0);

            // Insert and get id.
            //
            Person person = new Person();

            person.FirstName = "Crazy";
            person.LastName  = "Frog";
            person.Gender    = Gender.Unknown;

            int id = pa.Insert(person);

            Assert.IsFalse(id == 0);

            new SprocQuery<Person>().DeleteByKey(id);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Open and Configure

OpenConfig.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class OpenConfig
    {
        public class Person
        {
            [MapField("PersonID"), PrimaryKey, NonUpdatable]
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;
        }

        public abstract class TestAccessor : DataAccessor
        {
            public abstract Person SelectByKey(int id);
            public abstract Person SelectByKey(DbManager db, int id);
        }

        // DbManager is created by DataAccessor.
        //
        [Test]
        public void Test1()
        {
            TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

            Person person = ta.SelectByKey(1);

            Assert.IsNotNull(person);
        }

        // DataAccessor takes DbManager as a parameter.
        //
        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>(db);

                Person person = ta.SelectByKey(1);

                Assert.IsNotNull(person);
            }
        }

        // DataAccessor method takes DbManager as a parameter.
        //
        [Test]
        public void Test3()
        {
            using (DbManager db = new DbManager())
            {
                TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

                Person person = ta.SelectByKey(db, 1);

                Assert.IsNotNull(person);
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Transactions

Transaction.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class Transaction
    {
        public abstract class TestAccessor : DataAccessor<Person>
        {
            public abstract int  Insert(Person person);
            public abstract void Delete(int @PersonID);

            public abstract Person SelectByKey(int id);
            public abstract Person SelectByKey(DbManager db, int id);
        }

        // DataAccessor takes DbManager as a parameter.
        //
        [Test]
        public void Test1()
        {
            using (DbManager db = new DbManager())
            {
                TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>(db);

                ta.BeginTransaction();

                int id = ta.Insert(new Person { FirstName = "John", LastName = "Smith" });
                Assert.AreNotEqual(0, id);

                Person person = ta.SelectByKey(id);
                Assert.IsNotNull(person);

                ta.Delete(id);

                ta.CommitTransaction();
            }
        }

        // DataAccessor method takes DbManager as a parameter.
        //
        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                db.BeginTransaction();

                TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

                int id = ta.Insert(new Person { FirstName = "John", LastName = "Smith" });
                Assert.AreNotEqual(0, id);

                Person person = ta.SelectByKey(db, id);
                Assert.IsNotNull(person);

                ta.Delete(id);

                db.CommitTransaction();
            }
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ActionName

ActionName is a logical name of an operation. By default a method name is an action name, but ActionNameAttribute allows overriding this behavior. Actual stored procedure name will be built depending on naming convention which is defined in the GetDefaultSpName method.

ActionName.cs
using System;
using NUnit.Framework;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ActionName
    {
        public abstract class PersonAccessor : DataAccessor<Person, PersonAccessor>
        {
            // Default action name is 'SelectByKey'.
            // Stored procedure name is 'Person_SelectByKey'.
            //
            public abstract Person SelectByKey(int @id);

            // Explicit action name is 'SelectByName'.
            // Stored procedure name is 'Person_SelectByName'.
            //
            [ActionName("SelectByName")]
            public abstract Person AnyName    (string @firstName, string @lastName);
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = PersonAccessor.CreateInstance();

            Person person1 = pa.SelectByKey(1);

            Assert.IsNotNull(person1);

            Person person2 = pa.AnyName(person1.FirstName, person1.LastName);

            Assert.AreEqual(person1.ID, person2.ID);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ActionSprocName

The ActionSprocName attribute is applied to a business object (NOT to data accessor class) and associates ActionName with stored procedure name.

ActionSprocName.cs
using System;
using NUnit.Framework;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ActionSprocName
    {
        [ActionSprocName("GetByName", "Person_SelectByName")]
        public class Person
        {
            public int    PersonID;
            public string LastName;
            public string FirstName;
            public string MiddleName;
        }

        public abstract class PersonAccessor : DataAccessor
        {
            // Default action name is 'SelectByKey'.
            // Stored procedure name is 'Person_SelectByKey'.
            //
            public abstract Person SelectByKey(int @id);

            // Default action name is 'GetByName'.
            // Stored procedure name is 'Person_SelectByName'
            // defined by the ActionSprocName attribute of the Person class.
            //
            public abstract Person GetByName(string @firstName, string @lastName);
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

            Person person1 = pa.SelectByKey(1);

            Assert.IsNotNull(person1);

            Person person2 = pa.GetByName(person1.FirstName, person1.LastName);

            Assert.AreEqual(person1.PersonID, person2.PersonID);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ActualType

The ActualType attribute associates an actual type with the type returned by an abstract method. Note the ObjectType attribute has higher priority.

ActualType.cs
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ActualType
    {
        public interface IName
        {
            string Name { get; }
        }

        public class NameBase : IName
        {
            private string _name;
            public  string  Name { get { return _name; } set { _name = value; } }
        }

        public class Name1 : NameBase {}
        public class Name2 : NameBase {}

        [ActualType(typeof(IName), typeof(Name1))]
        public abstract class TestAccessor : DataAccessor
        {
            [SqlQuery("SELECT 'John' as Name")]
            public abstract IName GetName1();

            [SqlQuery("SELECT 'John' as Name"), ObjectType(typeof(Name2))]
            public abstract IName GetName2();

            [SqlQuery("SELECT 'John' as Name")]
            public abstract IList<IName> GetName1List();

            [SqlQuery("SELECT 'John' as Name"), ObjectType(typeof(Name2))]
            public abstract IList<IName> GetName2List();

            [SqlQuery("SELECT 1 as ID, 'John' as Name"), Index("@ID")]
            public abstract IDictionary<int, IName> GetName1Dictionary();

            [SqlQuery("SELECT 1 as ID, 'John' as Name"), Index("@ID"), ObjectType(typeof(Name2))]
            public abstract IDictionary<int, IName> GetName2Dictionary();
        }

        [Test]
        public void Test()
        {
            TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

            Assert.IsTrue(ta.GetName1()              is Name1);
            Assert.IsTrue(ta.GetName2()              is Name2);
            Assert.IsTrue(ta.GetName1List()[0]       is Name1);
            Assert.IsTrue(ta.GetName2List()[0]       is Name2);
            Assert.IsTrue(ta.GetName1Dictionary()[1] is Name1);
            Assert.IsTrue(ta.GetName2Dictionary()[1] is Name2);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


CommandBehavior

The CommandBehavior attribute provides a description of the results of the query and its effect on the database.

CommandBehavior.cs
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class CommandBehaviorDemo
    {
        public abstract class TestAccessor : DataAccessor
        {
            [SprocName("Person_SelectAll"), CommandBehavior(CommandBehavior.SchemaOnly)]
            public abstract IDataReader SelectAllIDataReaderSchemaOnly(DbManager db);
        }

        [Test]
        public void Test()
        {
            TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

            using (DbManager   db = ta.GetDbManager())
            using (IDataReader dr = ta.SelectAllIDataReaderSchemaOnly(db))
            {
                DataTable table = dr.GetSchemaTable();

                Assert.AreEqual("PersonID",  table.Rows[0]["ColumnName"]);
                Assert.AreEqual(typeof(int), table.Rows[0]["DataType"]);
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


DataSetTable

The DataSetTable attribute specifies table to be populated by the method in destination or returning dataset.

DataSetTable.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class DataSetTable
    {
        public abstract class TestAccessor : DataAccessor
        {
            [SprocName("Person_SelectAll"), DataSetTable("First")]
            public abstract void SelectFirstTable  ([Destination] DataSet ds);

            [SprocName("Person_SelectAll"), DataSetTable("Second")]
            public abstract void SelectSecondTable ([Destination] DataSet ds);

            [SprocName("Person_SelectAll"), DataSetTable(0)]
            public abstract void SelectFirstTable2 ([Destination] DataSet ds);

            [SprocName("Person_SelectAll"), DataSetTable(1)]
            public abstract void SelectSecondTable2([Destination] DataSet ds);
        }

        [Test]
        public void Test()
        {
            TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

            DataSet ds = new DataSet();

            ta.SelectFirstTable  (ds);
            ta.SelectSecondTable (ds);
            ta.SelectFirstTable2 (ds);
            ta.SelectSecondTable2(ds);

            Assert.IsTrue (ds.Tables.Contains("First"),  "Table 'First'  not found");
            Assert.IsTrue (ds.Tables.Contains("Second"), "Table 'Second' not found");
            Assert.IsFalse(ds.Tables.Contains("Table"),  "Table 'Table'  was found");
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Destination

Return value of an abstract method defines a result that this method returns. The Destination attribute allows specifying one of the method parameters as the result.

Destination.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class Destination
    {
        public abstract class PersonAccessor : DataAccessor
        {
            public abstract void SelectAll([Destination] IList<Person> list);
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

            List<Person> list = new List<Person>();

            pa.SelectAll(list);

            Assert.AreNotEqual(0, list.Count);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Direction

The Direction attributes allow controlling mapping from/to a business object. The following table contains brief description of the direction attributes:
Attribute | Description
Output | Provided members are output parameters of the query.
InputOutput | Provided members are input/output parameters of the query.
Ignore | Provided members are excluded from mapping.
ReturnValue | Provided member is mapped to return value.

Direction.cs
using System;
using NUnit.Framework;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class DirectionTest
    {
        public abstract class PersonAccessor : DataAccessor
        {
            [SprocName("Person_Insert_OutputParameter")]
            public abstract void Insert_OutputParameter([Direction.Output("PERSONID")] Person p);

            [SprocName("Scalar_ReturnParameter")]
            public abstract void Insert_ReturnParameter(
                [Direction.ReturnValue("@PersonID"),
                 Direction.Ignore("PersonID", "FirstName", "LastName", "MiddleName", "Gender")] Person p);

            [SprocName("Scalar_ReturnParameter")]
            public abstract void Insert_ReturnParameter2(
                [Direction.ReturnValue("ID"),
                 Direction.Ignore("PersonID", "FirstName", "LastName", "MiddleName", "Gender")] Person p);
        }

        PersonAccessor Accessor
        {
            get { return DataAccessor.CreateInstance<PersonAccessor>(); }
        }

        [Test]
        public void TestOutputParameter()
        {
            Person p = new Person { FirstName = "Crazy", LastName = "Frog", Gender = Gender.Other };

            Accessor.Insert_OutputParameter(p);

            Assert.IsTrue(p.ID > 0);

            new SprocQuery().Delete(p);
        }

        [Test]
        public void TestReturnParameter()
        {
            Person p = new Person();

            Accessor.Insert_ReturnParameter(p);

            Assert.AreEqual(12345, p.ID);
        }

        [Test]
        public void TestReturnParameter2()
        {
            Person p = new Person();

            Accessor.Insert_ReturnParameter2(p);

            Assert.AreEqual(12345, p.ID);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


DiscoverParameters

By default, BLToolkit expects method parameter names to match stored procedure parameter names. The sequential order of parameters is not important in this case. The DiscoverParameters attribute enforces BLToolkit to retrieve parameter information from the sproc and to assign method parameters in the order they go. Parameter names are ignored.

DiscoverParameters.cs
using NUnit.Framework;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class DiscoverParameters
    {
        public abstract class PersonAccessor : DataAccessor
        {
            [DiscoverParameters]
            public abstract Person SelectByName(string anyParameterName, string otherParameterName);
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();
            Person         p  = pa.SelectByName("Tester", "Testerson");

            Assert.AreEqual(2, p.ID);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Format

The Format indicates that the specified parameter is used to construct the stored procedure name or SQL statement:

Format.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class Format
    {
        public abstract class PersonAccessor : DataAccessor
        {
            [SqlQuery("SELECT TOP {0} * FROM Person")]
            public abstract List<Person> GetPersonList([Format] int top);
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa   = DataAccessor.CreateInstance<PersonAccessor>();
            List<Person>   list = pa.GetPersonList(2);

            Assert.That(list,       Is.Not.Null);
            Assert.That(list.Count, Is.LessThanOrEqualTo(2));
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Index

ExecuteDictionary.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Common;
using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ExecuteDictionary1
    {
        public class Person
        {
            [MapField("PersonID"), PrimaryKey]
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;
        }

        public abstract class PersonAccessor : DataAccessor<Person>
        {
            // This method uses Person class primary key information.
            //
            [ActionName("SelectAll")]
            public abstract Dictionary<int,Person> GetPersonDictionary1();

            // Define index field explicitly. "ID" is a field name of the Person class.
            //
            [ActionName("SelectAll")]
            [Index("ID")]
            public abstract Dictionary<int,Person> GetPersonDictionary2();

            // Define index field explicitly. "@PersonID" is a recordset field.
            // Note that the '@' symbol enforces the library to read index value
            // from recordset (not from object).
            //
            [ActionName("SelectAll")]
            [Index("@PersonID")]
            public abstract Dictionary<int,Person> GetPersonDictionary3();

            // This method reads a dictionary containing scalar values.
            //
            [SqlQuery("SELECT PersonID, FirstName FROM Person")]
            [Index("PersonID")]
            [ScalarFieldName("FirstName")]
            public abstract Dictionary<int,string> GetPersonNameDictionary();
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

            // ExecuteDictionary.
            //
            Dictionary<int,Person> dic;

            dic = pa.GetPersonDictionary1();
            dic = pa.GetPersonDictionary2();
            dic = pa.GetPersonDictionary3();

            foreach (int id in dic.Keys)
                Console.WriteLine("{0}: {1} {2}", id, dic[id].FirstName, dic[id].LastName);

            // ExecuteScalarDictionary.
            //
            Dictionary<int,string> sdic = pa.GetPersonNameDictionary();

            foreach (int id in dic.Keys)
                Console.WriteLine("{0}: {1}", id, sdic[id]);
        }
    }

    [TestFixture]
    public class ExecuteDictionary2
    {
        // This example demonstrates how to use a multiple field key.
        //
        public class Person
        {
            [PrimaryKey(1), MapField("PersonID")]
            public int    ID;
            [PrimaryKey(2)]
            public string LastName;

            public string FirstName;
            public string MiddleName;
        }

        public abstract class PersonAccessor : DataAccessor<Person>
        {
            // This method uses Person class primary key information.
            // Note that the key type of the dictionary must be of IndexValue type.
            // It is required if the index consists of more than one element.
            //
            [ActionName("SelectAll")]
            public abstract Dictionary<CompoundValue,Person> GetPersonDictionary();

            // This method reads a dictionary containing scalar values.
            //
            [SqlQuery("SELECT PersonID, LastName, FirstName FROM Person")]
            [Index("PersonID", "LastName")]
            [ScalarFieldName("FirstName")]
            public abstract Dictionary<CompoundValue,string> GetPersonNameDictionary();
        }

        [Test]
        public void Test()
        {
            PersonAccessor pa = DataAccessor.CreateInstance<PersonAccessor>();

            // ExecuteDictionary.
            //
            Dictionary<CompoundValue,Person> dic = pa.GetPersonDictionary();

            foreach (CompoundValue idx in dic.Keys)
                Console.WriteLine("{0}: {1} {2}", dic[idx].ID, dic[idx].FirstName, dic[idx].LastName);

            // ExecuteScalarDictionary.
            //
            Dictionary<CompoundValue,string> sdic = pa.GetPersonNameDictionary();

            string firstName = sdic[new CompoundValue(2, "Testerson")];

            Assert.AreEqual("Tester", firstName);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ObjectType

The ObjectType attribute associates an actual type with the type returned by an abstract method. Note the ActualType attribute has lower priority.

ActualType.cs
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ActualType
    {
        public interface IName
        {
            string Name { get; }
        }

        public class NameBase : IName
        {
            private string _name;
            public  string  Name { get { return _name; } set { _name = value; } }
        }

        public class Name1 : NameBase {}
        public class Name2 : NameBase {}

        [ActualType(typeof(IName), typeof(Name1))]
        public abstract class TestAccessor : DataAccessor
        {
            [SqlQuery("SELECT 'John' as Name")]
            public abstract IName GetName1();

            [SqlQuery("SELECT 'John' as Name"), ObjectType(typeof(Name2))]
            public abstract IName GetName2();

            [SqlQuery("SELECT 'John' as Name")]
            public abstract IList<IName> GetName1List();

            [SqlQuery("SELECT 'John' as Name"), ObjectType(typeof(Name2))]
            public abstract IList<IName> GetName2List();

            [SqlQuery("SELECT 1 as ID, 'John' as Name"), Index("@ID")]
            public abstract IDictionary<int, IName> GetName1Dictionary();

            [SqlQuery("SELECT 1 as ID, 'John' as Name"), Index("@ID"), ObjectType(typeof(Name2))]
            public abstract IDictionary<int, IName> GetName2Dictionary();
        }

        [Test]
        public void Test()
        {
            TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

            Assert.IsTrue(ta.GetName1()              is Name1);
            Assert.IsTrue(ta.GetName2()              is Name2);
            Assert.IsTrue(ta.GetName1List()[0]       is Name1);
            Assert.IsTrue(ta.GetName2List()[0]       is Name2);
            Assert.IsTrue(ta.GetName1Dictionary()[1] is Name1);
            Assert.IsTrue(ta.GetName2Dictionary()[1] is Name2);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ParamDbType

The ParamDbType can be used to specify parameter DbType.

Param.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class Param
    {
        public abstract class TestAccessor : DataAccessor
        {
            [SqlQuery("SELECT {0} = {1} FROM Person WHERE PersonID = 1")]
            public abstract void SelectJohn(
                [ParamSize(50), ParamDbType(DbType.String)] out string name,
                [Format] string paramName,
                [Format] string fieldName);
        }

        [Test]
        public void AccessorTest()
        {
            using (DbManager db = new DbManager())
            {
                TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>(db);

                string actualName;

                ta.SelectJohn(out actualName, "@name", "FirstName");

                Assert.AreEqual("John", actualName);
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ParamName

By default, the method parameter name should match the stored procedure parameter name. The ParamName specifies the sproc parameter name explicitly.

ParamName.cs
using System;
using NUnit.Framework;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ParamName
    {
        public abstract class TestAccessor : DataAccessor
        {
            public abstract Person SelectByName(
                [ParamName("FirstName")] string name1,
                [ParamName("@LastName")] string name2);
        }

        [Test]
        public void Test()
        {
            TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();
            Person       p  = ta.SelectByName("Tester", "Testerson");

            Assert.AreEqual(2, p.ID);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ParamNullValue

The ParamNullValue specifies the parameter value replaced with NULL.

ParamNullValue.cs
using System;
using NUnit.Framework;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ParamNullValue
    {
        public abstract class TestAccessor : DataAccessor
        {
            public abstract Person SelectByKey([ParamNullValue(1)] int id);
        }

        [Test]
        public void Test()
        {
            TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

            // Parameter id == 1 will be replaced with NULL
            //
            Person p1 = ta.SelectByKey(1);
            Assert.IsNull(p1);

            // Parameter id == 2 will be send as is
            //
            Person p2 = ta.SelectByKey(2);
            Assert.IsNotNull(p2);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ParamSize

The ParamSize can be used to specify db parameter size.

Param.cs
using System;
using System.Data;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class Param
    {
        public abstract class TestAccessor : DataAccessor
        {
            [SqlQuery("SELECT {0} = {1} FROM Person WHERE PersonID = 1")]
            public abstract void SelectJohn(
                [ParamSize(50), ParamDbType(DbType.String)] out string name,
                [Format] string paramName,
                [Format] string fieldName);
        }

        [Test]
        public void AccessorTest()
        {
            using (DbManager db = new DbManager())
            {
                TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>(db);

                string actualName;

                ta.SelectJohn(out actualName, "@name", "FirstName");

                Assert.AreEqual("John", actualName);
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ScalarFieldName

If your method returns a dictionary of scalar values, you will have to specify the name or index of the field used to populate the scalar list. The ScalarFieldName attribute allows you to do that.

ScalarFieldName.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ScalarFieldName
    {
        public abstract class TestAccessor : DataAccessor
        {
            [SqlQuery("SELECT PersonID, FirstName FROM Person")]
            [Index("PersonID")]
            [ScalarFieldName("FirstName")]
            public abstract Dictionary<int, string> GetPersonNameDictionary();
        }

        [Test]
        public void Test()
        {
            TestAccessor pa = DataAccessor.CreateInstance<TestAccessor>();

            IDictionary<int, string> dic = pa.GetPersonNameDictionary();

            Assert.AreEqual("John", dic[1]);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


ScalarSource

If a method returns a scalar value, this attribute can be used to specify how database returns this value. The ScalarSource attribute take a parameter of the ScalarSourceType type:
ScalarSourceType | Description
DataReader | Calls the DbManager.ExecuteReader method, and then calls IDataReader.GetValue method to read the value.
OutputParameter | Calls the DbManager.ExecuteNonQuery method, and then reads value from the IDbDataParameter.Value property.
ReturnValue | Calls the DbManager.ExecuteNonQuery method, and then reads return value from command parameter collection.
AffectedRows | Calls the DbManager.ExecuteNonQuery method, and then returns its return value.

ScalarSource.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class ScalarSource
    {
        public abstract class TestAccessor : DataAccessor
        {
            [ScalarSource(ScalarSourceType.DataReader)]
            public abstract int Scalar_DataReader();

            [ActionName("Scalar_DataReader")]
            [ScalarSource(ScalarSourceType.DataReader, 1)]
            public abstract string Scalar_DataReader2();

            [ActionName("Scalar_DataReader")]
            [ScalarSource(ScalarSourceType.DataReader, "stringField")]
            public abstract string Scalar_DataReader3();

            [ScalarSource(ScalarSourceType.OutputParameter)]
            public abstract int Scalar_OutputParameter();

            [ActionName("Scalar_OutputParameter")]
            [ScalarSource(ScalarSourceType.OutputParameter, 1)]
            public abstract string Scalar_OutputParameter2();

            [ActionName("Scalar_OutputParameter")]
            [ScalarSource(ScalarSourceType.OutputParameter, "outputString")]
            public abstract string Scalar_OutputParameter3();

            [ScalarSource(ScalarSourceType.ReturnValue)]
            public abstract int Scalar_ReturnParameter();

            [ActionName("Scalar_DataReader")]
            [ScalarSource(ScalarSourceType.AffectedRows)]
            public abstract int Scalar_AffectedRows();

            public static TestAccessor CreateInstance()
            {
                return (TestAccessor)CreateInstance(typeof(TestAccessor));
            }
        }

        [Test]
        public void DataReaderTest()
        {
            TestAccessor ta = TestAccessor.CreateInstance();

            int expectedValue = 12345;
            int actualValue   = ta.Scalar_DataReader();

            Assert.AreEqual(expectedValue, actualValue);
        }

        [Test]
        public void DataReader2Test()
        {
            TestAccessor ta = TestAccessor.CreateInstance();

            string expectedValue = "54321";
            string actualValue   = ta.Scalar_DataReader2();

            Assert.AreEqual(expectedValue, actualValue);
        }

        [Test]
        public void DataReader3Test()
        {
            TestAccessor ta = TestAccessor.CreateInstance();

            string expectedValue = "54321";
            string actualValue   = ta.Scalar_DataReader3();

            Assert.AreEqual(expectedValue, actualValue);
        }

        [Test]
        public void OutputParameterTest()
        {
            TestAccessor ta = TestAccessor.CreateInstance();

            int expectedValue = 12345;
            int actualValue   = ta.Scalar_OutputParameter();

            Assert.AreEqual(expectedValue, actualValue);
        }

        [Test]
        public void OutputParameter2Test()
        {
            TestAccessor ta = TestAccessor.CreateInstance();

            string expectedValue = "54321";
            string actualValue   = ta.Scalar_OutputParameter2();

            Assert.AreEqual(expectedValue, actualValue);
        }

        [Test]
        public void OutputParameter3Test()
        {
            TestAccessor ta = TestAccessor.CreateInstance();

            string expectedValue = "54321";
            string actualValue   = ta.Scalar_OutputParameter3();

            Assert.AreEqual(expectedValue, actualValue);
        }

        [Test]
        public void ReturnParameterTest()
        {
            TestAccessor ta = TestAccessor.CreateInstance();

            int expectedValue = 12345;
            int actualValue   = ta.Scalar_ReturnParameter();

            Assert.AreEqual(expectedValue, actualValue);
        }

        [Test]
        public void AffectedRowsTest()
        {
            TestAccessor ta = TestAccessor.CreateInstance();

            int expectedValue = -1;
            int actualValue   = ta.Scalar_AffectedRows();

            Assert.AreEqual(expectedValue, actualValue);
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


SprocName

The SprocName attribute allows assigning a stored procedure name explicitly.

SprocName.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class SprocName
    {
        public abstract class TestAccessor : DataAccessor
        {
            [SprocName("Person_SelectAll")]
            public abstract List<Person> GetPersonList();
        }

        [Test]
        public void Test()
        {
            TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

            List<Person> list = ta.GetPersonList();

            Assert.AreNotEqual(0, list.Count);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


SqlQuery

The SqlQuery attribute allows specifying SQL statement.

SqlQuery.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class SqlQuery
    {
        public abstract class TestAccessor : DataAccessor
        {
            [SqlQuery(@"
                SELECT
                    *
                FROM
                    Person
                WHERE
                    FirstName like @firstName AND
                    LastName  like @lastName")]
            public abstract List<Person> GetPersonListByName(string @firstName, string @lastName);
        }

        [Test]
        public void Test()
        {
            TestAccessor ta = DataAccessor.CreateInstance<TestAccessor>();

            List<Person> list = ta.GetPersonListByName("John", "P%");

            Assert.AreNotEqual(0, list.Count);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


SqlQuery customization 1

This example demonstrates how to create a custom attribute which allows specifying more than one SQL query for different data providers.

CustomSqlQuery1.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class CustomSqlQuery1
    {
        public class TestQueryAttribute : SqlQueryAttribute
        {
            public TestQueryAttribute()
            {
                IsDynamic = true;
            }

            public string OracleText { get; set; }
            public string FbText     { get; set; }
            public string SQLiteText { get; set; }

            public override string GetSqlText(DataAccessor accessor, DbManager dbManager)
            {
                switch (dbManager.DataProvider.Name)
                {
                    case "Sql"   :
                    case "Access": return SqlText;
                    case "Oracle": return OracleText ?? SqlText;
                    case "Fdp"   : return FbText     ?? SqlText;
                    case "SQLite": return SQLiteText ?? SqlText;
                }

                throw new ApplicationException(string.Format("Unknown data provider '{0}'", dbManager.DataProvider.Name));
            }
        }

        public abstract class PersonAccessor : DataAccessor
        {
            [TestQuery(
                SqlText    = "SELECT * FROM Person WHERE LastName = @lastName",
                OracleText = "SELECT * FROM Person WHERE LastName = :lastName")]
            public abstract List<Person> SelectByLastName(string lastName);

            [TestQuery(
                SqlText    = "SELECT * FROM Person WHERE {0} = @value",
                OracleText = "SELECT * FROM Person WHERE {0} = :value")]
            public abstract List<Person> SelectBy([Format] string fieldName, string value);

            [TestQuery(
                SqlText    = "SELECT TOP {0} * FROM Person WHERE LastName = @lastName",
                OracleText = "SELECT * FROM Person WHERE LastName = :lastName AND rownum <= {0}",
                FbText     = "SELECT FIRST {0} * FROM Person WHERE LastName = @lastName",
                SQLiteText = "SELECT * FROM Person WHERE LastName = @lastName LIMIT {0}")]
            public abstract List<Person> SelectByLastName(string lastName, [Format(0)] int top);

            [TestQuery(
                SqlText    = "SELECT @id as PersonID",
                OracleText = "SELECT :id PersonID FROM Dual",
                FbText     = "SELECT CAST(@id AS INTEGER) PersonID FROM Dual")]
            public abstract List<Person> SelectID(int @id);
        }

        [Test]
        public void Test1()
        {
            PersonAccessor da = DataAccessor.CreateInstance<PersonAccessor>();

            List<Person> list = da.SelectByLastName("Testerson");

            Assert.AreNotEqual(0, list.Count);
        }

        [Test]
        public void Test2()
        {
            PersonAccessor da = DataAccessor.CreateInstance<PersonAccessor>();

            List<Person> list = da.SelectBy("FirstName", "John");

            Assert.AreNotEqual(0, list.Count);
        }

        [Test]
        public void Test3()
        {
            PersonAccessor da = DataAccessor.CreateInstance<PersonAccessor>();

            List<Person> list = da.SelectByLastName("Testerson", 1);

            Assert.AreNotEqual(0, list.Count);
        }

        [Test]
        public void Test4()
        {
            PersonAccessor da = DataAccessor.CreateInstance<PersonAccessor>();

            List<Person> list = da.SelectID(42);

            Assert.AreEqual(42, list[0].ID);
        }
    }
}
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


SqlQuery customization 2

This example demonstrates how to specify SQL query for different data providers by using XML.

CustomSqlQuery2.cs
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Xml;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class CustomSqlQuery2
    {
        public abstract class TestAccessorBase<T> : DataAccessor
            where T : TestAccessorBase<T>
        {
            const int Sql    = 0;
            const int Access = 1;
            const int Oracle = 2;
            const int Fdp    = 3;
            const int SQLite = 4;

            Dictionary<int, string> _sql = new Dictionary<int,string>();

            private string GetSql(string providerName, int provider, int queryID)
            {
                Stream stream = Assembly.GetCallingAssembly().GetManifestResourceStream(
                    "HowTo.DataAccess.Sql." + providerName + ".xml");

                XmlDocument doc = new XmlDocument();

                doc.Load(stream);

                XmlNode node = doc.SelectSingleNode(string.Format("/sql/query[@id={0}]", queryID));

                return node != null? node.InnerText: null;

            }

            protected override string PrepareSqlQuery(DbManager db, int queryID, int uniqueID, string sqlQuery)
            {
                int    provider     = Sql;
                string providerName = db.DataProvider.Name;

                switch (providerName)
                {
                    case "Sql"   : provider = Sql;    break;
                    case "Access": provider = Access; break;
                    case "Oracle": provider = Oracle; break;
                    case "Fdp"   : provider = Fdp;    break;
                    case "SQLite": provider = SQLite; break;
                    default:
                        throw new ApplicationException(
                            string.Format("Unknown data provider '{0}'", providerName));
                }

                string text;
                int    key = provider * 1000000 + uniqueID;

                if (_sql.TryGetValue(key, out text))
                    return text;

                _sql[key] = text = GetSql(providerName, provider, queryID) ?? GetSql("Sql", Sql, queryID);

                return text;
            }

            public static T CreateInstance()
            {
                return DataAccessor.CreateInstance<T>();
            }
        }

        public abstract class PersonAccessor : TestAccessorBase<PersonAccessor>
        {
            [SqlQuery(ID = 1)]
            public abstract List<Person> SelectByLastName(string lastName);

            [SqlQuery(ID = 2)]
            public abstract List<Person> SelectBy([Format] string fieldName, string value);

            [SqlQuery(ID = 3)]
            public abstract List<Person> SelectByLastName(string lastName, [Format(0)] int top);

            [SqlQuery(ID = 4)]
            public abstract List<Person> SelectID(int @id);
        }

        [Test]
        public void Test1()
        {
            PersonAccessor da = PersonAccessor.CreateInstance();

            List<Person> list = da.SelectByLastName("Testerson");

            Assert.AreNotEqual(0, list.Count);
        }

        [Test]
        public void Test2()
        {
            PersonAccessor da = PersonAccessor.CreateInstance();

            List<Person> list = da.SelectBy("FirstName", "John");

            Assert.AreNotEqual(0, list.Count);
        }

        [Test]
        public void Test3()
        {
            PersonAccessor da = PersonAccessor.CreateInstance();

            List<Person> list = da.SelectByLastName("Testerson", 1);

            Assert.AreNotEqual(0, list.Count);
        }

        [Test]
        public void Test4()
        {
            PersonAccessor da = PersonAccessor.CreateInstance();

            List<Person> list = da.SelectID(42);

            Assert.AreEqual(42, list[0].ID);
        }
    }
}
Sql.xml
<?xml version="1.0" encoding="utf-8" ?>
<sql>
    <query id="1">SELECT * FROM Person WHERE LastName = @lastName</query>
    <query id="2">SELECT * FROM Person WHERE {0} = @value</query>
    <query id="3">SELECT TOP {0} * FROM Person WHERE LastName = @lastName</query>
    <query id="4">SELECT @id as PersonID</query>
</sql>
Access.xml
<?xml version="1.0" encoding="utf-8" ?>
<sql>
</sql>
Oracle.xml
<?xml version="1.0" encoding="utf-8" ?>
<sql>
    <query id="1">SELECT * FROM Person WHERE LastName = :lastName</query>
    <query id="2">SELECT * FROM Person WHERE {0} = :value</query>
    <query id="3">SELECT * FROM Person WHERE LastName = :lastName AND rownum &lt;= {0}</query>
    <query id="4">SELECT :id PersonID FROM Dual</query>
</sql>
Fdp.xml
<?xml version="1.0" encoding="utf-8" ?>
<sql>
    <query id="3">SELECT FIRST {0} * FROM Person WHERE LastName = @lastName</query>
    <query id="4">SELECT CAST(@id AS INTEGER) PersonID FROM Dual</query>
</sql>
SQLite.xml
<?xml version="1.0" encoding="utf-8" ?>
<sql>
    <query id="3">SELECT * FROM Person WHERE LastName = @lastName LIMIT {0}</query>
</sql>
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Open and Configure

The SqlQuery and SprocQuery classes can be used to perform simple CRUDL operations such as SelectByKey, SelectAll, Insert, Update, and Delete.

OpenConfigQuery.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class OpenConfigQuery
    {
        public class Person
        {
            [MapField("PersonID"), PrimaryKey, NonUpdatable]
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;
        }

        // DbManager is created by SqlQuery.
        //
        [Test]
        public void Test1()
        {
            SqlQuery<Person> query = new SqlQuery<Person>();

            Person person = query.SelectByKey(1);

            Assert.IsNotNull(person);
        }

        // SqlQuery takes DbManager as a parameter.
        //
        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                SqlQuery<Person> query = new SqlQuery<Person>(db);

                Person person = query.SelectByKey(1);

                Assert.IsNotNull(person);
            }
        }

        // SqlQuery method takes DbManager as a parameter.
        //
        [Test]
        public void Test3()
        {
            using (DbManager db = new DbManager())
            {
                SqlQuery<Person> query = new SqlQuery<Person>();

                Person person = query.SelectByKey(db, 1);

                Assert.IsNotNull(person);
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


SelectByKeySql

SelectByKeySql.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class SelectByKeySql
    {
        [Test]
        public void Test1()
        {
            SqlQuery<Person> query = new SqlQuery<Person>();

            Person person = query.SelectByKey(1);

            Assert.IsNotNull(person);
        }

        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                SqlQuery<Person> query = new SqlQuery<Person>();

                Person person = query.SelectByKey(db, 1);

                Assert.IsNotNull(person);
            }
        }
    }
}
DataAccessor.SelectByKetSql method generates and executes the following SQL statement:
SELECT
    [MiddleName],
    [PersonID],
    [LastName],
    [FirstName]
FROM
    [Person]
WHERE
    [PersonID] = @PersonID
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


SelectAllSql

SelectAllSql.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class SelectAllSql
    {
        [Test]
        public void Test1()
        {
            SqlQuery<Person> query = new SqlQuery<Person>();

            List<Person> list = query.SelectAll();
        }

        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                SqlQuery<Person> query = new SqlQuery<Person>();

                List<Person> list = query.SelectAll(db);
            }
        }
    }
}
DataAccessor.SelectAllSql method generates and executes the following SQL statement:
SELECT
    [MiddleName],
    [PersonID],
    [LastName],
    [FirstName]
FROM
    [Person]
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


InsertSql

InsertSql.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class InsertSql
    {
        [Test]
        public void Test1()
        {
            SqlQuery<Person> query = new SqlQuery<Person>();

            Person person = new Person();

            person.FirstName = "Crazy";
            person.LastName  = "Frog";
            person.Gender    = Gender.Unknown;

            query.Insert(person);
        }

        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                SqlQuery<Person> query = new SqlQuery<Person>();

                Person person = new Person();

                person.FirstName = "Crazy";
                person.LastName  = "Frog";
                person.Gender    = Gender.Other;

                query.Insert(db, person);
            }
        }
    }
}
DataAccessor.InsertSql method generates and executes the following SQL statement:
INSERT INTO [Person] (
    [MiddleName],
    [Gender],
    [LastName],
    [FirstName]
) VALUES (
    @MiddleName,
    @Gender,
    @LastName,
    @FirstName
)
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


UpdateSql

UpdateSql.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class UpdateSql
    {
        int _id;

        [TestFixtureSetUp]
        public void Insert()
        {
            using (DbManager db = new DbManager())
            {
                _id = db
                    .SetCommand(@"
                        INSERT INTO Person (
                             FirstName,  LastName,  Gender
                        ) VALUES (
                            @FirstName, @LastName, @Gender
                        )
                        SELECT Cast(SCOPE_IDENTITY() as int)",
                        db.Parameter("@FirstName", "Crazy"),
                        db.Parameter("@LastName",  "Frog"),
                        db.Parameter("@Gender",    Map.EnumToValue(Gender.Unknown)))
                    .ExecuteScalar<int>();
            }
        }

        [Test]
        public void Test1()
        {
            SqlQuery<Person> query = new SqlQuery<Person>();

            Person person = query.SelectByKey(_id);

            person.Gender = Gender.Other;

            query.Update(person);
        }

        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                SqlQuery<Person> query = new SqlQuery<Person>();

                Person person = query.SelectByKey(db, _id);

                person.Gender = Gender.Other;

                query.Update(db, person);
            }
        }

        [TestFixtureTearDown]
        public void Delete()
        {
            using (DbManager db = new DbManager())
            {
                db
                    .SetCommand("DELETE FROM Person WHERE PersonID = @id",
                        db.Parameter("@id", _id))
                    .ExecuteNonQuery();
            }
        }
    }
}
DataAccessor.UpdateSql method generates and executes the following SQL statement:
UPDATE
    [Person]
SET
    [MiddleName] = @MiddleName,
    [Gender]     = @Gender,
    [LastName]   = @LastName,
    [FirstName]  = @FirstName
WHERE
    [PersonID]   = @PersonID
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


DeleteSql

DeleteSql.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class DeleteSql
    {
        private int Insert()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand(@"
                        INSERT INTO Person (
                             FirstName,  LastName,  Gender
                        ) VALUES (
                            @FirstName, @LastName, @Gender
                        )
                        SELECT Cast(SCOPE_IDENTITY() as int)",
                        db.Parameter("@FirstName", "Crazy"),
                        db.Parameter("@LastName",  "Frog"),
                        db.Parameter("@Gender",    "U"))
                    .ExecuteScalar<int>();
            }
        }

        [Test]
        public void Test1()
        {
            int id = Insert();

            SqlQuery<Person> query = new SqlQuery<Person>();

            query.DeleteByKey(id);
        }

        [Test]
        public void Test2()
        {
            int id = Insert();

            using (DbManager db = new DbManager())
            {
                SqlQuery<Person> query = new SqlQuery<Person>();

                Person person = query.SelectByKey(db, id);

                query.Delete(db, person);
            }
        }
    }
}
Both DataAccessor.DeleteByKeySql and DataAccessor.DeleteSql methods generate and execute the following SQL statement:
DELETE FROM
    [Person]
WHERE
    [PersonID] = @PersonID
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


SelectByKey

SelectByKey.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class SelectByKey
    {
        [Test]
        public void Test1()
        {
            SprocQuery<Person> query = new SprocQuery<Person>();

            Person person = query.SelectByKey(1);

            Assert.IsNotNull(person);
        }

        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                SprocQuery<Person> query = new SprocQuery<Person>();

                Person person = query.SelectByKey(db, 1);

                Assert.IsNotNull(person);
            }
        }
    }
}
DataAccessor.SelectByKet method generates and executes the following SQL statement:
exec Person_SelectByKey @id=1
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


SelectAll

SelectAll.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class SelectAll
    {
        [Test]
        public void Test1()
        {
            SprocQuery<Person> query = new SprocQuery<Person>();

            List<Person> list = query.SelectAll();
        }

        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                SprocQuery<Person> query = new SprocQuery<Person>();

                List<Person> list = query.SelectAll(db);
            }
        }
    }
}
DataAccessor.SelectAll method generates and executes the following SQL statement:
exec Person_SelectAll
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Insert

Insert.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class Insert
    {
        [Test]
        public void Test1()
        {
            SprocQuery<Person> query = new SprocQuery<Person>();

            Person person = new Person();

            person.FirstName = "Crazy";
            person.LastName  = "Frog";
            person.Gender    = Gender.Unknown;

            query.Insert(person);
        }

        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                SprocQuery<Person> query = new SprocQuery<Person>();

                Person person = new Person();

                person.FirstName = "Crazy";
                person.LastName  = "Frog";
                person.Gender    = Gender.Other;

                query.Insert(db, person);
            }
        }
    }
}
DataAccessor.Insert method generates and executes the following SQL statement:
exec Person_Insert @FirstName='Crazy', @LastName='Frog', @MiddleName=NULL, @Gender='U'
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Update

Update.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class Update
    {
        int _id;

        [TestFixtureSetUp]
        public void Insert()
        {
            using (DbManager db = new DbManager())
            {
                _id = db
                    .SetCommand(@"
                        INSERT INTO Person (
                             FirstName,  LastName,  Gender
                        ) VALUES (
                            @FirstName, @LastName, @Gender
                        )
                        SELECT Cast(SCOPE_IDENTITY() as int)",
                        db.Parameter("@FirstName", "Crazy"),
                        db.Parameter("@LastName",  "Frog"),
                        db.Parameter("@Gender",    Map.EnumToValue(Gender.Unknown)))
                    .ExecuteScalar<int>();
            }
        }

        [Test]
        public void Test1()
        {
            SprocQuery<Person> query = new SprocQuery<Person>();

            Person person = query.SelectByKey(_id);

            person.Gender = Gender.Other;

            query.Update(person);
        }

        [Test]
        public void Test2()
        {
            using (DbManager db = new DbManager())
            {
                SprocQuery<Person> query = new SprocQuery<Person>();

                Person person = query.SelectByKey(db, _id);

                person.Gender = Gender.Other;

                query.Update(db, person);
            }
        }

        [TestFixtureTearDown]
        public void Delete()
        {
            using (DbManager db = new DbManager())
            {
                db
                    .SetCommand("DELETE FROM Person WHERE PersonID = @id",
                        db.Parameter("@id", _id))
                    .ExecuteNonQuery();
            }
        }
    }
}
DataAccessor.Update method generates and executes the following SQL statement:
exec Person_Update @PersonID=2, @FirstName='Crazy', @LastName='Frog', @MiddleName='', @Gender='O'
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


Delete

Delete.cs
using System;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.DataAccess;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class Delete
    {
        private int Insert()
        {
            using (DbManager db = new DbManager())
            {
                return db
                    .SetCommand(@"
                        INSERT INTO Person (
                             FirstName,  LastName,  Gender
                        ) VALUES (
                            @FirstName, @LastName, @Gender
                        )
                        SELECT Cast(SCOPE_IDENTITY() as int)",
                        db.Parameter("@FirstName", "Crazy"),
                        db.Parameter("@LastName",  "Frog"),
                        db.Parameter("@Gender",    "U"))
                    .ExecuteScalar<int>();
            }
        }

        [Test]
        public void Test1()
        {
            int id = Insert();

            SprocQuery<Person> query = new SprocQuery<Person>();

            query.DeleteByKey(id);
        }

        [Test]
        public void Test3()
        {
            int id = Insert();

            using (DbManager db = new DbManager())
            {
                SqlQuery<Person> query = new SqlQuery<Person>();

                Person person = query.SelectByKey(db, id);

                query.Delete(db, person);
            }
        }
    }
}
Both DataAccessor.DeleteByKey and DataAccessor.Delete methods generate and execute the following SQL statement:
exec Person_Delete @PersonID=2
Person.cs
using System;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public class Person
    {
        [MapField("PersonID"), PrimaryKey, NonUpdatable]
        public int    ID;

        public string LastName;
        public string FirstName;
        public string MiddleName;
        public Gender Gender;
    }
}
Gender.cs
using System;

using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    public enum Gender
    {
        [MapValue("F")] Female,
        [MapValue("M")] Male,
        [MapValue("U")] Unknown,
        [MapValue("O")] Other
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


NonUpdatable

The NonUpdatable attribute indicates the field that should not be updated by UPDATE or INSERT SQL statements.

NonUpdatable.cs
using System;

using NUnit.Framework;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class NonUpdatable
    {
        public enum Gender
        {
            [MapValue("F")] Female,
            [MapValue("M")] Male,
            [MapValue("U")] Unknown,
            [MapValue("O")] Other
        }

        public class Person
        {
            [MapField("PersonID"), PrimaryKey, NonUpdatable]
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;
            public Gender Gender;
        }

        [Test]
        public void Test()
        {
            SqlQuery<Person> query = new SqlQuery<Person>();

            Person person = new Person();

            person.FirstName = "Crazy";
            person.LastName  = "Frog";
            person.Gender    = Gender.Other;

            query.Insert(person);
        }
    }
}
DataAccessor.Insert method generates and executes the following SQL statement:
INSERT INTO [Person] (
    [MiddleName],
    [Gender],
    [LastName],
    [FirstName]
) VALUES (
    @MiddleName,
    @Gender,
    @LastName,
    @FirstName
)
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


PrimaryKey

The PrimaryKey attribute indicates the field that is a part of a primary key.

PrimaryKey.cs
using System;

using NUnit.Framework;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class PrimaryKey
    {
        public class Person
        {
            [MapField("PersonID"), PrimaryKey, NonUpdatable]
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;
        }

        [Test]
        public void Test()
        {
            SqlQuery<Person> da = new SqlQuery<Person>();

            Person person = da.SelectByKey(1);

            Assert.IsNotNull(person);
        }
    }
}
DataAccessor.SelectByKeySql method generates and executes the following SQL statement:
SELECT
    [MiddleName],
    [PersonID],
    [LastName],
    [FirstName]
FROM
    [Person]
WHERE
    [PersonID] = @PersonID
MultiplePrimaryKey.cs
using System;

using NUnit.Framework;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class MultiplePrimaryKey
    {
        [TableName("Person")]
        public class Person
        {
            [MapField("PersonID"), NonUpdatable]
            public int    ID;

            // These fields are not real primary key of the table.
            // They are made primary key for demonstration purpose only.
            //
            [PrimaryKey(1)] public string FirstName;
            [PrimaryKey(2)] public string LastName;

            public string MiddleName;
        }

        [Test]
        public void Test()
        {
            SqlQuery<Person> query = new SqlQuery<Person>();

            Person person = query.SelectByKey("Tester", "Testerson");

            Assert.IsNotNull(person);
        }
    }
}
In this case DataAccessor.SelectByKeySql method generates and executes the following SQL statement:
SELECT
    [MiddleName],
    [PersonID],
    [LastName],
    [FirstName]
FROM
    [Person]
WHERE
    [FirstName] = @FirstName AND
    [LastName] = @LastName
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


TableName

The TableName attribute specifies a name of the table represented by the decorated object.

TableName.cs
using System;

using NUnit.Framework;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class TableName
    {
        [TableName("Person")]
        public class MyPersonObject
        {
            [MapField("PersonID"), PrimaryKey, NonUpdatable]
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;
        }

        [Test]
        public void Test1()
        {
            SqlQuery<MyPersonObject> query = new SqlQuery<MyPersonObject>();

            MyPersonObject person = query.SelectByKey(1);

            Assert.IsNotNull(person);
        }

        [Test]
        public void Test2()
        {
            SprocQuery<MyPersonObject> query = new SprocQuery<MyPersonObject>();

            MyPersonObject person = query.SelectByKey(1);

            Assert.IsNotNull(person);
        }
    }
}
DataAccessor.SelectByKeySql and DataAccessor.SelectByKey methods generate and execute the following SQL statement:
-- SelectByKeySql
SELECT
    [MiddleName],
    [PersonID],
    [LastName],
    [FirstName]
FROM
    [Person]
WHERE
    [PersonID] = @PersonID

-- SelectByKey
exec Person_SelectByKey @id=1
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


XmlExtension

BLToolkit supports different ways to provide metadata information for business objects. One of the ways is using attributes. Another one is XmlExtension. XmlExtension is a simple xml file where you can specify a type and its attributes.

XmlExtension.cs
using NUnit.Framework;

using BLToolkit.DataAccess;
using BLToolkit.Mapping;
using BLToolkit.Reflection.Extension;

namespace HowTo.DataAccess
{
    [TestFixture]
    public class XmlExtension
    {
        public class MyPersonObject
        {
            [MapField("PersonID")]
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;
        }

        [Test]
        public void Test()
        {
            SqlQuery<MyPersonObject> query = new SqlQuery<MyPersonObject>();

            query.Extensions = TypeExtension.GetExtensions("XmlExtension.xml");

            MyPersonObject person = query.SelectByKey(1);

            Assert.IsNotNull(person);
        }
    }
}
XmlExtension.xml
<?xml version="1.0" encoding="utf-8" ?>
<Types
    xmlns    = "urn:schemas-bltoolkit-net:typeext"
    xmlns:da = "urn:schemas-bltoolkit-net:dataaccess">

    <Type Name="MyPersonObject" da:TableName="Person">
        <Member Name="ID" da:PrimaryKey="1" da:NonUpdatable="1" />
    </Type>
</Types>
DataAccessor.SelectByKetSql method generates and executes the following SQL statement:
SELECT
    [MiddleName],
    [PersonID],
    [LastName],
    [FirstName]
FROM
    [Person]
WHERE
    [PersonID] = @PersonID
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


EditableObjects

The EditableObjects namespace contains classes that can help you to make your object model editable. That means your objects will support the following list of standard and BLToolkit interfaces:
for business objects
namespace | interface
System.ComponentModel | IEditableObject
System.ComponentModel | INotifyPropertyChanged
System.ComponentModel | ICustomTypeDescriptor
System | ICloneable
BLToolkit.ComponentModel | INotifyObjectEdit
BLToolkit.EditableObjects | IEditable
BLToolkit.Validation | IValidatable
for collections
namespace | interface
System.ComponentModel | ITypedList
System.ComponentModel | IBindingList
System.ComponentModel | IBindingListView
System.ComponentModel | ICancelAddNew
BLToolkit.ComponentModel | ISortable
BLToolkit.EditableObjects | IEditable

• EditableObject
• Accept and Reject changes
• IsDirty flag


EditableObject class

EditableObject is an object that provides functionality to commit and rollback changes to itself. After verifying the accuracy of changes made to the object, you can accept the changes using the AcceptChanges method of the object, which will set the Current field values to be the Original values. The RejectChanges method rejects all changes made to the object since AcceptChanges was last called. The IsDirty property gets a value that indicates whether the object has changed.

If we wrote an editable object manually, we could get the following code just for two editable properties:

public class TestObject : INotifyPropertyChanged
{
    // The FirstName editable property.
    //
    private string _originalFirstName;
    private string _currentFirstName;

    public override string FirstName
    {
        get { return _currentFirstName; }
        set
        {
            _currentFirstName = value;
            OnPropertyChanged("FirstName");
        }
    }

    bool IsFirstNameDirty
    {
        get { return _currentFirstName != _originalFirstName; }
    }

    void AcceptFirstNameChange()
    {
        if (IsFirstNameDirty)
        {
            _originalFirstName = _currentFirstName;
            OnPropertyChanged("FirstName");
        }
    }

    void RejectFirstNameChange()
    {
        if (IsFirstNameDirty)
        {
            _currentFirstName = _originalFirstName;
            OnPropertyChanged("FirstName");
        }
    }

    // The LastName editable property.
    //
    private string _originalLastName;
    private string _currentLastName;

    public override string LastName
    {
        get { return _currentLastName; }
        set
        {
            _currentLastName = value;
            OnPropertyChanged("LastName");
        }
    }

    bool IsLastNameDirty
    {
        get { return _currentLastName != _originalLastName; }
    }

    void AcceptLastNameChange()
    {
        if (IsLastNameDirty)
        {
            _originalLastName = _currentLastName;
            OnPropertyChanged("LastName");
        }
    }

    void RejectLastNameChange()
    {
        if (IsLastNameDirty)
        {
            _currentLastName = _originalLastName;
            OnPropertyChanged("LastName");
        }
    }

    // Common members.
    //
    public bool IsDirty
    {
        get
        {
            return IsFirstNameChange || IsLastNameChange;
        }
    }

    public void AcceptChanges()
    {
        AcceptFirstNameChange();
        AcceptLastNameChange();
    }

    public void RejectChanges()
    {
        RejectFirstNameChange();
        RejectLastNameChange();
    }

    public virtual event PropertyChangedEventHandler PropertyChanged;

    protected virtual void OnPropertyChanged(string propertyName)
    {
        if (PropertyChanged != null)
            PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
    }
}

BLToolkit allows implementing the same functionality by inheriting your object from the BLToolkit EditableObject base class and replacing editable members with abstract properties.

EditableObjectTest.cs
using System;
using NUnit.Framework;
using BLToolkit.EditableObjects;

namespace HowTo.EditableObjects
{
    [TestFixture]
    public class EditableObjectTest
    {
        public abstract class TestObject : EditableObject<TestObject>
        {
            // Any abstract property becomes editable.
            //
            public abstract string FirstName { get; set; }
            public abstract string LastName  { get; set; }

            // This field is not editable.
            //
            public string FullName
            {
                get { return string.Format("{0} {1}", FirstName, LastName); }
            }
        }

        [Test]
        public void Test()
        {
            TestObject obj = TestObject.CreateInstance();

            obj.FirstName = "Tester";
            obj.LastName  = "Testerson";

            Assert.IsTrue(obj.IsDirty);

            obj.AcceptChanges();

            Assert.IsFalse(obj.IsDirty);
        }
    }
}

BLToolkit type builder will generate the following for the class above:

[BLToolkitGenerated]
public sealed class TestObject : EditableObjectTest.TestObject, IEditable, IMemberwiseEditable, IPrintDebugState
{
    // Note that the internal representation of the properties is EditableValue<string>.
    // The EditableValue class provides a mechanism to keep and control the field value state.
    //
    private EditableValue<string> _firstName;
    private EditableValue<string> _lastName;

    // PropertyInfo is used for internal purposes.
    //
    private static PropertyInfo _firstName_propertyInfo =
        TypeHelper.GetPropertyInfo(typeof(EditableObjectTest.TestObject), "FirstName", typeof(string), Type.EmptyTypes);
    private static PropertyInfo _lastName_propertyInfo  =
        TypeHelper.GetPropertyInfo(typeof(EditableObjectTest.TestObject), "LastName",  typeof(string), Type.EmptyTypes);

    // Constructors.
    //
    public TestObject()
    {
        this._firstName = new EditableValue<string>("");
        this._lastName  = new EditableValue<string>("");
    }

    public TestObject(InitContext ctx)
    {
        this._firstName = new EditableValue<string>("");
        this._lastName  = new EditableValue<string>("");
    }

    // Abstract property implementation.
    //
    public override string FirstName
    {
        get
        {
            return _firstName.Value;
        }

        set
        {
            _firstName.Value = value;

            // The PropertyChanged event support.
            //
            ((IPropertyChanged)this).OnPropertyChanged(_firstName_propertyInfo);
        }
    }

    public override string LastName
    {
        get
        {
            return _lastName.Value;
        }

        set
        {
            _lastName.Value = value;
            ((IPropertyChanged)this).OnPropertyChanged(_lastName_propertyInfo);
        }
    }

    // The IEditable interface implementation.
    //
    bool IEditable.IsDirty
    {
        get { return _firstName.IsDirty || _lastName.IsDirty; }
    }

    void IEditable.AcceptChanges()
    {
        this._firstName.AcceptChanges();
        this._lastName. AcceptChanges();
    }

    void IEditable.RejectChanges()
    {
        this._firstName.RejectChanges();
        this._lastName. RejectChanges();
    }

    // The IMemberwiseEditable interface implementation.
    //
    bool IMemberwiseEditable.AcceptMemberChanges(PropertyInfo propertyInfo, string memberName)
    {
        return
            _firstName.AcceptMemberChanges(_firstName_propertyInfo, memberName) ||
            _lastName. AcceptMemberChanges(_lastName_propertyInfo,  memberName);
    }

    void IMemberwiseEditable.GetDirtyMembers(PropertyInfo propertyInfo, ArrayList list)
    {
        _firstName.GetDirtyMembers(_firstName_propertyInfo, list);
        _lastName. GetDirtyMembers(_lastName_propertyInfo,  list);
    }

    bool IMemberwiseEditable.IsDirtyMember(PropertyInfo propertyInfo, string memberName, ref bool isDirty)
    {
        return
            _firstName.IsDirtyMember(_firstName_propertyInfo, memberName, ref isDirty) ||
            _lastName. IsDirtyMember(_lastName_propertyInfo,  memberName, ref isDirty);
    }

    bool IMemberwiseEditable.RejectMemberChanges(PropertyInfo propertyInfo, string memberName)
    {
        return
            _firstName.RejectMemberChanges(_firstName_propertyInfo, memberName) ||
            _lastName. RejectMemberChanges(_lastName_propertyInfo,  memberName);
    }

    // The IPrintDebugState interface implementation.
    //
    void IPrintDebugState.PrintDebugState(PropertyInfo propertyInfo, ref string str)
    {
        _firstName.PrintDebugState(_firstName_propertyInfo, ref str);
        _lastName. PrintDebugState(_lastName_propertyInfo,  ref str);
    }
}


Accept and Reject changes

After verifying the accuracy of changes made to the object, you can accept the changes using the AcceptChanges method of the object, which will set the Current field values to be the Original values. The RejectChanges method rejects all changes made to the object since AcceptChanges was last called.

AcceptRejectChanges.cs
using System;
using NUnit.Framework;
using BLToolkit.EditableObjects;

namespace HowTo.EditableObjects
{
    [TestFixture]
    public class AcceptRejectChanges
    {
        public abstract class TestObject : EditableObject<TestObject>
        {
            public abstract string FirstName { get; set; }
            public abstract string LastName  { get; set; }
        }

        [Test]
        public void Test()
        {
            // Create an instance.
            //
            TestObject obj = TestObject.CreateInstance();

            // Accept changes.
            //
            obj.FirstName = "Tester";
            obj.LastName  = "Testerson";

            Assert.IsTrue(obj.IsDirty);

            obj.AcceptChanges();

            Assert.AreEqual("Tester", obj.FirstName);
            Assert.IsFalse(obj.IsDirty);

            // Reject changes.
            //
            obj.FirstName = "Developer";

            Assert.IsTrue(obj.IsDirty);

            obj.RejectChanges();

            Assert.AreEqual("Tester", obj.FirstName);
            Assert.IsFalse(obj.IsDirty);
        }
    }
}


IsDirty flag

The IsDirty property gets a value that indicates whether the object has changed.

IsDirty.cs
using System;
using NUnit.Framework;
using BLToolkit.EditableObjects;

namespace HowTo.EditableObjects
{
    [TestFixture]
    public class IsDirty
    {
        public abstract class TestObject : EditableObject<TestObject>
        {
            public abstract string FirstName { get; set; }
            public abstract string LastName  { get; set; }
        }

        [Test]
        public void Test()
        {
            TestObject obj = TestObject.CreateInstance();

            Assert.IsFalse(obj.IsDirty);

            obj.FirstName = "Tester";
            obj.LastName  = "Testerson";

            Assert.IsTrue(obj.IsDirty);

            obj.AcceptChanges();

            Assert.IsFalse(obj.IsDirty);
        }
    }
}


Mapping

• ExecuteObject | How to map DB source to object.
• ObjectToObject | How to map object to object.
Enumerator mapping.
• EnumToValue | How to map enumerator to value.
• ValueToEnum | How to map value to enumerator.
Mapping attributes.
• MapFieldAttribute
• MapValueAttribute
Custom mapping.
• MapToJson


EnumToValue

EnumToValue.cs
using System;

using NUnit.Framework;

using BLToolkit.Mapping;

namespace HowTo.Mapping
{
    [TestFixture]
    public class EnumToValue
    {
        public enum Gender1
        {
            [MapValue("F")] Female,
            [MapValue("M")] Male,
            [MapValue("U")] Unknown,
            [MapValue("O")] Other
        }

        [Test]
        public void Test1()
        {
            object value = Map.EnumToValue(Gender1.Male);

            Assert.AreEqual("M", value);
        }

        public enum Gender2
        {
            [MapValue(1)] Female,
            [MapValue(2)] Male,
            [MapValue(3)] Unknown,
            [MapValue(4)] Other
        }

        [Test]
        public void Test2()
        {
            object value = Map.EnumToValue(Gender2.Male);

            Assert.AreEqual(2, value);
        }

        public enum Gender3
        {
            Female  = 1,
            Male    = 2,
            Unknown = 3,
            Other   = 4
        }

        [Test]
        public void Test3()
        {
            object value = (int)Map.EnumToValue(Gender3.Male);

            Assert.AreEqual(2, value);
        }
    }
}


MapFieldAttribute

Note that the mapper tries to convert the source values to their destination types.

ObjectToObject.cs
using System;

using NUnit.Framework;

using BLToolkit.Mapping;

namespace HowTo.Mapping
{
    [TestFixture]
    public class ObjectToObject
    {
        public class SourceObject
        {
            public bool   Value1   = true;
            public string Value2   = "10";
            public string StrValue = "test";
        }

        public class DestObject
        {
            [MapField("Value1")] public bool   BoolValue;
            [MapField("Value2")] public int    IntValue;

            // If the source and destination field/property names are equal,
            // there is no need for using the MapField attribute.
            //
                                 public string StrValue;
        }

        [Test]
        public void Test1()
        {
            SourceObject source = new SourceObject();
            DestObject   dest   = Map.ObjectToObject<DestObject>(source);

            Assert.AreEqual(true,   dest.BoolValue);
            Assert.AreEqual(10,     dest.IntValue);
            Assert.AreEqual("test", dest.StrValue);
        }
    }
}
To map data to inner objects the MapField attribute can be used in the following way:

MapFieldAttribute.cs
using System;

using NUnit.Framework;

using BLToolkit.Mapping;

namespace HowTo.Mapping
{
    [TestFixture]
    public class MapField
    {
        public class SourceObject1
        {
            public string Street = "1 Main";
            public string City   = "Bigtown";
            public string State  = "XX";
            public string Zip    = "00000";
        }

        public class Address
        {
            public string Street;
            public string City;
            public string State;
            public string Zip;
        }

        [MapField("Street", "Address.Street")]
        [MapField("City",   "Address.City")]
        [MapField("State",  "Address.State")]
        [MapField("Zip",    "Address.Zip")]
        public class Order1
        {
            public Address Address = new Address();
        }

        [Test]
        public void MapFieldTest1()
        {
            SourceObject1 source = new SourceObject1();
            Order1        order  = Map.ObjectToObject<Order1>(source);

            Assert.AreEqual("1 Main",  order.Address.Street);
            Assert.AreEqual("Bigtown", order.Address.City);
            Assert.AreEqual("XX",      order.Address.State);
            Assert.AreEqual("00000",   order.Address.Zip);
        }

        public class SourceObject2
        {
            public string BillingStreet = "1 Main";
            public string BillingCity   = "Bigtown";
            public string BillingState  = "XX";
            public string BillingZip    = "00000";

            public string ShippingStreet = "2 Main";
            public string ShippingCity   = "Bigtown";
            public string ShippingState  = "XX";
            public string ShippingZip    = "00000";
        }

        public class Order2
        {
            [MapField(Format="Billing{0}")]
            public Address BillingAddress = new Address();

            [MapField(Format="Shipping{0}")]
            public Address ShippingAddress = new Address();
        }

        [Test]
        public void MapFieldTest2()
        {
            SourceObject2 source = new SourceObject2();
            Order2        order  = Map.ObjectToObject<Order2>(source);

            Assert.AreEqual("1 Main",  order.BillingAddress.Street);
            Assert.AreEqual("Bigtown", order.BillingAddress.City);
            Assert.AreEqual("XX",      order.BillingAddress.State);
            Assert.AreEqual("00000",   order.BillingAddress.Zip);

            Assert.AreEqual("2 Main",  order.ShippingAddress.Street);
            Assert.AreEqual("Bigtown", order.ShippingAddress.City);
            Assert.AreEqual("XX",      order.ShippingAddress.State);
            Assert.AreEqual("00000",   order.ShippingAddress.Zip);
        }
    }
}


MapToJson

MapToJson.cs
using System;
using System.Globalization;
using System.Text;
using System.Xml;

using NUnit.Framework;

using BLToolkit.Mapping;
using BLToolkit.Reflection;

namespace HowTo.Mapping
{
    public class JsonMapper : MapDataDestinationBase, IMapDataDestinationList, ISupportMapping
    {
        private static readonly long   InitialJavaScriptDateTicks = new DateTime(1970, 1, 1).Ticks;

        private string[]               _fieldNames;
        private readonly StringBuilder _sb;
        private MappingSchema          _mappingSchema;
        private bool                   _scalar;
        private bool                   _first;
        private bool                   _firstElement;
        private int                    _indent;

        public JsonMapper() : this(new StringBuilder(), 0)
        {
        }

        public JsonMapper(StringBuilder sb) : this(sb, 0)
        {
        }

        public JsonMapper(StringBuilder sb, int indent)
        {
            _sb     = sb;
            _indent = indent;
        }

        public override Type GetFieldType(int index)
        {
            // Same as typeof(object)
            //
            return null;
        }

        public override int GetOrdinal(string name)
        {
            return Array.IndexOf(_fieldNames, name);
        }

        public override void SetValue(object o, int index, object value)
        {
            SetValue(o, _fieldNames[index], value);
        }

        public override void SetValue(object o, string name, object value)
        {
            if (!_scalar)
            {
                // Do not Json null values until it's an array
                //
                if (value == null || (value is XmlNode && IsEmptyNode((XmlNode)value)))
                    return;

                if (_first)
                    _first = false;
                else
                    _sb
                        .Append(',')
                        .AppendLine()
                        ;

                for (int i = 0; i < _indent; ++i)
                    _sb.Append(' ');

                _sb
                    .Append('"')
                    .Append(name)
                    .Append("\":")
                    ;
            }

            if (value == null)
                _sb.Append("null");
            else
            {
                switch (Type.GetTypeCode(value.GetType()))
                {
                    case TypeCode.Empty:
                    case TypeCode.DBNull:
                        _sb.Append("null");
                        break;
                    case TypeCode.Boolean:
                        _sb.Append((bool)value? "true": "false");
                        break;
                    case TypeCode.Char:
                        _sb
                            .Append('\'')
                            .Append((char)value)
                            .Append('\'')
                            ;
                        break;
                    case TypeCode.SByte:
                    case TypeCode.Int16:
                    case TypeCode.Int32:
                    case TypeCode.Int64:
                    case TypeCode.Byte:
                    case TypeCode.UInt16:
                    case TypeCode.UInt32:
                    case TypeCode.UInt64:
                    case TypeCode.Single:
                    case TypeCode.Double:
                    case TypeCode.Decimal:
                        _sb.Append(((IFormattable)value).ToString(null, CultureInfo.InvariantCulture));
                        break;
                    case TypeCode.DateTime:
                        _sb
                            .Append("new Date(")
                            .Append((((DateTime)value).Ticks - InitialJavaScriptDateTicks)/10000)
                            .Append(")");
                        break;
                    case TypeCode.String:
                        _sb
                            .Append('"')
                            .Append(encode((string)value))
                            .Append('"')
                            ;
                        break;
                    default:
                        if (value is XmlNode)
                        {
                            if (IsEmptyNode((XmlNode) value))
                                _sb.Append("null");
                            else
                                WriteXmlJson((XmlNode)value);
                        }
                        else
                        {
                            JsonMapper inner = new JsonMapper(_sb, _indent + 1);

                            if (value.GetType().IsArray)
                                _mappingSchema.MapSourceListToDestinationList(
                                    _mappingSchema.GetDataSourceList(value), inner);
                            else
                                _mappingSchema.MapSourceToDestination(
                                    _mappingSchema.GetDataSource(value), value, inner, inner);
                        }
                        break;
                }
            }
        }

        private static string encode(string value)
        {
            return value.Replace("\r\n", "\\r")
                .Replace("\n\r", "\\r")
                .Replace("\n", "\\r")
                .Replace("\r", "\\r")
                .Replace("\"","\\\"");
        }

        private void WriteXmlJson(XmlNode node)
        {
            XmlNode textNode = GetTextNode(node);
            if (textNode != null)
            {
                _sb
                    .Append("\"")
                    .Append(encode(textNode.Value))
                    .Append('\"')
                    ;
            }
            else
            {

                bool first = true;

                _sb.Append('{');

                if (node.Attributes != null)
                {
                    foreach (XmlAttribute attr in node.Attributes)
                    {
                        if (first)
                            first = false;
                        else
                            _sb.Append(',');

                        _sb
                            .Append("\"@")
                            .Append(attr.Name)
                            .Append("\":\"")
                            .Append(encode(attr.Value))
                            .Append('\"')
                            ;
                    }
                }

                foreach (XmlNode child in node.ChildNodes)
                {
                    if (IsWhitespace(child) || IsEmptyNode(child))
                        continue;

                    if (first)
                        first = false;
                    else
                        _sb.Append(',');

                    if (child is XmlText)
                        _sb
                            .Append("\"#text\":\"")
                            .Append(encode(child.Value))
                            .Append('\"')
                            ;
                    else if (child is XmlElement)
                    {
                        _sb
                            .Append('"')
                            .Append(child.Name)
                            .Append("\":")
                            ;
                        WriteXmlJson(child);
                    }
                    else
                        System.Diagnostics.Debug.Fail("Unexpected node type " + child.GetType().FullName);
                }
                _sb.Append('}');
            }
        }

        private static bool IsWhitespace(XmlNode node)
        {
            switch (node.NodeType)
            {
                case XmlNodeType.Comment:
                case XmlNodeType.Whitespace:
                case XmlNodeType.SignificantWhitespace:
                    return true;
            }
            return false;
        }

        private static bool IsEmptyNode(XmlNode node)
        {
            if (node.Attributes != null && node.Attributes.Count > 0)
                return false;

            if (node.HasChildNodes)
                foreach (XmlNode childNode in node.ChildNodes)
                {
                    if (IsWhitespace(childNode) || IsEmptyNode(childNode))
                        continue;

                    // Not a whitespace, nor inner empty node.
                    //
                    return false;
                }

            return node.Value == null;
        }

        private static XmlNode GetTextNode(XmlNode node)
        {
            if (node.Attributes != null && node.Attributes.Count > 0)
                return null;

            XmlNode textNode = null;

            foreach (XmlNode childNode in node.ChildNodes)
            {
                // Ignore all whitespace.
                //
                if (IsWhitespace(childNode))
                    continue;

                if (childNode is XmlText)
                {
                    // More then one text node.
                    //
                    if (textNode != null)
                        return null;

                    // First text node.
                    //
                    textNode = childNode;
                }
                else
                    // Not a text node - break;
                    //
                    return null;
            }

            return textNode;
        }

        #region ISupportMapping Members

        void ISupportMapping.BeginMapping(InitContext initContext)
        {
            _first         = true;
            _mappingSchema = initContext.MappingSchema;
            _fieldNames    = new string[initContext.DataSource.Count];

            for (int i = 0; i < _fieldNames.Length; ++i)
                _fieldNames[i] = initContext.DataSource.GetName(i);

            _scalar = _fieldNames.Length == 1 && string.IsNullOrEmpty(_fieldNames[0]);

            if (_scalar)
                return;

            if (_fieldNames.Length <= 1)
            {
                // Reset the indent since output is a single line.
                //
                _indent = 0;
                _sb.Append('{');
            }
            else
            {
                if (_indent > 0)
                    _sb.AppendLine();

                for (int i = 0; i < _indent; ++i)
                    _sb.Append(' ');

                _sb
                    .Append('{')
                    .AppendLine()
                    ;
            }
        }

        void ISupportMapping.EndMapping(InitContext initContext)
        {
            if (_scalar)
                return;

            if (_fieldNames.Length > 1)
                _sb.AppendLine();

            for (int i = 0; i < _indent; ++i)
                _sb.Append(' ');
            _sb.Append('}');
        }

        #endregion

        #region IMapDataDestinationList Members

        void IMapDataDestinationList.InitMapping(InitContext initContext)
        {
            _firstElement = true;
            _sb.Append('[');
        }

        IMapDataDestination IMapDataDestinationList.GetDataDestination(InitContext initContext)
        {
            return this;
        }

        object IMapDataDestinationList.GetNextObject(InitContext initContext)
        {
            if (_firstElement)
                _firstElement = false;
            else
                _sb.Append(',');

            return this;
        }

        void IMapDataDestinationList.EndMapping(InitContext initContext)
        {
            _sb.Append(']');
        }

        #endregion

        public override string ToString()
        {
            return _sb.ToString();
        }
    }

    [TestFixture]
    public class MapToJson
    {
        public class Inner
        {
            public string Name = "inner \"object \n name";
        }

        public class Inner2
        {
            public string Name;
            public int    Value;
        }

        public class SourceObject
        {
            public string   Foo = "Foo";
            public double   Bar  = 1.23;
            public DateTime Baz  = DateTime.Today;
            [MapIgnore(false)]
            public Inner    Inner = new Inner();
            [MapIgnore(false)]
            public Inner2   Inner2 = new Inner2();
            public string[] StrArray = {"One", "Two", "Three"};
        }

        [Test]
        public void Test()
        {
            JsonMapper    jm = new JsonMapper(new StringBuilder(256));

            Map.MapSourceToDestination(Map.GetObjectMapper(typeof(SourceObject)), new SourceObject(), jm, jm);
            Console.Write(jm.ToString());

            // Expected output:
            //
            // {
            // "Foo":"Foo",
            // "Bar":1.23,
            // "Baz":new Date(11823840000000000),
            // "Inner":{ "Name":"inner \"object \r name"},
            // "Inner2":
            //  {
            //  "Name":null,
            //  "Value":0
            //  },
            //  "StrArray":["One","Two","Three"]
            // }
        }
    }
}


MapValueAttribute

MapValueAttribute1.cs
using System;

using NUnit.Framework;

using BLToolkit.Mapping;

namespace HowTo.Mapping
{
    [TestFixture]
    public class MapValue1
    {
        public class SourceObject
        {
            public string Value = "Y";
        }

        public class TestObject1
        {
            // The attribute is applied to a field/property.
            //
            [MapValue(true,  "Y")]
            [MapValue(false, "N")]
            public bool Value;
        }

        [Test]
        public void Test1()
        {
            SourceObject so = new SourceObject();
            TestObject1  to = Map.ObjectToObject<TestObject1>(so);

            Assert.AreEqual(true, to.Value);
        }

        // The attribute is applied to a class.
        //
        [MapValue(true,  "Y")]
        [MapValue(false, "N")]
        public class TestObject2
        {
            public bool Value;
        }

        [Test]
        public void Test2()
        {
            SourceObject so = new SourceObject();
            TestObject2  to = Map.ObjectToObject<TestObject2>(so);

            Assert.AreEqual(true, to.Value);
        }

        // The attribute is applied to a base class and affects all child classes.
        //
        [MapValue(typeof(bool), true,  "Y")]
        [MapValue(typeof(bool), false, "N")]
        public class ObjectBase
        {
        }

        public class TestObject3 : ObjectBase
        {
            public bool Value;
        }

        [Test]
        public void Test3()
        {
            SourceObject so = new SourceObject();
            TestObject3  to = Map.ObjectToObject<TestObject3>(so);

            Assert.AreEqual(true, to.Value);
        }
    }
}
MapValueAttribute2.cs
using System;

using NUnit.Framework;

using BLToolkit.Mapping;

namespace HowTo.Mapping
{
    [TestFixture]
    public class MapValue2
    {
        public enum Gender1
        {
            [MapValue("F")] Female,
            [MapValue("M")] Male,
            [MapValue("U")] Unknown,
            [MapValue("O")] Other
        }

        [Test]
        public void Test1()
        {
            object value = Map.EnumToValue(Gender1.Male);

            Assert.AreEqual("M", value);
        }

        [MapValue(Gender2.Female,  1)]
        [MapValue(Gender2.Male,    2)]
        [MapValue(Gender2.Unknown, 3)]
        [MapValue(Gender2.Other,   4)]
        public enum Gender2
        {
            Female,
            Male,
            Unknown,
            Other
        }

        [Test]
        public void Test2()
        {
            Gender2 g = Map.ToEnum<Gender2>(2);

            Assert.AreEqual(Gender2.Male, g);
        }
    }
}


ObjectToObject

Note that the mapper tries to convert the source values to their destination types.

ObjectToObject.cs
using System;

using NUnit.Framework;

using BLToolkit.Mapping;

namespace HowTo.Mapping
{
    [TestFixture]
    public class ObjectToObject
    {
        public class SourceObject
        {
            public bool   Value1   = true;
            public string Value2   = "10";
            public string StrValue = "test";
        }

        public class DestObject
        {
            [MapField("Value1")] public bool   BoolValue;
            [MapField("Value2")] public int    IntValue;

            // If the source and destination field/property names are equal,
            // there is no need for using the MapField attribute.
            //
                                 public string StrValue;
        }

        [Test]
        public void Test1()
        {
            SourceObject source = new SourceObject();
            DestObject   dest   = Map.ObjectToObject<DestObject>(source);

            Assert.AreEqual(true,   dest.BoolValue);
            Assert.AreEqual(10,     dest.IntValue);
            Assert.AreEqual("test", dest.StrValue);
        }
    }
}


ValueToEnum

ValueToEnum.cs
using System;

using NUnit.Framework;

using BLToolkit.Mapping;

namespace HowTo.Mapping
{
    [TestFixture]
    public class ValueToEnum
    {
        public enum Gender1
        {
            [MapValue("F")] Female,
            [MapValue("M")] Male,
            [MapValue("U")] Unknown,
            [MapValue("O")] Other
        }

        [Test]
        public void Test1()
        {
            Gender1 g = Map.ToEnum<Gender1>("M");

            Assert.AreEqual(Gender1.Male, g);
        }

        public enum Gender2
        {
            [MapValue(1)] Female,
            [MapValue(2)] Male,
            [MapValue(3)] Unknown,
            [MapValue(4)] Other
        }

        [Test]
        public void Test2()
        {
            Gender2 g = Map.ToEnum<Gender2>(2);

            Assert.AreEqual(Gender2.Male, g);
        }

        public enum Gender3
        {
            Female  = 1,
            Male    = 2,
            Unknown = 3,
            Other   = 4
        }

        [Test]
        public void Test3()
        {
            Gender3 g = Map.ToEnum<Gender3>(2);

            Assert.AreEqual(Gender3.Male, g);
        }
    }
}


Reflection

• Emit
• ObjectFactory


Emit

• Hello, World! EmitHelper in action.


HelloWorld

The System.Reflection.Emit namespace provides classes to create dynamic assemblies at runtime. It allows compilers and tools to emit MSIL, execute it and store it to a disk. Although Emit is a powerful tool, it is also extremely hard to use.

Let us take a look at the following example, which demonstrates the "normal" way of emit programming:

HelloWorld.cs
using System;
using System.Reflection;
using System.Reflection.Emit;
using System.Threading;

using NUnit.Framework;

namespace Examples.Reflection.Emit
{
    [TestFixture]
    public class HelloWorldNormal
    {
        public interface IHello
        {
            void SayHello(string toWhom);
        }

        [Test]
        public void Test()
        {
            AssemblyName asmName = new AssemblyName();

            asmName.Name = "HelloWorld";

            AssemblyBuilder asmBuilder =
                Thread.GetDomain().DefineDynamicAssembly(asmName, AssemblyBuilderAccess.RunAndSave);

            ModuleBuilder   modBuilder  = asmBuilder.DefineDynamicModule("HelloWorld");

            TypeBuilder     typeBuilder = modBuilder.DefineType(
                "Hello",
                TypeAttributes.Public,
                typeof(object),
                new Type[] { typeof(IHello) });

            MethodBuilder   methodBuilder = typeBuilder.DefineMethod("SayHello",
                                    MethodAttributes.Private | MethodAttributes.Virtual,
                                    typeof(void),
                                    new Type[] { typeof(string) });

            typeBuilder.DefineMethodOverride(methodBuilder, typeof(IHello).GetMethod("SayHello"));

            ILGenerator il = methodBuilder.GetILGenerator();

            // string.Format("Hello, {0} World!", toWhom)
            //
            il.Emit(OpCodes.Ldstr, "Hello, {0} World!");
            il.Emit(OpCodes.Ldarg_1);
            il.Emit(OpCodes.Call, typeof(string).GetMethod("Format", new Type[] { typeof(string), typeof(object) }));

            // Console.WriteLine("Hello, World!");
            //
            il.Emit(OpCodes.Call, typeof(Console).GetMethod("WriteLine", new Type[] { typeof(string) }));
            il.Emit(OpCodes.Ret);

            Type   type  = typeBuilder.CreateType();

            IHello hello = (IHello)Activator.CreateInstance(type);

            hello.SayHello("Emit");
        }
    }
}

Note that the Emit method takes one parameter as an OpCode and optionally another one, which does not depend on the current operation context. So, this way is not entirely type safe.

Fortunately, there is an alternative way. BLToolkit provides a helper class, EmitHelper, which can make your life a little bit easy. It contains typed wrapper methods for almost all emit commands and allows writing source code that looks similar to MSIL.

The following examples show how to use the EmitHelper class with C#, VB.NET, and C++/CLI.

HelloWorld.cs
using System;

using NUnit.Framework;

using BLToolkit.Reflection;
using BLToolkit.Reflection.Emit;

namespace Examples.Reflection.Emit
{
    [TestFixture]
    public class HelloWorld
    {
        public interface IHello
        {
            void SayHello(string toWhom);
        }

        [Test]
        public void Test()
        {
            EmitHelper emit = new AssemblyBuilderHelper("HelloWorld.dll")
                .DefineType  ("Hello", typeof(object), typeof(IHello))
                .DefineMethod(typeof(IHello).GetMethod("SayHello"))
                .Emitter;

            emit
                // string.Format("Hello, {0}!", toWhom)
                //
                .ldstr   ("Hello, {0}!")
                .ldarg_1
                .call    (typeof(string), "Format", typeof(string), typeof(object))

                // Console.WriteLine("Hello, World!");
                //
                .call    (typeof(Console), "WriteLine", typeof(string))
                .ret()
                ;

            Type type = emit.Method.Type.Create();

            IHello hello = (IHello)TypeAccessor.CreateInstance(type);

            hello.SayHello("World");
        }
    }
}
Cool!!! Isn't it?

HelloWorld.vb
Imports System
Imports NUnit.Framework

Imports BLToolkit.Reflection
Imports BLToolkit.Reflection.Emit

Namespace Examples.Reflection.Emit

    <TestFixture()> _
    Public Class HelloWorld

        Public Interface IHello
            Sub SayHello(ByVal toWhom As String)
        End Interface

        <Test()> _
        Sub Test()
            Dim assemblyHelper As AssemblyBuilderHelper = New AssemblyBuilderHelper("HelloWorld.dll")
            Dim typeHelper As TypeBuilderHelper = assemblyHelper.DefineType("Hello", GetType(Object), GetType(IHello))
            Dim methodHelper As MethodBuilderHelper = typeHelper.DefineMethod(GetType(IHello).GetMethod("SayHello"))
            Dim emit As EmitHelper = methodHelper.Emitter

            ' string.Format("Hello, {0} World!", toWhom)
            '
            emit _
            .ldstr("Hello, {0} World!") _
            .ldarg_1 _
            .call(GetType(String), "Format", GetType(String), GetType(Object))

            ' Console.WriteLine("Hello, World!");
            '
            emit _
            .call(GetType(Console), "WriteLine", GetType(String)) _
            .ret()

            Dim type As Type = typeHelper.Create()

            Dim hello As IHello = TypeAccessor.CreateInstance(type)

            hello.SayHello("VB")
        End Sub

    End Class

End Namespace
HelloWorld.cpp
#include "stdafx.h"

using namespace System;

using namespace NUnit::Framework;

using namespace BLToolkit::Reflection;
using namespace BLToolkit::Reflection::Emit;

namespace Examples {
namespace Reflection {
namespace Emit
{
    [TestFixture]
    public ref class HelloWorld
    {
    public:

        interface class IHello
        {
            void SayHello(String ^toWhom);
        };

        [Test]
        void Test()
        {
            AssemblyBuilderHelper ^assembly = gcnew AssemblyBuilderHelper("HelloWorld.dll");

            EmitHelper ^emit = assembly
                ->DefineType  ("Hello", Object::typeid, IHello::typeid)
                ->DefineMethod(IHello::typeid->GetMethod("SayHello"))
                ->Emitter;

            emit
                // string.Format("Hello, {0} World!", toWhom)
                //
                ->ldstr   ("Hello, {0} World!")
                ->ldarg_1
                ->call    (String::typeid, "Format", String::typeid, Object::typeid)

                // Console.WriteLine("Hello, World!");
                //
                ->call    (Console::typeid, "WriteLine", String::typeid)
                ->ret()
                ;

            Type ^type = emit->Method->Type->Create();

            IHello ^hello = (IHello^)TypeAccessor::CreateInstance(type);

            hello->SayHello("C++");
        }
    };
}}}


ObjectFactory

The following example shows how to create objects of different types coming from the same data source.

Typically, to create an object, BLToolkit calls the TypeAccessor.CreateInstanceEx method. First of all, this method verifies if there is an object factory (ObjectFactory property) associated with the creating object�s type. If the property value is not null, it is used to create an object.

The ObjectFactory property can be assigned explicitly at any time or by decorating the target class with the ObjectFactory attribute.

ObjectFactory.cs
using System;
using System.Collections.Generic;

using NUnit.Framework;

using BLToolkit.Data;
using BLToolkit.Mapping;
using BLToolkit.Reflection;

namespace HowTo.Reflection
{
    [TestFixture]
    public class ObjectFactoryTest
    {
        [ObjectFactory(typeof(Person.ObjectFactory))]
        public class Person
        {
            [MapField("PersonID")]
            public int    ID;

            public string LastName;
            public string FirstName;
            public string MiddleName;

            class ObjectFactory : IObjectFactory
            {
                public object CreateInstance(TypeAccessor typeAccessor, InitContext context)
                {
                    // Get the object type indicator field.
                    //
                    object objectType = context.DataSource.GetValue(context.SourceObject, "PersonType");

                    // Target ObjectMapper must be changed in order to provide correct mapping.
                    //
                    switch ((string)objectType)
                    {
                        case "D": context.ObjectMapper = ObjectMapper<Doctor>. Instance; break;
                        case "P": context.ObjectMapper = ObjectMapper<Patient>.Instance; break;
                    }

                    // Create an object instance.
                    // Do not call ObjectMapper.CreateInstance as it will lead to infinite recursion.
                    //
                    return context.ObjectMapper.TypeAccessor.CreateInstance(context);
                }
            }
        }

        public class Doctor : Person
        {
            public string Taxonomy;
        }

        public class Patient : Person
        {
            public string Diagnosis;
        }

        [Test]
        public void Test()
        {
            using (DbManager db = new DbManager())
            {
                List<Person> list = db
                    .SetCommand(@"
                        SELECT
                            ps.*,
                            d.Taxonomy,
                            p.Diagnosis,
                            CASE
                                WHEN d.PersonID IS NOT NULL THEN 'D'
                                WHEN p.PersonID IS NOT NULL THEN 'P'
                            END as PersonType
                        FROM
                            Person ps
                                LEFT JOIN Doctor  d ON d.PersonID = ps.PersonID
                                LEFT JOIN Patient p ON p.PersonID = ps.PersonID
                        ORDER BY
                            ps.PersonID")
                    .ExecuteList<Person>();

                Assert.AreEqual(list[0].GetType(), typeof(Doctor));
                Assert.AreEqual(list[1].GetType(), typeof(Patient));

                if (list.Count > 2)
                    Assert.AreEqual(list[2].GetType(), typeof(Person));
            }
        }
    }
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add
            key   = "ConnectionString"
            value = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"/>
    </appSettings>
</configuration>


TypeBuilder

Abstract types.
• InitialValues | How to specify initial value for an abstract property.
• InternalTypes | How to use internal abstract types.
• XmlSerialization | How to serialize an abstract type to xml.


InitialValues

InitialValues.cs
using System;

using NUnit.Framework;

using BLToolkit.Reflection;
using BLToolkit.TypeBuilder;

namespace HowTo.TypeBuilder
{
    [TestFixture]
    public class InitialValueTest
    {
        [AttributeUsage(AttributeTargets.Property)]
        public class NewGuidParameterAttribute : ParameterAttribute
        {
            public NewGuidParameterAttribute() : base(Guid.NewGuid().ToByteArray())
            {
            }
        }

        public abstract class TestObject1
        {
            [Parameter("t")]   public abstract string   Str         { get; set; }
            [Parameter(20)]    public abstract string   this[int i] { get; set; }
            [Parameter(54)]    public abstract int      Int         { get; set; }
            [Parameter(2,2,2)] public abstract DateTime Date        { get; set; }
            [Parameter(222L)]  public abstract Decimal  Decimal1    { get; set; }
            [Parameter(-2.05)] public abstract Decimal  Decimal2    { get; set; }
            [NewGuidParameter] public abstract Guid     Guid        { get; set; }
        }

        [Test]
        public void Test()
        {
            TestObject1 o = (TestObject1)TypeAccessor.CreateInstance(typeof(TestObject1));

            Assert.That(o.Str,      Is.EqualTo("t"));
            Assert.That(o.Int,      Is.EqualTo(54));
            Assert.That(o.Date,     Is.EqualTo(new DateTime(2,2,2)));
            Assert.That(o.Decimal1, Is.EqualTo(222m));
            Assert.That(o.Decimal2, Is.EqualTo(-2.05m));
            Assert.That(o.Guid,     Is.Not.EqualTo(Guid.Empty));
        }
    }
}


InternalTypes

InternalTypes.cs
using System;
using System.Runtime.CompilerServices;
using NUnit.Framework;

using BLToolkit.Reflection;

// typeof(TargetType).FullName + "." + TypeBuilderConsts.AssemblyNameSuffix
//
[assembly: InternalsVisibleTo("HowTo.TypeBuilder.InternalTypesTest.TestObject.TypeBuilder")]
[assembly: InternalsVisibleTo("HowTo.TypeBuilder.InternalTypesTest.TestObject.TypeAccessor")]

namespace HowTo.TypeBuilder
{

    [TestFixture]
    public class InternalTypesTest
    {
        internal abstract class TestObject
        {
            public abstract string Value { get; set; }
        }

        [Test]
        public void Test()
        {
            TestObject o = TypeAccessor.CreateInstance<TestObject>();
            Assert.IsNotNull(o);
        }
    }
}


XmlSerialization

XmlSerialization.cs
using System.IO;
using System.Xml.Serialization;

using NUnit.Framework;

using BLToolkit.Reflection;
using BLToolkit.EditableObjects;
using BLToolkit.TypeBuilder;

namespace HowTo.TypeBuilder
{
    [TestFixture]
    public class XmlSerializationTest
    {
        [XmlType(AnonymousType = true)]
        public abstract class MyClassA
        {
            public abstract string ValueA { get; set; }
        }

        [XmlType(AnonymousType = true)]
        [XmlIncludeAbstract(typeof(MyClassA))]
        [XmlIncludeAbstract(typeof(MyClassC))]
        public abstract class MyClassB
        {
            public abstract string   ValueB        { get; set; }
            public abstract MyClassA ValueMyClassA { get; set; }

            public abstract EditableList<MyClassA> MyList { get; set; }
        }

        [XmlType("abs:MyClassC")]
        public abstract class MyClassC : MyClassA { }

        [Test]
        public void Test()
        {
            MyClassB      original = TypeAccessor<MyClassB>.CreateInstance();
            MyClassB      serialized;
            XmlSerializer sr       = new XmlSerializer(TypeAccessor<MyClassB>.Type);

            original.ValueB               = "string value B";
            original.ValueMyClassA.ValueA = "string value A";
            original.MyList.Add(TypeAccessor<MyClassA>.CreateInstance());
            original.MyList.Add(TypeAccessor<MyClassC>.CreateInstance());

            using (MemoryStream stm = new MemoryStream())
            {
                sr.Serialize(stm, original);
                stm.Position = 0L;
                serialized = (MyClassB)sr.Deserialize(stm);
            }

            Assert.That(serialized.ValueB,               Is.EqualTo(original.ValueB));
            Assert.That(serialized.ValueMyClassA.ValueA, Is.EqualTo(original.ValueMyClassA.ValueA));

            Assert.AreEqual(original.MyList.Count, serialized.MyList.Count);
            Assert.That(serialized.MyList[0] is MyClassA);
            Assert.That(serialized.MyList[1] is MyClassA);
            Assert.That(serialized.MyList[1] is MyClassC);

            Assert.AreEqual(serialized.MyList[0].GetType(), TypeFactory.GetType(typeof(MyClassA)));
            Assert.AreEqual(serialized.MyList[1].GetType(), TypeFactory.GetType(typeof(MyClassC)));
        }
    }
}


Validation