April 4, 2014 at 11:36

Micro-ORM in one class

Hello, dear habravchane!

I want to talk about my own bike tuning over ADO.NET.
Since in most of my projects I work with data in stored procedures, the idea of ​​creating an add-in appeared because of the need to read “complex” results, for example, selecting from a master table and several subordinates, and then filling models with this data.

If anyone is interested, I ask for Cat


Mapping by generating IL code to initialize the desired type from DataRecord and save 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 given the called stored procedure.

Examples of using:

Let's create a class heir that implements the creation of a connection to the database:
class MSSqlDataManager : DataManager 
{
    public MSSqlDataManager() : base(new SqlConnection("ConnectionString here")) { }
}


Fetching a simple dataset:
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 res = dm.Procedure("Test").GetList();
}

where the Test storage chooses data, for example, of this kind:
SELECT p.Id, p.Name, p.[Description], p.Price
    FROM dbo.Product p

Reading data from
storages
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;


choosing one master record and several subordinates:
Product res = dm.Procedure("Test").AddParams(new { id = 10 }).Get(p => p.Comments);

where is the structure of the model classes
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 Comments { get; set; }
        public Product()
        {
            this.Storage = new ProductStorage();
            this.Comments = new List();
        }
    }


Please note - in addition to the main properties of the class, properties and nested property classes are also initialized. To do this, you need to assign the correct name to the column names in the selection, which reflects nesting - for example, for UserLocationName it allows the mapper to find in the object for which mapping is performed (of the ProductComment type in this case), the User property, Location in it and the Name we already need.

Further more. Getting multiple master records with subordinates:
List res = dm.Procedure("Test")
      .GetList(
        (parents, detail)=>parents.First(p => p.Id == detail.ProductId).Comments
      );

In total, I announced four overloaded methods for getting one to many and many to many records, allowing 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 res = dm.Procedure("Test")
  .GetList(
    (dr, parents) => { 
        parents.Where(p=>p.Id == (int)dr["ProductId"]).First().Comments
            .Add(dm.Create(dr)); 
    },
    (dr, parents) => {  },
    ...
);

Well, if the data model does not fit the above patterns, you can use the Raw method - it accepts a lambda in which IDataReader is available, which can be used according to the situation.
dm.Procedure("Test")
  .Raw(dr =>
  {
    while (dr.Read())
    {
      ...
    }
});

Of course, there are methods for obtaining scalar values ​​and good-old Execute.
Adding parameters for storage is done by calling AddParams
dm.AddParams(new { id = 10, name = "stringparam", writeDate = DateTime.Now }) ...

A method for transmitting table-valued parameters (Table-Valued Parameters) is also implemented - of course, it works only for MS SQL Server starting from 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, we are a bit behind Dapper, especially when we call an object initializer in a singer.
The plans are to implement support for IQueryable results with passing parameters to the storage, which would be very useful in ApiController's AspNet MVC.

If someone is interested in this bike, the library code is available on github .
Thanks for attention!
Tags:

Also popular now: