forum

Home / DeveloperSection / Forums / Sqlite Database Backup

Sqlite Database Backup

Ajay Kembale 2116 12-Jan-2016

I want to take backup of sqlite database in windows application(Visual Studio 2012).I am able to do it using Sqlite in mozila firefox addon manually.

But I want to implement it on a button click in windows form.I was trying following code which errormessage 'BackupDatabase() does not exists in System.Data.SQLite.SQLiteConnection

I was looking for query in Sqlite but I couldnt find anything

public void takebackup()
        {
            da = new SQLiteDataAdapter();
            //con.Open();
            try
            {
                SQLiteConnection cnnIn = new SQLiteConnection('Data Source=test.db;foreign keys=True');
                SQLiteConnection cnnOut = new SQLiteConnection('Data Source=backup.db;foreign keys=True');
                cnnIn.Open();
                cnnOut.Open();
                cnnIn.BackupDatabase(cnnOut, 'main', 'main', -1, null, -1);
                cnnIn.Close();
                cnnOut.Close();
            }
            catch (Exception er)
            {
            }
            finally
            {
                //con.Close();
            }
        }

c# winforms sqlite3

Updated on 13-Jan-2016

Ajay Kembale

Can you answer this question?

Answer

1 Answers

Anupam Mishra

13-Jan-2016

I have a one solution of this problem:

If you are storing database as a physical drive then you must be given a full path name.

In the following, we have taking the same problem to implement on button click event in windows form. Here we have taken for source database is 'MyDatabase.db'(it's storing on local drive i.e. d ) and for backup we created 'backup.db'(It's also on local drive).Insertion operation is performed in 'mydatabase.db' database. Now, for checking backup is successfully or not ? so, we have taken a datagridview for displayng data in the 'backup.db' database .

using System;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;
namespace SQLiteDemo
{
    public partial class Form1 : Form
    {
        private SQLiteConnection sql_con1 = new SQLiteConnection('Data
           Source=d:/MyDatabase.db;foreign keys=True');
        private SQLiteCommand sql_cmd;
        private SQLiteDataAdapter da;
        private DataSet DS = new DataSet();
        private DataTable DT = new DataTable();
 
        public Form1()
        {
            InitializeComponent();
            sql_con1.Open(); // creating table 
            string sql = ' create table IF NOT EXISTS Student(name varchar(20), score int)';
            SQLiteCommand command = new SQLiteCommand(sql, sql_con1);
            command.ExecuteNonQuery();
        }
        public void takebackup()
        {
            da = new SQLiteDataAdapter();
 
            try
            {
                SQLiteConnection cnnIn = new SQLiteConnection('Data Source=d:/MyDatabase.db;foreign                        keys=True');
                SQLiteConnection cnnOut = new SQLiteConnection('Data Source=d:/backup.db;foreign                          keys=True');
                cnnIn.Open();
                cnnOut.Open();
                cnnIn.BackupDatabase(cnnOut, 'main', 'main', -1, null, -1);
                MessageBox.Show('Succesfully backup');//displaying message if backup is successfully                cnnIn.Close();
                cnnOut.Close();
            }
            catch (Exception er)
            {
                Console.WriteLine(er);
            }
            finally
            {
                sql_con1.Close();
            }
        }
        public void InsertData()
        {      // for insuring backup is succcessfully or not
            SQLiteConnection sql_con = new SQLiteConnection('DataSource=d:/backup.db;foreign                         keys=True');
            try
            {
                sql_con.Open();
                sql_con1.Open();// Open a connection of MyDatabase for inserting data
                string sql1 = 'insert into Student(name, score) values ('Anupam Mishra', 100)';
                SQLiteCommand command1 = new SQLiteCommand(sql1, sql_con1);
                command1.ExecuteNonQuery();
                sql_cmd = sql_con1.CreateCommand();
                string CommandText = 'select * from  Student';
                da = new SQLiteDataAdapter(CommandText, sql_con);
                da.Fill(DT);
                dataGridView1.DataSource = DT;
               
            }
            catch (Exception ex)
            {
                MessageBox.Show('Exception is:'+ex);
            }
            finally
            {
                sql_con.Close();
                sql_con1.Close();
            }
        }

         // Calling on View button click
        private void button1_Click(object sender, EventArgs e)
        {
            takebackup();
            InsertData();
 
        }

           // calling on Close button click
         private void button2_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

Output:

forum

Sqlite Database Backup

Ajay Kembale

1 Answers

Liked By