I am using Npgsql to access postgresql database

and I am running a query:

int a = connection.Execute(@"SELECT count(*) FROM account AS a WHERE a.account_name = 'food'");

The query will return value 4, but my a gets -1.what is the problem here and how can I solve it?

CodePudding user response：

It looks like you are using Dapper. If so, you want QueryFirst not Execute, as Execute just returns the number of rows

int a = connection.QueryFirst<int>(@"
SELECT count(*)
FROM account AS a
WHERE a.account_name = 'food'
");

CodePudding user response：

2 options:

1. Your query is failing to retrieve the data from the database, you need to check it with profiling the query on your database.

2. Try to use reader instead of Execution.

   // Retrieve all rows
   
   
   await using var cmd = new NpgsqlCommand(@"SELECT count(*) 
   FROM account AS a WHERE a.account_name = 'food'", conn);
   
    await using var reader = await cmd.ExecuteReaderAsync();
   
    while (await reader.ReadAsync())
    {
        var a=reader.GetString(0);
       if(a==4){ 
        //do something
        }
    }