DbExtensions consists of 4 components that can be used together or separately:

1. A set of extension methods that simplifies raw ADO.NET programming
2. A POCO query API: SqlSet
3. An API for building SQL queries: SqlBuilder
4. CRUD operations: Database, SqlTable

The key features of this library are the granularity of its components and code aesthetics.

DbConnection conn = Database.CreateConnection("name=Northwind");

SqlSet<Product> products = conn.From<Product>("Products");
SqlSet<Product> productsToReorder = products.Where("UnitsInStock < {0}", 10);

if (productsToReorder.Any()) {

   SqlSet<Product> top5WithLowestStock = productsToReorder.OrderBy("UnitsInStock").Take(5);
   Product first = top5WithLowestStock.First();

   if (top5WithLowestStock.Count() > 1) {
      Product second = top5WithLowestStock.Skip(1).First();
   }
}

SqlSet provides a LINQish API for making queries, but using SQL instead of lambda expressions. The above code executes the following queries:

SELECT (CASE WHEN EXISTS (
   SELECT *
   FROM Products
   WHERE UnitsInStock < @p0) THEN 1 ELSE 0 END)
-- @p0: Input Int32 (Size = 0) [10]
-- [-1] records affected.
SELECT *
FROM (
   SELECT *
   FROM Products
   WHERE UnitsInStock < @p0
   ORDER BY UnitsInStock
   LIMIT @p1) dbex_set5
LIMIT @p2
-- @p0: Input Int32 (Size = 0) [10]
-- @p1: Input Int32 (Size = 0) [5]
-- @p2: Input Int32 (Size = 0) [1]
-- [-1] records affected.
SELECT COUNT(*)
FROM (
   SELECT *
   FROM Products
   WHERE UnitsInStock < @p0
   ORDER BY UnitsInStock
   LIMIT @p1) dbex_count
-- @p0: Input Int32 (Size = 0) [10]
-- @p1: Input Int32 (Size = 0) [5]
-- [-1] records affected.
SELECT *
FROM (
   SELECT *
   FROM Products
   WHERE UnitsInStock < @p0
   ORDER BY UnitsInStock
   LIMIT @p1) dbex_set6
LIMIT @p2
OFFSET @p3
-- @p0: Input Int32 (Size = 0) [10]
-- @p1: Input Int32 (Size = 0) [5]
-- @p2: Input Int32 (Size = 0) [1]
-- @p3: Input Int32 (Size = 0) [1]
-- [-1] records affected.

var query = SQL
   .SELECT("p.ProductID, p.ProductName, s.UnitPrice, p.CategoryID")
   ._("c.CategoryID AS Category$CategoryID, c.CategoryName AS Category$CategoryName")
   .FROM("Products p")
   .JOIN("Categories c ON p.CategoryID = c.CategoryID")
   .WHERE()
   ._If(categoryId.HasValue, "p.CategoryID = {0}", categoryId);

IEnumerable<Product> products = conn.Map<Product>(query);

With SqlBuilder you have complete control of the executing SQL.

public class NorthwindDatabase : Database {
   
   public SqlTable<Product> Products { // SqlTable inherits from SqlSet
      get { return Table<Product>(); } 
   }

   public NorthwindDatabase() 
      : base("name=Northwind") { }
}

var db = new NorthwindDatabase();

Product prod = db.Products.Find(1);
prod.UnitPrice = prod.UnitPrice * 1.1;

db.Products.Update(prod);

You can also use SqlBuilder to build insert, update and delete commands.

• Deferred execution
• POCO and dynamic mapping for queries
  • Mapping to properties (including complex)
  • Mapping to constructor arguments
• Attributes or XML mapping for inserts, updates and deletes, using System.Data.Linq.Mapping (LINQ to SQL mapping)
• Generic and non-generic APIs (for when the type of the entity is not known at build time)
• Automatic connection management (no need to explicitly open connection, but you are allowed to)
• Optimistic concurrency (using version column)
• Batch and deep commands (e.g. recursively insert entity and all one-to-many associations)
• Query results as XML
• Profiling
• Provider-independent (tested against SQLite, SQL Server Compact, SQL Server and MySQL)

DbExtensions doesn't provide the following functionality:

• Identity map
• Lazy loading
• Change tracking
• Unit of work

• For SQL Server and SQL Server Compact, SqlSet.Skip() uses OFFSET, available in SQL Server 2012 and SQL Server Compact 4

Code hosted on GitHub. Releases available on GitHub and NuGet.

This project was originally hosted on SourceForge, source code and releases for versions 1.x, 2.x and 3.x remain available there.

• Documentation
• Ask for help
• Report an issue
• Roadmap