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).

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

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.

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.

Classes to cover:

• DbDataAdapter (one-record, RecordSet)
• DbCommandGenerator

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.

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

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.

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

Is there any way to obtain data source metadata?

Tables, Columns, Column Types, etc.

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.

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!

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

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

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

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).

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>).

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

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

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.

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).

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.

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).

• 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?)

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?

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

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

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

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.

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]])

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

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);

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

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).

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.

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

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).

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).

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

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 🏆

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).

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)

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

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.

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.

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.

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.

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

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

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).