Local Database SQLite for Windows 10
In this article we are going to learn how to create a local DB for a Windows 10 app and perform CRUD (Create Read Update and Delete) operations in a Windows 10 database.
See the step by step implementation.
Introduction
SQLite is a lightweight database used for mobile local storage.
Create New UWP project.
Setup SQLite environment.
Install SQLite-UAP extensions form NuGet Package Manager as shown like the following screen.
http://csharpcorner.mindcrackerinc.netdna-cdn.com/UploadFile/2b876a/local-data-base-sqlite-for-windows-10/Images/SQLite-UAP.jpg
Next Install SQLite.Net-PCL extension from NuGet Package
http://csharpcorner.mindcrackerinc.netdna-cdn.com/UploadFile/2b876a/local-data-base-sqlite-for-windows-10/Images/Install%20SQLite.jpg
Now, we are going to the following areas:
- How to perform SQLite CRUD operations.
- How to bind SQLite data to a ListBox
Design the UI like below.
http://csharpcorner.mindcrackerinc.netdna-cdn.com/UploadFile/2b876a/local-data-base-sqlite-for-windows-10/Images/Design.jpg
XAML code:
<Grid Background="#FFF589E2">
<Grid.ColumnDefinitions>
<ColumnDefinition></ColumnDefinition>
</Grid.ColumnDefinitions>
<Grid.RowDefinitions>
<RowDefinition Height="Auto"></RowDefinition>
<RowDefinition Height="Auto"></RowDefinition>
<RowDefinition Height="Auto"></RowDefinition>
<RowDefinition Height="Auto"></RowDefinition>
<RowDefinition Height="*"></RowDefinition>
</Grid.RowDefinitions>
<Button x:Name="CreateDBbutton" Grid.Row="0" Content="Create Local Database" HorizontalAlignment="Center" VerticalAlignment="Top" Click="button_Click"/>
<Button x:Name="create" Grid.Row="1" Content="Create New Students" HorizontalAlignment="Center" Click="create_Click"></Button>
<Button x:Name="read" Grid.Row="2" Content="Read Students List" Width="300" Click="read_Click" HorizontalAlignment="Center"></Button>
<Button x:Name="update" Grid.Row="3" Content="Update Details" Width="300" Click="update_Click" HorizontalAlignment="Stretch"></Button>
<ListView x:Name="allstudents" HorizontalAlignment="Stretch" Grid.Row="4" >
<ListView.ItemTemplate>
<DataTemplate>
<TextBlock x:Name="ee" Text="{Binding Name}" FontSize="14"></TextBlock>
</DataTemplate>
</ListView.ItemTemplate>
</ListView>
</Grid>
Now write the below code in your corresponding button click events.
This code is going to create one Student DB with Students Table with ID, Name, Address and Mobile. First design the table like below:
public class Students
{
[SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public string Mobile { get; set; }
public Students()
{
}
public Students(string name, string address, string mobile)
{
Name = name;
Address = address;
Mobile = mobile;
}
}
Create DB
public static void CreateDatabase()
{
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
conn.CreateTable<Students>();
}
}
Insert New Student details
public void Insert(Students objContact)
{
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
conn.RunInTransaction(() =>
{
conn.Insert(objContact);
});
}
}
Retrieve the specific contact from the database.
// Retrieve the specific contact from the database.
public Students ReadContact(int contactid)
{
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
var existingconact = conn.Query<Students>("select * from Students where Id =" + contactid).FirstOrDefault();
return existingconact;
}
}
Read All Student details
//Read All Student details
public ObservableCollection<Students> ReadAllStudents()
{
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
List<Students> myCollection = conn.Table<Students>().ToList<Students>();
ObservableCollection<Students> StudentsList = new ObservableCollection<Students>(myCollection);
return StudentsList;
}
}
Update student details
//Update student detaisl
public void UpdateDetails(string name)
{
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
var existingconact = conn.Query<Students>("select * from Students where Name =" + name).FirstOrDefault();
if (existingconact != null)
{
existingconact.Name = name;
existingconact.Address = "NewAddress";
existingconact.Mobile = "962623233";
conn.RunInTransaction(() =>
{
conn.Update(existingconact);
});
}
}
}
Delete all student or delete student table
//Delete all student or delete student table
public void DeleteAllContact()
{
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
conn.DropTable<Students>();
conn.CreateTable<Students>();
conn.Dispose();
conn.Close();
}
}
Delete specific student
//Delete specific student
public void DeleteContact(int Id)
{
var sqlpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "Studentdb.sqlite");
using (SQLite.Net.SQLiteConnection conn = new SQLite.Net.SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), sqlpath))
{
var existingconact = conn.Query<Students>("select * from Studentdb where Id =" + Id).FirstOrDefault();
if (existingconact != null)
{
conn.RunInTransaction(() =>
{
conn.Delete(existingconact);
});
}
}
}
Now run the app with different devices and you will get the output as shown below.
Here it is tested with Windows 10 Simulator.
http://csharpcorner.mindcrackerinc.netdna-cdn.com/UploadFile/2b876a/local-data-base-sqlite-for-windows-10/Images/Windows%2010%20Stimulator.jpg
