Exemplo n.º 1
0
        public static async Task<Int32> CountAsync(this DbSet<Contact> dbSet, ContactFilter filter)
        {
            const String query = @"SELECT CAST(COUNT(*) AS INT) FROM c.Contact WHERE {0}";

            String where = CreateWhere(filter);
            String curQuery = String.Format(query, where);

            ICollection<SqlParameter> parameters = CreateParameters(filter);

            return await GitTimeContext.GetContext(dbSet).Database.SqlQuery<Int32>(curQuery, parameters.ToArray()).FirstOrDefaultAsync();
        }
Exemplo n.º 2
0
        private static String CreateWhere(ContactFilter filter)
        {
            var where = new StringBuilder("1 = 1");

            if (!String.IsNullOrEmpty(filter.Subtype))
                where.Append(" AND Contact.Subtype = @Subtype");

            if (!String.IsNullOrEmpty(filter.Name))
                where.Append(" AND Contact.Name LIKE @Name");

            if (!String.IsNullOrEmpty(filter.PersonName))
                where.Append(" AND (Contact.FirstName + ' ' + Contact.LastName LIKE @PersonName OR Contact.LastName + ', ' + Contact.FirstName LIKE @PersonName)");

            return where.ToString();
        }
Exemplo n.º 3
0
        public static async Task<ICollection<ContactFinderRow>> SelectFinderRowsAsync(this DbSet<Contact> dbSet, Int32 startRow, Int32 endRow, ContactFilter filter, String sortExpression)
        {
            const String query = @"
WITH Contact AS
(
    SELECT
        Contact.pk_ID AS ContactID
       ,Contact.Name
       ,Contact.Email
       ,Contact.FirstName
       ,Contact.LastName
       ,Contact.Subtype
       ,ROW_NUMBER() OVER (ORDER BY {1}) AS RowNumber
    FROM
        c.Contact
    WHERE
        {0}
)
SELECT * FROM Contact
WHERE RowNumber BETWEEN @StartRow AND @EndRow
ORDER BY RowNumber
";

            if (String.IsNullOrEmpty(sortExpression))
                sortExpression = "ContactID";

            String rowNumberOrderBy = sortExpression
                .Replace("ContactID", "Contact.pk_ID")
                ;

            String where = CreateWhere(filter);
            String curQuery = String.Format(query, where, rowNumberOrderBy);

            ICollection<SqlParameter> parameters = CreateParameters(filter);
            GitTimeContext.AddParameter("@StartRow", SqlDbType.Int, startRow, parameters);
            GitTimeContext.AddParameter("@EndRow", SqlDbType.Int, endRow, parameters);

            return await GitTimeContext.GetContext(dbSet).Database.SqlQuery<ContactFinderRow>(curQuery, parameters.ToArray()).ToListAsync();

        }
Exemplo n.º 4
0
        private static ICollection<SqlParameter> CreateParameters(ContactFilter filter)
        {
            var parameters = new List<SqlParameter>();

            if (!String.IsNullOrEmpty(filter.Subtype))
                GitTimeContext.AddParameter("@Subtype", SqlDbType.NVarChar, filter.Subtype, parameters);

            if (!String.IsNullOrEmpty(filter.Name))
                GitTimeContext.AddParameterForLike("@Name", filter.Name, parameters);

            if (!String.IsNullOrEmpty(filter.PersonName))
                GitTimeContext.AddParameterForLike("@PersonName", filter.PersonName, parameters);

            return parameters;
        }