Npqsql query not returning any rows

Question

I have my query in a function and it should return 9 rows. But, for some reason the query doesn't return any rows.

However when I run it in pgadmin, it works splendidly.

The username I use this query with is "test", and I know that it exists in the database with the proper data.

So my question is, why is this query not working?

internal bool TryGetOrders(out List<OrderDetail> orders, out Exception exception, string fromUsername = null)
{
    try
    {
        orders = new List<OrderDetail>();

        NpgsqlCommand cmd = new NpgsqlCommand();
        cmd.Connection = _connection;
            cmd.CommandText = "SELECT \"order\".\"id\", SUM(\"product\".\"price\" * \"orderline\".\"amount\") AS \"total_price\", \"order\".\"order_status\" " +
            "FROM \"order\" " +
            "JOIN \"orderline\" ON \"orderline\".\"order_id\" = \"order\".\"id\" " +
            "JOIN \"product\" ON \"orderline\".\"product_id\" = \"product\".\"id\" " +

            "JOIN \"user\" ON \"order\".\"user_id\" = \"user\".\"id\" " +
            "WHERE \"user\".\"username\" = '@username' " +

            "GROUP BY \"order\".\"id\"" +
            ";";

            cmd.Parameters.AddWithValue("username", fromUsername);

        NpgsqlDataReader reader = cmd.ExecuteReader();

        bool failed = true;
        while (reader.Read())
        {
            failed = false;
            OrderDetail order = new OrderDetail();
            order.OrderId = reader.GetInt32(reader.GetOrdinal("id"));
            order.Total = reader.GetDecimal(reader.GetOrdinal("total_price"));
            order.OrderStatus = (OrderStatus)reader.GetInt64(reader.GetOrdinal("order_status"));

            orders.Add(order);
        }

        if(failed)
        {
              exception = new Exception("No rows returned");
              return false;
        }
        else
        {
             exception = null;
             return true;
        }
    }
    catch (Exception e)
    {
        orders = null;
        exception = e;
        return false;
    }
}

Edit:

Removing the WHERE clause made the code return all rows. But now I need it with that clause.

i don't think you need your column names to be contained in quotes, at least I didn't when i was using postgres. That might make your query look simpler since you wouldn't have all the escaping \" there. — user1666620
– user1666620, Commented Mar 15, 2016 at 10:13
As there's a comparison on a string case-sensitivity comes to mind, is test written exactly the same in the users table? — dnoeth
– dnoeth, Commented Mar 15, 2016 at 10:23
Well, user is a reserved keyword. So we had to reference the column. Then we decided that for consistency we'd do it everywhere. It's just poor naming from our side which caused all these escaping quotes. — blipman17
– blipman17, Commented Mar 15, 2016 at 10:23
@dnoeth I'm verry sure that's the case. However I'll test it in a few. — blipman17
– blipman17, Commented Mar 15, 2016 at 10:26
@dnoeth no, just tested it. I lowered both values, tested the querry in pgadmin (it also worked) and implemented it. But then it failed again. same result. — blipman17
– blipman17, Commented Mar 15, 2016 at 10:29

devio · Accepted Answer · 2016-03-15 18:48:24Z

2

Why do you quote the @username parameter? Replace

"WHERE \"user\".\"username\" = '@username' " +

with

"WHERE \"user\".\"username\" = @username " +

Besides, if you have multiline statements and need to quote tables and columns, the code probably becomes more readable if you use the @"" notation instead.

answered Mar 15, 2016 at 18:48

devio

37.3k7 gold badges85 silver badges147 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

blipman17 Over a year ago

Somewhere in time the people in my project group made the choice to not do that because of some reason. I'd do it too if I was allowed.

Collectives™ on Stack Overflow

Npqsql query not returning any rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related