How do I retrieve and set user_version in SQLite DB with EF

Question

What I am trying to do is load in and set the user_version (PRAGMA user_version) from my SQLite database. I am using entity framework, but if I can't do it through that, then I need to be able to do it some other way possible within C# 4.0.

I tried:

this.DatabaseConnection.Connection.Open();
System.Data.Common.DbCommand cmd = this.DatabaseConnection.Connection.CreateCommand();
cmd.CommandText = "PRAGMA user_version";
cmd.CommandType = System.Data.CommandType.Text;
System.Data.Common.DbDataReader reader = cmd.ExecuteReader();

where DatabaseConnection is my EF context and Connection is the DbConnection object within the context, but I get an exception:

The query syntax is not valid. Near identifier 'user_version', line 1, column 8.

Remillard

Answer 1

Well...I eventually figured it out. I believe this is the best way to do it while using entity framework.

To set the user_version you need to do this...

this.DatabaseConnection.ExecuteStoreCommand("PRAGMA user_version = 5");

To get the user_version you need to do this...

long result = this.DatabaseConnection.ExecuteStoreQuery<long>("PRAGMA user_version").FirstOrDefault();

Remillard

It's model.Database.SqlQuery<long>("...") in later versions of EF. – Excerpta

Answer 2

I know the question is old but this worked for me:

enter code here
            string connString = "Data Source = Data.sqlite;Version=3";
            SQLiteConnection conn = new SQLiteConnection(connString);
            conn.Open();
            SQLiteCommand command = new SQLiteCommand("PRAGMA user_version", Baglanti1);
            long result = (long)command.ExecuteScalar();
            conn.Close();

Disembark

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community? – Slate