Dapper.Accessor 3.0.6

Dapper.Accessor 3.0

What is Accessor

Accessor is a light-weight ORM based on Dapper, which provide object oriented operations for popular database systems: MySQL, SQL Server and Oracle.

What does Accessor do

Simplify connection management

In most cases you don't have to create connection object like traditional ways do, and save the trouble of passing connection object between functions and classes.

OO data operation

With Accessor you can manipulate data rows in form of class objects, like what other ORMs do.

And a lot more

Name Mapping, ATP syntax...a lot more features you may find in the doc.

Getting Started

First of all, you need to make some basic config for accessor, a typical config contains two parts, section node and accessor node.

Section Node

In order to let your app work with Accessor properly, you need to add following code under your configSections node

<section name="accessor" type="Dapper.Accessor.AccessorConfig, Dapper.Accessor"/>

Accessor Node

A minimal config looks like below

<accessor>
  <connections>
    <add name="SqlServer" connectionString="[your conn str]" databaseType="MSSQL"></add>
  </connections>
</accessor>

Here we have the basic config now, and then we need some entity class

Sample Entity Class

public class Person : BaseEntity
{
  [Column]
  public string Name { get; set; }
	
  [Column]
  public int Age { get; set; }
}

Sample Query

var p = DB.Default.Select<Person>();

Sample Insert

var person = new Person { Name = "Test", Age = 10 };
DB.Default.Insert(person);

Sample Update

person.Age = 12;
DB.Default.Update(person);

Sample Delete

DB.Default.Delete(person);

Use methods from BaseEntity

Or you can just use

person.Save();		// upsert person object to database
person.Delete();	// delete person from database

Connection Management

You can maintain multiple connections in accessor config, each connection node contains a name as alias, connection string, and also, you need to specify the database system of you target database. A connection may look like this:

<add name="[conn name]" connectionString="[conn str]" databaseType="MSSQL" />

You may specify a connection as default in the connections config node

<connections default="xxx">
  ...
</connections>

If not specified, Accessor will use the first connection as default.

Specify a connection

When your system are handling multiple database connections, you may need to specify a connection to perform an operation, here you need the AccessorClient class.

DB.Connect("someConn").Select<Person>();

Please make sure you've added connection with proper name into the accessor config before connect with it.

Data Mapping

First let's introduce some attributes

Table and Column Attribute

By default, Access will use class name as table name and property name as column name when performing select

For example, the code below

DB.Default.Select<Person>()

will be translate into

select * from Person

And also, column "Name" and "Age" will be mapped to property accordingly.

Sometimes we want to build model class with different class name or property name

[Table("Person")]
public class Guy
{
    [Column("Name")]
    public string Alias { get; set; }
}

Mapping Config

In order to let mapping work properly, you will need to let accessor now which class you want to use mapping feature

<accessor>
    <add assembly="your assembly name" namespace="your class namespace" ignoreCase="All" recursive="true" />
</accessor>

Accessor will scan all classes under the namespace and apply mapping settings to them

Data Reading

Select all data in table "Person"

List<Person> persons = DB.Default.Select<Person>();

Select with sql statement

Accessor.Select<Person>("select * from Person where Age > 10");

Use parameters

Note: You may need to use "@" as parameter prefix when using Oracle

DB.Default.Select<Person>("select * from Person where Name like @keyword + `%` and Age > @limit",
    new 
    {
        keyword = "James",
        limit = 10
    }
);

Select with paging

DB.Default.SelectPage<Person>(1, 10);
DB.Default.SelectPage<Person>(1, 10, "select * from Person where Age > 10");

ATP Syntax

DB.Default.Select<Person>(new { where_Age_gt = 10 });
DB.Default.SelectPage<Person>(1, 10, new { where_Age_gt = 10 });

There also some other methods to fetch a single row These methods all supports the same overloads as Select

var person1 = DB.Default.First<T>();
var person2 = DB.Default.FirstOrDefault<T>();
var person3 = DB.Default.Single<T>();
var person4 = DB.Default.SingleOrDefault<T>();