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

Using SimpleAccess

Insall your required SimpleAccess implementaion from nuget

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

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

Creating SimpleAccess instance for Sql Server

ISqlSimpleAccess simpleAccess = new SqlSimpleAccess();

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
BeginTransaction Begins and return 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.

BeginTransaction and all Execute methods support async functionality

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 with Stored Procedure

SimpleAccess now also provide SqlEntityRepository and SqlRepository has renamed to SqlSpRepository. Both SqlSpRepository and SqlEntityRepository implements the ISqlRepository interface (version 3.1)

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

All these operations also support async functionality

SqlEntityRepository is designed for the developers who don’t use the Stored Producers

Paged Extension methods

Methods Description
GetDynamicPagedList<TEntity> Get the paged records of the entity as IEnumberable<dynamic>
GetEntityPagedList<TEntity> Get the paged records of the entity as IEnumberable<TEntity>

All these operations also support async functionality

Others Extension methods

Methods Description
IsExist<TEntity> Executes the Exist query to check the record
GetCount<TEntity> Get the count of the records
GetSum<TEntity> Get the Sum of a column
GetMin<TEntity> Get the minimum value of the column
GetMax<TEntity> Get the maximum value of the column
GetAverage<TEntity> Get the average value of the column
GetAverage<TEntity> Get the average value of the column
GetAggrate<TEntity> Get the aggreage functions values as a dyanmic object
GetAggrateWithGroupBy<TEntity> Get the aggreage function values as a IEnumberable<dynamic> object

Using SimpleAccess Repository

Using SimpleAccess v3.1 SqlEntityRepository

Using SimpleAccess v3.1 SqlSpRepository (Repository based on Stored Procedures)

Using SimpleAccess v2 and later SqlRepository with StoredProcedure

Using SimpleAccess v1

Support

Simple Access ORM 3.1 supports dotnet 4.0, dotnet 4.5, .Net Standard 2.0 (for .netcore 2.1) and .Net Standard 2.1, while Simple Access ORM 2.x is built on dotnet full framework 3.5 to support our clients Windows XP applications which are deployed on 100s of PCs

Roadmap