Bulk Insert Example with Two SQLite DB Files using .NET

Merging two SQLite DB using Bulk insert or update in Dot Net c#. Will have two different Database files with different tables with data. In Sqlite Database file name, consider as database name and different structure of table can be created. Here Admin and User two DB file takes it as an example and will insert the Admin Database data into User Database.

For that will read the Admin DB file and insert into DataTable. Then will insert or update the DataTable into User Database Sqlite file. So that will achieve the bulk update of merging two DB files

Two DB files Example

Reading the Admin Database file example

Code Example

 DataTable dtBulkupload= new DataTable();
using (var sqlite_conn = new SQLiteConnection("Data Source=AdminDB.sqlite;Version=3;New = True; Compress = True;"))
{
    sqlite_conn.Open();
    string sql = "select * from Users";
    using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_conn))
    {
        mycommand.CommandText = sql;
        using (SQLiteDataReader reader = mycommand.ExecuteReader())
        {
            dtBulkupload.Columns.Add("Id");
            dtBulkupload.Columns.Add("AdminName");
            dtBulkupload.Columns.Add("Age"); 

            while (reader.Read())
            {
                DataRow row = dtBulkupload.NewRow();
                row["Id"] = reader["Id"]; 
                row["AdminName"] = reader["AdminName"];
                row["Age"] = reader["Age"];

                dtBulkupload.Rows.Add(row);
            }
        }
        sqlite_conn.Close();
    }
}

Bulk upload of two DB files Merging example coding

 DataTable dtBulkupload= new DataTable();
 using (var sqlite_conn = new SQLiteConnection("Data Source=AdminDB.sqlite;Version=3;New = True; Compress = True;"))
 {
     sqlite_conn.Open();
     string sql = "select * from Admin";
     using (SQLiteCommand mycommand = new SQLiteCommand(sqlite_conn))
     {
         mycommand.CommandText = sql;
         using (SQLiteDataReader reader = mycommand.ExecuteReader())
         {
             dtBulkupload.Columns.Add("Id");
             dtBulkupload.Columns.Add("AdminName");
             dtBulkupload.Columns.Add("Age"); 

             while (reader.Read())
             {
                 DataRow row = dtBulkupload.NewRow();
                 row["Id"] = reader["Id"]; 
                 row["AdminName"] = reader["AdminName"];
                 row["Age"] = reader["Age"];

                 dtBulkupload.Rows.Add(row);
             }
         }
         sqlite_conn.Close();
     }
 }

 using (var sqlite_conn = new SQLiteConnection("Data Source=UserDB.sqlite; Version = 3;New = True; Compress = True;"))
 {
   
     //sqlite connection open
     sqlite_conn.Open();
     //  Create a table
     string createTableQuery = "CREATE TABLE IF NOT EXISTS Admin (Id INTEGER PRIMARY KEY, AdminName TEXT, Age INTEGER)";
     using (var command = new SQLiteCommand(createTableQuery, sqlite_conn))
     {
         command.ExecuteNonQuery();
     }
     using (var transaction = sqlite_conn.BeginTransaction())
     {
         //insert bulk record into sqlite DB
         using (var adapter = new SQLiteDataAdapter("SELECT * FROM Admin", sqlite_conn))
         {
             using (var query = new SQLiteCommand("INSERT OR REPLACE INTO Admin(Id, AdminName, Age)" +
                     " VALUES(@Column1, @Column2, @Column3)", sqlite_conn))
             {
                 adapter.InsertCommand = query;
                 adapter.InsertCommand.Parameters.Add("@Column1", DbType.String, 50, "Id");
                 adapter.InsertCommand.Parameters.Add("@Column2", DbType.String, 50, "AdminName");
                 adapter.InsertCommand.Parameters.Add("@Column3", DbType.String, int.MaxValue, "Age");
                 adapter.Update(dtBulkupload);
             }
         }
         transaction.Commit();
     }
     sqlite_conn.Close();
 }

Output