SQLite数据库中获取新插入数据的自增长ID

SQLite数据库中有一有列名为ID的自增列，项目需求要在向数据库在插入新数据的同时返回新插入数据行的ID。

我这里用事务，把插入和查询语句通过ExecuteReader一起提交，返回DbDataReader。

实现代码

public bool Insert(string topic, string key, string value, out int id)

{

DbProviderFactory factory = SQLiteFactory.Instance;

using (DbConnection conn = factory.CreateConnection())

{

conn.ConnectionString = _connectionString;

conn.Open();

DbCommand cmdInsert = conn.CreateCommand();

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;

}