C# Generating new id from database on windows forms application

Question

I have to make automatic generate new AccountID on my load windows form app.

So for example when users start windows form "Add new Account" in textbox for "Account id" I have to show latest value from database. If i have two accounts in database on windows form in textbox value will be three.

My code perfectly work if i have at least one account in database, but when my database is empty i got exception.

This is my code:

public int GetLatestAccountID() 
{
    try
    {
        command.CommandText = "select Max(AccountID)as maxID from Account";
        command.CommandType = CommandType.Text;

        connection.Open();

        OleDbDataReader reader= command.ExecuteReader();

        if (reader.Read())
        {
            int valueID = Convert.ToInt32(reader["maxID"]);
            return valueID + 1;
        }

        return 1;
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        if (connection!= null)
        {
            connection.Close();
        }
    }
}

Also I find answer on stackoverflow:

object aa = DBNull.Value;
int valueID = (aa as int?).GetValueOrDefault(); 

But this line of code works if my database is empty, but when I have one account in the database, it will always show on my windows form in account id textbox value one. I use Microsoft Access 2007 database.

I appreciate any help.

Answer 1

I'm guessing you want:

public int GetLatestAccountID(string connectionString) 
{
    using(var dbConn = new OleDbConnection(connectionString))
    {
        dbConn.Open();

        string query = "select Max(AccountID) from Account";
        using(var dbCommand = new OleDbCommand(query, dbConn))
        {
            var value = dbCommand.ExecuteScalar();
            if ((value != null) && (value != DBNull.Value))
                return Convert.ToInt32(value) + 1;

            return 1;
        }
    }
}

It looks like you're opening your database connection once and leaving it open during your entire program. Don't do that; that leads to race conditions and data corruption. .NET implements database connection pooling so you're not improving performance at all by leaving connections open.

You're also not telling us what you're using GetLatestAccountID for. If you're trying to use that as a primary key you are also going to run into problems with race conditions. If you want a primary key you should let the database create it and return the value after you've created the record.