There is an add-on column named ID in the SQLite database, and the project needs to return the ID of the newly inserted data row while inserting new data into the database.
I use transactions here to commit the INSERT and query statements together through ExecuteReader.
Implementation code
Public BOOLInsert (stringTopicstringKeystringValue out intID) {DbProviderFactory Factory=sqlitefactory.instance; using(DbConnection conn =Factory. CreateConnection ()) {Conn. ConnectionString=_connectionstring; Conn. Open (); DbCommand Cmdinsert=Conn. CreateCommand (); CMDINSERT.PARAMETERS.ADD (Cmdinsert.createparameter ()); CMDINSERT.PARAMETERS.ADD (Cmdinsert.createparameter ()); CMDINSERT.PARAMETERS.ADD (Cmdinsert.createparameter ()); Dbtransaction Trans=Conn. BeginTransaction (); Try{Cmdinsert.commandtext="INSERT into [{0}] ([Topic],[key],[value]) VALUES (?,?,?); SELECT Last_insert_rowid () from [{0}]"; Cmdinsert.commandtext=string. Format (Cmdinsert.commandtext, _messagetablename); cmdinsert.parameters[0]. Value =topic; cmdinsert.parameters[1]. Value =key; cmdinsert.parameters[2]. Value =value; DbDataReader Reader=Cmdinsert.executereader (); Trans.commit (); if(reader. Read ()) {ID=int. Parse (reader[0]. ToString ()); Reader. Close (); return true; } Else{SAEC_Log4net.Log.Error ("insert message to DB fail"); ID=0; return false; } } Catch(Exception e) {trans. Rollback (); SAEC_Log4net.Log.Error (E.tostring ()); ID=0; return false; } }}
Get the self-growth ID of the newly inserted data in the SQLite database