Dapper question. Getting values from returned object

Question

Just started learning Dapper. I have an ADO.NET background. Using a demo I downloaded, I can insert/delete data from a webform into a MySql table just fine. This, however, I have searched all morning on.

In retrieving a single row from the db by ID, it doesn't return a LIST<>, it seems to be just an object (using code from the demo I downloaded). The query works, I get the object back. It has the fields: "ProductID, Description and Price".

The only way I could get the values to those three fields was like this:

System.Reflection.PropertyInfo pi = Product.GetType().GetProperty("ProductID");
System.Reflection.PropertyInfo desc = Product.GetType().GetProperty("Description");
System.Reflection.PropertyInfo price = Product.GetType().GetProperty("Price");

int _ProductID = (int)(pi.GetValue(Product, null));
string _Description = (string)(desc.GetValue(Product, null));
decimal _Price = (decimal)(price.GetValue(Product, null));

This works and gets the correct values for the three fields.

I'm used to looping through DataTables, but I just think there is probably a better way to get those values.

Is this the correct way to do this or am I missing something? I did actually read documentation and mess with this all morning before asking, too.

Some of the things I looked at seem to be very complex. I thought Dapper was supposed to simplify things.

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

OK, Thanks Marc. It was difficult for me to see what was supposed to be in the Dapper class files and what was supposed to be in my code behind. The original demo way of getting a product by ID had the query as .FirstOrDefault();

I changed everything to return a List<> and it all worked. I'm sure my ADO.NET is showing, but this works. In Dapper class files:

 public List<Product> ProductAsList(int Id)
        {
            return this._db.Query<Product>("SELECT * FROM Cart_product WHERE ProductID=@Id", new { Id = Id }).**ToList()**;
        }

This is just getting one row that matched the ProductID.

In page codebehind:

protected void CartItemAdd(string ProductId) // passing it the selected ProductID
    {

        var results = cartservice.ProductAsList(Convert.ToInt32(ProductId));

// returns that one row using Dapper ProductAsList(ProductId)

        int _ProductId = 0;
        string Description = string.Empty;
        decimal Price = 0;

// Loop through the list and get the value of each item:

        foreach (Product obj in results)
        {
            _ProductId = obj.ProductID;
            Description = obj.Description;
            Price = obj.Price;
        }

// Using Dapper to insert the selected product into the shopping cart (table):

        String UserName = "jbanks";

        cartitem = new CartItem();
        cartitem.ProductID = _ProductId;
        cartitem.Quantity = 1;
        cartitem.Description = Description;
        cartitem.Price = Price;
        cartitem.Created = DateTime.Now;
        cartitem.CreatedBy = UserName;
        result = cartservice.AddCartItem(cartitem);

        if (result)
        {
            lblMessage.Text = string.Empty;
            lblMessage.Text = "Successfully added a cart item";
        }
    }


}

It does indeed look up the product from one table and insert a selected item into another table.

Thanks again!

Why [AsList](https://stackoverflow.com/q/47794373/5779732) is explained here... — Amit Joshi, Oct 23 '19 at 07:02

score 1 · Accepted Answer · answered Oct 22 '19 at 16:56

1

The main Query<T> API returns an IEnumerable<T>, which often will be a List<T>; the AsList<T>() extension method can get it back to a list without a copy, but either way: they are just T, for whatever T you asked for. If you asked for Query<Product>, then: they should be Product instances:

var results = connection.Query<Product>(someSql, someArgs); // perhaps .AsList()
foreach (Product obj in results) { // "var obj" would be fine here too
    // now just use obj.ProductID, obj.Description and obj.Price
}

If that didn't work: check that you used the <T> version of Query. There is a non-generic variant too, which returns dynamic. Frankly, you should almost always use the <T> version.

Note: I'm assuming that somewhere you have something like

class Product {
    public int ProductID {get;set;}
    public string Description {get;set;}
    public decimal Price {get;set;}
}

answered Oct 22 '19 at 16:56

Marc Gravell

1,026,079
266
2,566
2,900

Is this in my Dapper class files or my code behind for the page I'm trying to get the results on? In the demo I got, some things are returned as a LIST<>, but not everything. The GetProductByID was not as a LIST<>. This is in the Dapper part of the code: public Product ProductById(int Id) { return this._db.Query("SELECT * FROM Cart_product WHERE ProductID=@Id", new { Id = Id }).FirstOrDefault(); } This is in my webform code: object Product = new object(); Product = cartservice.ProductById(Convert.ToInt32(ProductId)); – user1544428 Oct 22 '19 at 18:27
The object that comes back says it isn't an IEnumerable. So you are saying I should just return everything as a List? This is how the demo I got was getting all products from the table: public List GetAllProducts(). But on getting a single row it returns as just an object: public Product ProductById(int Id) – user1544428 Oct 22 '19 at 18:36
@user1544428 it really is an `IEnumerable`; source: I wrote it. If you're using LINQ on top (First(), etc) then yes that'll reshape it. – Marc Gravell Oct 22 '19 at 21:06

Dapper question. Getting values from returned object

2 Answers2