Question Using System.Windows.SQLite in an event handler

[cbreemer]

Hi all, I'm wondering if there is an issue with calling SQLite in an event handler ? I have a TextChanged handler that attempts to check for a row in the database. When the event handler is fired, my program freezes, as well as Visual Studio and, indeed, the entire Windows Explorer ! The only thing saving me from having to log out or reboot is that I can still do Ctrl-Alt-Del, run the task manager and kill my program.

The weird thing is that the debugger never hits a breakpoint which I have set in the event handler on a line before the SQLite. So I don't know where and why it locks up. Only when I actually comment out the SQLite code, the trigger starts working and becomes debuggable again. I am at a loss to understand what voodoo is happening here

Note, I'm using System.Data.SQLite because I had some problem (which I don't remember) with Microsoft.Data.SQLite.

Note 2, the same code works fine when called from the form's Load event handler.
My event handler :

        private void tbDate_TextChanged(object sender, EventArgs e)
        {
            if ( tbDate.Text.Length == 0 )
                return;

            long count = -1;      /* Breakpoint on this line is never hit ! */
            using (var conn = new SQLiteConnection($"Data Source={DATABASE}") )
            {
                conn.Open();
                var cmd = conn.CreateCommand();
                cmd.CommandText = $"select count(*) from meterstanden where date = '{tbDate.Text}'";

                try
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            foreach (var field in reader.GetValues())
                            {
                                count = (long)reader[(string)field];
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Select count(*) failed");
                }
                conn.Close();
            }

            MessageBox.Show("" + count, "Select count(*) result");
        }

 

[Skydiver]

Unless you are writing a shell extension, I find it highly unlikely that your program is also freezing Windows Explorer.

If also find it strange that your code would be skipping your breakpoint when the SQL query active. Unless you are building for Release and trying to debug that, in my experience breakpoints get hit. I suggest also setting a breakpoint at the method name as well.

 

[Skydiver]

Although not your specific question, if all you need to do is check for the presence of a row, you are partially on the correct approach with your use of SELECT COUNT(*) WHERE condition.

First, though, all you need to do is to use ExecuteScalar() to get back that count instead of ExecuteQuery() and trying to read that single row returned.

Next instead of COUNT(*), use COUNT(key) to help the database engine a little bit.

 

[cbreemer]

Unless you are writing a shell extension, I find it highly unlikely that your program is also freezing Windows Explorer.

If also find it strange that your code would be skipping your breakpoint when the SQL query active. Unless you are building for Release and trying to debug that, in my experience breakpoints get hit. I suggest also setting a breakpoint at the method name as well.

Thanks for your reply. Yes, highly unlikely and equally frustrating. I'm not sure I was right to conclude that the Windows Explorer was hung. But I certainly could do hardly anything with it. I'm 100% sure it wasn't hitting the breakpoint though. It's all very mysterious, especially that now I've changed some minor things in the code (mostly move the SQLite code to a function outside the event handler, and fixing a casting error in processing the row) the problem doesn't occur anymore ! So far, no problems whatsoever ! Which seems even weirder. Oh well... Fingers crossed

 

[cbreemer]

Although not your specific question, if all you need to do is check for the presence of a row, you are partially on the correct approach with your use of SELECT COUNT(*) WHERE condition.

First, though, all you need to do is to use ExecuteScalar() to get back that count instead of ExecuteQuery() and trying to read that single row returned.

Next instead of COUNT(*), use COUNT(key) to help the database engine a little bit.

Thanks. Yes, I realize that code is a bit cumbersome for the purpose, and there should be a smarter way to check the presence of a row. If only the System.Data.SQLite documentation was a bit more forthcoming... the .chm file keeps giving javascript errors and saying I need to install Edge (which stopped working for me some weeks ago for no good reason I could think of, and no way I could resurrect it).

As an aside, would you recommend using Microsoft.Data.SQLite instead of System.Data.SQLite ?

 

[Skydiver]

As an aside, would you recommend using Microsoft.Data.SQLite instead of System.Data.SQLite ?

I am anti-SQL so neither one.

But this may help you make a choice for yourself:

Comparison to System.Data.SQLite - Microsoft.Data.Sqlite

Describes some of the differences between the Microsoft.Data.Sqlite and System.Data.SQLite libraries.

learn.microsoft.com

 

[cbreemer]

I am anti-SQL so neither one.

Haha, say no more

But this may help you make a choice for yourself:

Comparison to System.Data.SQLite - Microsoft.Data.Sqlite

Describes some of the differences between the Microsoft.Data.Sqlite and System.Data.SQLite libraries.

learn.microsoft.com

I had actually read that article ! But did not see much functional difference, except they seem to have nicely cleaned up the creation of user-defined functions. No good reason to switch over though. I'm hoping that others here may have a preference. Choices always confuse me, especially when they're almost the same.