A thin database layer to make more easy queries (.NET C#)
NET-Database query layer components will be a useful addition to any application or website to create SQL queries to your database in a OO way. It supports the majority of different database types such as SQL Server® MySQL® o PostgreSQL®, but is easy extend to any ADO.NET provider.
No, NET-Database query layer only allow query to database and not include any persistence model.
In CQRS architectures, the persistence model (command model) is separated to query model. NET- Database query layer is perfect to implement the query model, allowing it to maintain in a different process or running on different hardware.
More info about CQRS: http://martinfowler.com/bliki/CQRS.html
Other application is in Command-query separation principle: every method should either be a command that performs an action, or a query that returns data to the caller, but not both. .
More info about Command-query separation http://martinfowler.com/bliki/CommandQuerySeparation.html
Microsoft SQL Server®
<configSections>
<sectionname="daProviders"type="ADO.Query.Helper.DataAccessSectionHandler, ADO.Query" />
</configSections>
<daProviders>
<daProvideralias="MsSQL"type="ADO.Query.Helper.MsSql, ADO.Query"connectionStringName="DBConnection" />
</daProviders>
<connectionStrings>
<addname="DBConnection"connectionString="YOUR_CONNECTION_STRING" />
</connectionStrings> MySQL®
<configSections>
<sectionname="daProviders"type="ADO.Query.Helper.DataAccessSectionHandler, ADO.Query" />
</configSections>
<daProviders>
<daProvideralias="MySQL"type="ADO.Query.Helper.MySql, ADO.Query"connectionStringName="DBConnection" />
</daProviders>
<connectionStrings>
<addname="DBConnection"connectionString="YOUR_CONNECTION_STRING" />
</connectionStrings> PostgreSQL®
<configSections>
<sectionname="daProviders"type="ADO.Query.Helper.DataAccessSectionHandler, ADO.Query" />
</configSections>
<daProviders>
<daProvideralias="PgSQL"type="ADO.Query.Helper.PgSql, ADO.Query"connectionStringName="DBConnection" />
</daProviders>
<connectionStrings>
<addname="DBConnection"connectionString="YOUR_CONNECTION_STRING" />
</connectionStrings> NET-Database query layer implement factory pattern to determinate which type of class to create.
varqueryRunner=QueryRunner.CreateHelper("MsSQL",newQueryMapper());The first paramaeter allow to specificate the alias used in the configuration: this parameter identify the instance to create. The second parameter allow to pass the query mapper object used to convert query resulto to DTO.
Using dependency injection (Unity)
varcontainer=newUnityContainer();container.RegisterType<IQueryRunner>(newInjectionFactory(c =>QueryRunner.CreateHelper("MsSQL",newQueryMapper())));varqueryRunner=container.Resolve<IQueryRunner>();An override method of factory can be used without query mapper object parameter, but in this case your query will be used only to return Datatable, datareader or scalar result.
varqueryRunner=QueryRunner.CreateHelper("MsSQL");All query model implement ISqlQuery Interface
publicinterfaceISqlQuery{stringExpression{get;}IDictionary<string,object>Parameters{get;}}- Expression: SQL string to execute in database
- Parameters: Name and value collection with parameters included in SQL string
My first Query using NET-Database query layer
classQuerySimple:ISqlQuery{publicQuerySimple(){this.Expression="select id as Id, name as Name from table_in_database";}publicstringExpression{get;privateset;}publicIDictionary<string,object>Parameters{get;privateset;}}Get DataTable
varqueryRunner=QueryRunner.CreateHelper("MsSQL");vardt=queryRunner.ExecuteDataTable(newQuerySimple());Get Datareader
varqueryRunner=QueryRunner.CreateHelper("MsSQL");vardr=queryRunner.ExecuteReader(newQuerySimple());Get the first column of the first row in the query result
varqueryRunner=QueryRunner.CreateHelper("MsSQL");varid=queryRunner.ExecuteScalar<int>(newQuerySimple());Query model with parameters
classQueryWithParameters:ISqlQuery{publicQueryWithParameters(intid,stringname){this.Expression="select id as Id, name as Name from table_in_database where id = @id and name = @name";this.Parameters=newDictionary<string,object>{{"id",id},{"name",name}};}publicstringExpression{get;privateset;}publicIDictionary<string,object>Parameters{get;privateset;}}Get DTO from query result
NET-Database query layer use Slapper.AutoMapper https://github.com/SlapperAutoMapper/Slapper.AutoMapper to convert dynamic data into static types and populate complex nested child objects returned by queries.
Transform Query result to DTO
Example
publicclassSimpleDto{publicintId{get;set;}publicstringName{get;set;}}Return IEnumerable<DTO>
varresult=queryRunner.Execute<SimpleDto>(newQuerySimple()).ToList();Return Single DTO
varsingleDto=queryRunner.Execute<SimpleDto>(newQuerySimple()).ToSingle();Return First or default
varsingleDto=queryRunner.Execute<SimpleDto>(newQuerySimple()).ToFirstOrDefault();Paged result
To paged result, must implement the interface ISqlPagedQuery
publicinterfaceISqlPagedQuery:ISqlQuery{stringSqlCount{get;}intPage{get;}intItemsPerPage{get;}}A sample pagination SQL query
publicclassQueryPageSpecification:ISqlPagedQuery{publicQueryPageSpecification(intpage,intitemsPerPages){this.Expression="select...";this.SqlCount="select count(*)...";this.ItemsPerPage=itemsPerPages;this.Page=page;}publicstringExpression{get;privateset;}publicIDictionary<string,object>Parameters{get;privateset;}publicstringSqlCount{get;privateset;}publicintPage{get;privateset;}publicintItemsPerPage{get;privateset;}}Execute paged query
varpagedList=queryRunner.Execute<SimpleDto>(newQueryPageSpecification(page:1,itemsPerPages:2));and will return a PageSqlResult
publicclassPageSqlResult<T>{publiclongTotalItems{get;set;}publiclongTotalPages{get;set;}publiclongCurrentPage{get;set;}publicIEnumerable<T>Result{get;set;}}Store procedure is not supported at this moment.
Enjoy ;)
MIT License: http://opensource.org/licenses/MIT
Copyright (c) 2015, Omar del Valle ( http://odelvalle.com ) All rights reserved.
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.