When working with SQL databases it can often be useful to keep a record of the ID of the row in a table from which a piece of data was read. For example, if you have a Customers table in a database then if you populate a list of Customer objects in your application from the rows in the table, storing the row ID for each object lets you easily update the correct row if you edit the values in one of the Customer objects.

In SQLite, if you have a field of type INTEGER PRIMARY KEY in a table, the database engine will automatically fill the field with the ID for the row. When you insert a new row in the table, e.g. add a new customer into the Customers table taking the example above, then you will need to find out the ID of the new row so you can write it back into the Customer object you’ve just added.

SQLite has a special SQL function – last_insert_rowid() – that returns the ID of the last row inserted into the database so getting the ID of a new row after performing a SQL insert just involves executing the last_insert_rowid() command.

Here is an example in C# showing how to get the ID of the last row inserted into a table in a database.

Create the SQLite database and open a connection to it:

 String mPathName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "testdba.db3");

 SQLiteConnection.CreateFile(mPathName);

 // Open a connection to the database.
 SQLiteConnection Conn = new SQLiteConnection("Data Source = " + mPathName);

 Conn.Open();

Create a table (Customers) in the SQLite database:

 // Create a table.
 //
 String SQL = "CREATE TABLE Customers (ID INTEGER PRIMARY KEY, UNIQUEID VARCHAR(30), ADDRESS VARCHAR(100))";

 SQLiteCommand Command = new SQLiteCommand(SQL, Conn);

 // Create the table.
 //
 Command.ExecuteNonQuery();

Insert a row of data into the table:

 // Reuse the command object and insert a row into the table.
 //
 Command.CommandText = "INSERT INTO Customers (UNIQUEID, ADDRESS) VALUES (@UNIQUEID, @ADDRESS)";

 Command.CommandType = System.Data.CommandType.Text;

 Command.Parameters.Add(new SQLiteParameter("@UNIQUEID", "Fred Bloggs"));
 Command.Parameters.Add(new SQLiteParameter("@ADDRESS", "Acacia Avenue"));

 int Status = Command.ExecuteNonQuery();

Now get the ID of the last row inserted:

 Command.CommandText = "select last_insert_rowid()";

 // The row ID is a 64-bit value - cast the Command result to an Int64.
 //
 Int64 LastRowID64 = (Int64)Command.ExecuteScalar();

 // Then grab the bottom 32-bits as the unique ID of the row.
 //
 int LastRowID = (int)LastRowID64;

Note that in SQLite the row ID is a 64-bit integer but for all practical database sizes you can cast the 64 bit value to a 32-bit integer.

For more SQLite database tips check out:

http://www.sliqtools.co.uk/blog/net/sqlite-how-to-determine-if-a-columns-exists-in-a-table/