How do I use the sqlite max() function from C#?

Question

0.00/5 (No votes)

See more:

I am using an SQLite database from a C#/WPF application. I have a table that has a column that is declared as INTEGER UNIQUE.

I execute the SQL script below from my usual ExecuteReader() code and I get one single result, which is what I would expect. The problem is that the result is always zero. Does anyone know what I may be doing wrong?

SQL

SELECT max( enquiry_job_number ) FROM EnquiryJobs;

What I have tried:

Looking in the debugger, it appears that the column does not exist. Even if I add an AS clause to the SQL (shown below), the named column SQLiteDataReader.GetOrdinal( columnName ) returns -1.

SQL

SELECT max( enquiry_job_number ) FROM EnquiryJobs AS "ABC";

Posted 29-Nov-16 5:25am

Patrick Skelton

Updated 29-Nov-16 6:03am

v2

Add a Solution

Comments

ZurdoDev 29-Nov-16 11:36am

ExecuteScalar() would likely be a better option. It returns the first column of the first row. Otherwise, post the code for ExecuteReader and the code around it to see what you might be doing wrong.

Patrick Skelton 29-Nov-16 11:46am

Outstanding! That works perfectly. That might have occurred to me before Christmas if I were lucky. Thank you.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-11-29T05:54:00

In addition to RyanDev's excellent suggestion, it's also worth noting that if you use this SQL:

SQL

SELECT max( enquiry_job_number ) FROM EnquiryJobs;

You cannot access the data from a DataReader like this:

C#

SQLiteDataReader.GetOrdinal( columnName )

Becaus eyou haven;'t given the data column a name at all.
In future, try naming the column when you return it:

SQL

SELECT max( enquiry_job_number ) AS MaxJobNo FROM EnquiryJobs;

And you can access the column by name:

C#

int max = (int) myDataReader["MaxJobNo"];

ZurdoDev · Accepted Answer · 2016-11-29T06:03:00

Solution 2

As mentioned in comments use ExecuteScalar instead since it returns the first column of the first row.

And to use DataReader see Solution 1.

Posted 29-Nov-16 6:03am

ZurdoDev

Updated 29-Nov-16 6:04am

v2

Comments

OriginalGriff 29-Nov-16 12:30pm

I'm glad you posted that as a solution - it gave me a chance to upvote it!
:thumbsup: !

Patrick Skelton 29-Nov-16 12:32pm

One problem I am having with ExecuteScalar() is that when it is first run on an empty database, I am not getting null. I get an object that simply shows an empty set of brackets in the debugger. I'm not sure what this is or, more importantly, what if() statement I can write to catch this first run.

ZurdoDev 29-Nov-16 12:38pm

Object temp = cmd.ExecuteScalar();
if (temp != null){
String jobNo = temp.ToString();
}

Patrick Skelton 29-Nov-16 13:38pm

I'm afraid that doesn't appear to work. I test against null, get false, but then get an exception when I try to cast the result to an int. Do I need to test for DbNull or something? (As you've no doubt gathered, I'm no SQL expert.)

ZurdoDev 29-Nov-16 13:52pm

What is the result of temp.ToString()?

Patrick Skelton 30-Nov-16 4:16am

Using the following code:

if( result != null )
{
string s = (string)result;
int x = 10;
}

...I can't get as far as a breakpoint on the assignment to x because the cast to a string causes an exception.

Patrick Skelton 30-Nov-16 4:20am

Ah... the following works:

if( result != DBNull.Value )
{
...
}
Thank you for your help.

ZurdoDev 30-Nov-16 6:36am

What I posted should have worked. I guess I'd need to see the code that assigns to result.

But glad it works.

Patrick Skelton 30-Nov-16 7:42am

The code is actually split amongst a few helper functions, but pulling it together and stripping out error checking, it is this:

SQLiteDataStore.Connection.Open();
string sqlScript = "SELECT max( enquiry_job_number ) FROM EnquiryJobs;";
SQLiteCommand sqlCommand = new SQLiteCommand( sqlScript, SQLiteDataStore.Connection );
object result = sqlCommand.ExecuteScalar();
if( result != null && result != DBNull.Value )
{
// At least one record in DB.
}
else
{
// No records in DB.
}

ZurdoDev 30-Nov-16 8:00am

Hmm. Must be something to do with SqlLite because I only check against null and that always works. If something does come back as DBNull.Value calling ToString() give an empty string.

Anyway, it works, which is what is important.

Patrick Skelton 1-Dec-16 11:33am

Unfortunately, I spoke too soon. I now have the belt-and-braces approach shown below. When control gets past my over-the-top if() statement, the debugger shows a valid positive integer; however, when I perform the cast I get an invalid cast exception. Anyone have any ideas what could be going on? Think I might have to try the ExecuteNonQuery and named column approach instead. I just don't appear to be able to do anything reliable with the result from ExecuteScalar.

SQLiteDataStore.Connection.Open();
string sqlScript = "SELECT max( enquiry_job_number ) FROM EnquiryJobs;";
SQLiteCommand sqlCommand = new SQLiteCommand( sqlScript, SQLiteDataStore.Connection );
object result = sqlCommand.ExecuteScalar();
if( result != null && result.ToString() != String.Empty && result != DBNull.Value )
{
// At least one record in DB; debugger shows positive int but the following cast gives an exception. :(
int resultAsInteger = (int)result;
}
else
{
// No records in DB.
}

ZurdoDev 1-Dec-16 11:36am

What value does result show in the watch window?

Patrick Skelton 1-Dec-16 11:37am

It shows a valid positive integer.

ZurdoDev 1-Dec-16 11:40am

What exactly?

Patrick Skelton 1-Dec-16 11:48am

It shows (with each line nested inside the one above):
result {} object {System.DBNull}
Static members
Value {} System.DBNull

Actually, now you have got me to write it out, I've suddenly realised how the hell is it getting past my if() statement if the value is DBNull? It implies I have two different values for DBNull. Is it okay to test for DBNull with the standard '==' operator?

Patrick Skelton 1-Dec-16 11:56am

Oh, hang on. I've mixed up two use cases. When the value being returned is a valid number, its type is 'long', which explains the invalid cast. Doh! My stupid mistake. Thank you for letting me bounce this off you. I'd have spent a lot longer getting to the result otherwise.

ZurdoDev 1-Dec-16 13:07pm

Glad to hear it.