コード例 #1
0
        private static IReadOnlyCollection <string> GetPagingSql(SelectConfiguration selectConfiguration)
        {
            var result = new List <string>
            {
                "COUNT(1) OVER() ROW_COUNT, ",

                $@"{GetSortSql(selectConfiguration)}
                        OFFSET {(selectConfiguration.PageNumber - 1) * selectConfiguration.PageSize} ROWS
                        FETCH NEXT {selectConfiguration.PageSize} ROWS ONLY;"
            };

            return(result);
        }
コード例 #2
0
 private static string GetSortSql(SelectConfiguration selectConfiguration)
 {
     if (selectConfiguration?.SortColumns?.Any() ?? false)
     {
         var orderByColumnsBuilder = new StringBuilder();
         selectConfiguration.SortColumns.ForEach(s =>
         {
             orderByColumnsBuilder.Append($"{s.Name} {s.SortOrder}, ");
         });
         return(orderByColumnsBuilder.ToString().TrimEnd(new char[] { ',', ' ' }));
     }
     return($"ORDER BY {selectConfiguration?.KeyColumnName} ");
 }
コード例 #3
0
        private static string GetTableRelationships(SelectConfiguration selectConfiguration)
        {
            if (!(selectConfiguration?.Relationships?.Any() ?? false))
            {
                return(string.Empty);
            }

            var result = new StringBuilder();

            selectConfiguration.Relationships.ForEach(t =>
            {
                result.Append($"{t.JoinType} {t.Table2Name} {(string.IsNullOrEmpty(t.Table2AsName)? string.Empty : "AS " + t.Table2AsName)} ON {t.Table1Name}.{t.Table1ColumnName} = {(string.IsNullOrEmpty(t.Table2AsName) ? t.Table2Name : t.Table2AsName)}.{t.Table2ColumnName} " + Environment.NewLine);
            });
            return(result.ToString());
        }
コード例 #4
0
        private static string GetTableColumns(SelectConfiguration selectConfiguration)
        {
            if (!(selectConfiguration?.Columns?.Any() ?? false))
            {
                return(string.Empty);
            }

            var result = new StringBuilder();

            selectConfiguration.Columns.ForEach(t =>
            {
                result.Append(
                    $", {t.TableName}.{t.Name}{(string.IsNullOrEmpty(t.AsName) ? string.Empty : " AS " + t.AsName)} ");
            });
            return(result.ToString().TrimStart(new char[] { ',' }));
        }
コード例 #5
0
        public string GetSelectQuery(SelectConfiguration selectConfiguration)
        {
            if (selectConfiguration == null)
            {
                throw new ArgumentException("Invalid table parameter values supplied");
            }
            if (string.IsNullOrEmpty(selectConfiguration.KeyColumnName))
            {
                throw new ArgumentException("Key column is required");
            }
            if (string.IsNullOrEmpty(selectConfiguration.MainTableName))
            {
                throw new ArgumentException("Main table name is required");
            }
            if (selectConfiguration.Columns == null || !selectConfiguration.Columns.Any())
            {
                throw new ArgumentException("Atleast one column is required");
            }

            if (selectConfiguration?.IsPaging ?? false)
            {
                if (selectConfiguration.PageNumber <= 0)
                {
                    throw new ArgumentException("Invalid pagenumber is supplied");
                }
                if (selectConfiguration.PageSize <= 0)
                {
                    throw new ArgumentException("Invalid pagesize is supplied");
                }

                var pagingSqlArray = GetPagingSql(selectConfiguration);
                return($@"SELECT {pagingSqlArray.First()} 
                            {GetTableColumns(selectConfiguration)} FROM 
                            {selectConfiguration.MainTableName} 
                            {GetTableRelationships(selectConfiguration)}
                            {GetWhereCondition(selectConfiguration.WhereConditions)}
                            {pagingSqlArray.ElementAt(1)}");
            }
            return($"SELECT {GetTableColumns(selectConfiguration)} FROM {selectConfiguration.MainTableName} {GetTableRelationships(selectConfiguration)}");
        }