Get specific column from a dapper result set

Question

I'm new using Dapper and I want a better way to fetch data from a certain column in a Dapper row. Data is being retrieved from a stored procedure that returns 1 or more rows with 5 columns but I only need to get 1 column data for this specific method. My code is actually working but I know there's a better way to write this and would love to apply it.

Here's the sample result from stored procedure: {{DapperRow, prodid = 'b1', prodname = 'sample product', description = 'test only', qty = '1', remainingstock = '10'}}

Here's the snippet of my code:

     var products = new List<Product>();
            using (var db = DbConnection)
            {
                var data = //sp call here

                //code for improvement starts here
                foreach (var row in data)
                {
                    foreach (var col in row)
                    {
                        if (col.Key == "prodname")
                        {
                            var product = new Product
                            {
                                DisplayResult = col.Value
                            };
                            products.Add(product);
                            break; //skip the remaining columns
                        }
                    }
                }
                //to here
            }

        return products;

expected result would be: products[0].DisplayResult = "sample product"

score 2 · Answer 1 · answered Oct 22 '19 at 05:24

2

Cast your row to dictionary and access by column name.

var data = (IDictionary<string,object>)row;
object value = data["prodname"];

answered Oct 22 '19 at 05:24

Orifjon

915
8
25

Caius Jard · Answer 2 · 2019-10-22T06:51:05.130

You seem to be missing the most compelling feature of Dapper; it will map the Products for you

It's supposed to work like (super simplified:

class Product{
  int ID;
  string Name;
}

And if your column names in the db don't match then you alias them in the query:

using(con = ...){
  var prodList = con.Query<Product>("select prodid as ID, prodname as Name from products where prodid between @a and @b", new{ a = 1, b = 100});
}

That's it: dapper runs the query and generates you an ienumerable full of Products, without the "row by row, if column then ..."

As you're using an sp you have a problem in that you cannot alias your columns but you can either:

use a technique to map your column names with your properties - Manually map column names with class properties
run the sp and accept an ienumerable of anonymous type with properties named as the sp outputted them (you're doing something like this currently) then use a LINQ select to create your products; it would look something like con.Query("spname", ..params..).Select(e => new Product(){ ID = e.prodid, Name = e.prodname})
don't use the sp; select from the tables and alias the columns to match your properties (more like how dapper was intended to be used)

score 1 · Answer 3 · answered Mar 20 '22 at 19:39

Additionally to the recommended strong typed way mentioned here, you can also use Dapper with dynamic to access the columns on a DapperRow:

{{DapperRow, prodid = 'b1', prodname = 'sample product', description = 'test only', qty = '1', remainingstock = '10'}}

For example:

var row = conn.QueryFirst<dynamic>("SELECT prodid as ID, PRODNAME as Name FROM products").QTD;

var ID = row.ID;
var Name = row.Name;

Get specific column from a dapper result set

3 Answers3