コード例 #1
0
 public PageResult<string> GetResult(int page, FieldCriteria[] criterias)
 {
     var provider = new DbDataProvider();
     var result = new PageResult<string>();
     result.TotalCount = Convert.ToInt32(provider.Count(criterias));
     result.PageCount = result.TotalCount / pageSize + 1;
     result.Page = page;
     result.Result = provider.GetEntitiesId(page, pageSize, criterias);
     return result;
 }
コード例 #2
0
        public static string BuildCountQuery(string tableName, FieldCriteria[] criterias)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT Count(Id)");
            query.AppendFormat("FROM {0}\n", tableName);
            string whereClause = BuildWhereClause(criterias);
            if (!string.IsNullOrWhiteSpace(whereClause))
                query.AppendFormat("WHERE {0}\n", whereClause);
            string orderClause = BuildOrderClause(criterias);
            return query.ToString();
        }
コード例 #3
0
        public long Count(FieldCriteria[] criterias)
        {
            try {
                connection.Open();

                SqlCeCommand command = new SqlCeCommand(SqlQueryBuilder.BuildCountQuery("Entity", criterias), connection);
                object o = command.ExecuteScalar();
                return Convert.ToInt64(o);
            }
            finally {
                connection.Close();
            }
        }
コード例 #4
0
 private static string BuildOrderClause(FieldCriteria[] criterias)
 {
     StringBuilder clause = new StringBuilder();
     for (int i = 0; i < criterias.Length; ++i) {
         if (criterias[i].Sort != 0) {
             clause.Append(criterias[i].FieldName);
             clause.Append(" ");
             clause.Append(criterias[i].Sort > 0 ? "ASC" : "DESC");
             if (i + 1 != criterias.Length)
                 clause.Append(", ");
         }
     }
     return clause.ToString();
 }
コード例 #5
0
        public static string BuildSelectQuery(string tableName, FieldCriteria[] criterias, int pageNumber, int pageSize)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT *");
            query.AppendFormat("FROM {0}\n", tableName);
            string whereClause = BuildWhereClause(criterias);
            if (!string.IsNullOrWhiteSpace(whereClause))
                query.AppendFormat("WHERE {0}\n", whereClause);
            string orderClause = BuildOrderClause(criterias);
            if (!string.IsNullOrWhiteSpace(orderClause))
                query.AppendFormat("ORDER BY {0}\n", orderClause);
            else
                query.AppendLine("ORDER BY Code ASC");
            query.Append(BuildPagingClause(pageNumber, pageSize));
            return query.ToString();
        }
コード例 #6
0
        public string[] GetEntitiesId(int page, int pageLength, FieldCriteria[] criterias)
        {
            List<string> ids = new List<string>();
            try {
                connection.Open();

                SqlCeCommand command = new SqlCeCommand(SqlQueryBuilder.BuildSelectIdsQuery("Entity", criterias, page, pageLength), connection);
                var reader = command.ExecuteReader();
                while (reader.Read()) {
                    string id = reader.GetValue(0).ToString();
                    ids.Add(id);
                }
                return ids.ToArray();
            }
            finally {
                connection.Close();
            }
        }
コード例 #7
0
        public Entity[] GetEntities(int page, int pageLength, FieldCriteria[] criterias)
        {
            List<Entity> entities = new List<Entity>();
            try {
                connection.Open();

                SqlCeCommand command = new SqlCeCommand(SqlQueryBuilder.BuildSelectQuery("Entity", criterias, page, pageLength), connection);
                var reader = command.ExecuteReader();
                while (reader.Read()) {
                    Entity entity = new Entity();
                    PopulateEntity(entity, reader);
                    entities.Add(entity);
                }
                return entities.ToArray();
            }
            finally {
                connection.Close();
            }
        }
コード例 #8
0
 private static string BuildWhereClause(FieldCriteria[] criterias)
 {
     StringBuilder clause = new StringBuilder();
     for (int i = 0; i < criterias.Length; ++i) {
         if (!string.IsNullOrWhiteSpace(criterias[i].Filter)) {
             clause.Append(criterias[i].FieldName);
             clause.Append(" LIKE ");
             clause.AppendFormat("'%{0}%'", criterias[i].Filter);
             if (i + 1 != criterias.Length)
                 clause.Append(" AND ");
         }
     }
     return clause.ToString();
 }