SqlFu: A C# repository from snielsson

#Welcome to SqlFu

SqlFu is a versatile micro-orm (like dapper.net, peta poco , massive etc) for .Net 4. SqlFu is Apache licensed. If you're wondering if there's a reason for yet another micro-orm read this

Latest version: 1.4.1 Change Log

Read about the (new) Advanced Features

SqlFu uses Apache 2.0 license

Why should you use it

The main USP (unique selling proposition - advantage) of SqlFu is Versatility. This is the reason I've developed it. I need it more flexibility and the micro-orm I was using (peta poco) didn't have it and if other micro-orms had it, they were too slow.

I've designed SqlFu based on three equally important principles:

User Friendliness - Versatility - Performance

SqlFu supports

SqlServer 2005+
MySql
Postgresql
SqlServerCE 4 (new in SqlFu 1.1)
Sqlite (new in SqlFu 1.1)

User Friendly

Intuitive usage and automatic multi poco mapping by convention, similar to EF. Multi poco mapping automatically works with pagination without any special setup.

var db= new DbAccess(connection,DbType.SqlServer);

//usual stuff
db.Get<Post>(id)

//1.3.0+
db.Get<Post>(p=>p.Id==12 && p.Title.StartsWith("A"))

db.Query<Post>("select * from posts where id=@0",1);
db.Query<dynamic>("select * from posts where id=@0",1);
db.Query<Post>(p=>p.CreatedAt.Year==2013 && p.IsActive)


//you can pass ordinal params or anonymous objects
db.Query<Post>("select * from posts where id=@id",new{id=1});

db.GetValue<int>("select count(*) from posts")

//1.3.0+
db.GetColumnValue<Post,string>(p=>p.Title,p=>p.Id==12)

//stored procedure support (1.2.0+) - see wiki for details
db.ExecuteStoredProcedure("spSomething",new{Param1="bla",_OutParam=0});

//quick helpers 1.3.0+
db.Count<Post>();
db.Count<Post>(p=>p.Title.StartsWith("Test"));

//check if table contains rows
db.HasAnyRows<Post>();
db.HasAnyRows<Post>(p=>p.IsActive);


var ids=new[]{1,2,3};
db.DeleteFrom<Post>(p=>ids.Contains(p.Id));

db.Drop<Post>();

if (db.TableExists<Post>()){ }

//insert 
var p= new Post{ Id=1, Title="Test"};
db.Insert(p);
p.Title="changed title";
db.Update<Post>(p);


//1.3.0+
// Update Post set Title=@0 where Id=23
db.Update<Post>(new{Title="bla"},p=>p.Id==23);

//update table sql builder

// update Post set [Count]=[Count]+1, IsActive=@0 where Id=12
db.Update<Post>().Set(p=>p.Count,p=>p.Count+1).Set(p=>p.IsActive,false).Where(p=>p.Id==12).Execute();


//paged queries , result contains Count and Items properties
var result=db.PagedQuery<Post>(0,5,"select * from post order by id desc");


//Multi poco mapping by convention similar to EF, no special setup

public class PostView
{
   public int Id {get;set}
   public string Title {get;set;}
   public IdName Author {get;set;} 
}

public class IdName
{
   public int Id {get;set;} // <- Author_Id
   public string Name {get;set;} // <- Author_Name
}

//'Author' is automatically instantiated and populated with data. The convention is to use [Property]_[Property]
var sql=@"
select p.Id, p.Title, p.AuthorId as Author_Id, u.Name as Author_Name 
from posts p inner join Users u on u.Id=p.AuthorId
where p.Id=@0";
var posts=db.Query<PostView>(sql,3);

//complex type mapping AND pagination with no special setup
result=db.PagedQuery<PostView>(0,10,sql,3)

Rules

All the parameters in sql must be prefixed with '@' . The specific db provider will replace it with the proper prefix.
Enums are automatically handled from int or string when querying. When insert/update they are treated as ints.
Use the [InsertAsStringAttribute] to save it as string
Multi poco mapping is done automatically if a column name has '_'. That can be changed (1.2.0+)
If a table or column name is already escaped by the user, it won't be escaped by SqlFu
Every identifier containing a '.' will be split and each part will be escaped e.g dbo.table -> [dbo].[table]

//let's use '.' instead of '_'
PocoFactory.ComplexTypeMapper.Separator = '.';

```
* Any property/column which can't be mapped is ignored
* However an exception is thrown if you want to assign a value to an object type for example, or null to a non-nullable

 
#### Attributes

``` csharp
[Table("Posts", PrimaryKey="Id" /*default*/,Autogenerated=true /*default*/)]
public class Post
{
 public int Id {get;set;}
 [QueryOnly]
 public string ReadOnly {get;set;} //any property marked as QueryOnly will not be used for insert/update
 [InsertAsString]
 public MyEnum Type {get;set;}// any property with this attribute will be converted to string
}

```

## Versatility
* SqlFu knows how to map by default Nullable(T),Guid, TimeSpan, Enum and CultureInfo
* Allows manual mapping when you need it

``` csharp
//custom sql for those special cases
db.WithSql("select * from posts").ExecuteQuery<MyType>(reader=>{ /* mapping by hand */  })

db.WithSql("select item from myTable where id=@0",3).ExecuteScalar<myStruct>(result=> /* conversion by hand */)

//want to always use that mapper for every query
PocoFactory.RegisterMapperFor<MyType>(reader=> {/* do mapping */});
db.Query<MyType>(sql,args) // will automatically use the registered mapper instead of the default one

//same with converters
PocoFactory.RegisterConverterFor<myStruct>(obj=>{ /* do conversion */}) 

//or for value objects
PocoFactory.RegisterConverterFor<EmailValueObject>(obj=> new EmailValueObject(obj.ToString()))
db.GetValue<Email>("select email from users where id=@0",8)

//execute some command processing before query
db.WithSql(sql,args).ApplyToCommand(cmd=> { /* modify DbCommand */}.Query<MyType>()

```
### Multi Poco mapping
As shown above, the only thing you need to do is to name the column according tot the [Property]_[Property] format. 
This feature is designed to be used for populating View Models where every object is a Poco with a parameterless constructor.

However if needed you can customize the instantion of any type (except the main Poco itself). Let's suppose you have this setup

``` csharp

public class Address
{
  //no parameterless constructor
  public Address(int userId)
  {
      UserId=userId;
  }
   
 public int UserId {get;private set;}
 public string Street {get;set;}
 public string Country {get;set;}
}

public class ViewModel
{
 /* ... other properties... */
 public Address Address {get;set;}
}

db.Query<ViewModel>("select u.* , addr.Street as Address_Street, addr.Country as Address_Country from users u, addresses addr where u.Id=4")

```

Ok, maybe the sql itself isn't very correct, that's not the point. The point is you want to populate that ViewModel and those are the relevant columns.
Since the Address object requires the userId, you can configure the **DefaultComplexTypeMapper** to use this for instantiating Address

``` csharp
  DefaultCompexTypeMapper.ToCreate<Address>(user=> new Address(user.Id));
  
```
The lambda is basically a Func<dynamic,T> and in this example the ViewModel is passed on as the dynamic argument.

What if you want to use your very own complex type mapping with any convention you like. It's a bit tricky but it's not hard.
You just need to implement the _IMapComplexType_ interface then assign it (you can also subclass the _DefaultComplexTypeMapper_ class).

```csharp
public class MyComplexMapper:IMapComplexType
{
 /* implementation */
}

PocoFactory.ComplexTypeMapper= new MyComplexMapper();

```

Note that the mapper should act as a singleton so it has to be thread safe. 
When implementing a complex mapper you have 2 ways to do it: in the _MapType<T>_ method or in the _EmitMapping_ method.
The first method is for normal people, the second involves emitting IL code with Reflection.Emit so it's aimed at the hardcore masochists. 
However, the second method is usually the most performant one. The SqlFu automapper will try to use the EmitMapping method first, but if it returns false, it will call the MapType method instead.

**Hint**: if you go for the first method and you want to set a property via reflection, use the _SetValueFast_ extension method (around 5-7x faster than reflection) defined in CavemanTools (used by SqlFu) and the same for the getters.

## Performance
[Updated benchmarks here](http://www.sapiensworks.com/blog/post/2013/02/08/SqlFu-131-Released-Updated-Benchmarks.aspx)

snielsson/SqlFu

Why should you use it

User Friendly

Rules