Skip to content
Dapper with query generator from NServicekit.OrmLite
C# Other
  1. C# 99.6%
  2. Other 0.4%
Branch: develop
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.vscode
src
test/SimpleStack.Orm.Tests
.gitignore
LICENSE
README.md
SimpleStack.Orm.sln
pack.bat
pack.sh

README.md

A Dapper based ORM for .NET

Introduction

Follow @simplestackproj on twitter for updates.

SimpleStack.Orm is a set of light-weight C# extension methods around System.Data.* interfaces which is designed to persist POCO classes with a minimal amount of intrusion and configuration.

SimpleStack.Orm is based on the wonderfull Dapper project for all database acces. The SQL query generation code is based on NServiceKit.OrmLite

Main objectives:

  • Map a POCO class 1:1 to an RDBMS table, cleanly by conventions, without any attributes required.
  • Create/Drop DB Table schemas using nothing but POCO class definitions (IOTW a true code-first ORM)
  • Simplicity - typed, wrist friendly API for common data access patterns.
  • Fully parameterized queries
  • Cross platform - supports multiple dbs (currently: Sql Server, Sqlite, MySql, PostgreSQL) running on both .NET and Mono platforms.

In SimpleStak.Orm : 1 Class = 1 Table. There should be no surprising or hidden behaviour.

Effectively this allows you to create a table from any POCO type and it should persist as expected in a DB Table with columns for each of the classes 1st level public properties.

Install

Depending on the database you want to target:

2 minutes sample

using SimpleStack.Orm;
using SimpleStack.Orm.SqlServer;

namespace Test{

   public class sample{

      public class Dog{
         [PrimaryKey]
         public int Id{get; set;}
         public string Name{get; set;}
         public DateTime? BirthDate{get; set;}
         public decimal Weight{get; set;}
         public string Breed{get; set;}
      }

      var factory = new OrmConnectionFactory(new SqlServerDialectProvider(), "server=...");
      using (var conn = factory.OpenConnection())
      {
         conn.CreateTable<Dog>();

         conn.Insert(new Dog{Name="Snoopy", BirthDate = new DateTime(1950,10,01), Weight=25.4});
         conn.Insert(new Dog{Name="Rex", Weight=45.6});
         conn.Insert(new Dog{Name="Rintintin", BirthDate = new DateTime(1918,09,13), Weight=2});

         var rex = conn.First<Dog>(x => Id == 2);
         rex.BirthDate = new DateTime(1994,11,10);

         conn.Update(rex);

         conn.Delete<Dog>(x => x.Name == "Rintintin");
      }
   }
}

20 Minutes sample

As SimpleStack.Orm is based on Dapper, I encourage you to have a look at Dapper documentation.

The first thing todo is to create an OrmConnectionFactory specifying the Dialectprovider to use and the connectionstring of your database.

var factory = new OrmConnectionFactory(new SqlServerDialectProvider(), "server=...");
using (var conn = factory.OpenConnection())
{
   //TODO use connection
}

The DialectProvider contains all the specific code required for each database.

WHERE clause generation using strong type LINQ queries

Equals, Not equals, Bigger than, Less than, Contains,...

db.Select<Dog>(q => q.Name == "Rex"); // WHERE ("Name" = 'Rex')
db.Select<Dog>(q => q.Name != "Rex"); // WHERE ("Name" <> 'Rex')
db.Select<Dog>(q => q.Weight == 10); // WHERE ("Weight" = 10)
db.Select<Dog>(q => q.Weight > 10); // WHERE ("Weight" > 10)
db.Select<Dog>(q => q.Weight >= 10); // WHERE ("Weight" >= 10)
db.Select<Dog>(q => q.Weight < 10); // WHERE ("Weight" < 10)
db.Select<Dog>(q => q.Weight <= 10); // WHERE ("Weight" <= 10)
db.Select<Dog>(q => q.Name.Contains("R")); // WHERE ("Name" LIKE("%R%"))
db.Select<Dog>(q => q.Name.StartWith("R")); // WHERE ("Name" LIKE("R%"))
db.Select<Dog>(q => q.Name.EndWidth("R")); // WHERE ("Name" LIKE("%R"))

Combine criterias with AND or OR

// WHERE ("Name" LIKE 'R' OR "Weight" > 10)
db.Select<Dog>(q => q.Name.Contains("R") || q.Weight > 10);
// WHERE ("Name" LIKE 'R' AND "Weight" > 10)
db.Select<Dog>(q => q.Name.Contains("R") && q.Weight > 10);

Sql class

IN Criteria

// WHERE "Breed" In ('Beagle', 'Border Collie', 'Golden Retriever')
db.Select<Dog>(q => Sql.In(q.Breed, "Beagle", "Border Collie", "Golden Retriever"));

Date part methods

Use the date function (specific for each database)

// SELECT YEAR("BirthDate") FROM DOG
conn.GetScalar<Dog, int>(x => Sql.Year(x.BirthDate))
// OR
conn.GetScalar<Dog, int>(x => x.BirthDate.Year)

// SELECT "Id","Name","Breed","DareBirth","Weight" FROM DOG WHERE MONTH("BirthDate") = 10
conn.Select<Dog>(x => Sql.Month(x.BirthDate) = 10)
// OR
conn.Select<Dog>(x => x.BirthDate.Month = 10)

Aggregation function

// SELECT MAX("BirthDate") FROM DOG
conn.GetScalar<Dog, DateTime>(x => Sql.Max(x.BirthDate))
// SELECT AVG("Weight") FROM DOG
conn.GetScalar<Dog, decimal>(x => Sql.Avg(x.Weight))

INSERT, UPDATE and DELETEs

To see the behaviour of the different APIs, all sample uses this simple model

public class Person
{
	public int Id { get; set; }
	public string FirstName { get; set; }
	public string LastName { get; set; }
	public int? Age { get; set; }
}

Update

The "Update" method will always update up to one row by generating the where clause using PrimaryKey definitions

//UPDATE "Person" SET "FirstName" = 'Jimi',"LastName" = 'Hendrix',"Age" = 27 WHERE "Id" = 1
db.Update(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27});

To update only some columns, you can use the "onlyField" parameter

//UPDATE "Person" SET "Age" = 27 WHERE "Id" = 1
db.Update(new Person { Id = 1, Age = 27}, x => x.Age);
//UPDATE "Person" SET "LastName" = 'Hendrix',"Age" = 27 WHERE "Id" = 1
db.Update(new Person { Id = 1, LastName = "Hendrix", Age = 27}, x => new {x.Age, x.LastName});

Anonymous object can also be used

//UPDATE "Person" SET "Age" = 27 WHERE "Id" = 1
db.Update<Person>(new { Id = 1, Age = 27}, x => x.Age);
//UPDATE "Person" SET "LastName" = 'Hendrix',"Age" = 27 WHERE "Id" = 1
db.Update<Person>(new { Id = 1, LastName = "Hendrix", Age = 27}, x => new {x.Age, x.LastName});

UpdateAll

The "UpdateAll" method will update rows using the specified where clause (if any).

//UPDATE "Person" SET "FirstName" = 'JJ'
db.UpdateAll(new Person { FirstName = "JJ" }, p => p.FirstName);
//UPDATE "Person" SET "FirstName" = 'JJ' WHERE AGE > 27
db.UpdateAll(new Person { FirstName = "JJ" }, p => p.FirstName, x => x.Age > 27);

INSERT

Insert a single row

//INSERT INTO "Person" ("Id","FirstName","LastName","Age") VALUES (1,'Jimi','Hendrix',27)
db.Insert(new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 });

Insert multiple rows

//INSERT INTO "Person" ("Id","FirstName","LastName","Age") VALUES (1,'Jimi','Hendrix',27)
db.Insert(new []{
   new Person { Id = 1, FirstName = "Jimi", LastName = "Hendrix", Age = 27 },
   new Person { Id = 2, FirstName = "Kurt", LastName = "Cobain", Age = 27 },
   });

AutoIncremented Primary Keys

if you specify a PrimaryKey as AutoIncrement, the PrimaryKey is not added in the INSERT query

public class Person
{
   [PrimaryKey]
   [AutoIncrement]
	public int Id { get; set; }
	public string FirstName { get; set; }
	public string LastName { get; set; }
	public int? Age { get; set; }
}

//INSERT INTO "Person" ("FirstName","LastName","Age") VALUES ('Jimi','Hendrix',27)
db.Insert(new Person { FirstName = "Jimi", LastName = "Hendrix", Age = 27 });

Delete

The "Delete" method will always delete up to one row by generating the where clause using PrimaryKey definitions

//DELETE FROM "Person" WHERE ("Id" = 2)
db.Delete(new Person{Id = 2});

DeleteAll

Or an Expression Visitor:

//DELETE FROM "Person" WHERE ("Age" = 27)
db.DeleteAll<Person>(x => x.Age = 27);

Primary Keys, Foreign keys and Indexes

TODO

Available Attributes

TODO

Select using JOINs

TODO

TypeMappers

TODO

You can’t perform that action at this time.