SQLite EXISTS parameter return -1 regardless if the record exists or not, C#

user_13825946 0
Viewed 54

I'm having an issue with my SQLite code. I'm trying to check if a record exists in my SQLite table, but for some reason, it always returns -1, no matter if the record exists or not. As much as I understand, it should return 1 if the record exists, and 0 if it doesn't. Can someone please point me to my error? Thanks!

var freeHours = new List<DateTime>();

            User user = new User();

            for (int i = user.HoursOfWork[0]; i < user.HoursOfWork[1]; i++)
            {
                DateTime hour = new DateTime(date.Year, date.Month, date.Day, i, 0, 0);
                int exist;

                using (var con = new SQLiteConnection(cs))
                using (var comm = new SQLiteCommand("SELECT EXISTS(SELECT 1 FROM Schedule WHERE date = @date)", con))
                {
                    comm.Parameters.AddWithValue("@date", hour.ToString());
                    con.Open();

                    exist = comm.ExecuteNonQuery();
                }

                if(exist != 1) //The object doesn't exists in the DB
                {
                    freeHours.Add(hour);
                }
            }
sqlite .net c#
1 Answers

ExecuteNonQuery should not be used when you expect some data to return. Being it a full set of rows or just a single scalar value.

ExecuteNonQuery will always return -1 when used with a SELECT statement. At least this is what Microsoft says for its implementation, not sure if this is the same with SQLite.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

In your context, when you expect a single SCALAR value then you use ExecuteScalar

 exist = (int)comm.ExecuteScalar();

The cast is necessary because ExecuteScalar returns an object

user_11975180
Related