C# Async SQL query

bchip

bchip

Expert Member
Joined
Mar 12, 2013
Messages
1,324
Reaction score
418
Hi everyone

Im playing around with a project and getting an annoying bug. Hopefully someone knows their way around it.
On the startup of a C# .NET application I connect to a postgresSQL server and send a basic SELECT query.
It simply asks for all the accounts in the database to be loaded into the application.

Sometimes the application comes back with data, and other times it doesnt.
If I debug with breakpoints it always comes back with the data, which made me suspect that
it had to do with the timing of the query.

So with that then, I next tried to add async/await into the process.
Maybe the connection is taking longer than expected? Maybe its the query? Not sure
So I added in async and didnt work, the application froze.
So next I tried to simply add a Task.Delay(1000) to make sure its not the connection or anything giving issues.
Even the Task.Delay is freezing. Did some further googling and found that its likely that the await command
freezes the whole process including the Task.Delay - hence why it doesnt continue at all....just pauses indefinitely.

Any ideas how to get around this? All Im trying to do is make sure that everytime I request data from the DB
it returns and then continues (which is what sync programming is suppose to do...pause until its finished)

Any help will be much appreciated.
Thanks
 
and the SQL as well please. Can maybe optomize it.
 
So its not a timeout issue?
How many seconds does it take to run the sql?
No errors being thrown?

Location of DB?Local Machine?
 
Some answers:
Its on a local machine, no errors thrown. In fact about 60% of the time the data comes through as expected.

Heres the code I can show
string sqlqry = "SELECT Distinct account, Min(accdate) as datefirst, Max(accdate) as datelast ";
sqlqry += "FROM accounts ";

NpgsqlConnection dbcon = getConnection();
dbcon.Open();

List<anAccount> allAcc = new List<anAccount> { };
try
{
//tried to do the dbcon.open bit through an async method (OpenASync) here
//int x = DoTaskOpen(dbcon).Result;

NpgsqlCommand cmd = new NpgsqlCommand(sqlqry, dbcon);
cmd.ExecuteNonQuery();

//tried to do the dbcon.ExecuteNonQuery bit through an async method (ExecuteNonQueryASync) here
//int y = DoTaskGet(cmd).Result;

NpgsqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string account = reader["account"].ToString();
DateTime datefirst = DateTime.Parse(reader["datefirst"].ToString());
DateTime datelast = DateTime.Parse(reader["datelast"].ToString());

anAccount newItem = new anAccount();
newItem.Account = account;
newItem.DateFirst = datefirst;
newItem.DateLast = datelast;

allAcc.Add(newItem);

}
reader.Close();
}
catch (Exception mm)
{

}
 
Last edited:
To add to the above - if I run DoTaskOpen() it had this code

public static async Task<int> DoTaskOpen(NpgsqlConnection dbcon)
{
await Task.Delay(3000);
//await dbcon.OpenAsync();
return 1;
}
 
Nothing in your catch (Exception mm) block?
Did you forget to copy it, or are you intentionally ignoring errors?

At least (for now) write the errors to the console.
C#: 
catch (Exception mm) {
  Console.WriteLine("message: {0}\ntrace: {1}\n", mm.Message, mm.StackTrace);
}

Also this code could throw exceptions:
C#: 
NpgsqlConnection dbcon = getConnection();
dbcon.Open();

...and why do you have both cmd.ExecuteNonQuery(); and cmd.ExecuteReader();, the latter is what you want re the SELECT.

As for the Async processing, you should rather consider using e.g. ContinueWith instead of await Task.Delay(3000); to async update to your UI.

Side note:
I've been chatting recently over PM with @Solarion re a functional approach to this area of code, e.g. interfacing via SQL, running async processes, etc.
So if there's any interest I could certainly consider turning some of that into a thread that demonstrates this..
Click to expand...
 
Last edited:
Thanks for the reply.

You are right this try catch is silly, I forgot to check this.
Seems an error did pop up just now - "Error reading the stream"
Will investigate further - seems to appear when the DB hasnt been queried in a while.

Thanks again


[)roi(] said:
Nothing in your catch (Exception mm) block?
Did you forget to copy it, or are you intentionally ignoring errors?

At least (for now) write the errors to the console.
C#: 
catch (Exception mm) {
  Console.WriteLine("message: {0}\ntrace: {1}\n", mm.Message, mm.StackTrace);
}
Click to expand...
 
I haven't done C# in a while but try this.
Not in favour of that inline SQL though.
You can also use one "using" command to bundle up the connection string and the query, or even a sproc.

C#: 
  string connectionString = "your connection string";
   using (SqlConnection con = new SqlConnection(connectionString))
     {
        con.Open();
        using (SqlCommand command = new SqlCommand("SELECT Distinct account etc.", con))
            using (SqlDataReader reader = command.ExecuteReader())
            {
              while (reader.Read())
              {   // You shouldn't declare and initialize like you are doing below but I am leaving that to you to sort out
                string account = reader["account"].ToString();
                 DateTime datefirst = DateTime.Parse(reader["datefirst"].ToString());
                 DateTime datelast = DateTime.Parse(reader["datelast"].ToString());
                 anAccount newItem = new anAccount();
                 newItem.Account = account;
                 newItem.DateFirst = datefirst;
                 newItem.DateLast = datelast;
              }
            }
      }
 
CamiKaze said:
I haven't done C# in a while but try this.
Not in favour of that inline SQL though.
You can also use one "using" command to bundle up the connection string and the query, or even a sproc.

C#: 
  string connectionString = "your connection string";
   using (SqlConnection con = new SqlConnection(connectionString))
     {
        con.Open();
        using (SqlCommand command = new SqlCommand("SELECT Distinct account etc.", con))
            using (SqlDataReader reader = command.ExecuteReader())
            {
              while (reader.Read())
              {   // You shouldn't declare and initialize like you are doing below but I am leaving that to you to sort out
                string account = reader["account"].ToString();
                 DateTime datefirst = DateTime.Parse(reader["datefirst"].ToString());
                 DateTime datelast = DateTime.Parse(reader["datelast"].ToString());
                 anAccount newItem = new anAccount();
                 newItem.Account = account;
                 newItem.DateFirst = datefirst;
                 newItem.DateLast = datelast;
              }
            }
      }
Click to expand...

The problem with this approach to this is that it inevitably results in a lot of duplication; each new SQL statement ends up repeating most of this code over and over again -- and exception handling is more often than not, done poorly, or not at all.

/EDIT:
With a bit of polish; that type of query call could be turned into something as simple as this, and plus points the concept is repeatable:
C#: 
public static Either<string, List<anAccount>> GetAnAccounts(string sql, string connectionString) {
  return SQL
    .Connect(connectionString)
    .Bind(SQL.ExecuteReader(sql: sqlqry, transform: anAccount.Transform));
}
 
Last edited:
bchip said:
Sometimes the application comes back with data, and other times it doesnt.
If I debug with breakpoints it always comes back with the data, which made me suspect that
it had to do with the timing of the query.
Click to expand...

You probably exit a method before the results are returned. When the DB hasn't been queried for some time it might take just too long for results. Sounds like an async/await issue.

What type of app is it?
 
Hi everyone. Thanks for all the help (especially Droid).
Turns out it was a SQL timeout issue, the SQL query timed out before it got the data
and then the try catch would ignore the issue.

I fixed it by changing my connection string from
SERVER=localhost;PORT=1234; Database=...; User ID=...; Password=...; Timeout=195;
To:
SERVER=localhost;PORT=1234; Database=...; User ID=...; Password=...; Timeout=195; CommandTimeout=195;

Thought I did initially fix it with just the "Timeout"
 
CamiKaze said:
I haven't done C# in a while but try this.
Not in favour of that inline SQL though.
You can also use one "using" command to bundle up the connection string and the query, or even a sproc.

C#: 
  string connectionString = "your connection string";
   using (SqlConnection con = new SqlConnection(connectionString))
     {
        con.Open();
        using (SqlCommand command = new SqlCommand("SELECT Distinct account etc.", con))
            using (SqlDataReader reader = command.ExecuteReader())
            {
....
      }
Click to expand...

Thanks. Will definitely Update.
Not sure why I didnt use it in the first place.
 
Last edited:
[)roi(] said:
The problem with this approach to this is that it inevitably results in a lot of duplication; each new SQL statement ends up repeating most of this code over and over again -- and exception handling is more often than not, done poorly, or not at all.

/EDIT:
With a bit of polish; that type of query call could be turned into something as simple as this, and plus points the concept is repeatable:
C#: 
public static Either<string, List<anAccount>> GetAnAccounts(string sql, string connectionString) {
  return SQL
    .Connect(connectionString)
    .Bind(SQL.ExecuteReader(sql: sqlqry, transform: anAccount.Transform));
}
Click to expand...

Yeah SQL injection attacks and all.
 
semaphore said:
Passing in raw queries, unless you're using some different type of SQL.ExecuteReader package or that Either<T,T> thing.
Click to expand...
Sure (I guess SPs versus RAW), but we are really touching on two different things here;

My comment related more to a common tendency to:
  • Repeat a lot of the setup, compute and breakdown code that usually accompanies SQL queries.
  • Not properly manage exceptions.
Either btw is a just a functional sum type that forces you into a pattern of equating for both outcomes within the function i.e. failure / success. State here is monadically bound, meaning we avoid all grey path computations, and state bound computations only compute for the happy path.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X