Search:

Custom Search
_________________________________________________________________

Saturday, April 18, 2009

Get integer sequence primary key in C#

Here is a nice method of getting the primary key from a table. Basicly what I do here is obtanning the first available key to insert new rows with this obtained key.

First we set the instance of the connection with the database and then we create a sql command. After this, we open the connection and execute the command directly to the database.
If everything executes correctly, we will obtain the first available key. If not, an exception is going to be raised.

public int GetOid()

{

OleDbConnection conn = ConnectionMgr.GetInstance().GetConnection();

OleDbCommand cmd = new OleDbCommand("Select * From Oid", conn);

int oidNum = 0;

try

{

conn.Open();

oidNum = (int)cmd.ExecuteScalar();

oidNum++;

cmd = new OleDbCommand("UPDATE Oid SET lastOid=" + oidNum, conn);

cmd.ExecuteNonQuery();

}

catch (OleDbException ex)

{

throw new DatabaseException("Error updating oid", ex);

}

finally

{

conn.Close();

}

return oidNum;

}