-
Notifications
You must be signed in to change notification settings - Fork 39
RecordSet
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).
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}");
}
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
It is possible to get data reader (DbDataReader / IDataReader) for any RecordSet:
RecordSet rs;
var rsReader = new RecordSetReader(rs);
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 );