SqlChic extends your DbConnection with extension methods to make fully async database queries a breeze.
Note: all extension methods leave the connection in the same open/closed state that it started in.
public static IObservable<T> Query<T>(this DbConnection cnn, string sql, object param = null, DbTransaction transaction = null)Example usage:
public class Dog
{
public int? Age { get; set; }
public Guid Id { get; set; }
public string Name { get; set; }
public float? Weight { get; set; }
public int IgnoredProperty { get { return 1; } }
}
var guid = Guid.NewGuid();
var dogs = await connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid }).ToArray();
dogs.Count()
.IsEqualTo(1);
dogs.First().Age
.IsNull();
dogs.First().Id
.IsEqualTo(guid);public static IObservable<dynamic> Query(this DbConnection cnn, string sql, object param = null, DbTransaction transaction = null)This method will execute SQL and return a dynamic list.
Example usage:
var rows = await connection.Query("select 1 A, 2 B union all select 3, 4").ToArray();
((int)rows[0].A)
.IsEqualTo(1);
((int)rows[0].B)
.IsEqualTo(2);
((int)rows[1].A)
.IsEqualTo(3);
((int)rows[1].B)
.IsEqualTo(4);public static async Task<int> ExecuteAsync(this DbConnection cnn, string sql, object param = null, DbTransaction transaction = null)Example usage:
await connection.ExecuteAsync(@"
set nocount on
create table #t(i int)
set nocount off
insert #t
select @a a union all select @b
set nocount on
drop table #t", new {a=1, b=2 })
.IsEqualTo(2);The same signature also allows you to conveniently and efficiently execute a command multiple times (for example to bulk-load data)
Example usage:
await connection.ExecuteAsync(@"insert MyTable(colA, colB) values (@a, @b)",
new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } }
).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3"This works for any parameter that implements IEnumerable for some T.
The key feature of SqlChic is performance. The following metrics show how long it takes to execute SELECT statements against a DB and map the data returned to objects.
| Method | Average Duration | Times Slower than SqlChic | Remarks |
|---|---|---|---|
| SqlChic (Buffered) | 0.0102ms | 1x | |
| PetaPoco | 0.1911ms | 19x | |
| Dapper (Non-buffered, Async) | 0.2663ms | 26x | |
Hand coded (using a SqlDataReader, Async) |
0.2691ms | 26x | |
| Entity Framework (LINQ) | 0.3786ms | 37x | |
| NHibernate SQL | 0.3836ms | 38x | |
| Linq2Sql (CompiledQuery) | 0.3866ms | 38x | Not super typical involves complex code |
| SubSonic CodingHorror | 0.3866ms | 38x | |
| NHibernate HQL | 0.3876ms | 38x | |
| BLToolkit | 0.3914ms | 38x | |
Linq2Sql ExecuteQuery |
0.5058ms | 50x | |
| Linq2Sql (LINQ) | 1.1453ms | 112x | |
Entity Framework (ExecuteStoreQuery) |
1.3787ms | 135x | |
| SubSonic ActiveRecord.SingleOrDefault | 7.3637ms | 722x |
Performance benchmarks are available as part of the source code.
Feel free to submit patches that include other ORMs - when running benchmarks, be sure to compile in Release and not attach a debugger (ctrl F5)
Parameters are passed in as anonymous classes. This allow you to name your parameters easily and gives you the ability to simply cut-and-paste SQL snippets and run them in Query analyzer.
new {A = 1, B = "b"} // A will be mapped to the param @A, B to the param @B SqlChic allows you to pass in IEnumerable and will automatically parameterize your query.
For example:
connection.Query<int>("select * from (select 1 as Id union all select 2 union all select 3) as X where Id in @Ids", new { Ids = new int[] { 1, 2, 3 });Will be translated to:
select * from (select 1 as Id union all select 2 union all select 3) as X where Id in (@Ids1, @Ids2, @Ids3)" // @Ids1 = 1 , @Ids2 = 2 , @Ids2 = 3SqlChic's default behavior is to execute your SQL and buffer the entire reader on return. This is ideal in most cases as it minimizes shared locks in the db and cuts down on db network time.
However when executing huge queries you may need to minimize memory footprint and only load objects as needed.
SqlChich will use buffered readers if the db connection it receives is closed. If the db connection is already open, SqlChic will not use buffered readers.
SqlChic allows you to map a single row to multiple objects. This is a key feature if you want to avoid extraneous querying and eager load associations.
Example:
var sql =
@"select * from #Posts p
left join #Users u on u.Id = p.OwnerId
Order by p.Id";
var data = connection.QueryAsync<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = await data.FirstAsync();
post.Content.IsEqualTo("Sams Post1");
post.Id.IsEqualTo(1);
post.Owner.Name.IsEqualTo("Sam");
post.Owner.Id.IsEqualTo(99);important note SqlChic assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter.
SqlChic allows you to process multiple result grids in a single query.
Example:
var sql =
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";
using (var multi = await connection.QueryMultipleAsync(sql, new {id=selectedId}))
{
var customer = await multi.Read<Customer>().SingleAsync();
var orders = await multi.Read<Order>().ToList();
var returns = await multi.Read<Return>().ToList();
...
} SqlChic supports stored procs:
var user = await cnn.Query<User>("spGetUser", new {Id = 1},
commandType: CommandType.StoredProcedure).FirstAsync();}}}...though output parameters are still a work in progress.
SqlChic supports varchar params, if you are executing a where clause on a varchar column using a param be sure to pass it in this way:
Query<Thing>("select * from Thing where Name = @Name", new {Name = new DbString { Value = "abcde", IsFixedLength = true, Length = 10, IsAnsi = true });On Sql Server it is crucial to use the unicode when querying unicode and ansi when querying non unicode.
SqlChic caches information about every query it runs, this allow it to materialize objects quickly and process parameters quickly. The current implementation caches this information in a ConcurrentDictionary object. The objects it stores are never flushed. If you are generating SQL strings on the fly without using parameters it is possible you will hit memory issues. We may convert the dictionaries to an LRU Cache.
SqlChic's simplicity means that many feature that ORMs ship with are stripped out, there is no identity map, there are no helpers for update / select and so on.
SqlChic does not manage your connection's lifecycle, it assumes the connection it gets is open (or open-able) AND has no existing datareaders enumerating (unless MARS is enabled)
SqlChic currently only supports MS SQL, to ensure full availability of async functionality.
SqlChic has a comprehensive test suite in the source code.
It is! Except it is fully and thoroughly async, down to the minutiae of using IObservable to represent data streaming back out of the database.