Beispiel #1
0
        internal virtual void CreateReadAllPagedCommand(IDbCommand cmd, string query, string orderBy, int pageNo, int pageSize, object parameters = null)
        {
            StringBuilder cmdText = new StringBuilder();

            if (this is MsSqlDatabase)
            {
                if (GetDBVersion(cmd.Connection).Version.Major >= 11) //SQL server 2012 and above supports offset
                {
                    cmdText.Append($@"{query} 
                                    ORDER BY {orderBy} 
                                    OFFSET {((pageNo - 1) * pageSize)} ROWS 
                                    FETCH NEXT {pageSize} ROWS ONLY");
                }
                else
                {
                    cmdText.Append($@"SELECT * FROM (
                        SELECT ROW_NUMBER() OVER(ORDER BY {orderBy}) AS rownumber, 
                        * FROM ({query}) as sq
                    ) AS q
                    WHERE (rownumber between {((pageNo - 1) * pageSize) + 1} AND {pageNo * pageSize})");
                }
            }
            else
            {
                cmdText.Append($@"{query} 
                                ORDER BY {orderBy}
                                LIMIT {pageSize}
                                OFFSET {((pageNo - 1) * pageSize)}");
            }
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = cmdText.ToString();
            ParameterCache.AddParameters(parameters, cmd); //ParameterCache.GetFromCache(parameters, cmd).Invoke(parameters, cmd);
        }
Beispiel #2
0
        internal virtual void CreateTextCommand(IDbCommand cmd, string query, string criteria = null, object parameters = null)
        {
            bool hasWhere = query.ToLowerInvariant().Contains("where");

            if (!string.IsNullOrEmpty(criteria))
            {
                //add WHERE statement if not exists in query or criteria
                if (!hasWhere && !criteria.ToLowerInvariant().Contains("where"))
                {
                    query = query + " WHERE ";
                }
            }

            query = query + " " + criteria;

            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            ParameterCache.AddParameters(parameters, cmd);
        }
Beispiel #3
0
        internal virtual StringBuilder CreateSelectCommand(IDbCommand cmd, string query, string criteria = null, object parameters = null)
        {
            bool hasWhere = query.ToLowerInvariant().Contains("where");

            StringBuilder cmdText = new StringBuilder(query);

            if (!string.IsNullOrEmpty(criteria))
            {
                //add WHERE statement if not exists in query or criteria
                if (!hasWhere && !criteria.ToLowerInvariant().Contains("where"))
                {
                    cmdText.Append(" WHERE ");
                }

                cmdText.Append(criteria);
            }

            ParameterCache.AddParameters(parameters, cmd);

            return(cmdText);
        }
Beispiel #4
0
        internal virtual void CreateReadAllPagedNoOffsetCommand <T>(IDbCommand cmd, string query, string orderBy, int pageSize, PageNavigationEnum navigation, object[] lastOrderByColumnValues = null, object lastKeyId = null, object parameters = null)
        {
            string[] orderByColumns   = orderBy.Split(',');
            string[] orderByDirection = new string[orderByColumns.Length];
            for (int i = 0; i < orderByColumns.Length; i++)
            {
                if (orderByColumns[i].ToLowerInvariant().Contains("desc"))
                {
                    orderByDirection[i] = "DESC";
                    orderByColumns[i]   = orderByColumns[i].ToLowerInvariant().Replace("desc", "").Trim();
                }
                else
                {
                    orderByDirection[i] = "ASC";
                    orderByColumns[i]   = orderByColumns[i].ToLowerInvariant().Replace("asc", "").Trim();
                }
            }
            if (orderByColumns.Length == 0)
            {
                throw new MissingMemberException("Orderby column(s) is missing");
            }
            if ((navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous) && lastOrderByColumnValues.Length != orderByColumns.Length)
            {
                throw new MissingMemberException("For Next and Previous Navigation Length of Last Values must be equal to orderby columns length");
            }
            if ((navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous) && lastKeyId == null)
            {
                throw new MissingMemberException("For Next and Previous Navigation Last KeyId is required");
            }

            TableAttribute tableInfo = EntityCache.Get(typeof(T));
            bool           hasWhere  = query.ToLowerInvariant().Contains("where");

            StringBuilder pagedCriteria = new StringBuilder();
            StringBuilder pagedOrderBy  = new StringBuilder();

            if (!hasWhere)
            {
                pagedCriteria.Append(" WHERE 1=1");
            }

            for (int i = 0; i < orderByColumns.Length; i++)
            {
                string applyEquals = (i <= orderByColumns.Length - 2 ? "=" : "");

                if (navigation == PageNavigationEnum.Next)
                {
                    //when multiple orderbycolumn - apply '>=' or '<=' till second last column
                    if (orderByDirection[i] == "ASC")
                    {
                        pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo,">")}) OR {orderByColumns[i]} >{applyEquals} @p_{orderByColumns[i]})");
                    }
                    else
                    {
                        pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, "<")}) OR ({orderByColumns[i]} IS NULL OR {orderByColumns[i]} <{applyEquals} @p_{orderByColumns[i]}))");
                    }
                }
                else if (navigation == PageNavigationEnum.Previous)
                {
                    if (orderByDirection[i] == "ASC")
                    {
                        pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, "<")}) OR ({orderByColumns[i]} IS NULL OR {orderByColumns[i]} <{applyEquals} @p_{orderByColumns[i]}))");
                    }
                    else
                    {
                        pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, ">")}) OR {orderByColumns[i]} >{applyEquals} @p_{orderByColumns[i]})");
                    }
                }

                if (navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous)
                {
                    //add Parameter for Last value of ordered column
                    DbType dbType;
                    //see if column exists in TableInfo
                    tableInfo.Columns.TryGetValue(orderByColumns[i], out ColumnAttribute orderByColumn);
                    if (orderByColumn != null)
                    {
                        dbType = orderByColumn.ColumnDbType;
                    }
                    else
                    {
                        TypeCache.TypeToDbType.TryGetValue(lastOrderByColumnValues[i].GetType(), out dbType);
                    }

                    cmd.AddInParameter("@p_" + orderByColumns[i], dbType, lastOrderByColumnValues[i]);
                }

                if (i > 0)
                {
                    pagedOrderBy.Append(",");
                }

                if (navigation == PageNavigationEnum.Last || navigation == PageNavigationEnum.Previous)
                {
                    //reverse sort as we are going backward
                    pagedOrderBy.Append($"{orderByColumns[i]} {(orderByDirection[i] == "ASC" ? "DESC" : "ASC")}");
                }
                else
                {
                    pagedOrderBy.Append($"{orderByColumns[i]} {orderByDirection[i]}");
                }
            }

            //add keyfield parameter for Next and Previous navigation
            if (navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous)
            {
                //add LastKeyId Parameter

                if (tableInfo.PkColumnList.Count > 1)
                {
                    if (!(lastKeyId is T))
                    {
                        throw new InvalidOperationException("Entity has multiple primary keys. Pass entity setting Primary Key attributes.");
                    }

                    int index = 0;
                    foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
                    {
                        cmd.AddInParameter("@p_" + pkCol.Name, pkCol.ColumnDbType, tableInfo.GetKeyId(lastKeyId, pkCol));
                        index++;
                    }
                }
                else
                {
                    cmd.AddInParameter("@p_" + tableInfo.PkColumn.Name, tableInfo.PkColumn.ColumnDbType, lastKeyId);
                }
            }

            //add keyfield in orderby clause. Direction will be taken from 1st orderby column
            if (navigation == PageNavigationEnum.Last || navigation == PageNavigationEnum.Previous)
            {
                //reverse sort as we are going backward
                if (tableInfo.PkColumnList.Count > 1)
                {
                    foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
                    {
                        pagedOrderBy.Append($",{pkCol.Name} {(orderByDirection[0] == "ASC" ? "DESC" : "ASC")}");
                    }
                }
                else
                {
                    pagedOrderBy.Append($",{tableInfo.PkColumn.Name} {(orderByDirection[0] == "ASC" ? "DESC" : "ASC")}");
                }
            }
            else
            {
                if (tableInfo.PkColumnList.Count > 1)
                {
                    foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
                    {
                        pagedOrderBy.Append($",{pkCol.Name} {orderByDirection[0]}");
                    }
                }
                else
                {
                    pagedOrderBy.Append($",{tableInfo.PkColumn.Name} {orderByDirection[0]}");
                }
            }

            cmd.CommandType = CommandType.Text;

            if (this is MsSqlDatabase)
            {
                cmd.CommandText = $"SELECT * FROM (SELECT TOP {pageSize} * FROM ({query} {pagedCriteria.ToString()}) AS r1 ORDER BY {pagedOrderBy}) AS r2 ORDER BY {orderBy}";
            }
            else
            {
                cmd.CommandText = $"SELECT * FROM ({query} {pagedCriteria.ToString()} ORDER BY {pagedOrderBy} LIMIT {pageSize}) AS r ORDER BY {orderBy}";
            }

            ParameterCache.AddParameters(parameters, cmd); //ParameterCache.GetFromCache(parameters, cmd).Invoke(parameters, cmd);
        }