DataAccess.AbstractAccessor

Home / DataAccess

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>
    <connectionStrings>
        <add
            name             = "DemoConnection"
            connectionString = "Server=.;Database=BLToolkitData;Integrated Security=SSPI"
            providerName     = "System.Data.SqlClient" />
    </connectionStrings>
</configuration>