Пример #1
0
        /// <summary>
        /// Gets the record count for the current type's mapped table.
        /// </summary>
        /// <param name="conditions">The search items.</param>
        /// <returns></returns>
        public int GetRecordCount(QueryConditions conditions = null)
        {
            if (string.IsNullOrWhiteSpace(TargetTable))
            {
                return(0);
            }
            var searchItems = conditions.SearchItems;
            var searchLogic = conditions.SearchLogic;

            using (var connection = CreateConnection())
            {
                using (var command = CreateCommand(connection))
                {
                    // Get the overall record count
                    const string countQuery   = @"SELECT COUNT(1) FROM {0}";
                    var          queryBuilder = new StringBuilder().AppendFormat(countQuery, TargetTable);

                    if (searchItems != null || conditions.RequiredSearchItems.Any())
                    {
                        var first = true;
                        conditions.RequiredSearchItems.ForEach(delegate(SearchItem item)
                        {
                            if (first)
                            {
                                first = false;
                                queryBuilder.AppendFormat(" WHERE ({0}", item.DetailSql);
                            }
                            else
                            {
                                queryBuilder.AppendFormat(" AND {0}", item.DetailSql);
                            }
                            item.Parameters.ForEach(
                                parameter =>
                                command.Parameters.Add(CreateParameter(parameter.ParameterName, parameter.Value)));
                        });

                        if (conditions.RequiredSearchItems.Any())
                        {
                            queryBuilder.AppendFormat(")");
                        }

                        first = true;
                        searchItems.ForEach(
                            delegate(SearchItem item)
                        {
                            if (first)
                            {
                                first = false;
                                queryBuilder.AppendFormat(conditions.RequiredSearchItems.Any() ? " AND (({0})" : " WHERE ({0})",
                                                          item.DetailSql);
                            }
                            else
                            {
                                queryBuilder.AppendFormat(" {0} ({1})", searchLogic, item.DetailSql);
                            }

                            item.Parameters.ForEach(
                                parameter =>
                                command.Parameters.Add(CreateParameter(parameter.ParameterName, parameter.Value)));
                        });

                        if (conditions.RequiredSearchItems.Any() && searchItems.Any())
                        {
                            queryBuilder.AppendFormat(")");
                        }
                    }

                    command.CommandText = queryBuilder.ToString();
                    connection.Open();

                    int count;
                    int.TryParse(Convert.ToString(command.ExecuteScalar()), out count);
                    return(count);
                }
            }
        }
Пример #2
0
        /// <summary>
        /// Gets the record list for this instance's target type.
        /// </summary>
        /// <param name="conditions">The conditions.</param>
        /// <returns></returns>
        public List <T> GetRecords(QueryConditions conditions = null)
        {
            var records = new List <T>();

            using (var connection = CreateConnection())
            {
                using (var command = CreateCommand(connection))
                {
                    // Build and execute the main query
                    var queryBuilder = new StringBuilder("SELECT * FROM (SELECT a.*,");
                    if (conditions != null)
                    {
                        if (conditions.SortOptionSql != null)
                        {
                            queryBuilder.AppendFormat(" ROW_NUMBER() OVER (ORDER BY {0})", string.Join(", ", conditions.SortOptionSql.ToArray()));
                        }
                        else
                        {
                            queryBuilder.Append(GetRowNumber());
                        }

                        queryBuilder.AppendFormat(" rn FROM {0} a", TargetTable);

                        if (conditions.SearchItems != null || conditions.RequiredSearchItems.Any())
                        {
                            var first = true;
                            conditions.RequiredSearchItems.ForEach(delegate(SearchItem item)
                            {
                                if (first)
                                {
                                    first = false;
                                    queryBuilder.AppendFormat(" WHERE ({0}", item.DetailSql);
                                }
                                else
                                {
                                    queryBuilder.AppendFormat(" AND {0}", item.DetailSql);
                                }
                                item.Parameters.ForEach(
                                    parameter =>
                                    command.Parameters.Add(CreateParameter(parameter.ParameterName, parameter.Value)));
                            });

                            if (conditions.RequiredSearchItems.Any())
                            {
                                queryBuilder.AppendFormat(")");
                            }

                            first = true;

                            conditions.SearchItems.ForEach(
                                delegate(SearchItem item)
                            {
                                if (first)
                                {
                                    first = false;
                                    queryBuilder.AppendFormat(
                                        conditions.RequiredSearchItems.Any() ? " AND (({0})" : " WHERE ({0})", item.DetailSql);
                                }
                                else
                                {
                                    queryBuilder.AppendFormat(" {0} ({1})", conditions.SearchLogic, item.DetailSql);
                                }
                                item.Parameters.ForEach(delegate(IDataParameter parameter) { command.Parameters.Add(parameter); });
                            });

                            if (conditions.RequiredSearchItems.Any() && conditions.SearchItems.Any())
                            {
                                queryBuilder.AppendFormat(")");
                            }
                        }

                        queryBuilder.Append(GetSubQueryTerminator());

                        if (conditions.RecordLimit > 0)
                        {
                            queryBuilder.AppendFormat(" WHERE rn > {0} AND rn <= {1}", conditions.SkipOffset, conditions.SkipOffset + conditions.RecordLimit);
                        }
                    }
                    else
                    {
                        queryBuilder.AppendFormat(" {0} rn FROM {1} a)", GetRowNumber(), TargetTable);
                    }

                    connection.Open();
                    command.CommandText = queryBuilder.ToString();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            records.Add(FillModel(reader));
                        }
                    }
                }
            }

            return(records);
        }