nreco / data

Fast DB-independent DAL for .NET Core: abstract queries, SQL commands builder, schema-less data access, POCO mapping (micro-ORM).

NReco.Data

Lightweight high-performance data access components for generating SQL commands, mapping results to strongly typed POCO models or dictionaries, schema-less CRUD-operations with RecordSet.

NuGet	Windows x64	Ubuntu

• very fast: NReco.Data shows almost the same performance as Dapper but offers more features
• abstract DB-independent Query structure: no need to compose raw SQL in the code + query can be constructed dynamically (at run-time)
• automated CRUD commands generation
• generate several SQL statements into one IDbCommand (batch inserts, updates, selects for multiple recordsets: DbBatchCommandBuilder)
• supports mapping to annotated POCO models (EF Core entity models), allows customized mapping of query result
• API for schema-less data access (dictionaries, RecordSet, DataTable)
• can handle results returned by stored procedure, including multiple record sets
• application-level data views (for complex SQL queries) that accessed like simple read-only tables (DbDataView)
• parser for compact string query representation: relex expressions
• can be used with any existing ADO.NET data provider (SQL Server, PostgreSql, Sqlite, MySql, Oracle etc)
• supports .NET Framework 4.5+, .NET Core 2.x / 3.x (netstandard2.0)

Quick reference

Class	Dependencies	Purpose
DbFactory		incapsulates DB-specific functions and conventions
DbCommandBuilder	IDbFactory	composes IDbCommand and SQL text for SELECT/UPDATE/DELETE/INSERT, handles app-level dataviews
DbDataAdapter	IDbCommandBuilder, IDbConnection	CRUD operations for model, dictionary, DataTable or RecordSet: Insert/Update/Delete/Select. Async versions are supported for all methods.
Query		Represents abstract query to database; used as parameter in DbCommandBuilder, DbDataAdapter
RelexParser		Parsers query string expression (Relex) into Query structure
RecordSet		RecordSet model represents in-memory data records, this is lightweight and efficient replacement for classic DataTable/DataRow
DataReaderResult	IDataReader	reads data from any data reader implementation and efficiently maps it to models, dictionaries, DataTable or RecordSet

NReco.Data documentation:

• Getting started and HowTos
• Full API Reference
• something is still not clear? Feel free to ask a question on StackOverflow

How to use

Generic implementation of DbFactory can be used with any ADO.NET connector.

DbFactory initialization for SqlClient:

var dbFactory = new DbFactory(System.Data.SqlClient.SqlClientFactory.Instance) {
	LastInsertIdSelectText = "SELECT @@IDENTITY" };

DbFactory initialization for Mysql:

var dbFactory = new DbFactory(MySql.Data.MySqlClient.MySqlClientFactory.Instance) {
	LastInsertIdSelectText = "SELECT LAST_INSERT_ID()" };

DbFactory initialization for Postgresql:

var dbFactory = new DbFactory(Npgsql.NpgsqlFactory.Instance) {
	LastInsertIdSelectText = "SELECT lastval()" };

DbFactory initialization for Sqlite:

var dbFactory = new DbFactory(Microsoft.Data.Sqlite.SqliteFactory.Instance) {
	LastInsertIdSelectText = "SELECT last_insert_rowid()" };

DbCommandBuilder generates SQL commands by Query:

var dbCmdBuilder = new DbCommandBuilder(dbFactory);
var selectCmd = dbCmdBuilder.GetSelectCommand( 
	new Query("Employees", (QField)"BirthDate" > new QConst(new DateTime(1960,1,1)) ) );
var selectGroupByCmd = dbCmdBuilder.GetSelectCommand( 
	new Query("Employees").Select("company_id", new QAggregateField("avg_age", "AVG", "age") ) );
var insertCmd = dbCmdBuilder.GetInsertCommand(
	"Employees", new { Name = "John Smith", BirthDate = new DateTime(1980,1,1) } );
var deleteCmd = dbCmdBuilder.GetDeleteCommand(
	new Query("Employees", (QField)"Name" == (QConst)"John Smith" ) );

DbDataAdapter - provides simple API for CRUD-operations:

var dbConnection = dbFactory.CreateConnection();
dbConnection.ConnectionString = "<db_connection_string>";
var dbAdapter = new DbDataAdapter(dbConnection, dbCmdBuilder);
// map select results to POCO models
var employeeModelsList = dbAdapter.Select( new Query("Employees") ).ToList<Employee>();
// read select result to dictionary
var employeeDictionary = dbAdapter.Select( 
    new Query("Employees", (QField)"EmployeeID"==(QConst)newEmployee.EmployeeID ).Select("FirstName","LastName") 
  ).ToDictionary();
// update by dictionary
dbAdapter.Update( 
	new Query("Employees", (QField)"EmployeeID"==(QConst)1001 ),
	new Dictionary<string,object>() {
		{"FirstName", "Bruce" },
		{"LastName", "Wayne" }
	});
// insert by model
dbAdapter.Insert( "Employees", new { FirstName = "John", LastName = "Smith" } );  

RecordSet - efficient replacement for DataTable/DataRow with very similar API:

var rs = dbAdapter.Select(new Query("Employees")).ToRecordSet();
rs.SetPrimaryKey("EmployeeID");
foreach (var row in rs) {
	Console.WriteLine("ID={0}", row["EmployeeID"]);
	if ("Canada".Equals(row["Country"]))
		row.Delete();
}
dbAdapter.Update(rs);
var rsReader = new RecordSetReader(rs); // DbDataReader for in-memory rows

Relex - compact relational query expressions:

var relex = @"Employees(BirthDate>""1960-01-01"":datetime)[Name,BirthDate]"
var relexParser = new NReco.Data.Relex.RelexParser();
Query q = relexParser.Parse(relex);

More examples

• Command Builder: illustrates SQL commands generation, command batching (inserts)
• Data Adapter: CRUD operations with dictionaries, POCO, RecordSet
• DataSet GenericDataAdapter: how to implement generic DataSet DataAdapter (Fill/Update) for any ADO.NET provider
• SQL logging: how to extend DbFactory and add wrapper for DbCommand that logs SQL commands produced by DbDataAdapter
• DB WebApi: configures NReco.Data services in MVC Core app, simple REST API for database tables
• MVC Core CRUD: full-functional CRUD (list, add/edit forms) that uses NReco.Data as data layer in combination with EF Core
• DB Metadata: extract database metadata (list of tables, columns) with information_schema queries
• GraphQL API for SQL database: provides simple GraphQL API by existing database schema (simple queries only, no mutations yet)

Who is using this?

NReco.Data is in production use at SeekTable.com and PivotData microservice.

License

Copyright 2016-2023 Vitaliy Fedorchenko and contributors

Distributed under the MIT license

data's Issues

Does this library handle sending a UDT as a stored procedure parameter?

I was curious if you have an abstraction for stored procedures, specifically sending in User Defined Table Types as parameters. The old "ToDataTable" method in the old .Net extensions doesn't work and I'm trying to send something to the stored procedure as a SqlDbType.Structured parameter. The extensions used to convert a List<> to a DataTable and that could be sent to the stored procedure. I'm using the FromSql() method for other stored procedure calls rather successfully, but now things are bogged down by this. THANKS!

CRUD abstraction and implementation.

I have implemented the CRUD, but I still have a few issues. The issues are described in the second message.

First, about the attached files.
Idea is to create a layer depends only on Dictionaries, modify/comment/use them. That realization isn't included the Batch operations, but will be included in the next version.

СRUD.zip

The basic idea is to create an abstraction level, for that operation you have to put a list of the following class

        public class CRUDInfo
        {
            /// <summary>
            /// Table name
            /// </summary>
            public string TableName;
            /// <summary>
            /// ID name
            /// </summary>
            public string IdName;


            public KeyValuePair<string, object> IdPair
            {
                get
                {
                    return new KeyValuePair<string, object>(IdName, ObjectDictionary[IdName]);
                }
            }
            /// <summary>
            /// Dictionary contains dictionary with datatable name of columns and values 
            /// </summary>


            public IDictionary<string, object> ObjectDictionary;
}

CRUD operations:

            abstract internal void Create(IEnumerable<CRUDInfo> data);
            abstract internal IEnumerable<CRUDInfo> Retrieve(IEnumerable<CRUDInfo> rInfo);
            abstract internal void Update(IEnumerable<CRUDInfo> data);
            abstract internal void Delete(IEnumerable<CRUDInfo> data);

For example, you can pass a following list to Create

   crudInfoList.Add( new CRUDInfo()
            {
                TableName = "People",
                IdName = "PeopleId",
                ObjectDictionary = new Dictionary<string, object>()
            {
                { "PeopleId", "123"},
                { "Name", "John"},
            }
            };);
    crudInfoList.Add( new CRUDInfo()
            {
                TableName = "Pets",
                IdName = "Id",
                ObjectDictionary = new Dictionary<string, object>()
            {
                { "Id", "1"},
                { "Name", "John' pet"},
                { "PeopleFK", "123"},
            }
            };);
///This command should create the two records
Create(crudInfoList);

Ability to log/intercept IDbCommand before execution in DbDataAdapter

Currently it is not possible to get IDbCommand instance that is internally produced by DbDataAdapter.
This might be useful in the following scenarios:

• logging of sql commands
• collecting performance metrics (time of command execution)

Technically this might be a delegate (or protected method to override).

Add RecordSet.Load(IDataReader) method

Move functionality that populates RecordSet by IDataReader from DbDataAdapter to RecordSet.Load(IDataReader) method.
In addition to more flexibility, this will enable ability to read multiple result sets into several RecordSets (command with several SQL select statements may be generated by DbBatchCommandBuilder).
Also Load(IDataReader) is usable for loading data from any custom DbCommand (say, to load results returned by stored procedure).

GraphQL API example: auto-generated schema by SQL database

Hi guys,

Let's try to implement GraphQL.Net based on NReco.Data (I'm talking about this project - https://github.com/graphql-dotnet/graphql-dotnet )
So, here's my vision:

• add one API endpoint (let's call it - graphql)
• try to implement graphql.net based on NReco.Data (just simple 1-level queries)

Thx, D.

Add IRecordSetAdapter interface

Add IRecordSetAdapter interface (and implement it in DbDataAdapter)

interface IRecordSetAdapter {
 RecordSet Select(Query q);
 Task<RecordSet> SelectAsync(Query q);
 int Update(string table, RecordSet rs);
 Task<int> UpdateAsync(string table, RecordSet rs);
}

Any way to obtain metadata?

Is there any way to obtain data source metadata?

Tables, Columns, Column Types, etc.

'OFFSET' keyword is generating even if there is no records limit and offset is 0

For MySQL, we are using the following select template:

SELECT @columns FROM @table @where[WHERE {0}] @orderby[ORDER BY {0}] @recordcount[LIMIT {0}] @recordoffset[OFFSET {0}]

During selecting all accounts (without any limits) we've got incorrect generated sql query:

SELECT * FROM accounts OFFSET 0

According to format rules, @recordcount marked as NotDefined because we are selecting max count of records but OFFSET is still rendering because @recordoffset token exists even it has 0 value.
I think it should be ok to mark @recordoffset token as NotDefined if value is 0. It should not affect the result dataset.

Implement RecordSet index for fast rows access

In some cases it is useful to have fast access to in-memory rows represented by RecordSet structure (for example, for getting rows by ID values - like Legacy DataRowCollection.Find method).

I think RecordSet should remain as simple as possible (it should not become a monster like DataTable) and it internally should not handle any indexed access. Instead of that, separate class (RecordSetIndex) may incapsulate this functionality; it may accept list of columns for indexed access (may be used not only for primary key):

RecordSet rs;
var rsIdIndex = new RecordSetIndex(rs, "id");
var someRow = rsInIndex[5].FirstOrDefault();

RecordSetIndex should have .Refresh() method to rebuild index in case if RecordSet is changed.

Insert operation fails when executing in Transaction

Insert operation works perfect without Transaction, however it is failing inside Transaction.
Since, TransactionScope is not working in NetCore 2, I was manually opened transaction using BeginTransaction method in Connection.
Example:

var isClosedConn = dbDataAdapter.Connection.State == ConnectionState.Closed;
if (isClosedConn)
dbDataAdapter.Connection.Open();
using (var tr = dbDataAdapter.Connection.BeginTransaction()) {
try {
dbDataAdapter.Transaction = tr;
var insertedCount = dbDataAdapter.InsertAsync(newAcc).Result;
tr.Commit();
} catch (Exception ex) {
tr.Rollback();
}
finally {
if (isClosedConn)
dbDataAdapter.Connection.Close();
dbDataAdapter.Transaction = null;
}

After main insert operation and if you have autoincrement column in your POCO model, additional select is executing to get last inserted id and failing because transaction was not injected to that command.

table schema name

Is it possible to select/update/insert on tables with custom schema name and how?
Can't find nothing about that

Custom object mapper for DbDataAdapter.Select result

Currently DbDataAdapter.SelectQuery can perform simple column-to-property POCO mapping.
In some cases single query result should be mapped to more complex structure with nested objects.

This is possible to achieve with custom mapping handler, smth like this:

DbDataAdapter dbAdapter;
var myModel = dbAdapter.Select(...)
   .Map( (context) => {
     var res = new MyModel();
     context.MapTo(res);   // populate properties of main model
     context.MapTo(res.Submodel);  // populate properties of submodel
     return res;
   } )
   .Single<MyModel>();

Also context.MapTo should have an overload that will accept column-to-property map (Dictionary<string,string>).

Relex Parser Spaces in Names

How can someone use escape spaces in column names.
For Example
string relex = @"Users([user id]=100:int)[[name],[last name]]"; var relexParser = new NReco.Data.Relex.RelexParser(); var query = relexParser.Parse(relex);

returns
An unhandled exception of type 'NReco.Data.Relex.RelexParseException' occurred in NReco.Data.dll
Additional information: Invalid syntax (position: 7, expression: Users([user id]=100:int)[[name],[last name]])

Implement DbDataReader/IDataReader for RecordSet

In some cases it is useful to have in-memory structure that implements IDataReader interface; in full .NET framework this is possible with DataTable + DataTableReader.

Lets add RecordSet reader (once it is implemented in #2 ): RecordSetReader.

Usage example:

• load a lot of records with DbDataAdapter.Select into RecordSet
• copy them with SqlBulkCopy.WriteToServer(DbDataReader reader) -- with help of RecordSetReader

Implement RecordSetReader.GetSchemaTable for compatibility with netcore3 GetColumnSchema() impl

Implementation of CanGetColumnSchema GetColumnSchema (DbDataReaderExtensions) was changed: dotnet/runtime@c10cc1e

In particular, now CanGetColumnSchema always returns true (previously it was true only if DbDataReader implements IDbColumnSchemaGenerator). As a result, now check CanGetColumnSchema becomes useless -> it is expected that DbDataReader always implement GetSchemaTable.

To fix this RecordSetReader.GetSchemaTable should be implemented.

Unable to only select First without specifying order By

In the NReco.GraphQL documentation, the template string looks as follows for MSSQL:

SELECT @columns FROM @table @where[ WHERE {0}] @orderby[ ORDER BY {0}]  @recordcount[LIMIT {0}] @recordoffset[OFFSET {0}]"

From my understanding, the Limit keyword does not exist in T-SQL, but rather it exists in MySQL. And if you look at the MySQL Select Template string on the documentation, it uses FETCH rather than Limit which seems is just a typo. Correct me if I am wrong.

I ran across an issue when using the NReco.GraphQL library, and wanting to only query the first 10 records. The Syntax you have in the Select Template works, but only if I assign a ORDER BY clause. When I query the first: 10 in the graphql string, when the select query is run, my profiler displayed the select query similar to the following:

SELECT Name, Date, Number FROM Table FETCH 10

Which would not work because FETCH as well as OFFSET have to follow the ORDER BY clause. I temporarily solved the issue by replacing @recordcount[FETCH {0}] from the back to the front as @recordcount[TOP {0}] but I realize that might not be the best solution. Is there a way to have a fetch query without having to specify the Order By clause?

Working example for mvc+react+EF+nreco.data

Hi guys,

It seems that we don't have example which is based on NReco.Data.

Let's try to develop it by using next stack:

• mvc (.net core/api)
• react (or angular 2, we should mull it over and decide what we will use for our example)
• EF - CodeFirst and migration
• NReco.Data - to provide data from database

Please, confirm these steps.

Thx, D.

Update relex wiki

" like " terminal is missing in 'condition_type' symbol in Relex BNF

<condition_type> ::= "=" | "==" | "!=" | "<>" | ">=" | "<=" | " in " | " !in " | " like ";

Example for DataSet CRUD with GenericDataAdapter

It is possible to use DbCommandGenerator to produce commands for generic System.Data.Common.DbDataAdapter implementation and use it with any ADO.NET connector- especially if database-specific DataAdapter (or CommandBuilder) is not available at all: like in Microsoft.Data.Sqlite or MySqlConnector or even System.Data.SqlClient (SqlCommandBuilder is not available in netstandard2.0).

Migrate project.json to SDK 1.0 csproj

SDK 1.0 was released several days ago and now project.json is a history and no longer supported.

Add support of nullable types for DataReaderResult.Single<T>()

Currently it is not possible to read single value if it can be null, smth like Select<int?>()

DbDataAdapter for typed poco

It is possible to add DbDataAdapter for typed CRUD operations with POCO with methods:

class DbDataAdapter<T> {
 T LoadByKey(params object[] key);
 List<T> Load(QConditionNode condition);  // maybe this method should be protected ?..
 void Insert(T model);
 void Update(T model);
 void Delete(T model);
 void DeleteByKey(params object[] key);    
}

• async methods

This implementation may effectively reuse DbCommand instances and perform POCO-mapping very fast by caching getters/setters.

Implement RecordSet - lighweight and efficient structure for set of db records

Corefx System.Data library doesn't include DataTable/DataRow any more; currently NReco.Data supports IDictionary<string,object> for schema-less representation of DB records. Despite of simplicity, this approach have significant drawbacks when used for record-sets:

• efficient data load of 1,000+ records by select queries (creating 1,000+ dictionaries definitely far from optimal solution)
• access to metadata about records columns (name/type)
• efficient mass inserts / updates

Internally RecordSet should store record data like DataRow (as object[]) and have Columns list declarations.
DbDataAdapter.Select should be able to return results as RecordSet .ToRecordSet(), Insert / Update methods should have overloads that accept RecordSet.

Sqlite-based unit tests

Classes to cover:

• DbDataAdapter (one-record, RecordSet)
• DbCommandGenerator

Provide running project for asp.net core

I am getting lots of error after downloading sample project from github. What to do now?.

Please make lowercase keys in ColNamMap dictionary

data/src/NReco.Data/DataMapper.cs

Line 160 in 59a0cba

ColNameMap[Columns[i].ColumnName] = Columns[i];

data/src/NReco.Data/DataMapper.cs

Line 172 in 59a0cba

ColNameMap.TryGetValue(colName, out colMapping);

Mapper doesn't work when column and POCO property are in different case (For example: item_id and Item_ID)

DbDataAdapter async methods

SelectQuery (returned by DbDataAdapter.Select) should have the following methods:

• SingleAsync
• ToDictionaryAsync
• ToDictionaryListAsync
• ToListAsync
• ToRecordSetAsync

Async versions for other DbDataAdapter methods:

• DeleteAsync(Query)
• UpdateAsync(RecordSet)

I'm not sure that Insert/Update for single record should have async versions (insert/update for one record is usually fast enough -- lets wait until someone really asks for them).

Support data annotations for POCO mapping

DbDataAdapter Select/Insert/Update methods: handle System.ComponentModel.DataAnnotations.Schema.ColumnAttribute (NotMappedAttribute, DatabaseGenerated)

I think DbDataAdapter.Select(Query, IDictionary<string,string> fldMap) should be removed: custom mapping will be covered by DbDataAdapter for typed POCO in #13 .

Lets add RecordSet.FromList(IList list) method that will automatically infer RecordSet schema from poco. It should handle KeyAttribute, ColumnAttribute, NotMappedAttribute, DatabaseGeneratedAttribute.

Get details using SQL Stored Procedure in Nreco.data, .net core c#

How to get result table using SQL stored procedures with parameter values in NReco.Data.DbDataAdapter
.Net core

Nullable Type in Graphql Schema Object

I was looking through the documentation, but was not able to locate any information on how to assign a nullable type in the Nreco.GraphQL schema object field definitions.

For example. If I have a column that can be of DateTime?, when defining the Field definition, under the "DataType" entry, I would put "datetime". In GraphQL we define non-nullable types as String! so I figured not adding the ! to the DataType entry would be the same. But when querying fields that have a null value, the result returns an error, specifying that it does not not find a conversion from System.DBNull to System.Datetime.

Is it possible to query nullable types in the NReco.GraphQl library yet? Or is it down the pipeline?

Thanks for the great library.

• Xavid

Add DbDataAdapter.Select overload that accepts raw sql with parameters

Similar to EF Core "FromSql" method.
This API is needed for stored procedure calls and read results with standard API (to POCO, dictionaries or RecordSet. Also this API will be useful with stored procedures that accept TVP (table-valued-parameters).

How to get multiple Recordsets from storedprocedure?

Hi,
can you help me how to call a stored procedure, passing parameters (if possible also output parameters) which returns me multiple recordsets?
At the moment i call: dbAdapter.Select($"STOREDNAME @{nameof(SQLPARAMETER)}", SQLPARAMETER).ToRecordSet()
but there I have only the first one and also the output SQLPARAMETERS are null

Add MVC Core example that uses NReco.Data DAL

Example should illustrate:

• how to configure NReco.Data DAL with MVC Core DI-container
• how to use DbDataAdapter for POCO models
• how to use RecordSet for REST API that provides access to any DB table

Add support for DataSet/DataTable in netstandard2.0 / net45 build

DataSet/DataTable come back in netstandard2.0 ( dotnet/corefx#12426, https://github.com/dotnet/corefx/issues/8622 ), lets add extra overloads to support this structure by [DbDataAdapter] (https://github.com/nreco/data/blob/master/src/NReco.Data/DbDataAdapter.cs) class:

• DataSet SelectQuery.ToDataSet() , void SelectQuery.ToDataSet(DataSet)
• DataTable SelectQuery.ToDataTable()
• int DbDataAdapter.Update(string tableName, DataSet ds)

As result, NReco.Data.DbDataAdapter will become generic alternative to provider-specific System.Data.Common.DbDataAdapter implementations (or replacement for it in netstandard1.5-only ADO.NET connectors).

Invalid operation. The connection is closed

Hi,
in my application I make 2 api requests, sometimes I got on the second request that message:
The connection does not support MultipleActiveResultSets.
After adding MultipleActiveResultSets=True in my connection string I got sometimes an other message:
Invalid operation. The connection is closed.
But always only the second request fails. Is there missing a check if the connection is opened?

My code:

SqlParameter xmlInput = new SqlParameter("@xmlInput", SqlDbType.Xml);
xmlInput.Value = "<SQLP>...</SQLP>";
dbAdapter.Select($"storedProcedureName @{nameof(parameters.xmlInput)}", parameters.xmlInput)

Issue with schema qualified tables for DbDataAdapter insert/update

In Insert/Update methods (DbDataAdapter class) that accept table name schema-qualified names (like 'main.contacts' are handled incorrectly.

Handle IDisposable in DbDataAdapter

• Implement IDisposable for DbDataAdapter
• Dispose IDataCommand instances used inside DbDataAdapter. Select method might need to be reorganized to do that (create db command only when "SelectQuery.ToSmth" method is called?)

Convert POCO models to TVP for SQL Server stored procedure UDT parameters

This is SQL-Server specific feature (table-valued parameters), so I guess it is better to implement it in separate assemply (say, NReco.Data.SqlClient ).

Primary interface for the component:

• input: RecordSet, or list of POCO models (possibly data-schema annotated)
• output SqlParameter that may be passed to EF Core FromSql (or DbDataAdapter.Select(string, params object[] args) from #15 )

Proof of concept provided by @SpilledMilkCOM (from issue #14 )
GitHub sample.txt

Adapter. ToFullDictionary()

I have found that to extract full dataset you have to implement POCO model, whereas ToDictionary() returns headers only.
Could you provide a method to extract all dataset to Dictionary? I made something by myself, but I'm not sure it is well written and done according to .NET core.

        public Dictionary<string, List<object>> ToFullDictionary()
        {
            System.Collections.Concurrent.ConcurrentDictionary<string, List<object>> results = new System.Collections.Concurrent.ConcurrentDictionary<string, List<Object>>();

             DataHelper.ExecuteReader(SelectCommand, CommandBehavior.Default, DataReaderRecordOffset, Query.RecordCount,
                (rdr) => {
                    Dictionary<string, object> dictionary = ReadDictionary(rdr);
                    foreach (KeyValuePair<string, object> entry in dictionary)
                    {
                        if (!results.ContainsKey(entry.Key))
                            results[entry.Key] = new List<object>();
                        results[entry.Key].Add(entry.Value);
                    }
                });
            return results.ToDictionary(dict => dict.Key, dict => dict.Value); ;
        }

Love it!!

This is like blue print for NqSql and JS, but only better!

Can you also support graphs like stateless - and markdown. With this you can easily do graph or db queries - i.e. go back and forth without worrying what the backend is, for e.g. I could tell it query a queue or even stream or db or graph!!!

Way to go FANTASTIC job 🏆

Add ability to set 'ApplyOffset' option only for specific Select() call

Currently DbDataAdapter.ApplyOffset option is applied for all Select() calls of the DbDataAdapter instance. In some cases ApplyOffset should be false only for some concrete queries (say, to some specific dataview), lets add to Select() result ability to specify this option with 'SetApplyOffset' method.

Add == and != operator overload for DBNull

Right now "IS NULL" (or "IS NOT NULL") condition is possible only with
new QConditonNode( (QField)"col_name", Conditions.Null, null) what is not very handy.

Lets add overloads for DBNull and allow comparisons like (QField)"col_name" == DBNull.Value

Escape column names

QField value may have special characters like spaces.
DbSqlExpressionBuilder can be enhanced to render such fields in a special way - in brackets (SQL Server) or backquotes (MySQL).
DbFactory should have a property for configuring escape rules (format string).

Add example for custom POCO mapping handler

DbDataAdapter.SelectQuery.SetMapper was added in #28 , lets add simple example that illustrates how to use it (map select result to several POCO models, custom mapping logic).

Extend RecordSet

Hi,
i think it would be a nice feature to add some methods to the RecordSet:

ToList<T>()
ToDictionaryList()
Single<T>()

Add async methods for DbDataAdapter Insert/Update (dictionary/poco)

Support for commands with multiple result sets

Lets add support for handling SQL commands that return multiple result sets:

• DbDataAdapter.Select(IDbCommand) overload (to allow custom command if needed)
• DbDataAdapter.SelectQuery() should handle multiple results sets (still not sure how to do that in best way).

Add records access control example

Sometimes access to the database records should be controlled on a data layer. Typical situations:

• SaaS applications that use single database for all "instances"; in this case data is isolated by adding something like "instance_id" or "client_id' in all tables. Data layer should guarantee that business logic will able to select/update/delete records only of the current "instance".

• business apps where access to the records is controlled by some rules. For instance, someone should able to select a record only if it is "assigned" to this record.

NReco.Data supports this kind of access control; it is possible to add extra 'access control' conditons to the Query before execution by wrapping IDbCommandBuilder (responsible for ADO.NET commands generation by Query). Lets add simple example to illustrate this approach.

How to have binary data in your POCO?

I could use some help inserting binary data.

The database I'm using is sql server 2014. The column i'm trying to insert som bytes into looks like this:

[BinaryData]varbinary NULL,

In my code the property looks like this:

public byte?[] BinaryData { get; set; }

I'm simply inserting this using

dbAdapter.Insert("MyTable", entity);

but when It's inserted I get this exception "Implicit conversion from data type nvarchar to varbinary(max) is not allowed".

If I comment out the binary property everything works just fine.

I've tried adding the Column annotation like this on the property in my code but that didnt help.

[Column(“BinaryData", TypeName="varbinary")]

Some help would be much appreciated.

Add DbDataAdapter.Select that accepts FormattableString

Lets add DbDataAdapter.Select(FormattableString) overload to support interpolated string like in EF Core 2.0; implementation should convert specified parameters to DB command parameters to avoid potential sql injection when interpolated C# strings are used.