SqliteCommand.ExecuteScalar() returns null (C# 5.0 and System.Data.Sqlite)

Question

This code works!!!

DB path was wrong. Because of this, when I was executing:

dbConnection.Open();

Instead of opening my DB, I was creating an empty one.

Thank you all for your help!

The problem

I am using C#.NET 5.0 and System.Data.Sqlite to create a console application that runs on windows.

I am trying to check if a table called "parcelas" exist on the database For that I am using this query:

SELECT name FROM sqlite_master WHERE type='table' AND name='parcelas'

This query runs with no problems on DB Browser. But when I run it on my C# program the query returns "null" without any exceptions (for sure I have one "System.NullReferenceException" when trying to read the null result)

I tried casting the result object and calling .toString() without success.

¿Am I doing something wrong?

Here is the code:

public void TableExist(string tableName)
    {
        if (tableName == null || db == null || db.State != System.Data.ConnectionState.Open)
        {
            Console.WriteLine("ERROR!!!");
            return;
        }

        SQLiteCommand cmd = new SQLiteCommand(db);
        cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='"+ tableName +"'";
        var result = cmd.ExecuteScalar();
        Console.WriteLine("RESULT: " + result.ToString());
        return;

    }

I suggest you to check this code with a debugger and verify what value is really present in the variable _tableName_ — Steve, Oct 05 '21 at 15:32
NB: If this is not just a toy example but real code: Please replace the first `if` with proper checks: `if (tableName == null) throw new ArgumentNullException(nameof(tableName));`, etc. Think about your future self who sits in front of the customer's machine, sees "ERROR!!!" on the Console, wonders what the heck went wrong and curses your present self. :-) — Heinzi, Oct 05 '21 at 15:33
Sure! for now, I am just testing. I will refactor for sure. I promise ^^ — Iroqas, Oct 05 '21 at 15:34
Are you sure that you don't get exceptions here? Because if the table name doesn't exist then you get null as result and calling result.ToString() should raise a NRE exception. Otherwise I find very strange that a query on a sqlite_master for a table name returns a row with null value. — Steve, Oct 05 '21 at 15:40
Sorry, I didn't explain properly. The query does not generate any exception when executed. Trying to print "result" does. I will edit it on the question. :) The point is that it shouldn't be null... — Iroqas, Oct 05 '21 at 15:43
@Steve a row with null value would be `DBNull.Value`; instead, it seems to be returning `null`, which is: zero rows — Marc Gravell, Oct 05 '21 at 15:50
Right, I see that the OP has added the info about the NRE. So it is working on an incorrect name or the db is not right — Steve, Oct 05 '21 at 15:57

score 3 · Accepted Answer · answered Oct 05 '21 at 15:27

3

ExecuteScalar returns null when zero rows are returned from a query; it returns DBNull.Value when at least one row is returned and the value in the first column is a database null.

So: your query is not returning rows. Check whether that is expected, and if not: fix it. I would, however, be much more concerned about the SQL injection vulnerability in AND name='"+ tableName +"'";; parameters should always be preferred.

answered Oct 05 '21 at 15:27

Marc Gravell

1,026,079
266
2,566
2,900

I am confused about why executing the query in DB Browser returns a row... and why c# returns null (it is the same query)... Thank you for letting me know about that security issue, I will have a look at it. :) – Iroqas Oct 05 '21 at 15:32
@Iroqas question: are you looking at the same database? note that when you build and run your code, if you're using a file-based database (as opposed to a server-hosted database): you're probably now running against a different copy of the file, which may have different database contents (if you've done different things on the other copy) – Marc Gravell Oct 05 '21 at 15:34
Yes, I am sure I am opening the same file. I did no changes to it because it already contains all the info. My plan is to use it as a read-only database. – Iroqas Oct 05 '21 at 15:41
@Iroqas have you tried running `select count(name) from sqlite_master` in both the .NET code and in the DB browser? does it return the same number? is `tableName` what you expect it to be? I can't debug it from here... – Marc Gravell Oct 05 '21 at 15:48
I think you found the point. On DB browser it returns 5, on C# returns 0... This brings a lot of light to the issue. I am not very used to work with SQL! Thank you very much! – Iroqas Oct 05 '21 at 15:52
@Iroqas that strongly suggests you're looking at a different database... – Marc Gravell Oct 05 '21 at 16:01
ok... yeap. Entering the full path reveals the right result. OMG what a funny issue. Seems C# code is creating an empty database in case nothing is found on the specified path... and the relative path was wrong... So I had a new empty database... What a noob issue hahaha. Thank you for your time! – Iroqas Oct 05 '21 at 16:07

SqliteCommand.ExecuteScalar() returns null (C# 5.0 and System.Data.Sqlite)

This code works!!!

The problem

1 Answers1