How to check if a Dapper QueryMultiple returned results

2.5k Views Asked by At

I have a repository method that takes optional parameters, which may or may not return any records. If the query returns records everything works fine, but if there are no results then I get an exception thrown within Dapper. There doesn't appear to be a method on the connection or multi to allow me to check whether there is a result set that was returned before trying to read. Is there a way to do this or am I doing something illogical here?

    public ICustomer GetCustomer(int? custId = null, string email = null, string phoneNumber = null)
    {
        using (var connection = GetConnection())
        {
            using (var multi = connection.QueryMultiple("BusinessEntity.usp_Customer_GetCustomer", 
            new { CustId = custId, Email = email, Phone = phoneNumber },
            commandType: CommandType.StoredProcedure))
            {
   Exception -> var customer = multi.Read<Customer>().SingleOrDefault();

                //customer.Address = multi.Read<Address>() as IList<IAddress>;
                //customer.Phone = multi.Read<Phone>() as IList<IPhone>;

                return customer;
            }
        }
    }

The exception is

No columns were selected

        if (reader.FieldCount <= startBound)
        {
            throw MultiMapException(reader);
        }
1

There are 1 best solutions below

0
On

This was actually a flaw in the logic of my stored procedure. I was conditionally skipping the part where I actually performed the multiple selects based on a previous query for the customer Id.

ALTER PROCEDURE [BusinessEntity].[usp_Customer_GetCustomer]
   @CustId INT = NULL,
   @Email  VARCHAR(100) = NULL,
   @Phone  VARCHAR(100) = NULL
AS
 BEGIN TRAN;
 IF @CustId IS NULL
     BEGIN
         SELECT @CustId = c.CustomerId
        ...
     END;
         IF @@ROWCOUNT > 1 AND @Cust IS NOT NULL --Wrong
         IF @CustId IS NULL @CustId = 0 --Sort of correct
             BEGIN
                 SELECT ...
                 SELECT ...
                 SELECT ...
             END;
                 COMMIT;
                 RETURN 0;