Skip to content
Simple Access ORM provides database access in Ado.net methods naming pattern
C#
Branch: master
Clone or download

Latest commit

Fetching latest commit…
Cannot retrieve the latest commit at this time.

Files

Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
Examples
Source
docs
.gitattributes
.gitignore
README.md

README.md

Simple Access ORM

SimpleAccess provides a simple and easy database access as well as a repository for CRUD and other helper methods.

SimpleAccess supports multiple databases. All implement the same interface for each database.

SimpleAccess also provides excpetion logging.

SimpleAccess returns data in Entity and dynamic data type but also allow developers to work on direct DataReader or DataSet

Platform support

Simple Access ORM 3.x is built on .Net Standard 2.0, while Simple Access ORM 2.x is built on dotnet full framework 3.5 to support our clients windows applications which are deployed on 100s of PCs with support of Windows XP and later Windows operating systems

Using SimpleAccess

Insall your required SimpleAccess implementaion from nuget

We will use the SimpleAccess implementation for Sql Server in our example, The Implementation for Oracle, MySql and SQLite have there own IOracleSimpleAccess with OracleSimpleAccess, IMySqlSimpleAccess with MySqlSimpleAccess and ISQLiteSimpleAccess with SQLiteSimpleAccess. They all implement ISimpleAccess

Nuget package

Sql Server

PM > Install-Package SimpleAccess.SqlServer

Oralce

PM > Install-Package SimpleAccess.Oracle

MySql

PM > Install-Package SimpleAccess.MySql

SQLite

PM > Install-Package SimpleAccess.SQLite

Creating SimpleAccess object for Sql Server

ISqlSimpleAccess simpleAccess = new SqlSimpleAccess();

There are other constructors to configurtion the SimpleAccess

Reading single record from the database as dynamic object

var person = simpleAccess.ExecuteDynamic("SELECT * FROM dbo.People where id = @id;", new { id  = 12});

Reading records from the database as IEnumerable<dynamic>

var people = simpleAccess.ExecuteDynamics("SELECT * FROM dbo.People;");

Reading multiple recoreds of a column from the database as value type

var peopleNames = simpleAccess.ExecuteValues<string>("SELECT Name FROM dbo.People;");

Reading single record from the database as Person object

var person = simpleAccess.ExecuteEntity<Person>("SELECT * FROM dbo.People where id = @id;", new { id  = 12});

Reading records from the database as IEnumerable<Person>

var people = simpleAccess.ExecuteEntities<Person>("SELECT * FROM dbo.People;");

Get DataReader to read the records from the database

var dataReader = simpleAccess.ExecuteReader("SELECT * FROM dbo.People;");

Executing aggregate query using SimpleAccess

var totalPeople = simpleAccess.ExecuteScalar<int>("SELECT COUNT(*) FROM dbo.People;");

Executes a Insert or Update SQL statement with a class object and returns the number of rows affected

public class PersonInsertViewModel
{
    public string Name { get; set; }
    public string Address { get; set; }
}

var person = new PersonInsertViewModel {Name = "Ahmed", Address = "Madina"};
var rowAffected = simpleAccess.ExecuteNonQuery("INSERT INTO dbo.People values (@name, @address);", person);

var rowAffected = simpleAccess.ExecuteNonQuery("UPDATE dbo.People SET Name=@name WHERE Id = @id;", new {id = 1, name = "Muhammad"});

Using transactions with SimpleAccess

using (var transaction = simpleAccess.BeginTrasaction())
{
    try
    {
        var person = new Person() { Name = "Ahmed", Address = "Madina" };

        var newId = simpleAccess.ExecuteScalar<int>(transaction, "INSERT INTO dbo.People VALUES (@name, @Address); SELECT SCOPE_IDENTITY();", person);

        simpleAccess.EndTransaction(transaction);
    }
    catch (Exception)
    {
        simpleAccess.EndTransaction(transaction, false);
        throw;
    }
}

SimpleAccess interface

Methods

Methods Description
BeginTrasaction Begins a database transaction.
CloseDbConnection Close the current open connection.
EndTransaction Close an open database transaction.
ExecuteEntity<TEntity> Sends the CommandText to the Database Connection and builds a TEntity from DataReader.
ExecuteEntities<TEntity> Sends the CommandText to the Database Connection and builds a IEnumerable<TEntity> from DataReader.
ExecuteDynamic Sends the CommandText to the Database Connection and builds a dynamic object from DataReader.
ExecuteDynamics Sends the CommandText to the Database Connection and builds a IEnumerable<dynamic> from DataReader.
ExecuteNonQuery Execute CommandText and returns the count of rows affected.
ExecuteReader Executes the commandText and returns a DataReader.
ExecuteScalar<T> Executes the command text, and returns the first column of the first row in the result set returned by the query.Additional columns or rows are ignored.
ExecuteValues<T> Executes the command text, and returns rows as IEnumerable<T> of the first column
Fill Execute commant text against connection and add or refresh rows in DataSet or DataTable.
GetNewConnection Gets the new connection with the SimpleAccess Ojbect ConnectionString.

All Execute and Fill methods have multiple overloads.

Creating SimpleAccess object for Sql Server

// Uses the provided connnection string
ISqlSimpleAccess simpleAccess = new SqlSimpleAccess("Data Source=SQLEXPRESS2014;Initial Catalog=SimpleAccessTest;Persist Security Info=True;Integrated Security=True;");

// Loads the connectionString from web.config or app.config connection strings
ISqlSimpleAccess simpleAccess = new SqlSimpleAccess("defaultConnectionString");

// Loads the connection string name from the value of appSetting/simpleAccess:sqlConnectionStringName key in web.confg or app.config.
ISqlSimpleAccess simpleAccess = new SqlSimpleAccess();

// Uses the provided SqlConnection object.
var sqlConnection = new SqlConnection("Data Source=SQLEXPRESS2014;Initial Catalog=SimpleAccessTest;Persist Security Info=True;Integrated Security=True;");
ISqlSimpleAccess simpleAccess = new SqlSimpleAccess(sqlConnection);

There are more constructors to configurtion the SimpleAccess

SimpleAccess with Repository pattern

SimpleAccess provides ready repository of each supported database.

Properties

Property Description
SimpleAccess Base SimpleAccess object of repository.

All methods are based on stored procedures with its related sotred procedure naming convention.

Methods

Methods Sp Name Description
Get<TEntity> TEntity_GetById
ie. People_GetById
Get TEntity by Id or anyother parameter
GetAll<TEntity> TEntity_GetAll
ie. People_GetAll
Get all TEntity object in an IEnumerable<TEntity>.
Find<TEntity> TEntity_Find
ie. People_Find
Searches for TEntity that matches the conditions defined by the specified predicate, and returns the first record of the result.
FindAll<TEntity> TEntity_Find
ie. People_Find
Searches for all TEntity that matches the conditions defined by the specified predicate, and returns the result as IEnumerable<TEntity>.
Insert<TEntity> TEntity_Insert
ie. People_Insert
Inserts the given TEntity
InsertAll<TEntity> TEntity_Insert
ie. People_Insert
Inserts all the given entities
Update<TEntity> TEntity_Update
ie. People_Update
Updates the given TEntity
UpdateAll<TEntity> TEntity_Update
ie. People_Update
Updates all the given entities
Delete<TEntity> TEntity_Delete
ie. People_Delete
Deletes TEntity by the given Id
DeleteAll<TEntity> TEntity_Delete
ie. People_Delete
Deletes all the TEntity records by the given Ids
SoftDelete<TEntity> TEntity_SoftDelete
ie. People_SoftDelete
Marks TEntity deleted by the given Id
Usingj SimpleAccess v2 SqlRepository with StoredProcedure
Using SimpleAccess v1

Support

  • SimpleAccess is written in C# and support .net Managed Code languages (C# and VB.net etc)
  • Sql Server 2005 and later
  • Oracle 10g and later (in default SimpleAccess uses Oracle Managed Data Provider for .NET)
  • SQLite
  • MySql
  • PostgreSQL (coming)

Roadmap

  • Separate SimpleCommand and Repositoy
  • vitual properties must behave like NotASpParameter marked perperty in Entities drived from StoredProcedureParameters
  • Remove StoredProcedureParameters inheritance from Enity Class to make entity more lighter
  • Add InsertAll<TEntity>, UpdateAll<TEntity>, DeleteAll<TEntity> with support of internal trasaction in Repository
  • Add Find<TEntity> and FindAll<TEntity> in Repository
  • Add ExecuteValues<T> for getting result of a single column query in IEnumerable<T>
  • NetStandard 2.0 support
  • Write unit test
  • NetStandard 2.1 support with Microsoft.Data.SqlClient
  • Configure StoredProcedure naming convention mapping to repository method (Insert, Update, Delete, Get, GetAll, Find, FindAll) methods in repository settings
  • Add Sql Generation for Non StoredProcedures command types (Insert, Update, Delete, Get, GetAll, Find, FindAll) (90%)

SqlEntityRepository Without SP implementation (version 3.1)

  • SqlEntityRepository implementation (90%)
  • Add DefaultView property in EntityAttribute
  • Add KeyAttribute for decorating primary key of an entity
  • Add database sequence support using with PrimaryKeyAttribute
  • DefaultView property in EntityAttribute support for default select
  • Column selection with Find, FindAll, Get, GetAll
  • Fixing comments, documention and adding examples
  • SimpleAccess Factory, Allow SimpleAccess to create SimpleAccess object the base of configuration(xml/json)
ISimpleAccess sqlSimpleAccess = SimpleAccessFactory.Create("SqlServer")
ISimpleAccess oralceSimpleAccess = SimpleAccessFactory.Create("OracleServer")
  • Allow developer to add database column custom/special value mapper and parameter builder using IDbDataMapper.
SimpleAccess.DataMappers.Add(new GeomaryDataMapper());
  • Allow developer to force SimpleAccess to use specific custom DataMapper to map and build parameter using DbMapperAttribute DbMapper(typeof(GeomaryDataMapper))
  • ExecuteJson and ExecuteBson
  • Rewrite code generation application
  • Allow developer to add more T4 Templates
  • Allow developer to edit T4 Templates directly inside the application
You can’t perform that action at this time.