Micro ORM one class
Hello, dear habravchane!
I want to tell you about my own bicycle over ADO.NET.
Since in most of my projects I work with data in stored procedures, the idea of creating an add-in arose because of the need to read “complex” results, for example, sampling from a master table and several subordinates, and then filling models with this data.
If anyone is interested, I ask under the cat
')
Mapping is done by generating IL code to initialize the desired type from the DataRecord and saving it in a static dictionary, which eliminates the need to re-generate the initialization code for this type, regardless of the instance of the DataManager itself, but taking into account the stored procedure being called.
Examples of using:
Create a class heir that implements the creation of a database connection:
Selection of a simple data set:
where the Test store selects data, for example, of the following form:
Reading data from
selecting one master record and several subordinates:
where is the class structure of models
Note that in addition to the basic properties of the class, properties and nested property classes are initialized. For this, it is necessary to assign the correct name reflecting nesting to the column names in the sample — for example, for UserLocationName it allows the mapper to find in the object for which mapping is performed (of the type ProductComment in this case), the User property, the Location in it, and the Name we need.
Further more. Getting several master records with subordinates:
In total, I announced four overloaded methods for obtaining one-to-many and many-to-many records that allow reading up to four sets of subordinate records. If there are more subordinate sets (which is quite rare), you can add more overloads, or use another method:
Well, if the data model does not fit the above templates, you can use the Raw method - it takes the lambda, in which IDataReader is available, which you can use according to the situation.
Of course, there are methods for obtaining scalar values and the good old Execute.
Adding parameters for storage is done by calling AddParams
The method for transferring tabular parameters (Table-Valued Parameters) is also implemented - of course, it works only for MS SQL Server since the 2008 version.
In terms of performance, quite a bit is lagging behind Dapper, especially when we call the object's initializer.
The plans are to implement support for IQueryable results with the transfer of parameters to the storage, which would be very useful in ApiControllers of AspNet MVC.
If someone is interested in this bike, the library code is available on github .
Thanks for attention!
I want to tell you about my own bicycle over ADO.NET.
Since in most of my projects I work with data in stored procedures, the idea of creating an add-in arose because of the need to read “complex” results, for example, sampling from a master table and several subordinates, and then filling models with this data.
If anyone is interested, I ask under the cat
')
Mapping is done by generating IL code to initialize the desired type from the DataRecord and saving it in a static dictionary, which eliminates the need to re-generate the initialization code for this type, regardless of the instance of the DataManager itself, but taking into account the stored procedure being called.
Examples of using:
Create a class heir that implements the creation of a database connection:
class MSSqlDataManager : DataManager { public MSSqlDataManager() : base(new SqlConnection("ConnectionString here")) { } } Selection of a simple data set:
public class Product { public int Id { get; set; } public string Name { get; set; } public string Description { get; set; } public int? Price { get; set; } } ... using (var dm = new MSSqlDataManager()) { List<Product> res = dm.Procedure("Test").GetList<Product>(); } where the Test store selects data, for example, of the following form:
SELECT p.Id, p.Name, p.[Description], p.Price FROM dbo.Product p Reading data from
the storehouse
SELECT p.Id , p.Name, , p.[Description] , p.Price , StorageId = s.Id , StorageName = s.Name FROM dbo.Product p INNER JOIN dbo.Storages s ON s.Id = p.StorageId WHERE p.Id = @Id; SELECT c.Id , c.Body , c.WriteDate , UserId = u.Id , UserName = u.Name , UserLocationId = l.Id , UserLocationName = l.Name , c.ProductId FROM dbo.Comments c INNER JOIN dbo.Users u ON u.Id = c.UserId INNER JOIN dbo.Locations l ON l.Id = u.LocationId WHERE c.ProductId = @Id; selecting one master record and several subordinates:
Product res = dm.Procedure("Test").AddParams(new { id = 10 }).Get<Product, ProductComment>(p => p.Comments); where is the class structure of models
such is
public class UserLocation { public int Id { get; set; } public string Name { get; set; } } public class UserModel { public int Id { get; set; } public string Name { get; set; } public UserLocation Location { get; set; } public UserModel() { this.Location = new UserLocation(); } } public class ProductComment { public int Id { get; set; } public string Body { get; set; } public DateTime WriteDate { get; set; } public UserModel User { get; set; } public int ProductId { get; set; } public ProductComment() { this.User = new UserModel(); } } public class ProductStorage { public int Id { get; set; } public string Name { get; set; } } public class Product { public int Id { get; set; } public string Name { get; set; } public string Description { get; set; } public int? Price { get; set; } public ProductStorage Storage { get; set; } public List<ProductComment> Comments { get; set; } public Product() { this.Storage = new ProductStorage(); this.Comments = new List<ProductComment>(); } } Note that in addition to the basic properties of the class, properties and nested property classes are initialized. For this, it is necessary to assign the correct name reflecting nesting to the column names in the sample — for example, for UserLocationName it allows the mapper to find in the object for which mapping is performed (of the type ProductComment in this case), the User property, the Location in it, and the Name we need.
Further more. Getting several master records with subordinates:
List<Product> res = dm.Procedure("Test") .GetList<Product, ProductComment>( (parents, detail)=>parents.First(p => p.Id == detail.ProductId).Comments ); In total, I announced four overloaded methods for obtaining one-to-many and many-to-many records that allow reading up to four sets of subordinate records. If there are more subordinate sets (which is quite rare), you can add more overloads, or use another method:
List<Product> res = dm.Procedure("Test") .GetList<Product>( (dr, parents) => { parents.Where(p=>p.Id == (int)dr["ProductId"]).First().Comments .Add(dm.Create<ProductComment>(dr)); }, (dr, parents) => { }, ... ); Well, if the data model does not fit the above templates, you can use the Raw method - it takes the lambda, in which IDataReader is available, which you can use according to the situation.
dm.Procedure("Test") .Raw(dr => { while (dr.Read()) { ... } }); Of course, there are methods for obtaining scalar values and the good old Execute.
Adding parameters for storage is done by calling AddParams
dm.AddParams(new { id = 10, name = "stringparam", writeDate = DateTime.Now }) ... The method for transferring tabular parameters (Table-Valued Parameters) is also implemented - of course, it works only for MS SQL Server since the 2008 version.
dm.AddEnumerableParam("Details", Enumerable.Range(1, 10) .Select(e => new {id = e, name = string.Concat("Name", e.ToString())}) ); In terms of performance, quite a bit is lagging behind Dapper, especially when we call the object's initializer.
The plans are to implement support for IQueryable results with the transfer of parameters to the storage, which would be very useful in ApiControllers of AspNet MVC.
If someone is interested in this bike, the library code is available on github .
Thanks for attention!
Source: https://habr.com/ru/post/218225/
All Articles