How to update existing sqlite database without losing data in Windows Phone 8?

Question

I am currently working with SQLite Database in Windows Phone 8. I have a table with some columns and data in it on app which is live. Now with next update, I want to add a new column in that table without losing data inserted previously.

Can someone suggest how can I achieve this requirement?

Edit

This is my class in which I want to add a new column.

public class Info
{
   [PrimaryKey]
   public int id{get;set;}
   public string name{get;set;}
   public string information{get;set;}
   public string nick_name{get;set;}
}

The column dataType is string and its as below

public string disp_id{get;set;}

Answer 1

• Rename old table with some temporary name
• Create new table with desired name and new column definition
• Copy data from old table to new table and adding the new column data
• Drop old table

Adding your table definition and new column type might help us provide some SQL for the job, too.

The application will stop, anyway, while it's getting updated.

EDIT:

Since you've not posted the SQL needed for a precise response, I "imagined" how it would look like so I can complete this answer. Given that your table would look like this:

CREATE TABLE "info" (
"id"  INTEGER NOT NULL,
"name"  TEXT,
"information"  TEXT,
"nick_name"  TEXT,
PRIMARY KEY ("id" ASC)
);

Adding the column would resume to this:

PRAGMA foreign_keys = OFF;

alter table "info" rename to "info_temp";

CREATE TABLE "info" (
"id"  INTEGER NOT NULL,
"name"  TEXT,
"information"  TEXT,
"nick_name"  TEXT,
"disp_id"  TEXT,
PRIMARY KEY ("id" ASC)
);

insert into "info" ("id", "name", "information", "nick_name") select "id", "name", "information", "nick_name" from "info_temp";

DROP TABLE "main"."info_temp";

What this does:

1. Disables foreign key integrity checks for the current transaction so that the modifications don't fail because of integrity checks;
2. Renames info table to info_temp;
3. Creates a new info table with the updated schema;
4. Copies the data from the original info table to the new one;
5. Deletes the old info table (info_temp);