UnitOfWork

UnitOfWork is a Dot Net Core Library to implemented for unit of work patterns, Query Handler Patter, repository and direct MS SQL queries using dapper.

Installation

Use Nuget the package manager to install UnitOfWork.

PM > Install-Package UnitOfWork.Service

Usage

Regular usage for EFCore

usingUnitOfWork.Service;publicvoidConfigureServices(IServiceCollectionservices){varconnectionString="Your connection string";// TKey :- Primary column type for your identity tables, ex:- int,string,Guid. It is used for Audit purporse. In Future will create extension to configure audit settings.//YourDbContext :- DbContext.//SqlScriptContext:- This is the default ISqlScriptContext you can override and modify according to your need.services.RegisterUnitOfWork<TKey,YourDbContext,SqlScriptContext>(connectionString);//Register Unit Of Work with Query Handler Pattern// TKey :- Primary column type for your identity tables, ex:- int,string,Guid. It is used for Audit purporse. In Future will create extension to configure audit settings.//YourDbContext :- DbContext.//SqlScriptContext:- This is the default ISqlScriptContext you can override and modify according to your need.//TQueryHandlerAssemblyFromType :- Assembly in which all your IQueryHandler Defined so that it can add all the Handlers into DI pipeline automaticallyservices.RegisterUnitOfWork<TKey,YourDbContext,SqlScriptContext,TQueryHandlerAssemblyFromType>(connectionString);}publicclassTestController:Controller{privatereadonlyIUnitOfWork_unitOfWork;publicTestController(IUnitOfWorkunitOfWork){_unitOfWork=unitOfWork;}publicasyncTask<IActionResult>TestService(){varcountryRepo=_unitOfWork.Repository<Country>();varcountry=countryRepo.GetFirstOrDefault(x =>x.Id==2);varcountries=countryRepo.Where(x =>x.Id==2);countryRepo.Insert(newCountry{Name="USA",Id=2});await_unitOfWork.SaveChangesAsync();}publicasyncTask<int>ExecuteAsync(objectparameter){varsqlQuery=@"EXEC [TreeSetup].[usp_RemoveTree] @TreeId = @TreeId";varresult=(await_sqlContext.ExecuteAsync(sqlQuery,parameter));returnresult;}}

Executing SP or Raw SQL Queries to the database.

publicclassTestController:Controller{privatereadonlyIUnitOfWork_unitOfWork;publicTestController(IUnitOfWorkunitOfWork){_unitOfWork=unitOfWork;}publicasyncTask<IActionResult>TestService(){//Executing SP or Script for which we dont require any return objectvarsqlQuery=@"EXEC [usp_RemoveItem] Id = @ItemId";intresult=await_unitOfWork.ExecuteSqlAsync(sqlQuery,new{ItemId=1});//Executing SP or Script for which will return a dataset of a objectsqlQuery=@"SELECT * FROM Items";IEnumerable<Item>items=await_unitOfWork.ExecuteSqlAsync<Item>(sqlQuery,null);//Executing SP or Script for which will return multiple datasets of different objects, It will return the dataset in the same order QueryItem PassedsqlQuery=@"SELECT * FROM Items WHERE ItemId = @ItemId; SELECT * FROM ItemType WHERE ItemTypeId = @ItemTypeId";varquerymapItems=newList<QueryMapItem>(){newQueryMapItem(typeof(Item),DataRetriveTypeEnum.List,"Item"),newQueryMapItem(typeof(ItemType),DataRetriveTypeEnum.List,"ItemType")};IDictionary<string,object>multipleDataSet=await_unitOfWork.ExecuteSqlAsync(sqlQuery,new{ItemId=1,ItemTypeId=1},querymapItems);varmenuTypes=multipleDataSet["Item"].Convert<IEnumerable<Item>>();varmenus=multipleDataSet["ItemType"].Convert<IEnumerable<ItemType>>();}}

Using SQL Query Handler Pattern to execute SP or Raw SQL Queries to the database

Approach 1 - Using ISqlScriptContext Interface

publicclassTestController:Controller{privatereadonlyISqlScriptContext_sqlContext;publicTestController(ISqlScriptContextsqlContext){_sqlContext=sqlContext;}publicasyncTask<IActionResult>TestService(){//Executing SP or Script for which we dont require any return objectvarsqlQuery=@"EXEC [usp_RemoveItem] Id = @ItemId";intresult=await_sqlContext.ExecuteAsync(sqlQuery,new{ItemId=1});//Executing SP or Script for which will return a objectsqlQuery=@"SELECT top 1 * FROM Items";Itemitem=await_sqlContext.GetAsync<Item>(sqlQuery,null);//Executing SP or Script for which will return a dataset of a objectsqlQuery=@"SELECT * FROM Items";IEnumerable<Item>items=await_sqlContext.SelectAsync<Item>(sqlQuery,null);//Executing SP or Script for which will return multiple datasets of different objects, It will return the dataset in the same order QueryItem PassedsqlQuery=@"SELECT * FROM Items WHERE ItemId = @ItemId; SELECT * FROM ItemType WHERE ItemTypeId = @ItemTypeId";varquerymapItems=newList<QueryMapItem>(){newQueryMapItem(typeof(Item),DataRetriveTypeEnum.List,"Item"),newQueryMapItem(typeof(ItemType),DataRetriveTypeEnum.List,"ItemType")};IDictionary<string,object>multipleDataSet=await_sqlContext.ExecuteQueryMultipleAsync(sqlQuery,new{ItemId=1,ItemTypeId=1},querymapItems);varitems=multipleDataSet["Item"].Convert<IEnumerable<Item>>();varitemTypes=multipleDataSet["ItemType"].Convert<IEnumerable<ItemType>>();}}

Approach 1 - Using IQueryHandler<TParam, TResult> Interface

This way you can keep your service clean and move all the logic to create/write SP/SQL queries in each Functional Handler

//Query Object to map the public properties with the sql querypublicclassItemQuery{publicintId{get;set;}publicintName{get;set;}}// Created this to separate the logic for all different type of Entities in a different file. Do not want to overload main service class.publicclassItemQueryHandler:IQueryHandler<ItemQuery,IEnumerable<Item>>,IQueryHandler<ItemQuery,Item>IQueryHandler<ItemQuery,int>,IQueryHandler<ItemQuery,IList<ItemType>>{privatereadonlyISqlScriptContext_sqlContext;publicTestDefaultRawQueryHandler(ISqlScriptContextsqlContext){_sqlContext=sqlContext;}asyncTask<IList<ItemType>>IQueryHandler<ItemQuery,IList<ItemType>>.ExecuteAsync(ItemQueryparameter){varsqlQuery=@"SELECT * FROM Items WHERE @ItemId = @ItemId; SELECT * FROM ItemType WHERE @ItemTypeId = @ItemTypeId";varquerymapItems=newList<QueryMapItem>(){newQueryMapItem(typeof(Item),DataRetriveTypeEnum.List,"Item"),newQueryMapItem(typeof(ItemType),DataRetriveTypeEnum.List,"ItemType")};IDictionary<string,object>multipleDataSet=await_sqlContext.ExecuteQueryMultipleAsync(sqlQuery,new{ItemId=1,ItemTypeId=1},querymapItems);varitems=multipleDataSet["Item"].Convert<IEnumerable<Item>>();varitemTypes=multipleDataSet["ItemType"].Convert<IEnumerable<ItemType>>();foreach(varitemTypeinitemTypes){itemType.items.AddRange(items.Where(x =>x.ItemTypeId==itemType.Id));}returnitemTypes;}asyncTask<IEnumerable<Menu>>IQueryHandler<ItemQuery,IEnumerable<Menu>>.ExecuteAsync(ItemQueryparameter){varsqlQuery=@"SELECT * FROM Items WHERE NAME LIKE '%@Name%'";varresult=(await_sqlContext.SelectAsync<Menu>(sqlQuery,parameter));returnresult;}asyncTask<int>IQueryHandler<ItemQuery,int>.ExecuteAsync(ItemQueryparameter){varsqlQuery=@"EXEC [usp_CreateItem] @Id = @Id, @Name = @Name";varresult=(await_sqlContext.ExecuteAsync(sqlQuery,parameter));returnresult;}asyncTask<Item>IQueryHandler<ItemQuery,Item>.ExecuteAsync(ItemQueryparameter){varsqlQuery=@"SELECT * FROM Items WHERE Id = @Id";varresult=(await_sqlContext.GetAsync(sqlQuery,parameter));returnresult;}}publicclassTestController:Controller{privatereadonlyIQueryDispatcher_queryDispatcher;publicTestController(IQueryDispatcherqueryDispatcher){_queryDispatcher=queryDispatcher;}publicasyncTask<IActionResult>TestService(){//Executing SP or Script for which we dont require any return objectintresult=await_queryDispatcher.DispatchAsync<ItemQuery,int>(newItemQuery{Id=2,Name="Item1"});//Executing SP or Script for which will return a objectItemitem=await_queryDispatcher.DispatchAsync<ItemQuery,Item>(newItemQuery{Id=2});//Executing SP or Script for which will return a dataset of a objectIEnumerable<Item>items=await_queryDispatcher.DispatchAsync<ItemQuery,IEnumerable<Item>>(newItemQuery{Name="Item"});//Executing SP or Script for which will return multiple datasets of different objects, It will return the dataset in the same order QueryItem PassedsqlQuery=@"SELECT * FROM Items WHERE @ItemId = @ItemId; SELECT * FROM ItemType WHERE @ItemTypeId = @ItemTypeId";varquerymapItems=newList<QueryMapItem>(){newQueryMapItem(typeof(Item),DataRetriveTypeEnum.List,"Item"),newQueryMapItem(typeof(ItemType),DataRetriveTypeEnum.List,"ItemType")};IDictionary<string,object>multipleDataSet=await_sqlContext.ExecuteQueryMultipleAsync(sqlQuery,new{ItemId=1,ItemTypeId=1},querymapItems);varmenuTypes=multipleDataSet["Item"].Convert<IEnumerable<Item>>();varmenus=multipleDataSet["ItemType"].Convert<IEnumerable<ItemType>>();}}

Using on QueryHandler Pattern without EntityFramework

usingUnitOfWork.Service;publicvoidConfigureServices(IServiceCollectionservices){varconnectionString="Your connection string";// Using Query Handler Pattern - Use IQuery Dispatcher as as above given example.// SqlScriptContext:- This is the default ISqlScriptContext you can override and modify according to your need.// TQueryHandlerAssemblyFromType :- Assembly in which all your IQueryHandler Defined so that it can add all the Handlers into DI pipeline automatically, Ex:- StartUpservices.RegisterQueryHandlerPattern<SqlScriptContext,TQueryHandlerAssemblyFromType>(connectionString);//Using Direct Query in the services class without Query Handler Pattern - Use ISqlScriptContext as above given example.//SqlScriptContext:- This is the default ISqlScriptContext you can override and modify according to your need.services.AddSqlScriptContext<SqlScriptContext>(connectionString);}

Contributing

Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.

Please make sure to update tests as appropriate.

License

MIT