I have just created my first NuGet package and I need critical feedback.
https://www.nuget.org/packages/Dapper.SimpleRepository/1.0.2
and
https://github.com/ccrookston/Dapper.SimpleRepository
The only code is in this file:
https://github.com/ccrookston/Dapper.SimpleRepository/blob/master/Repository.cs
I would appreciate your honest feedback. I'm not looking for attaboys. Some things I'm curious about:
- Any blatant code problems? Safety? Connection leaks? Etc?
- Are there any other ways to improve?
- The general benefit of the package? It's something I use a lot, but I'm amazed at your general thoughts.
Here is the entire Repository.cs file:
using Microsoft.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
// Dapper.SimpleRepository was creatd by Casey Crookston. You are free to use it however you would like.
// I hope it makes your developent faster and easier.
namespace Dapper.SimpleRepository
{
public class Repository
{
private readonly string _connectionString;
public Repository(string connectionString)
{
_connectionString = connectionString;
}
private SqlConnection OpenConnection(string connectionString)
{
SqlConnection con = new SqlConnection(connectionString);
con.Open();
return con;
}
// ----------- GET Single Item Methods ----------- //
///
/// Get a specific record from any table by the primary key
///
///
///
public T Get(int id)
{
T entity;
using (var connection = OpenConnection(_connectionString))
{
entity = connection.Get(id);
}
return entity;
}
///
/// Get a specific record from any table by the primary key
///
///
///
public async Task GetAsync(int id)
{
T entity;
using (var connection = OpenConnection(_connectionString))
{
entity = await connection.GetAsync(id);
}
return entity;
}
///
/// Get a specific record from any table that matches the specified filter
///
///
///
///
///
public T Get(string where, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
if (parms != null)
entities = connection.GetList(where, new DynamicParameters(parms));
else
entities = connection.GetList(where);
}
if (entities != null && entities.Any())
return entities.FirstOrDefault();
else
return default;
}
///
/// Get a specific record from any table that matches the specified filter
///
///
///
///
///
public async Task GetAsync(string where, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
if (parms != null)
entities = await connection.GetListAsync(where, new DynamicParameters(parms));
else
entities = await connection.GetListAsync(where);
}
if (entities != null && entities.Any())
return entities.FirstOrDefault();
else
return default;
}
///
/// Get a specific type from any query. This type could be a database model (list), or it could be a single string, or it could be an INT if the query is a SELECT COUNT().
///
///
///
///
public T GetFromQuery(string query, Dictionary parms = null)
{
T entity;
using (var connection = OpenConnection(_connectionString))
{
if (parms != null)
entity = connection.Query(query, new DynamicParameters(parms)).FirstOrDefault();
else
entity = connection.Query(query).FirstOrDefault();
}
return entity;
}
///
/// Get a specific type from any query. This type could be a database model (list), or it could be a single string, or it could be an INT if the query is a SELECT COUNT().
///
///
///
///
public async Task GetFromQueryAsync(string query, Dictionary parms = null)
{
IEnumerable entity;
using (var connection = OpenConnection(_connectionString))
{
if (parms != null)
{
entity = await connection.QueryAsync(query, new DynamicParameters(parms));
}
else
{
entity = await connection.QueryAsync(query);
}
}
return entity.FirstOrDefault();
}
// ----------- GET List Methods ----------- //
///
/// Get an IEnumerable of all records of any database table
///
///
public IEnumerable GetAll()
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
entities = connection.GetList();
}
return entities;
}
///
/// Get an IEnumerable of all records of any database table
///
///
public async Task> GetAllAsync()
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
entities = await connection.GetListAsync();
}
return entities;
}
///
/// Get an IEnumerable from any table that matches the specified filter
///
///
///
///
public IEnumerable GetList(string where, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
if (parms != null)
entities = connection.GetList(where, new DynamicParameters(parms));
else
entities = connection.GetList(where);
}
return entities;
}
///
/// Get an IEnumerable from any table that matches the specified filter
///
///
///
///
public async Task> GetListAsync(string where, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
if (parms != null)
entities = await connection.GetListAsync(where, new DynamicParameters(parms));
else
entities = await connection.GetListAsync(where);
}
return entities;
}
///
/// Get an IEnumerable from any table based on a custom query and any (optional) parms
///
///
///
///
public IEnumerable GetListFromQuery(string query, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
if (parms != null)
entities = connection.Query(query, new DynamicParameters(parms));
else
entities = connection.Query(query);
}
return entities;
}
///
/// Get an IEnumerable from any table based on a custom query and any (optional) parms
///
///
///
///
public async Task> GetListFromQueryAsync(string query, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
if (parms != null)
entities = await connection.QueryAsync(query, new DynamicParameters(parms));
else
entities = await connection.QueryAsync(query);
}
return entities;
}
///
/// Get a paged IEnumerable of all records of any database table
///
///
///
///
///
///
///
public IEnumerable GetListPaged(int pageNumber, int rowsPerPage, string conditions, string orderBy, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
entities = connection.GetListPaged(pageNumber, rowsPerPage, conditions, orderBy, parms);
}
return entities;
}
///
/// Get a paged IEnumerable of all records of any database table
///
///
///
///
///
///
///
public async Task> GetListPagedAsync(int pageNumber, int rowsPerPage, string conditions, string orderBy, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
entities = await connection.GetListPagedAsync(pageNumber, rowsPerPage, conditions, orderBy, parms);
}
return entities;
}
// ----------- UPDATE Methods ----------- //
///
/// Update as existing record in any database table, matching by Id of the record
///
///
///
public int Update(T entity)
{
using (var connection = OpenConnection(_connectionString))
{
return connection.Update(entity);
}
}
///
/// Update any existing record in any database table. Returns number of rows affected.
///
///
///
public async Task UpdateAsync(T entity)
{
int records;
using (var connection = OpenConnection(_connectionString))
{
records = await connection.UpdateAsync(entity);
}
return records;
}
// ----------- INSERT Methods ----------- //
///
/// Insert a new record into any database table. Retuns the Id of the newly created record.
///
///
///
public int? Insert(T entity)
{
int? newId;
using (var connection = OpenConnection(_connectionString))
{
newId = connection.Insert(entity);
}
return newId;
}
///
/// Insert a new record into any database table. Retuns the Id of the newly created record.
///
///
///
public async Task InsertAsync(T entity)
{
int? newId;
using (var connection = OpenConnection(_connectionString))
{
newId = await connection.InsertAsync(entity);
}
return newId;
}
// ----------- DELETE Methods ----------- //
///
/// Delete a record by primary key from any database table
///
///
public int Delete(int id)
{
using (var connection = OpenConnection(_connectionString))
{
return connection.Delete(id);
}
}
///
/// Delete a record by primary key from any database table
///
///
public async Task DeleteAsync(int id)
{
using (var connection = OpenConnection(_connectionString))
{
return await connection.DeleteAsync(id);
}
}
///
/// Delete all records from any table that match the specified filter
///
///
///
public int Delete(string where, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
return connection.DeleteList(where, new DynamicParameters(parms));
}
}
///
/// Delete all records from any table that match the specified filter
///
///
///
public async Task DeleteAsync(string where, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
return await connection.DeleteListAsync(where, new DynamicParameters(parms));
}
}
// ----------- EXECUTE QUERY Method ----------- //
///
/// Execute any custom query where a return data set it not expected.
///
///
///
public void ExecuteQuery(string query, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
connection.Query(query, param: parms);
}
}
///
/// Execute any custom query where a return data set it not expected.
///
///
///
public async Task ExecuteQueryAsync(string query, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
await connection.ExecuteAsync(query, param: parms);
}
}
///
/// Execute any custom query where a single return item is expected. This type could be a database model (list), or it could be a single string, or it could be an INT if the query is a SELECT COUNT().
///
///
///
///
public T ExecuteScalar(string query, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
return connection.ExecuteScalar(query, param: parms);
}
}
///
/// Execute any custom query where a single return item is expected. This type could be a database model (list), or it could be a single string, or it could be an INT if the query is a SELECT COUNT().
///
///
///
///
public async Task ExecuteScalarAsync(string query, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
return await connection.ExecuteScalarAsync(query, param: parms);
}
}
// ----------- STORED PROCEDURE Methods ----------- //
///
/// Execute any Stored Procedure where a return data set it not expected.
///
///
///
public void ExecuteSP(string storedProcedureName, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
connection.Execute(storedProcedureName, parms, commandType: CommandType.StoredProcedure);
}
}
///
/// Execute any Stored Procedure where a return data set it not expected.
///
///
///
public async Task ExecuteSPAsync(string storedProcedureName, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
await connection.ExecuteAsync(storedProcedureName, parms, commandType: CommandType.StoredProcedure);
}
}
///
/// Execute any Stored Procedure where a single item is expected as a return.
///
///
///
///
///
public T ExecuteSPSingle(string storedProcedureName, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
entities = connection.Query(storedProcedureName, parms, commandType: CommandType.StoredProcedure);
}
if (entities != null && entities.Any())
return entities.FirstOrDefault();
else
return default;
}
///
/// Execute any Stored Procedure where a single item is expected as a return.
///
///
///
///
///
public async Task ExecuteSPSingleAsync(string storedProcedureName, Dictionary parms = null)
{
IEnumerable entities;
using (var connection = OpenConnection(_connectionString))
{
entities = await connection.QueryAsync(storedProcedureName, parms, commandType: CommandType.StoredProcedure);
}
if (entities != null && entities.Any())
return entities.FirstOrDefault();
else
return default;
}
///
/// Execute a Store Procedure when a List of T is expected in return.
///
///
///
///
/// Returns a List of T
public IEnumerable ExecuteSPList(string storedProcedureName, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
IEnumerable output = connection.Query(storedProcedureName, param: parms, commandTimeout: 0, commandType: CommandType.StoredProcedure);
return output;
}
}
///
/// Execute a Store Procedure when a List of T is expected in return.
///
///
///
///
/// Returns a List of T
public async Task> ExecuteSPListAsync(string storedProcedureName, Dictionary parms = null)
{
using (var connection = OpenConnection(_connectionString))
{
var output = await connection.QueryAsync(storedProcedureName, param: parms, commandTimeout: 0, commandType: CommandType.StoredProcedure);
return output;
}
}
}
public class Repository
{
private readonly Repository _base;
public Repository(string connectionString)
{
_base = new Repository(connectionString);
}
// ----------- GET Single Item Methods ----------- //
///
/// Get a specific record from any table by the primary key
///
///
///
public T Get(int id) => _base.Get(id);
///
/// Get a specific record from any table by the primary key
///
///
///
public async Task GetAsync(int id) => await _base.GetAsync(id);
///
/// Get a specific record from any table that matches the specified filter
///
///
///
///
public T Get(string where, Dictionary parms = null) => _base.Get(where, parms);
///
/// Get a specific record from any table that matches the specified filter
///
///
///
///
public async Task GetAsync(string where, Dictionary parms = null) => await _base.GetAsync(where, parms);
///
/// Get a specific type from any query. This type could be a database model (list), or it could be a single string, or it could be an INT if the query is a SELECT COUNT().
///
///
///
///
public T GetFromQuery(string query, Dictionary parms = null) => _base.GetFromQuery(query, parms);
///
/// Get a specific type from any query. This type could be a database model (list), or it could be a single string, or it could be an INT if the query is a SELECT COUNT().
///
///
///
///
public async Task GetFromQueryAsync(string query, Dictionary parms = null) => await _base.GetFromQueryAsync(query, parms);
// ----------- GET List Methods ----------- //
///
/// Get an IEnumerable of all records of any database table
///
///
public IEnumerable GetAll() => _base.GetAll();
///
/// Get an IEnumerable of all records of any database table
///
///
public async Task> GetAllAsync() => await _base.GetAllAsync();
///
/// Get an IEnumerable from any table that matches the specified filter
///
///
///
///
public IEnumerable GetList(string where, Dictionary parms = null) => _base.GetList(where, parms);
///
/// Get an IEnumerable from any table that matches the specified filter
///
///
///
///
public async Task> GetListAsync(string where, Dictionary parms = null) => await _base.GetListAsync(where, parms);
///
/// Get an IEnumerable from any table based on a custom query and any (optional) parms
///
///
///
///
public IEnumerable GetListFromQuery(string query, Dictionary parms = null) => _base.GetListFromQuery(query, parms);
///
/// Get an IEnumerable from any table based on a custom query and any (optional) parms
///
///
///
///
public async Task> GetListFromQueryAsync(string query, Dictionary parms = null) => await _base.GetListFromQueryAsync(query, parms);
///
/// Get a paged IEnumerable of all records of any database table
///
///
///
///
///
///
///
public IEnumerable GetListPaged(int pageNumber, int rowsPerPage, string conditions, string orderBy, Dictionary parms = null) => _base.GetListPaged(pageNumber, rowsPerPage, conditions, orderBy, parms);
///
/// Get a paged IEnumerable of all records of any database table
///
///
///
///
///
///
///
public async Task> GetListPagedAsync(int pageNumber, int rowsPerPage, string conditions, string orderBy, Dictionary parms = null) => await _base.GetListPagedAsync(pageNumber, rowsPerPage, conditions, orderBy, parms);
// ----------- UPDATE Methods ----------- //
///
/// Update as existing record in any database table, matching by Id of the record
///
///
///
public int Update(T entity) => _base.Update(entity);
///
/// Update as existing record in any database table, matching by Id of the record
///
///
///
public async Task UpdateAsync(T entity) => await _base.UpdateAsync(entity);
// ----------- INSERT Methods ----------- //
///
/// Insert a new record into any database table. Retuns the Id of the newly created record.
///
///
///
public int? Insert(T entity) => _base.Insert(entity);
///
/// Insert a new record into any database table. Retuns the Id of the newly created record.
///
///
///
public async Task InsertAsync(T entity) => await _base.InsertAsync(entity);
// ----------- DELETE Methods ----------- //
///
/// Delete a record by primary key from any database table
///
///
///
public int Delete(int id) => _base.Delete(id);
///
/// Delete a record by primary key from any database table
///
///
///
public async Task DeleteAsync(int id) => await _base.DeleteAsync(id);
///
/// Delete all records from any table that match the specified filter
///
///
///
///
public int Delete(string where, Dictionary parms = null) => _base.Delete(where, parms);
///
/// Delete all records from any table that match the specified filter
///
///
///
///
public async Task DeleteAsync(string where, Dictionary parms = null) => await _base.DeleteAsync(where, parms);
// ----------- EXECUTE QUERY Methods ----------- //
///
/// Execute any custom query where a return data set it not expected.
///
///
///
public void ExecuteQuery(string query, Dictionary parms = null) => _base.ExecuteQuery(query, parms);
///
/// Execute any custom query where a return data set it not expected.
///
///
///
///
public async Task ExecuteQueryAsync(string query, Dictionary parms = null) => await _base.ExecuteQueryAsync(query, parms);
///
/// Execute any custom query where a single return item is expected. This type could be a database model (list), or it could be a single string, or it could be an INT if the query is a SELECT COUNT().
///
///
///
///
public T ExecuteScalar(string query, Dictionary parms = null) => _base.ExecuteScalar(query, parms);
///
/// Execute any custom query where a single return item is expected. This type could be a database model (list), or it could be a single string, or it could be an INT if the query is a SELECT COUNT().
///
///
///
///
public async Task ExecuteScalarAsync(string query, Dictionary parms = null) => await _base.ExecuteScalarAsync(query, parms);
// ----------- STORED PROCEDURE Methods ----------- //
///
/// Execute any Stored Procedure where a return data set it not expected.
///
///
///
public void ExecuteSP(string storedProcedureName, Dictionary parms = null) => _base.ExecuteSP(storedProcedureName, parms);
///
/// Execute any Stored Procedure where a return data set it not expected.
///
///
///
///
public async Task ExecuteSPAsync(string storedProcedureName, Dictionary parms = null) => await _base.ExecuteSPAsync(storedProcedureName, parms);
///
/// Execute any Stored Procedure where a single item is expected as a return.
///
///
///
///
public T ExecuteSPSingle(string storedProcedureName, Dictionary parms = null) => _base.ExecuteSPSingle(storedProcedureName, parms);
///
/// Execute any Stored Procedure where a single item is expected as a return.
///
///
///
///
public async Task ExecuteSPSingleAsync(string storedProcedureName, Dictionary parms = null) => await _base.ExecuteSPSingleAsync(storedProcedureName, parms);
///
/// Execute a Store Procedure when a List of T is expected in return.
///
///
///
///
public IEnumerable ExecuteSPList(string storedProcedureName, Dictionary parms = null) => _base.ExecuteSPList(storedProcedureName, parms);
///
/// Execute a Store Procedure when a List of T is expected in return.
///
///
///
///
public async Task> ExecuteSPListAsync(string storedProcedureName, Dictionary parms = null) => await _base.ExecuteSPListAsync(storedProcedureName, parms);
}
}
````
