Data Access for .NET CRUD operations, dynamic queries, SQL generation in C#


features open-source NReco.Data library

  • abstract db-agnostic Query structure (no need to compose SQL):
    • easily composed from C# code with overloaded operators
    • can be built and modified at the run-time
    • can be parsed from string expression (relex syntax)
  • provider-independent DAL with simple API:
    • DbCommandBuilder: generates SELECT/INSERT/UPDATE/DELETE SQL commands, application-level data views for complex queries
    • DbBatchCommandBuilder: generates several SQL statements into one DbCommand (multiple result sets, batch inserts/updates)
    • RecordSet : lightweight and efficient replacement for DataTable/DataRow, can be used for TVP (table-valued parameter)
    • DbDataAdapter: CRUD operations for annotated POCO models and non-model types (dictionary, RecordSet)
    • full async support
  • generates parameterized SQL statements: protected against SQL injections, commands may be reused
  • best for schema-less data access and dynamic (runtime) queries
  • supports both .NET Core and full .NET Framework 4.5+ projects
  • can be used with any ADO.NET data provider: MsSql, PostgreSql, Sqlite, Mysql, ODBC provider etc.
  • free and open-source: MIT license

download

parse relex and generate SQL

Relex #1 | Relex #2 | Relex #2
NReco.Data can generate SQL commands by abstract query, perform CRUD operations, map results to POCO objects and non-model types (dictionary, RecordSet), encapsulate DB-specific SQL from business logic. It may be used as efficient alternative to missed DataTable/DataRow in .NET Core projects.

how to use

  1. Install NReco.Data nuget package
  2. Configure DAL components for concrete ADO.NET data provider:
    var dbFactory = new DbFactory(SqlClientFactory.Instance) {
        LastInsertIdSelectText = "SELECT @@IDENTITY"
    };
    var dbCmdBuilder = new DbCommandBuilder(dbFactory);
    var dbConnection = dbFactory.CreateConnection();
    dbConnection.ConnectionString = "Data Source=..."; 
    var dbAdapter = new DbDataAdapter(dbConnection, dbCmdBuilder);
    
  3. Use DbDataAdapter methods for select/insert/update/delete operations:
    var employeeQuery = new Query("Employees", 
    	(QField)"EmployeeID"==(QConst)newEmployee.EmployeeID )
    	.Select("FirstName","LastName");
    var employeeDictionary = dbAdapter.Select(employeeQuery)
    	.ToDictionary();
    dbAdapter.Insert("Employees", new { FirstName = "John", LastName = "Smith" } );
    dbAdapter.Delete( new Query("Employees", 
    	(QField)"DismissDate"< new QConst(DateTime.Now) );
  4. RecordSet usage:
    var customerRS = dbAdapter.Select( 
    	new Query("Customers", (QField)"Country"==(QConst)"USA")
    	).ToRecordSet();
    customerRS.SetPrimaryKey("CustomerID");
    customerRS[0]["Active"] = false;
    customerRS[1].Delete();
    var newCustomerRow = customerRS.Add();
    newCustomerRow["name"] = "New Customer";
    dbAdapter.Update("Customers", customerRS);
  5. More examples:
  6. Documentation links:

frequently asked questions

NReco.Data is useful in the following cases:
  • CRUD operations with models
  • schema-less data access to DB (without POCO models)
  • batch inserts/updates/selects in one DbCommand, handle multiple result sets
  • you need full control over complex SQL queries (JOIN/UNION/GROUP BY etc) but want to keep your business layer DB-independent
  • performance-critical parts where ORM (Entity Framework, NHibernate) add to much overhead. NReco.Data is very fast, see comparision with Dapper below.
  • you're looking for DataAdapter/DataTable/DataRow alternative/replacement in .NET Core applications
In most cases NReco.Data is a right choice:
  • if you need fast, lightweight and extensible db-agnostic data access layer that supports models mapping (micro-ORM) and you don't want to use fully-functional ORM
  • you need data access library for advanced scenarious (multiple result sets, commands batching etc) in addition to ORM
Lets assume we have simple annotated POCO model:
[Table("employees")]
public class Employee {
	[Column("id")][Key]
	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public int? EmployeeID { get; set; }

	[Column("first_name")]
	public string FirstName { get; set; }

	[Column("last_name")]
	public string LastName { get; set; }
}
Load select results into POCO models list:
var employeesList = dbAdapter.Select( new Query("Employees") )
    .ToList<Employee>()
Update only specific columns:
dbAdapter.Update( 
  new Query("Employees", (QField)"id"==(QConst)5 ), 
  new {
	first_name = "John"
  } );
Delete a record:
Employee johnEmployee;
dbAdapter.Delete( johnEmployee );
Performance of SELECT mapping over 500 iterations (Dapper performance test):

Method Duraton
Hand coded (using a SqlDataReader) 45 ms
Dapper raw SQL QueryFirstOrDefault 46 ms
Dapper raw SQL Query (non-buffered) 59 ms
NReco.Data raw SQL to dictionary 50 ms
NReco.Data raw SQL to RecordSet 50 ms
NReco.Data raw SQL to Model 59 ms
NReco.Data abstract Query to Model 60 ms
In the same scenarios NReco.Data shows performance very close to Dapper, but in addition it supports data annotations, abstract dynamic queries and very fast schema-less data access API.

what's new  Watch NReco.Data on Github

2017 Mar 24 v.1.0.2 changes:
  • added DataReaderResult class that can map data from any IDataReader to model/dictionary/recordset.
  • added DbDataAdapter.Select(IDbCommand cmd) overload (execute custom db command)
  • added ExecuteReader/ExecuteReaderAsync methods to DbDataAdapter.SelectQuery for custom result handling (like processing multiple result sets).
2017 Mar 03 v.1.0.1 changes:
  • added DbDataAdapter.SelectQuery.SetMapper method (custom POCO-models mapping handler) #28
  • fixed issue with insert/update for binary type columns (varbinary, BLOB), special handling for parameters with NULL value #24
  • fixed issue with incorrect handling of schema qualified tables in DbDataAdapter #30
2017 Jan 31 v.1.0.0 (final, production-ready) changes:
  • fixed issue with Select().ToRecordSet when no records returned: now returns empty recordset instead of null
  • DbDataView: do not wrap select expressions with brackets (some databases like sqlite don't support this syntax)
  • DbDataAdapter throws ExecuteDbCommandException that keeps reference to IDbCommand that causes an exception
  • added DbFactory.IdentifierFormat for specifying db-dependent delimited identifiers (table and column names)
  • now relex supports table name in quotes (with special :table type) and "field" constant type (used for field names with spaces)
  • fixed bug in mapping null values to POCO (null cannot be converted exception) + POCO mapping performance improvements (create instance, default values, set value)
2016 Sept 19 v.1.0.0-alpha6 changes:
  • added support for annotated models (attributes from System.ComponentModel.DataAnnotations)
  • added DbDataAdapter.Select overload that accepts raw sql (works like EF Core FromSql, supports parameters)
  • added RecordSet.FromModel method: creates recordset with schema and data from annotated POCO model(s)
  • added missed async methods for all DbDataAdapter Insert/Update methods
  • DbDataAdapter disposes all DbCommand instances
  • introduced IRecordSetAdapter interface
  • Fixed DbDataAdapter.Select().ToDictionaryAsync signature
2016 Aug 24 v.1.0.0-alpha5 changes:
  • RecordSet enhancements: added Column.ReadOnly property, strongly-typed row value accessor (Row.Field<>), Add(IDicitonary<string,object>), introduced ColumnCollection
  • fixed bug in DbAdapter.Update(RecordSet) when connection is closed
  • implemented RecordSetReader (DbDataReader for RecordSet)
  • added RecordSet.FromReader (load RecordSet from any IDataReader)
  • full async support for DbAdapter.Select + DbAdapter.DeleteAsync, DbAdapter.UpdateAsync(RecordSet), RecordSet.FromReaderAsync
2016 Aug 15 v.1.0.0-alpha4 changes:
2016 Jul 20 v.1.0.0-alpha3 changes:
  • added DbDataView (DbCommandBuilder.Views) for application-level data views that accessed like read-only tables. Useful for encapsulating complex SQL queries, stored procedures.
  • added DbBatchCommandBuilder for generating several SQL statements into one DbCommand
  • added DbDataAdapter.ApplyOffset property (option for disabling offset logic on data reader level if offset is applied on DB level)
  • bugfixes
2016 Jul 17 v.1.0.0-alpha2 changes:
  • added DbDataAdapter for CRUD operations, supports mapping to POCO
  • a lot of small changes and fixes
2016 Jul 16 v.1.0.0-alpha1 first version:
  • abstract Query structure
  • DbCommandBuilder for generating SELECT, INSERT, UPDATE and DELETE SQL statements
  • generic DbFactory suitable for popular ADO.NET providers (SqlClient, Sqlite, Npgsql, MySQL)
  • parser for query string representation (relex)
  • builds for both .NET 4.5 and .NET Core

more components

  • PivotData Toolkit for .NET

    .NET components for manipulating multidimensional dataset (in-memory data cube) without OLAP server, aggregating huge datasets, calculating data for pivot table / chart reports, ASP.NET pivot table builder control.

  • Natural Language Query Parser

    NLQuery parses search-based queries and performs named entity recognition (NER) in context of the structured data source (database, OLAP cube). Recognition results may be used for generating formal data query (for example SQL) and providing natural language interface in .NET applications.