Get item from database by ID and get all items from database
First some general information. Your code currently solves a specific problem of the "object relational impedance mismatch" in a specific way. Consider to minimize the effort and complexity to handle this mismatch by using an object-relational mapping framework. Here is the thing: Every single step of code improvement code will lead towards an implementation of an more generalized object-relational mapper anyway. But that will be a hard way to go. Every answer given will be one of those:


Write your own OR-Mapper
Use an existing OR-Mapper
Develop your code towards an OR-Mapper
Ignore the problem through adding specific code that makes your solution more inflexible


I don't think that 1. or 2. will be covered here. I will ignore 4 because we are here to improve code. Therefore I will only provide some steps of 3.

The first step to remove redundancy is to identifiy the parts that need to be flexible. In your case you may need a new class of objects that holds following information:


table column name (unitid)
the type of the table column (NpgsqlDbType.Integer)
value to query (id)
the placeholder within the query (":id")


So what you have built then is something like a QueryParameter for a specific table (average_hdop).

Now you can generalize your parameter of the query-method:

private List&lt;Tuple&lt;double, DateTime&gt;&gt; GetCarHdops(List&lt;QueryParameter&gt; queryParams)


With this information you will be able to built your where-clause dynamically through string concatenation. This will be some effort and error prone. Furthermore you are currently restricted to one conjunction. You have to decide between "and" or "or". And make sure to omit the where-clause in the whole if the queryParams are empty.

The parameter will be passed to the method "QueryDatabase" as well. There you can built your "NpgsqlCommand" dynamically.

This is only a suggestion to develop the code in the "right" direction. As you already asked to avoid redundancy on this way you also gain flexibility.

The flexibility now ends at the level where you want to built queries with "and" and "or". If you want to do that you must have somethig like the Criteria-API in JPA (Java). But as I said I won't cover that in this answer.

One other thing you should think about: db.OpenConnection(); and db.CloseConnection(); are perfect candidates be extracted to a template. (see https://en.wikipedia.org/wiki/Template_method_pattern)