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

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:
   • DbCommandBuilder usage examples
   • DbDataAdapter usage examples
   • MVC Core: simple REST API for SQL database
   • MVC Core: CRUD UI (list, add/edit form) that uses NReco.Data in combination with EF Core.
   • How to get database metadata by querying 'information_schema' views.
   • GraphQL for SQL DB: provides GraphQL API by existing database schema (simple queries only, no mutations yet).
6. Documentation links:
   • Query structure and usage
   • RecordSet model
   • Relex expressions

frequently asked questions

[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; }
}

var employeesList = dbAdapter.Select( new Query("Employees") )
    .ToList<Employee>()

dbAdapter.Update( 
  new Query("Employees", (QField)"id"==(QConst)5 ), 
  new {
	first_name = "John"
  } );

Employee johnEmployee;
dbAdapter.Delete( johnEmployee );

what's new  Watch NReco.Data on Github