CODEWITHSUNDEEP
c#sqlpostgresql-9.1npgsql
blipman17
blipman17

Reputation: 531

Npqsql query not returning any rows

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.

Upvotes: 0

Views: 907

Answers (1)

devio
devio

Reputation: 37205

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.

Upvotes: 2

Related Questions