CODEWITHSUNDEEP
c#npoco
Nick Coad
Nick Coad

Reputation: 3694

Handling arbitrary queries in NPoco

I feel like I'm missing something obvious here, how can I run a query through NPoco and retrieve an arbitrary number of potentially differently typed values as a result.

Example

Say I have a table that looks like this:

|-------------------|------------|
| ID    |    AMOUNT | DATE       |
|-------------------|------------|
| 1     |      2300 | 01/02/2014 |
| 2     |       400 | 03/02/2014 |
| 3     |      1200 | 06/02/2014 |
| 4     |       950 | 06/02/2014 |
|-------------------|------------|

I want to query this table with the following (MSSQL):

SELECT
MAX(DATE) AS MOST_RECENT_ORDER_DATE, SUM(AMOUNT) AS TOTAL_AMT_OF_ORDERS
FROM
ORDERS

So I figure I'd use code similar to the following, where db is an NPoco Database object.

var result = db.Query(<query string>);

The problem is NPoco's Query method requires a type parameter and I'm not sure what to give it. The problem is the two values actually have different types, one will be a DateTime and the other will be an int. I tried using the following:

List<Dictionary<string, object>> result;
result = db.Query<Dictionary<string, object>>(queryString).ToList();

But I can't figure out the syntax to access/use the results, and it all just seems very clunky and I'm sure I must have gone about this the wrong way.

To summarise:

How can I use NPoco to run a query returning columns of different (arbitrary) types, and then access and use those values?

Upvotes: 1

Views: 3425

Answers (2)

Adrian F&#226;ciu
Adrian F&#226;ciu

Reputation: 12552

You're on the right track. The query returns only one row so you can simply fetch the first dictionary from the result:

result = db.Query<Dictionary<string, object>>(queryString)
var dictionary = result.First();

Then you can get the objects using the names from the query:

dictionary["MOST_RECENT_ORDER_DATE"]
dictionary["TOTAL_AMT_OF_ORDERS"]

Afterwards you can simply convert the objects to your desired data types. (eg. using Convert methods)

Upvotes: 3

Alex Art.
Alex Art.

Reputation: 8781

Try to use db.FirstOfDefault<Dictionary<string, object>>(queryString) instead of db.Query and then simply get your values from the dictionary (the keys are the column names).

Upvotes: 3

Related Questions