CODEWITHSUNDEEP
c#ms-accessexceptiontry-catchnullreferenceexception
Keven M
Keven M

Reputation: 992

Check for the existence of a record in Access database using C#

First, I did search for this first and found this question answered: Previous Answer

The problem I have with this answer is that using this method causes a NullReferenceException. Obviously the code will still work with a try/catch block, but I had always understood that intentionally using an Exception as a means of controlling flow was poor design. Is there a better method of doing this?

To be clear as to the parameters, I am using OleDbConnection and OleDbCommand to read/write an Access 2010 database (.accdb format).

Below is the code I have currently using the above answer's approach:

public bool ReadAccessDb(string filePath, string queryString, bool hasRecords)
    {
        string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=" + filePath + ";" +
            @"User Id=;Password=;";

        using (OleDbConnection connection = new OleDbConnection(connectionString))
        using (OleDbCommand command = new OleDbCommand(queryString, connection))
        {
            try
            {
                connection.Open();

                int count = (int)command.ExecuteScalar();

                //This works, but if no records exist it uses the Exception system to halt further action.  Look for better approach.
                if(count > 0)
                {
                    hasRecords = true;
                }
            }
            catch (System.Exception ex)
            {
            }
        }

        return hasRecords;
    }

Upvotes: 3

Views: 2158

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123594

I'm posting this answer because the question is a bit ambiguous and the (currently) accepted answer can conceivably be "fooled" if a row exists but the first column returned by the SELECT statement happens to contain a NULL. Consider the test table

CREATE TABLE MyTable (ID COUNTER PRIMARY KEY, NullableColumn INTEGER NULL)

INSERT INTO MyTable (NullableColumn) VALUES (NULL)

which would look like

ID  NullableColumn
--  --------------
 1          <NULL>

If the SELECT statement used for testing was

string sql = "SELECT NullableColumn FROM MyTable WHERE ID=1";

then the method

static bool RecordExists(string queryString, OleDbConnection conn)
{
    bool rtn;
    using (var command = new OleDbCommand(queryString, conn))
    {
        object obj = command.ExecuteScalar();
        int count = obj is DBNull ? 0 : Convert.ToInt32(obj);
        rtn = (count != 0);
    }
    return rtn;
}

would return False, whereas this method

static bool RecordExists(string queryString, OleDbConnection conn)
{
    bool rtn;
    string rowCountString = String.Format("SELECT COUNT(*) AS n FROM ({0})", queryString);
    using (var command = new OleDbCommand(rowCountString, conn))
    {
        int count = (int)command.ExecuteScalar();
        rtn = (count != 0);

    }
    return rtn;
}

would return True.

Upvotes: 0

Abdellah OUMGHAR
Abdellah OUMGHAR

Reputation: 3745

You can use :

int count = command.ExecuteScalar() is DBNull ? 0 : Convert.ToInt32(command.ExecuteScalar());

or use :

object obj = command.ExecuteScalar();
int count = obj is DBNull ? 0 : Convert.ToInt32(obj);

Upvotes: 2

Related Questions