RecordSet - nreco/data GitHub Wiki

RecordSet model concept is very similar to DataTable/DataRow from full .NET Framework: this is in-memory representation of data rows. This structure is very lightweight (in comparing to DataTable) and can be used with large results (many thousands of rows).

Load query results to RecordSet

RecordSet can be populated from a select query:

DbDataAdapter dbAdapter; // lets assume that we have configured DbDataAdapter
var userRS = dbAdapter.Select( new Query("Users") ).ToRecordSet();
IDataReader rdr; // lets assume that we have a data reader
var someRS = new RecordSet(rdr);

When RecordSet is created in this way, schema is automatically inferred by data reader. Unlike DataTable, RecordSet doesn't bound to concrete table name, but it knows about rows columns and (optionally) about primary key:

foreach (var column in userRS.Columns) {
	Console.WriteLine($"Column: {column.Name} Type: {column.DataType}");
}

Create and populate RecordSet

Empty RecordSet may be created programmatically:

var newRS = new RecordSet( new[] {
	new RecordSet.Column("id", typeof(int) ),
	new RecordSet.Column("name", typeof(string) )
} );
newRS.Columns["id"].AutoIncrement = true; // this metadata is needed to avoid inserting/updating autoincrement columns
newRS.SetPrimaryKey("id"); // composite primary keys are also supported

Like DataRow, RecordSet.Row has State property. It is used to determine what SQL command (insert, update or delete) should be used when RecordSet is committed to the database:

var newRow = newRS.Add();  // newRow.State is 'Added'
newRow["name"] = "Bart Simpson";
dbAdapter.Update("Users", newRS); // adapted inserts newRow and it becomes 'Unchanged'
newRow["name"] = "Gomer Simpson"; // 'Modified' flag is added to the newRow.State 
dbAdapter.Update("Users", newRS); // adapter updates newRow and it again becomes 'Unchanged'
newRow.Delete(); // newRow.State becomes 'Deleted'
dbAdapter.Update("Users", newRS); // adapter deletes newRow

Also it is possible to create RecordSet by annotated POCO model:

Person p;  // Person is a model annotated with System.ComponentModel.DataAnnotations attributes
var emptyRS = RecordSet.FromModel<Person>(); // infer schema from Person properties
var oneRowRS = RecordSet.FromModel(p, RecordSet.RowState.Unchanged); // infer schema + import as row
var manyRowsRS = RecordSet.FromModel( new[] { p } ); // infer schema + import rows from sequence

DbDataReader implementation for RecordSet

It is possible to get data reader (DbDataReader / IDataReader) for any RecordSet:

RecordSet rs;
var rsReader = new RecordSetReader(rs);

Use RecordSet as Table-Valued Parameter for SQL Server stored procedure

In full .NET Framework TVP (table-valued parameters) are usually passed as DataTable; it is missed in .NET Core, but you can use RecordSet for the same purposes (EF Core example):

DbSet<MyModel> dbSet;

var tvpArgValue = new RecordSet(
    new[] {
        new RecordSet.Column("id", typeof(int)),
        new RecordSet.Column("name", typeof(string)),
    }
);
tvpArgValue .Add(new[] { 1, "Test" });
// as alternative you can use RecordSet.FromModel

var tvpParam = new SqlParameter(nameof(tvpArgValue), SqlDbType.Structured) {
  Direction = ParameterDirection.Input,
  TypeName = "dbo.TvpArgTableType",
  Value = new RecordSetReader( tvpArgValue )
};

var res = dbSet.FromSql($"EXEC dbo.SomeStoredProc @{nameof(tvpArgValue)}", tvpParam );