Merge Two SQLite Databases In Windows Runtime Apps

Here are the steps, 

Step 1

Firstly, Create a Blank Windows Project.



Step 2

Install ‘sqlite-net’ from the Package manager and also Add reference for ‘SQLite for Windows Runtime’.

In the MainPage.xaml, we add the following buttons with click events-
  • Button to create a first database

  • Button to create a second database

  • Button to merge two databases

Complete XAML code

  1. <Page  
  2. x:Class="SQLiteMerge.MainPage"  
  3.     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"  
  4.     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  
  5.     xmlns:local="using:SQLiteMerge"  
  6.     xmlns:d="http://schemas.microsoft.com/expression/blend/2008"  
  7.     xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"  
  8. mc:Ignorable="d">  
  9.     <Grid Background="#FF5A7FD6">  
  10.         <Button x:Name="btnCreateFirstDb" Content="Create First Database" HorizontalAlignment="Left" Margin="154,97,0,0" VerticalAlignment="Top" Width="440" Height="77" Click="btnCreateFirstDb_Click"/>  
  11.         <Button x:Name="btnCreateSecondDb" Content="Create Second Database" HorizontalAlignment="Left" Margin="148,206,0,0" VerticalAlignment="Top" Width="446" Height="77" Click="btnCreateSecondDb_Click"/>  
  12.         <Button x:Name="btnMerge" Content="Merge Two Database" HorizontalAlignment="Left" Margin="154,316,0,0" VerticalAlignment="Top" Width="446" Height="77" Click="btnMerge_Click"/>  
  13.     </Grid>  
  14. </Page> 
Step 3

In the code behind MainPage.xaml.cs, for each first two buttons we add the code for creating two Databases and a third button to merge values from second database to the first one.

Complete Code snippet
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.IO;  
  4. using System.Linq;  
  5. using System.Runtime.InteropServices.WindowsRuntime;  
  6. using Windows.Foundation;  
  7. using Windows.Foundation.Collections;  
  8. using Windows.UI.Xaml;  
  9. using Windows.UI.Xaml.Controls;  
  10. using Windows.UI.Xaml.Controls.Primitives;  
  11. using Windows.UI.Xaml.Data;  
  12. using Windows.UI.Xaml.Input;  
  13. using Windows.UI.Xaml.Media;  
  14. using Windows.UI.Xaml.Navigation;  
  15. using SQLite;  
  16. using Windows.Storage;  
  17. using System.Diagnostics;  
  18.   
  19. namespace SQLiteMerge  
  20. {  
  21.   
  22.     public sealed partial class MainPage : Page  
  23.     {  
  24.         private SQLiteAsyncConnection dbCon;  
  25.   
  26.         public MainPage()  
  27.         {  
  28.             this.InitializeComponent();  
  29.   
  30.         }  
  31.   
  32.         [Table("Students")]  
  33.         public sealed class Student  
  34.         {  
  35.             public string Name { getset; }  
  36.             public int RollNo { getset; }  
  37.             public string Faculty { getset; }  
  38.         }  
  39.   
  40.         //Creates First Database    
  41.         private async void btnCreateFirstDb_Click(object sender, RoutedEventArgs e)  
  42.         {  
  43.             try  
  44.             {  
  45.                 dbCon = new SQLiteAsyncConnection(ApplicationData.Current.LocalFolder.Path + "\\FirstDatabase.sqlite");  
  46.                 await dbCon.CreateTableAsync<Student>();  
  47.   
  48.                 var student = new Student  
  49.                 {  
  50.                     Name = "Ram",  
  51.                     RollNo = 1,  
  52.                     Faculty = "Physics"  
  53.                 };  
  54.                 await dbCon.InsertAsync(student);  
  55.             }  
  56.             catch (Exception ex)  
  57.             {  
  58.                 Debug.WriteLine(ex.ToString());  
  59.             }  
  60.         }  
  61.   
  62.         //Creates Second Database    
  63.         private async void btnCreateSecondDb_Click(object sender, RoutedEventArgs e)  
  64.         {  
  65.             try  
  66.             {  
  67.                 SQLiteAsyncConnection dbCon = new SQLiteAsyncConnection(ApplicationData.Current.LocalFolder.Path + "\\SecondDatabase.sqlite");  
  68.                 await dbCon.CreateTableAsync<Student>();  
  69.   
  70.                 var student = new Student  
  71.                 {  
  72.                     Name = "Shyam",  
  73.                     RollNo = 2,  
  74.                     Faculty = "Biology"  
  75.                 };  
  76.                 await dbCon.InsertAsync(student);  
  77.             }  
  78.             catch (Exception ex)  
  79.             {  
  80.                 Debug.WriteLine(ex.ToString());  
  81.             }  
  82.         }  
  83.   
  84.         //Merging two databases. Inserts values from Second database to First Database    
  85.         private async void btnMerge_Click(object sender, RoutedEventArgs e)  
  86.         {  
  87.             try  
  88.             {  
  89.                 string firstDbPath = ApplicationData.Current.LocalFolder.Path + "\\FirstDatabase.sqlite";  
  90.                 string secondDbPath = ApplicationData.Current.LocalFolder.Path + "\\SecondDatabase.sqlite";  
  91.                 await dbCon.ExecuteAsync("ATTACH DATABASE '" + firstDbPath + "' AS firstDB;");  
  92.                 await dbCon.ExecuteAsync("ATTACH DATABASE '" + secondDbPath + "' AS secondDB");  
  93.   
  94.                 string query = "INSERT OR REPLACE INTO firstDB.Students ("  
  95.                 + "Name, RollNo, Faculty) "  
  96.                 + "SELECT Name, RollNo, Faculty "  
  97.                 + "FROM secondDB.Students";  
  98.                 await dbCon.ExecuteAsync(query);  
  99.             }  
  100.             catch (Exception ex)  
  101.             {  
  102.                 Debug.WriteLine(ex.ToString());  
  103.             }  
  104.         }  
  105.     }  

Step 4

Run the application.

Click on Create First Database button. You will see the first SQLite database created in local folder. (This PC > C > Users > [Your User Name] > AppData > Local > Packages > [App package name] > LocalState)




Click on Create Second Database button. You will see the second SQLite database created in local folder. (This PC > C > Users > [Your User Name] > AppData > Local > Packages > [App package name] > LocalState)



Finally Click on Merge two databases button, you will see the values from second database merged into first database.



You can get the complete project from GitHub https://github.com/olikishor/SQLiteMerge