How to return an integer value by query object using dapper

Question

With the below method I am trying to return an integer which is return by a stored procedure it return value 0 or more than zero. In my understanding when the data is returned it is going to be a dictionary (not sure) where as I am trying to return an integer. How I can achieve that. Below code is incomplete and the code in comment is what I did at first and I realized that I can rewrite the same code in much simpler way. I left both of them there so I am wrong someone can fix me thanks.

public int Exists(string plan)
{
    using (var connection = new SqlConnection(Connection))
    {
        connection.Open();
       var parameters = new DynamicParameters();
       //parameters.Add("@Plan", plan, DbType.String,
           //ParameterDirection.Input, null);
           //I guess I can directly pass param but not sure how ??

        var data=connection.Query("storeProcmem_Plan", new{ ? ?},
               CommandType.StoredProcedure);

        //how to return an integer               
        int myvalue= data ???                


        connection.Close();
        return Convert.ToInt32(data);

Answer 1

Assuming your parameters are fixed (which they typically are for stored-procedures - the main time you would need DynamicParameters is if you are generating SQL on-the=fly to match some complex flexible query) - then the parameter aspect is as simple as:

new { Plan = plan }

This uses the compiler's anonymous type feature to define that the parameter should be named Plan, be of type string, and have the value taken from the variable plan.

If we assume that the stored procedure selects the result (rather than return, which needs to be coded separately) in a single row, then the easiest way to read that is:

var myvalue = connection.Query<int>("storeProcmem_Plan", new { Plan = plan },
           CommandType.StoredProcedure).First();

The generic vs non-generic API switches between typed results and dynamic results - both has uses, but <int> seems appropriate here. The .First() is the standard LINQ operation that translates from IEnumerable<T> to T by picking the first value (and throwing an exception if there wasn't one) - you could also use Single(), FirstOrDefault(), or SingleOrDefault() depending on the exact semantics you want.

For completeness, if you were using the non-generic API, then you need to know the column name - basically, that would be:

dynamic row = connection.Query("storeProcmem_Plan", new { Plan = plan },
       CommandType.StoredProcedure).First();
int myvalue = row.ColumnName;