Simple Access ORM

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

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

SimpleAccess 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

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;User ID=whoever;Password=whatever");

// 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;User ID=whoever;Password=whatever");
ISqlSimpleAccess simpleAccess = new SqlSimpleAccess(sqlConnection);

There are more constructors to configurtion the SimpleAccess

SimpleAccess with Repository pattern

SimpleAccess provides ready repository. Each database provide has it's on repository.

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

Using SqlRepository with StoredProcedure

People Table

CREATE TABLE [dbo].[People](
	[Id] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [NVARCHAR](100) NOT NULL,
	[PhoneNumbers] [NVARCHAR](30) NULL,
	[Address] [NVARCHAR](300) NULL,
	[IsDeleted] [BIT] NOT NULL,
	[CreatedBy] [BIGINT] NULL,
	[CreatedOn] [SMALLDATETIME] NULL,
	[ModifiedBy] [BIGINT] NULL,
	[ModifiedOn] [SMALLDATETIME] NULL
)

GO

People_GetById

CREATE PROC [dbo].[People_GetById]
	@id INT
AS
BEGIN
    SELECT  Id, Name, PhoneNumbers, Address, IsDeleted, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn
         FROM dbo.People
		 WHERE Id = @Id AND IsDeleted = 0;
END

People_GetAll

CREATE PROC [dbo].[People_GetAll]
AS
BEGIN
    SELECT  Id, Name, PhoneNumbers, Address, IsDeleted, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn
         FROM dbo.People;
         WHERE IsDeleted = 0;
END

People_Find

CREATE PROC [dbo].[People_Find]
	@whereClause NVARCHAR(4000)
    WITH EXEC AS CALLER
AS
BEGIN
    DECLARE @sql NVARCHAR(4000);
    SET @sql =
		'SELECT  Id, Name, PhoneNumbers, Address, IsDeleted, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn ' + ' FROM dbo.People ' +
        ISNULL(@whereClause, '') + 'AND IsDeleted = 0';

     EXEC sp_executesql @sql;
END

People_Insert

CREATE PROC [dbo].[People_Insert]
	  @name NVARCHAR(100)
	 , @phoneNumbers NVARCHAR(30)
	 , @address NVARCHAR(300)
	 , @isDeleted BIT
	 , @createdBy BIGINT
	 , @createdOn SMALLDATETIME
	 , @modifiedBy BIGINT
	 , @modifiedOn SMALLDATETIME
	,@Id INT OUTPUT
AS
BEGIN
     INSERT INTO dbo.People 
        ( Name, PhoneNumbers, Address, IsDeleted, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn )
        VALUES ( @name, @phoneNumbers, @address, @isDeleted, @createdBy, @createdOn, @modifiedBy, @modifiedOn );

	SELECT @Id = SCOPE_IDENTITY();
END

People_Update

CREATE PROC [dbo].[People_Update]
	@Id INT
	 , @name NVARCHAR(100)
	 , @phoneNumbers NVARCHAR(30)
	 , @address NVARCHAR(300)
	 , @isDeleted BIT
	 , @createdBy BIGINT
	 , @createdOn SMALLDATETIME
	 , @modifiedBy BIGINT
	 , @modifiedOn SMALLDATETIME
AS
BEGIN
    UPDATE dbo.People SET Name =  @name
         , PhoneNumbers = @phoneNumbers
         , Address = @address
         , IsDeleted = @isDeleted
         , CreatedBy = @createdBy
         , CreatedOn = @createdOn
         , ModifiedBy = @modifiedBy
         , ModifiedOn = @modifiedOn
     WHERE Id = @Id

END

People_Delete

CREATE PROC [dbo].[People_Delete]
	@Id INT
AS
BEGIN
    DELETE FROM dbo.People
    	WHERE Id = @Id

END

People_SoftDelete

CREATE PROC [dbo].[People_SoftDelete]
	@Id INT
AS
BEGIN
    UPDATE dbo.People SET IsDelete = 1
    	WHERE Id = @Id
END

c# Entity

    [Entity("People")]
    public class Person
    {
        [Identity]
        public int Id { get; set; }
        public string Name { get; set; }
        public string PhoneNumbers { get; set; }
        public string Address { get; set; }
        public bool IsDeleted { get; set; }
        public long CreatedBy { get; set; }
        public DateTime CreatedOn { get; set; }
        public long ModifiedBy { get; set; }
        public DateTime ModifiedOn { get; set; }
    }

Using SimpleAccess v2 Sql Server Repository (SqlRepository)

using System.Data;
using System.Collections.Generic;
using SimpleAccess;
using SimpleAccess.Repository;

namespace SimpleAccess.SqlServer.ConsoleTest
{
    class Program
    {
        static void Main(string[] args)
        {

            ISqlRepository repo = new SqlRepository("connectionStringName");

            // Retrive data using SimpleAccess SqlRepository
            var people = repo.GetAll<Person>();
            var person = repo.Get<Person>(1);
            person = repo.Find<Person>(b => b.Id == 1);
            people = repo.FindAll<Person>(b => b.Address.EndsWith("Munawwarah")
            								&& b.Name == "البيداء"); // EndsWith & StartsWith uses LIKE
            people = repo.FindAll<Person>(b => b.Address == null);  // Where Address is null

			// Insert
			var newPerson = new Person {
            	Name = "Ahemd"
                , PhoneNumbers = "1231231323"
                , Address = "Some address"
                , CreatedOn = DateTime.Now
                , CreatedBy = 1 // user id
            };

	    	repo.Insert<Person>(newPerson);
            Console.Write("New person id: {0}", newPerson.Id);

			//Update
            var personToUpdate = repo.GetById(1);

            personToUpdate.Name = "Muhammad";
            personToUpdate.ModifiedOn = DateTime.Now
            personToUpdate.ModifiedBy = 1 // user id

			var rowAffected = repo.Update<Person>(personToUpdate);

            var rowAffected = repo.Delete<Person>(1);


            // Retrive data using SqlRepository.SimpleAccess
            var peopleDeleted = repo.SimpleAccess.ExecuteDynamics("People_GetAllDeleted");

            // while using SqlRepository with StoredProcedures SimpleAccess default command type will be stored procedure
			var peopleInDyanmics = repo.SimpleAccess.ExecuteDynamics("Select * FROM people", CommandType.Text);
			var peopleEnumerable = repo.SimpleAccess.ExecuteEntities<Person>("Select * FROM people", CommandType.Text);

			// Retrive scalar value with query
            var totalPeople = repo.SimpleAccess.ExecuteScalar<int>("SELECT COUNT([Id]) FROM people;", CommandType.Text);


    	}
    }

}

Using SimpleAccess v1 Sql Server Repository (Repository)

It is recommanded to use SimpleAccess v2 SqlRepository instead of SimpleAccess v1 repository. Although SimpleAccess v1 repository is supported and included in SimpleAccess v2 for backward compatibility

using System.Data;
using System.Collections.Generic;
using SimpleAccess;
using SimpleAccess.Repository;

namespace SimpleAccess.SqlServer.ConsoleTest
{
    class Program
    {
        static void Main(string[] args)
        {

            IRepository repo = new Repository("connectionStringName");

            // Retrive data using SimpleAccess SqlRepository
            var people = repo.GetAll<Person>();
            var person = repo.Get<Person>(1);

			// Insert
			var newPerson = new Person {
            	Name = "Ahemd"
                , PhoneNumbers = "1231231323"
                , Address = "Some address"
                , CreatedOn = DateTime.Now
                , CreatedBy = 1 // user id
            };

		    repo.Insert<Person>(newPerson);
            Console.Write("New person id: {0}", newPerson.Id);

			//Update
            var personToUpdate = repo.GetById(1);

			personToUpdate.Name = "Muhammad";
            personToUpdate.ModifiedOn = DateTime.Now
            personToUpdate.ModifiedBy = 1 // user id

			var rowAffected = repo.Update<Person>(personToUpdate);

			// Delete
            var rowAffected = repo.Delete<Person>(1);

            // Retrive data using SimpleAccess Repository v1
            var peopleDeleted = repo.ExecuteReader("People_GetAllDeleted", CommandType.StoredProcedure);

			var peopleInDyanmics = repo.ExecuteReader("Select * FROM people", CommandType.Text);
			var peopleEnumerable = repo.ExecuteReader<Person>("Select * FROM people", CommandType.Text);

			// Retrive scalar value with query
            var totalPeople = repo.ExecuteScalar<int>("SELECT COUNT([Id]) FROM people;", CommandType.Text);
    	}
    }

}

Support

Simple Access 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 Simple Access uses Oracle Data Provider for .NET, to use Oracle Data Access Components (ODAC))

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 Read more...

Name		Name	Last commit message	Last commit date
Latest commit History 148 Commits
Builds/nightly-build		Builds/nightly-build
Docs		Docs
Source		Source
.gitattributes		.gitattributes
.gitignore		.gitignore
README.md		README.md

yelkarama/simple-access-orm

Folders and files

Latest commit

History

Repository files navigation

Simple Access ORM

Using SimpleAccess

Nuget package

Sql Server

Oralce

MySql

SQLite

SimpleAccess interface

Methods

SimpleAccess with Repository pattern

Properties

Methods

Using SqlRepository with StoredProcedure

People Table

People_GetById

People_GetAll

People_Find

People_Insert

People_Update

People_Delete

People_SoftDelete

c# Entity

Using SimpleAccess v2 Sql Server Repository (SqlRepository)

Using SimpleAccess v1 Sql Server Repository (Repository)

Support

Roadmap

About

Resources

Stars

Watchers

Forks

Languages