/// <summary>
/// Sets the database dialect
/// </summary>
/// <param name="dialect"></param>
private void SetDialect()
{
switch (_dialect)
{
case Dialect.PostgreSQL:
SqlManipulationExtensions.SetEncaapsulate("\"{0}\"");
_getIdentitySql = string.Format("SELECT LASTVAL() AS id");
_getPagedListSql = "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {RowsPerPage} OFFSET (({PageNumber}-1) * {RowsPerPage})";
break;
case Dialect.MySQL:
SqlManipulationExtensions.SetEncaapsulate("`{0}`");
_getIdentitySql = string.Format("SELECT LAST_INSERT_ID() AS id");
_getPagedListSql = "Select {SelectColumns} from {TableName} {WhereClause} Order By {OrderBy} LIMIT {Offset},{RowsPerPage}";
break;
default:
SqlManipulationExtensions.SetEncaapsulate("[{0}]");
_getIdentitySql = string.Format("SELECT CAST(SCOPE_IDENTITY() AS BIGINT) AS [id]");
_getPagedListSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY {OrderBy}) AS PagedNumber, {SelectColumns} FROM {TableName} {WhereClause}) AS u WHERE PagedNUMBER BETWEEN (({PageNumber}-1) * {RowsPerPage} + 1) AND ({PageNumber} * {RowsPerPage})";
break;
}
}
public PageData <TResult> GetListPaged(int pageNumber, int rowPerpage)
{
if (string.IsNullOrEmpty(_getPagedListSql))
{
throw new Exception("GetListPage is not supported with the current SQL Dialect");
}
if (pageNumber < 1)
{
throw new Exception("Page must be greater than 0");
}
var currenttype = typeof(TResult);
var idProps = SqlManipulationExtensions.GetIdProperties(currenttype).ToList();
if (!idProps.Any())
{
throw new ArgumentException("Entity must have at least one [Key] property");
}
var query = _getPagedListSql;
var tableName = SqlManipulationExtensions.GetTableName(currenttype);
var whereAndargs = SqlManipulationExtensions.BuildWhere(_whereExp);
query = query.BuildTableName(tableName)
.BuildColumns(SqlManipulationExtensions.BuildSelect <TResult>())
.BuildWhere(whereAndargs.Item1)
.BuildPage(pageNumber, rowPerpage)
.BuildOrderBy(SqlManipulationExtensions.BuildOrderBy(_orderbyExp, _orderbyAseOrDesc));
var total = RecordCount();
var list = _connection.Query <TResult>(query, whereAndargs.Item2, DapperDbContext.GetInnerTransaction());
return(new PageData <TResult>(list, total, pageNumber, rowPerpage));
}
public TResult GetFisrt()
{
var currenttype = typeof(TResult);
var tableName = SqlManipulationExtensions.GetTableName(currenttype);
var whereAndargs = SqlManipulationExtensions.BuildWhere(_whereExp);
var query = _getListSql;
query = query.BuildTableName(tableName)
.BuildColumns(SqlManipulationExtensions.BuildSelect <TResult>())
.BuildWhere(whereAndargs.Item1)
.BuildOrderBy(SqlManipulationExtensions.BuildOrderBy(_orderbyExp, _orderbyAseOrDesc));
return(_connection.QueryFirstOrDefault <TResult>(query, whereAndargs.Item2, DapperDbContext.GetInnerTransaction()));
}
public long RecordCount()
{
var currenttype = typeof(TResult);
var idProps = SqlManipulationExtensions.GetIdProperties(currenttype).ToList();
if (!idProps.Any())
{
throw new ArgumentException("Entity must have at least one [Key] property");
}
var tableName = SqlManipulationExtensions.GetTableName(currenttype);
var whereAndargs = SqlManipulationExtensions.BuildWhere(_whereExp);
string query = $" Select count(1) from {tableName} {whereAndargs.Item1}";
return(_connection.ExecuteScalar <long>(query, whereAndargs.Item2, DapperDbContext.GetInnerTransaction()));
}
public IEnumerable <TResult> GetList()
{
var currenttype = typeof(TResult);
var idProps = SqlManipulationExtensions.GetIdProperties(currenttype).ToList();
if (!idProps.Any())
{
throw new ArgumentException("Entity must have at least one [Key] property");
}
var tableName = SqlManipulationExtensions.GetTableName(currenttype);
var whereAndargs = SqlManipulationExtensions.BuildWhere(_whereExp);
var query = _getListSql;
query = query.BuildTableName(tableName)
.BuildColumns(SqlManipulationExtensions.BuildSelect <TResult>())
.BuildWhere(whereAndargs.Item1)
.BuildOrderBy(SqlManipulationExtensions.BuildOrderBy(_orderbyExp, _orderbyAseOrDesc));
return(_connection.Query <TResult>(query, whereAndargs.Item2, DapperDbContext.GetInnerTransaction()));
}
public Task <int> DeleteAsync(TEntity entityToDelete)
{
var currenttype = typeof(TEntity);
var idProps = SqlManipulationExtensions.GetIdProperties(currenttype).ToList();
if (!idProps.Any())
{
throw new ArgumentException("Entity must have at least one [Key] or Id property");
}
var name = SqlManipulationExtensions.GetTableName(currenttype);
var where = SqlManipulationExtensions.BuildWhere <TEntity>(idProps, entityToDelete);
string deleteSqlStr = $"delete from {name} where {where}";
if (DapperDbContext.ThreadLocal_Tag.Value == Enums.TransationWay.UnitOfWork)
{
UnitOfWork.AddToUnit(entityToDelete, deleteSqlStr);
return(new Task <int>(() => 0));
}
return(_connection.ExecuteAsync(deleteSqlStr, entityToDelete, DapperDbContext.GetInnerTransaction()));
}
public int Update(TEntity entityToUpdate)
{
var currenttype = typeof(TEntity);
var idProps = SqlManipulationExtensions.GetIdProperties(currenttype).ToList();
if (!idProps.Any())
{
throw new ArgumentException("Entity must have at least one [Key] or Id property");
}
var name = SqlManipulationExtensions.GetTableName(currenttype);
var columns = SqlManipulationExtensions.BuildUpdateSet(entityToUpdate);
var where = SqlManipulationExtensions.BuildWhere <TEntity>(idProps, entityToUpdate);
string updateSqlStr = $"update {name} set {columns} where {where}";
if (DapperDbContext.ThreadLocal_Tag.Value == Enums.TransationWay.UnitOfWork)
{
UnitOfWork.AddToUnit(entityToUpdate, updateSqlStr);
return(0);
}
return(_connection.Execute(updateSqlStr, entityToUpdate, DapperDbContext.GetInnerTransaction()));
}
public Task <int> DeleteAsync(object id)
{
var currenttype = typeof(TEntity);
var idProps = SqlManipulationExtensions.GetIdProperties(currenttype).ToList();
if (!idProps.Any())
{
throw new ArgumentException("Delete<T> only supports an entity with a [Key] or Id property");
}
var name = SqlManipulationExtensions.GetTableName(currenttype);
string where = SqlManipulationExtensions.BuildWhere <TEntity>(idProps);
string deleteSqlStr = $"delete from {name} where {where}";
var dynParms = new DynamicParameters();
dynParms.Add("@" + idProps.First().Name, id);
if (DapperDbContext.ThreadLocal_Tag.Value == Enums.TransationWay.UnitOfWork)
{
UnitOfWork.AddToUnit(dynParms, deleteSqlStr);
return(new Task <int>(() => 0));
}
return(_connection.ExecuteAsync(deleteSqlStr, dynParms, DapperDbContext.GetInnerTransaction()));
}
public async Task <TKey> InsertAsync <TKey>(TEntity entityToInsert)
{
var currenttype = typeof(TEntity);
var idProps = SqlManipulationExtensions.GetIdProperties(currenttype).ToList();
if (!idProps.Any())
{
throw new ArgumentException("Insert<T> only supports an entity with a [Key] or Id property");
}
var keyHasPredefinedValue = false;
var baseType = typeof(TKey);
var underlyingType = Nullable.GetUnderlyingType(baseType);
var keytype = underlyingType ?? baseType;
if (keytype != typeof(int) && keytype != typeof(uint) && keytype != typeof(long) && keytype != typeof(ulong) && keytype != typeof(short) && keytype != typeof(ushort) && keytype != typeof(Guid) && keytype != typeof(string))
{
throw new Exception("Invalid return type");
}
var name = SqlManipulationExtensions.GetTableName(currenttype);
var sb = new StringBuilder();
sb.AppendFormat("insert into {0}", name);
sb.Append(" (");
SqlManipulationExtensions.BuildInsertParameters <TEntity>(sb);
sb.Append(") ");
sb.Append("values");
sb.Append(" (");
SqlManipulationExtensions.BuildInsertValues <TEntity>(sb);
sb.Append(")");
if (keytype == typeof(Guid))
{
var guidvalue = (Guid)idProps.First().GetValue(entityToInsert, null);
if (guidvalue == Guid.Empty)
{
var newguid = SqlManipulationExtensions.SequentialGuid();
idProps.First().SetValue(entityToInsert, newguid, null);
}
else
{
keyHasPredefinedValue = true;
}
sb.Append(";select '" + idProps.First().GetValue(entityToInsert, null) + "' as id");
}
if ((keytype == typeof(int) || keytype == typeof(long)) && Convert.ToInt64(idProps.First().GetValue(entityToInsert, null)) == 0)
{
sb.Append(";" + base._getIdentitySql);
}
else
{
keyHasPredefinedValue = true;
}
if (DapperDbContext.ThreadLocal_Tag.Value == Enums.TransationWay.UnitOfWork)
{
UnitOfWork.AddToUnit(entityToInsert, sb.ToString());
return((TKey)idProps.First().GetValue(entityToInsert, null));
}
var r = await _connection.QueryAsync(sb.ToString(), entityToInsert, DapperDbContext.GetInnerTransaction());
if (keytype == typeof(Guid) || keyHasPredefinedValue)
{
return((TKey)idProps.First().GetValue(entityToInsert, null));
}
return((TKey)r.First().id);
}