Sqlite automatically deleted old table when creating new table

Question

i am using sqlite version 3. i created a new table using a console application and then populated the data in that table using a different application (mvc). then i decided to create a new table and uncommented the first bit of code and added new structure to create another table but to my horror, first table created is completely gone along with the data. is that expected or i am doing something wrong? if i keep the code to create the first table, then it deletes all the data from it.

static void Main(string[] args)
    {
        SQLiteConnection.CreateFile(@"C:\Users\DV\Documents\Visual Studio 2015\Projects\Study\Leo\Leo\App_Data\LeoDatabase.sqlite");
        using (SQLiteConnection connection = new SQLiteConnection(@"Data Source=C:\Users\DV\Documents\Visual Studio 2015\Projects\Study\Leo\Leo\App_Data\LeoDatabase.sqlite;Version=3"))
        {


            connection.Open();
            //string sql = "create table tblPersonalDetails (ID INTEGER PRIMARY KEY AUTOINCREMENT, FirstName varchar(100), LastName varchar(100), DateofBirth datetime, Title varchar(10), Address varchar(200), Suburb varchar(200), HomePhone varchar(20), Mobile varchar(20), Email varchar(100),  EmergencyName varchar(100), EmergencyPhone varchar(20), EmergencyRelation varchar(100), EmergencyEmail varchar(100), DrivingLicenceNo varchar(50), DrivingLicenceExpiryDate datetime, ScannedImageLocation varchar(300))";
            //SQLiteCommand command= new SQLiteCommand(sql, connection);
            //command.ExecuteNonQuery();

            string sql = "create table tblAppointment(ID INTEGER PRIMARY KEY AUTOINCREMENT, InstructorName varchar(100), LessonDate date, LessonTime time, Address varchar(300), UserId INTEGER)";
            SQLiteCommand command = new SQLiteCommand(sql, connection);
            command.ExecuteNonQuery();



        }

score 1 · Accepted Answer · answered Aug 13 '16 at 14:59

1

You are using SQLiteConnection.CreateFile every time. Please either comment this for second table creation or add some check like (!File.Exists(pathToDatabase))

answered Aug 13 '16 at 14:59

Ramit

416
3
8

in the sqlite documentation, it says if the file already exists it will ignore this? http://stackoverflow.com/questions/24178930/programmatically-create-sqlite-db-if-it-doesnt-exist – Baahubali Aug 13 '16 at 15:30
Please share the link if any. In my knowledge it will override old file. For testing purpose you can try it. – Ramit Aug 13 '16 at 15:34
its in my comment above – Baahubali Aug 13 '16 at 15:35
The example you shared have a check if (!System.IO.File.Exists("C:\\Users\\abc\\Desktop\\1\\synccc.sqlite")) Please add similar check and your code will run fine. – Ramit Aug 13 '16 at 15:38

Sqlite automatically deleted old table when creating new table

1 Answers1