示例#1
0
        /// <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;
            }
        }
示例#2
0
        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));
        }
示例#3
0
        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()));
        }
示例#4
0
        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()));
        }
示例#5
0
        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()));
        }
示例#6
0
        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()));
        }
示例#7
0
        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()));
        }
示例#8
0
        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()));
        }
示例#9
0
        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);
        }