Getting the Last Insert ID with SQLite.NET in C#

I have a simple problem with a not so simple solution... I am currently inserting some data into a database like this:

                kompenzacijeDataSet.KompenzacijeRow kompenzacija = kompenzacijeDataSet.Kompenzacije.NewKompenzacijeRow();
                kompenzacija.Datum = DateTime.Now;
                kompenzacija.PodjetjeID = stranka.id;
                kompenzacija.Znesek = Decimal.Parse(tbZnesek.Text);

                kompenzacijeDataSet.Kompenzacije.Rows.Add(kompenzacija);

                kompenzacijeDataSetTableAdapters.KompenzacijeTableAdapter kompTA = new kompenzacijeDataSetTableAdapters.KompenzacijeTableAdapter();
                kompTA.Update(this.kompenzacijeDataSet.Kompenzacije);

                this.currentKompenzacijaID = LastInsertID(kompTA.Connection);

The last line is important. Why do I supply a connection? Well there is a SQLite function called last_insert_rowid() that you can call and get the last insert ID. Problem is it is bound to a connection and .NET seems to be reopening and closing connections for every dataset operation. I thought getting the connection from a table adapter would change things. But it doesn't.

Would anyone know how to solve this? Maybe where to get a constant connection from? Or maybe something more elegant?

Thank you.

EDIT:

This is also a problem with transactions, I would need the same connection if I would want to use transactions, so that is also a problem...

Answers


select last_insert_rowid();

And you will need to execute it as a scalar query.

string sql = @"select last_insert_rowid()";
long lastId = (long)command.ExecuteScalar(sql); // Need to type-cast since `ExecuteScalar` returns an object.

Need Your Help

MapKit doesn't show Blue Dot for Current Location

iphone mapkit core-location

I am a newbie in MapKit for iPhone and tried to implement this, for some reason I can't see the current location blue dot, anyone else had this issue????

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.