RepoDb 1.0.0

RepoDb by Michael Pendon A .Net class library used to create a dynamic entity repository-based data access to the database.

Whats with RepoDb?

• Supported Dynamic DbConnection
• Supported Mapping Command Type (Entity Level)
• Supported Execute Scalar
• Supported Primary Attribute (IsIdentity)
• Supported BaseRepository that uses the functionality of the DbRepository
• Supported the GetSqlStatement (Entity Level)
• Supported Object Driven approach when doing a query, delete, update and merge
• Supported Merge Method (Entity Level only)
• Supported Bulk Insert (SqlBulkCopy only)
• Supported to get the Connection at all Repositories
• Supported Transaction Handlers
• Supported Async methods
• Supported DbRepository property at the BaseRepository
• Supported Dynamic object returns for ExecuteReaderEx
• Supported code-guards at the DbRepository (Queryable, Insertable, Deletable, Mergeable, Updateable)
• Supported Type Mapper

How to use?

Below are some guidelines on how to use the RepoDb.

*** Creating an Entity

public interface IStock : IDataEntity { int Id { get; set; }

string ParentId { get; set; }

string Code { get; set; }

string Name { get; set; }

DateTime CreatedDate { get; set; }

DateTime ModifiedDate { get; set; }

}

[Map("[dbo].[Stock]")] public class Stock : DataEntity { [Ignore(Command.Insert | Command.Update)] [Primary] public int Id { get; set; }

public string ParentId { get; set; }

[Ignore(Command.Update)]
public string Code { get; set; }

public string Name { get; set; }

[Ignore(Command.Insert | Command.Update)]
public DateTime CreatedDate { get; set; }

[Ignore(Command.Insert)]
public DateTime ModifiedDate { get; set; }

}

• Map defines which object to use in the database. Usually defines the name of the table or a stored procedure
• Another param is the CommandType that tells whether the object is for TableDirect, StoredProcedure or Text base execution
• Ignore attribute is used in the property level to set a flag whether the property will be ignore on a certain commands (SELECT, DELETE, UPDATE, INSERT, MERGE)
• Primary attribute is used to flag a property as the primary field from the database object.

*** Creating a repository

public class StockRepository : BaseRepository<Stock, SqlConnection> { public StockRepository(ISettings settings) : base(settings.ConnectionString) { } }

• First dynamic type TEntity is a IDataEntity object that defines the entity to be used by the repository
• Second dynamic type TDbConnection is a type of Connection object to be used
• BaseRepository accepts 2 parameters in the constructor (ConnectionString and CommandTimeout)

*** Querying a data

var settings = new Settings(); var stockRepository = new StockRepository(settings.ConnectionString);

var stock = stockRepository.Query({ Id = 256 }); or var stock = stockRepository.Query(new List<IQueryField>() { new QueryField("Id", Operation.GreaterThanOrEqual, 50) });

• The first codes will return the Stock data from the database where the Id = 256;
• The second codes will return the Stock data from the database where the Id >= 50;

*** Updating a data

var settings = new Settings(); var stockRepository = new StockRepository(settings.ConnectionString); var stock = stockRepository.Query({ Id = 256 }); stock.Name = $"{stock.Name} - some updates";

var affectedRows = stockRepository.Update(stock); or var affectedRows = stockRepository.Update(stock, new { Id = 256 }); or var affectedRows = stockRepository.Update(stock, new List<IQueryField>() { new QueryField("Id", Operation.GreaterThanOrEqual, 50) });

*** Deleting a data

var settings = new Settings(); var stockRepository = new StockRepository(settings.ConnectionString); var stock = stockRepository.Query({ Id = 256 });

var affectedRows = stockRepository.Delete({ Id = 256 }); or var affectedRows = stockRepository.Delete(stock, new List<IQueryField>() { new QueryField("Id", Operation.GreaterThanOrEqual, 256) });

*** Merging a data

var settings = new Settings(); var stockRepository = new StockRepository(settings.ConnectionString); var stock = stockRepository.Query({ Id = 256 }); stock.Name = $"{stock.Name} - some merge updates";

var affectedRows = stockRepository.Merge(stock); or var affectedRows = stockRepository.Merge(stock, Field.From("ParentId", "Code"));

• Merge is only capable on a row level (for now)
• The 2nd parameter signifies the column to be used for qualifiers, if the parameter is not supplied, then the Primary field will be used

*** Bulk inserting a data

var settings = new Settings(); var stockRepository = new StockRepository(settings.ConnectionString); var stocks = new List<Stock>();

stocks.Add(new Stock() { .... }); . . . stocks.Add(new Stock() { .... });

var affectedRows = stockRepository.BulkInsert(stocks);

Above are the samples on how to use the basic CRUD operations.

RepoDb supports transactional operations and a direct execution of the DbCommand 3 popular methods (ExecuteNonQuery, ExecuteScalar, ExecuteReader)

var settings = new Settings();
var stockRepository = new StockRepository(settings.ConnectionString);
var stocks = stockRepository.CreateConnection().EnsureOpen().ExecuteNonQuery("SELECT * FROM [dbo].[Stock];"); // { ParentId = 23 });

You can even create a repository via DbRepository and access any entity that you like.

var settings = new Settings();
var repository = new DbRepository<SqlConnection>(settings.ConnectionString, settings.CommandTimeout);
var sale = repository.Query<Sale>({ TransactionDate = DateTime.Parse("2018-01-01 00:00:00.000") });
var stock = repository.Query<Stock>({ ParentId = sale.Id });
or
var sale = repository.CreateConnection().ExecuteReader<Sale>("SELECT * FROM [dbo].[Sale] WHERE (TransactionDate = @TD);", new { ID = DateTime.Parse("2018-01-01 00:00:00.000") });