how to return an intger value by query object using dapper

c# dapper

Question

With the below method I am trying to return an intger which is return by a stored procedure it return value 0 or more than zero. In my understanding when the data is return it is going to be a dictionary(not sure) where as I am trying to return an integer. How I can achive 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 simple 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);

Expert Answer

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;


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why