SQLite In Windows Store App

The first thing you need to do is get SQLite. Select Tools|Extensions and Updates. In the Extensions and Updates dialog, select Online. Type sqlite in the search box. You will see SQLite for Windows Runtime. This is the SQLite binaries packaged up by the SQLite team. Install this.

 

Next, create a new C# Windows Store project. Select the Blank App template and name the project SQLiteDemo. Next, add a reference to both SQLite and the VC++ Runtime Package.

After you add the references, you will notice yellow triangles in the Solution Explorer. This is never a good sign. Build the app and you will see the following errors:

 

When you create a new C# or VB Windows Store project in Visual Studio, it supports all architectures (x86, x64 and ARM) by default. But since you added SQLite to the project, you can’t build one package that targets all architectures. You have to build one target for each. Select Build|Configuration Manager and select x86, x64 or ARM from the Platform drop-down list. Now the project builds. 

Before you write code to work with SQLite, you should add a NuGet package that will make your life easier. Select Tools|Library Package Manager|Manage NuGet Packages for Solution. In the Manage NuGet Packages dialog, select Online and type sqlite-net in the search text box. The sqlite-net package enables you to work with SQLite databases using a LINQ syntax. After you install it, you will see two new files in your project: SQLite.cs and SQLiteAsync.cs.

 

NOTE: You can also install this package using the Package Manager Console. Select Tools|Library Package Manager|Package Manager Console and type install-package sqlite-net. 

Now you are ready to work with SQLite.

Add a new class. If you want to create a table with two fields, just create a class with two objects as follows. Include "using SQLite" namespace.

[Table("Users")]
    class UserInfo
    {
        [PrimaryKey, Unique]
        public int uId { get; set; }
        public string uName { get; set; }
    }

In above code, we created a Table with fields, uId as PrimaryKey and uName.

Write the following code to create database,

 try
 {
        var path = ApplicationData.Current.LocalFolder.Path + @"\users.db";
        var db = new SQLiteAsyncConnection(path);
        await db.CreateTableAsync<UserInfo>();
 }
 catch { }

In the above code, we are creating database "users" then connecting that database and creating a table asynchronously.

Design the page as shows below,

 

 XAML code for above design is,

    <Grid Background="{StaticResource ApplicationPageBackgroundThemeBrush}">
        <TextBlock HorizontalAlignment="Left" TextWrapping="Wrap" Text="Uid" VerticalAlignment="Top" Margin="130,124,0,0" Height="51" Width="114" FontSize="36"/>
        <TextBox x:Name="uidtxt" HorizontalAlignment="Left" TextWrapping="Wrap" VerticalAlignment="Top" Margin="276,130,0,0" Width="228" Height="45"/>
        <TextBlock HorizontalAlignment="Left" TextWrapping="Wrap" Text="Name" VerticalAlignment="Top" Margin="130,231,0,0" Height="54" Width="114" FontSize="36"/>
        <TextBox x:Name="unametxt" HorizontalAlignment="Left" TextWrapping="Wrap" VerticalAlignment="Top" Margin="276,231,0,0" Height="41" Width="228"/>
        <Button x:Name="submit" Content="Submit" HorizontalAlignment="Left" VerticalAlignment="Top" Margin="174,324,0,0" Height="51" Width="116"/>
        <Button x:Name="display" HorizontalAlignment="Left" VerticalAlignment="Top" Height="49" Width="113" Margin="333,326,0,0" Content="Display"/>
    </Grid>

After entering data, when I click on Submit button, data should store in a table, following is a code for that,

 var path = ApplicationData.Current.LocalFolder.Path + @"\users.db";
            var db = new SQLiteAsyncConnection(path);

            var data = new UserInfo
            {
                uId = int.Parse(uidtxt.Text),
                uName = unametxt.Text
            };

            int x = await db.InsertAsync(data);

In the above code we are reading the two TextBoxes Data into a variable called “data” and then we are inserting that data into the table.

When I click on Display button, data should display which is stored in table.

            string Result = "";
            var path = ApplicationData.Current.LocalFolder.Path + @"\users.db";
            var db = new SQLiteAsyncConnection(path);
            List<UserInfo> allUsers = await db.QueryAsync<UserInfo>("Select * From Users");
            var count = allUsers.Any() ? allUsers.Count : 0;
            foreach (var item in allUsers)
            {
                Result += "Email: " + item.uId.ToString() + "\nFirst Name: " + item.uName + "\n\n";
            }

            MessageDialog dialog = new MessageDialog(Result.ToString());
            await dialog.ShowAsync();

 In above code, I am retrieving data and displaying in MessageDialog.

 It will display all data which is stored in table.