How do I used Count(*) with DAL2?

Question

I want to get counts for various groupings of data in some of my tables and am not sure if it is possible using DAL2.

I want perform queries such as:

SELECT  DISTINCT productType, COUNT(*) FROM Products GROUP BY productType

The information I come across only includes examples that allow the user to specify the WHERE part of the SQL. This example unfortunately skirts right around the WHERE part of the query so I am not sure how I should approach this using DAL2. Is it possible using DAL2 or do I need to query the database another way? If it can be done using DAL2, how do I execute such a query?

score 3 · Accepted Answer · answered Dec 21 '13 at 13:13

3

The examples showing only the WHERE part mean that PetaPoco fills in the "SELECT * FROM TableName" part for you, but of course you can execute your own sql statement

In your case:

public class ProductCount {
   public int ProductType {get; set;}
   public int Count {get; set;}
}

var ProductCountList = db.Fetch<ProductCount>(@"SELECT DISTINCT productType, 
        COUNT(*) as Count 
        FROM Products 
        GROUP BY productType");

answered Dec 21 '13 at 13:13

Eduardo Molteni

38,786
23
141
206

Thanks Eduardo. It looks like this is the correct solution however in my code I am using an IDataContext object to interact with the database. I used a similar technique with the .ExecuteQuery() from the IDataContext object and it seems to be working. The object I am using doesn't have .Fetch(). What type of object is "db" here? – RacerNerd Jan 02 '14 at 17:02
I'm talking about PetaPoco. I've read that DAL2 it's based on PetaPoco. Maybe you have the `Query()` function – Eduardo Molteni Jan 02 '14 at 19:54
Thanks. This is the answer if people use .ExecuteQuery(). My question in the comment is directed at the type of object the variable "db" in your example is. What would the declaration look like? – RacerNerd Jan 03 '14 at 17:15
like `var db = new PetaPoco.Database(connectionString);` – Eduardo Molteni Jan 06 '14 at 13:45

score 0 · Answer 2 · answered Dec 21 '13 at 06:31

I can't tell you what is best practice. But I have a SQL server back end and use dal2 with a dnn module. I just created a view in SQL server with my grouping and joins and then mapped that view like a table (use view name instead of table name in the class annotations) with an auto increment of false. Worked for me and I get the benefit of precompiled and non dynamic queries. If you need to generate this dynamically, I am not sure what the best approach is.

I would love to hear from other members about this.

How do I used Count(*) with DAL2?

2 Answers2