Introduction

In this first article, we will introduce you to a very important tool related to storing data permanently in the storage of our device. From developers, you know that the support to the storage of information which is settings or other data is very important. Currently, the Windows Runtime has not natively support the Database, but provides the ability to serialize / deserialize data in different formats: xml and json. Although very important and easy to use, and the disadvantage that all information must still be kept in memory at the expense of delays and impact on the performance of our application. For this reason, the return useful approach to the use of the database, and an excellent choice to resort to Sqlite. SQLite is an open source engine, created supported by SQLite Consortium supported by Windows Runtime thanks to compatibility with C ++. Written in C ++, provides excellent performance in terms of execution and perform any operation, working in disconnected mode data, unlike for example Sqlserver, where there is a service called DBMS for interaction with data. E-platform, then over Windows Phone Store, and is compatible with Windows Store, Android and other platforms. Finally, you can use some methods extension Linq (Language Integrated Query), to be precise LinqToObject that we will see in the article. Until now, it was made a simple introduction on what and Sqlite, but for more details and explanations, refer to the official source. In this article we will see in the order:

  • Installation of Sqlite Engine
  • Creating the Project
  • Installation Sqlite-Net
  • Creation of the class for the Database
  • Data entry
  • Updating Data
  • Deleting Data
  • Other classes needed
  • Posting namespace needed
  • Architecture Compilation
  • Test the Application
  • Conclusion
  • Other Resources

 


Installation of Sqlite Engine

First thing to do, since there is as said support data in Windows Runtime, some extensions to be installed in order to interact with Sqlite, the first is found at this link. Let's pay attention to one thing, in this case, we will install the appropriate extension to Windows Phone Store, but if we wanted to develop a Windows Store, we have to install the engine suitable, because if we have a project Universal App, it is not a single application, but we are faced with a project that eventually will create two separate applications for the platform.  Once downloaded and installed, it will add to the project that slowly we will create in the course of the article.
 


Creating the Project

It's time to create our sample project. We initiate VisualStudio 2013; in my case I use the professional version. From the File menu, select "New" and right after "Project". We will create an application using C # as the development language. In templates available to the "Windows Phone Application", select "Blank Application (Windows Phone)" and assign the project the name "Sqlite Sample" as shown in the figure.

  
Image 1.1 The templates available for development on Windows Phone.

 

Expect that the development environment is initialized correctly, as anticipated in the introduction; let's add all the needed extensions to interact with Sqlite. First, add the reference to Engine for Windows Phone, in exploring solutions, clicks with the right mouse button on "References", and we're going to select "Add Reference". In the next dialog box, on the left we expand the "Extensions", if we have correctly installed the engine, we will find among the available extensions, as shown in the figure.

Image 1.2 The Sqlite engine for Windows Phone 8.1.

 

Now in conjunction with the drafting of the article is the version 3.8.7.4, so all the exercises will be with this version. At the moment, we installed the first of two necessary elements, this is because the engine itself is written in C ++, so to interact require that the project is C ++, then giving many problems for those unfamiliar with this language, or if we including all in a C # project as in our case. For this reason, the libraries were created by different developers, able to abstract what is actually the engine of Sqlite, through a series of classes and methods that help the developer to interact writing code with high-level languages such as C # and VB .NET. One of these is called Sqlite-net.

 


Installation Sqlite-Net

With this library, we will be able to perform all the operations that you normally do in a database, such as Insert, Delete, Update and run search queries. Sqlite-net also offers an approach typical ORM, is taken as the last current example LinqToSql or EntityFramework. In addition, it has the support LinqToObject, so you can perform searches on collections of objects such as lists and ObservableCollection. For more details, refer to the official documentation and examples found at this link. Being told how a third-party library, we must add to our project. The easiest way is to use Nuget. We return to our project, still in the "References", right click, select the command "Manage Nuget packages". In the next dialog, we type in the search box at the top right that we find "Sqlite-net", as shown in the figure.

Image 1.3 The library Sqlite-net on Nuget.

 

After finding the library, and clicked on the "Install" button, if all goes well, he, as shown in the figure, a green circle with a check mark in it, this means that it was installed correctly. We installed all you need; it is time to proceed with the creation of the class for the database, but before we look at what was added after installation of Engine of Sqlite and Sqlite-net.

Image 1.4 The window explores solutions after installation of the necessary files.

 

We reference SQLite for Windows Phone 8.1 in the references, it is of Engine that we added first, and the files and SqLite.cs SQLiteAsync.cs, belonging to the installation of the library Sqlite-net, with which we will be able to perform operations on the database.

 


Creation of the class for the Database

As said before, Sqlite-net, offers a typical approach ORM, so we should not worry about the database, but to create the necessary classes or for the creation then back them through the library and the engine of Sqlite. Mouse click on the project name, then Sqlite Sample, right click, and choose the command "Add" and then "New Folder". We will call it "Classes" in it will put everything related to the interaction with the database and more. With the same procedure we create another folder, and we will call "Screen" where we put, the screens that make up our application. In the Classes folder we create a new class called "Employee"; the procedure is always the same for the creation of folders, but choosing the command "Class". Within the file insert the following code.

 

using SQLite;
 
namespace SqlLite_Sample.Classes
{
 class Employee
 {
  [PrimaryKey,AutoIncrement]
  public int Id { get; set; }
 
  [MaxLength(30)]
  public string Name { get; set; }
 
  [MaxLength(30)]
  public string SurName { get; set; }
 
  [MaxLength(3)]
  public int Age { get; set; }
 }
} 

How can see, we simply created a class called Employee, which contains four properties, with the attributes included in square brackets, which will give other customization. It 'good to know that in this case, will be created within the database, a table with the same name as the class, four fields with exactly the same name of the property, will be deducted automatically the data type for fields, varchar for Name and SurName, int for Id and Age. In addition to the attributes, further customize these four properties, for the field Id, we say by "PrimaryKey" that it is the counter field and that will be increased automatically, this means the attribute "AutoIncrement". We define a maximum length of thirty characters for the fields Name and SurName, and a maximum of three numbers for the Age field. We defined a simple class that represents an employee, defining the name, surname and age. Screen MainPage, we define a simple graphical interface that will allow us to access the other screens. We copy this code xaml file MainPage.xaml.

 

<Grid x:Name="LayoutRoot">
  <Grid.ChildrenTransitions>
   <TransitionCollection>
    <EntranceThemeTransition/>
   </TransitionCollection>
  </Grid.ChildrenTransitions>
 
  <Grid.RowDefinitions>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="*"/>
  </Grid.RowDefinitions>
 
  <!-- Pannello del titolo -->
  <StackPanel Grid.Row="0" Margin="19,0,0,0">
   <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>
   <TextBlock Text="Main page" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>
  </StackPanel>
 
  <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->
  <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">
   <StackPanel>
    <Button
     x:Name="btnInsertSample"
     Content="Insert sample page"
     Tapped="btnInsertSample_Tapped"
     Width="300"/>
 
    <Button
     x:Name="btnUpdateSample"
     Content="Update sample page"
     Tapped="btnUpdateSample_Tapped"
     Width="300"/>
 
    <Button
     x:Name="btnDeleteSample"
     Content="Delete sample page"
     Tapped="btnDeleteSample_Tapped"
     Width="300"/>
   </StackPanel>
  </Grid>
 </Grid> 

If everything is entered correctly, our initial screen should look like this.

Image 1.5 The Screen MainPage.

 

Let's now code editor, with key F7, we identify the class constructor MainPage, and insert this code.

DatabaseManagement.CreateDatabase();

DatabaseManagement and a class in which we're going to put all the management for interaction with the database. In this case we're going to call the method CreateDatabase, which will create the database if it did not exist on isolated storage. This is the part of code that will create the affected database.

public static async void CreateDatabase()
  {
   var person = await ConnectionDb().CreateTableAsync<Employee>();
  }

We define a variable named person SqliteAsyncConnection type, which is nothing other than the class for the management of the connection string to the database, and using the following method:

private static SQLiteAsyncConnection ConnectionDb()
{
 var conn = new SQLite.SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "employee.db"),true);
 return conn;
}

We go to retrieve the variable conn, which is located inside the method ConnectionDb (), which gives us the following line of code, then that will be the location path of the Database.

Path.Combine(ApplicationData.Current.LocalFolder.Path, "employee.db"

In this way, after the execution of the code, we have created in the "Local" on isolated storage for a file named "employee.db". We will see later all the code within this class, because we will need the example that we will create. To complete the whole piece of code on the screen, we're going to handle the event tapped the three buttons, insert the following piece of code.

private void btnInsertSample_Tapped(object sender, TappedRoutedEventArgs e)
{
 Frame.Navigate(typeof(Insert));
}
 
private void btnUpdateSample_Tapped(object sender, TappedRoutedEventArgs e)
{
 Frame.Navigate(typeof(Update));
}
 
private void btnDeleteSample_Tapped(object sender, TappedRoutedEventArgs e)
{
 Frame.Navigate(typeof(Delete));
}

For those who have had experience with previous versions, you will notice the differences in navigation between pages. If you used the Windows Phone 7/8 Navigate method of property NavigationService, then passing through Uri relative type the name of the page on which to access, with any parameters for the exchange of information between pages, applications Windows Phone Store is slightly different. We pass directly to the Navigate method the reference to the page to which we want to access, in other words typeof (Delete) for example. Then we have two overloads, the first is always the / parameters to pass to the page on which we sail, the last on the opportunity to determine how animation intend view the page at display time.

 


Data entry

At this point, we have implemented all the part concerning the creation of the database. We create now, everything related to data entry within the Employee table. Returning to the project, we place the cursor on the "Screen", right-click, and choose the command "Add" and immediately after "New Item". In the next dialog box, look for the template "Basic page", as shown in the figure, and Rename with Insert.

Image 1.6 The template page basis.

 

Created the template and page appears, enter the following code xaml to define the graphical interface.

<!-- Pannello del titolo -->
<StackPanel Grid.Row="0" Margin="19,0,0,0">
 <TextBlock
  Text="Sqlite sample"
  Style="{ThemeResource TitleTextBlockStyle}"
  Margin="0,12,0,0"/>
 
 <TextBlock
  Text="Insert page"
  Margin="0,-6.5,0,26.5"
  Style="{ThemeResource HeaderTextBlockStyle}"
  CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>
</StackPanel>
 
<!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->
<Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">
 <Grid.RowDefinitions>
  <RowDefinition Height="Auto"/>
  <RowDefinition Height="Auto"/>    
 </Grid.RowDefinitions>
     
 <Grid Grid.Row="0">
  <Grid.RowDefinitions>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="Auto"/>
  </Grid.RowDefinitions>
 
  <Grid.ColumnDefinitions>
   <ColumnDefinition Width="Auto"/>
   <ColumnDefinition Width="*"/>
  </Grid.ColumnDefinitions>
 
  <TextBlock
     Grid.Column="0"
     Grid.Row="0"
     x:Name="tbkName"
     FontSize="25"    
     Text="Name"
     VerticalAlignment="Center"
  />
 
  <TextBlock
     Grid.Column="0"
     Grid.Row="1"    
     x:Name="tbkSurname"
     FontSize="25"
     Text="Surname"
     VerticalAlignment="Center"
  />
 
  <TextBlock
     Grid.Column="0"
     Grid.Row="2"    
     x:Name="tbkAge"
     FontSize="25"
     Text="Age"
     VerticalAlignment="Center"
  />
 
  <TextBox
     Grid.Column="1"
     Grid.Row="0"
     x:Name="tbxName"
  />
 
  <TextBox
     Grid.Column="1"
     Grid.Row="1"    
     x:Name="tbxSurname"
  />
 
  <TextBox
     Grid.Column="1"
     Grid.Row="2"    
     x:Name="tbxAge"
     InputScope="Number"
  />
 </Grid>
 
 <Grid Grid.Row="1">
  <Grid.RowDefinitions>
   <RowDefinition Height="20"/>
   <RowDefinition Height="Auto"/>     
  </Grid.RowDefinitions>
   
  <Button
     Grid.Row="1"
     x:Name="btnInsert"
     Content="Insert"
     HorizontalAlignment="Center"
     Tapped="btnInsert_Tapped"
  />   
 </Grid>
</Grid>

If all the code is entered correctly, this will be the appearance of the page.

Image 1.7 The data entry screen.

 

At the code level, all we have to manage is nothing but the event tapped the Insert button. With F7 key, we enter in the code editor, and insert everything you need within the event tapped the button.

private async void btnInsert_Tapped(object sender, Windows.UI.Xaml.Input.TappedRoutedEventArgs e)
  {
   if(Validations.CheckTextBox(tbxName,tbxSurname,tbxAge).Equals(true))
   {
    var dialog = new MessageDialog("Valuing all fields ");
    await dialog.ShowAsync();
   }
 
   else
   {
    DatabaseManagement.InsertData(tbxName.Text, tbxSurname.Text, int.Parse(tbxAge.Text));
   }
  }

We note that we have included one if construct; this is because you may not enter any data and then touch the button so by inserting null values. To avoid this, you have created a new class called Validations, inside which there are methods that do nothing but run a check. In our case it will be verified that the TextBox are all valued, if only it were not, we will notify you via a MessageDialog. We will not dwell further on the methods of this class, but we'll just show then the code, since the article is dedicated to Sqlite. What is interesting however, is the code located within the construct else, who will perform data entry in the Employee table. The method InsertData, we find him in the class DatabaseManagement, let's see how it works.

public async static void InsertData(string _name, string _surname, int _age)
  {
   var newemployee = new Employee
   {
    Name = _name,
    SurName = _surname,
    Age = _age,
   };
 
   await ConnectionDb().InsertAsync(newemployee);
  }

The method is very simple, let's create a new Employee object, where initialize Name, Age and SurName with the values of the parameters that the method requires InsertData at the time when it is invoked. Later we call the method asynchronously InsertAsync (), acting class SqliteAsyncConnection, passing the object as a parameter to add to the Employee table, or the variable newemployee. The method ConnectionDb we have already seen, when part of the code to create the database.

 


Updating Data

The updating procedure of the data is similar to that for the insertion, if not for the difference that we recover the data / the edit. Let's go back to our project, and with the procedure that we used for the creation of the Insert page, always in the folder Screen, we create one called Update. How to insert page insert the code xaml to define the graphical interface.

 

<!-- Pannello del titolo -->
 <StackPanel Grid.Row="0" Margin="19,0,0,0">
  <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>
  <TextBlock Text="Update page" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>
 </StackPanel>
 
 <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->
 <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">
  <Grid.RowDefinitions>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="Auto"/>
   <RowDefinition Height="Auto"/>    
  </Grid.RowDefinitions>
 
  <ListBox Grid.Row="0" x:Name="lstUpdatePerson" SelectionChanged="lstUpdatePerson_SelectionChanged">
   <ListBox.ItemTemplate>
    <DataTemplate>
     <StackPanel>
      <StackPanel Orientation="Horizontal">
       <TextBlock x:Name="tbkName"
         FontWeight="Bold"
         Text="Name"/>
 
       <TextBlock Width="30"/>
       <TextBlock x:Name="tbkSurname"
         FontWeight="Bold"           
         Text="Surname"/>
 
       <TextBlock Width="30"/>
       <TextBlock x:Name="tbkAge"
         FontWeight="Bold"           
         Text="Age"/>
 
       <TextBlock Height="50"/>
      </StackPanel>
 
      <StackPanel Orientation="Horizontal">
       <TextBlock
        x:Name="tbkFindForName"
        Text="{Binding Name}"/>
 
       <TextBlock Width="20"/>
 
       <TextBlock
        x:Name="tbkFindForSurName"
        Text="{Binding SurName}"/>
       <TextBlock Width="20"/>
 
       <TextBlock
        x:Name="tbkFindForAge"         
        Text="{Binding Age}"/>
      </StackPanel>
     </StackPanel>
    </DataTemplate>
   </ListBox.ItemTemplate>
  </ListBox>
  
  <TextBlock Grid.Row="3" x:Name="tbkNewData" Text="New data" HorizontalAlignment="Center"/>
  
  <StackPanel Grid.Row="4" x:Name="splNewData">
   <StackPanel x:Name="splNewName" Orientation="Horizontal">
    <TextBlock Text="Name" VerticalAlignment="Center"/>
    <TextBlock Width="30"/>
    <TextBox x:Name="tbxNewName" Width="Auto"/>
   </StackPanel>
 
   <StackPanel x:Name="splNewSurName" Orientation="Horizontal">
    <TextBlock Text="SurName" VerticalAlignment="Center"/>
    <TextBlock Width="15"/>
    <TextBox x:Name="tbxNewSurName" Width="Auto"/>
   </StackPanel>
 
   <StackPanel x:Name="splNewAge" Orientation="Horizontal">
    <TextBlock Text="Age" VerticalAlignment="Center"/>
    <TextBlock Width="40"/>
    <TextBox x:Name="tbxNewAge" Width="Auto" InputScope="Number"/>
   </StackPanel>
  </StackPanel>   
  
  <Button Grid.Row="6" x:Name="btnUpdatePerson" Content="Update" Tapped="btnUpdatePerson_Tapped"/>
 </Grid> 

Again, if all the code is entered correctly, this will be the final appearance of the page.

Image 1.8 The Screen update data.

 

As for data entry, in this circumstance, we must handle the event tapped the Update button plus the SelectionChanged event of the ListBox, who will see all the data available in the Employee table, and l 'On NavigatedTo override. The latter is performed at the time when you access the page chosen by the user when using the application. With the F7 key, we access the editor and insert the following code.

private void lstUpdatePerson_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
 Parametri_ricerca.NewName = ((Employee)(lstUpdatePerson.SelectedValue)).Name;
 tbxNewName.Text = ((Employee)(lstUpdatePerson.SelectedValue)).Name;
 tbxNewSurName.Text = ((Employee)(lstUpdatePerson.SelectedValue)).SurName;
 tbxNewAge.Text = ((Employee)(lstUpdatePerson.SelectedValue)).Age.ToString();
}

Analyze the code SelectionChanged event of the ListBox. We have defined a new class called Parametri_ricerca, where inside there are fields that will serve as an exchange of information between pages, basically back to the subject of navigation between pages, for the exchange of information between them, we have the ability to use within the parameters of the Navigate method, as we have seen, or the system I used, in other words, create a static class and define all the necessary fields for use. We value NewName field, with the value of the property SelectedValue of the ListBox, TextBox controls most of the page. There is one thing to which we must pay attention: the SelectedValue property is of type Object and cannot be assigned directly to the Text property of the TextBox controls, and even the variable NewName, since all are of type String, but you have to make a cast, in other words convert the type Object returned in Employee, then using the properties of the class, we will have to return the value of the correct type. In other words Name, Surname Age and converted to string using the ToString () method. After the enhancement of all objects within SelectionChanged event, we see the code for updating data.

 

private async void btnUpdatePerson_Tapped(object sender, TappedRoutedEventArgs e)
 {
  await Validations.MessageConfirmDeleteoUpdatePerson("Vuoi aggiornare i dati?");
 
  if (Validations.result.Equals(true))
  {
   if (Validations.CheckTextBox(tbxNewName, tbxNewSurName, tbxNewAge).Equals(true))
   {
    var dialog = new MessageDialog("Valorizzare tutti i campi");
    await dialog.ShowAsync();
   }
 
   else
   {
    DatabaseManagement.UpdateData(Parametri_ricerca.NewName,tbxNewName.Text);
   }
  }
 } 

To tap the button btnUpdate, we display a MessageDialog user, where it asked for confirmation to update the data, if you do one more check that all the TextBox are correctly valued; we have already seen this in the procedure concerning the 'data entry. What matters is the code within the construct else. Even this method will find it in the class DatabaseManagement.

public async static void UpdateData(string _name, string _newname)
 {
  var updateemployee = await ConnectionDb().Table<Employee>().Where(w => w.Name.Equals(_name)).FirstOrDefaultAsync();
 
  updateemployee.Name = _newname;
  await ConnectionDb().UpdateAsync(updateemployee);
 }

Analyze the code concerning, updating data. For the connection and the recovery of the Employee table, we make use of the now known ConnectionDb (), but then takes over as we mentioned at the outset, support for Linq. This line of code:

Where(w => w.Name.Equals(_name)).FirstOrDefaultAsync();

We uses extension methods Where (), where according to a condition, (in our example, we want to change the name of an employee "employee") given by parameter _name, then we recover, through the methods exstension FirstOrDefaultAsync () the data correct and set them to the name property of the variable of type Employee updateemployee value _newname where _newname matches the name changed. Finally by the method UpdateAsync (), is upgraded to the Employee table inside the Database.

We still have to enter the code override OnNavigatedTo; can be found in the Record Navigation Helper present in .cs code file.

protected async override void OnNavigatedTo(NavigationEventArgs e)
  {
   await  DatabaseManagement.LoadData(lstUpdatePerson);
   this.navigationHelper.OnNavigatedTo(e);
  }

The line of code that interests us is the first or the method LoadData () inside the class now known DatabaseManagement.

public async static Task LoadData(ListBox box)
{
 var employee = new List<Employee>();
 var query = ConnectionDb().Table<Employee>();
 var result = await query.ToListAsync();
 
 foreach (var person in result)
 {
  employee.Add(new Employee { Name = person.Name, SurName = person.SurName, Age = person.Age });
 }
 box.ItemsSource = employee;
}

It is a task with which wants to upgrade the control of the page Listbox Update. In fact, if we notice the method requires a parameter of type ListBox. In other words, we should pass a reference to a control, in our case the ListBox lstUpdatePerson. Then, declared a list of Employee type, perform the connection to the database and recover the Employee table using the method Table <>, but the actual query is executed when we invoke education await query.ToListAsync (). This method returns a collection of objects of type Employee that then let's add to the list employee using a foreach loop. After the interaction of the cycle, the last thing to do is assigned to the parameter box the result of employee. This way when we access the screen Update, if there is no data, we can see them properly.

 


Deleting Data

The process for deleting data, is almost identical to that for the update, the only difference is called a query differently at the time when we want to delete the data. Back to our original plan, we follow the procedure that we have seen the creation of screens Insert and Update, and we always add a new page in the folder Screen base and call Delete. We add the following code xaml to define the graphical interface.

 

<!-- Pannello del titolo -->
 <StackPanel Grid.Row="0" Margin="19,0,0,0">
  <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>
  <TextBlock Text="Delete page" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>
 </StackPanel>
 
 <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->
  <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">
   <Grid.RowDefinitions>
    <RowDefinition Height="Auto"/>
    <RowDefinition Height="Auto"/>
    <RowDefinition Height="Auto"/>
    <RowDefinition Height="Auto"/>
    <RowDefinition Height="Auto"/>
   </Grid.RowDefinitions>
 
   
  <ListBox Grid.Row="0" x:Name="lstDeletePerson" SelectionChanged="lstDeletePerson_SelectionChanged">
   <ListBox.ItemTemplate>
    <DataTemplate>
     <StackPanel>
      <StackPanel Orientation="Horizontal">
       <TextBlock x:Name="tbkName"
         FontWeight="Bold"
         Text="Name"/>
       <TextBlock Width="30"/>
 
       <TextBlock x:Name="tbkSurname"
         FontWeight="Bold"           
         Text="Surname"/>
 
       <TextBlock Width="30"/>
 
       <TextBlock x:Name="tbkAge"
         FontWeight="Bold"           
         Text="Age"/>
 
       <TextBlock Height="50"/>
      </StackPanel>
 
      <StackPanel Orientation="Horizontal">
       <TextBlock
        x:Name="tbkFindForName"
        Text="{Binding Name}"/>
 
       <TextBlock Width="20"/>
 
       <TextBlock
        x:Name="tbkFindForSurName"
        Text="{Binding SurName}"/>
 
       <TextBlock Width="20"/>
 
       <TextBlock
        x:Name="tbkFindForAge"
        Text="{Binding Age}"/>
      </StackPanel>
     </StackPanel>
    </DataTemplate>
   </ListBox.ItemTemplate>
  </ListBox>
 
  <ScrollViewer Grid.Row="2">
   <StackPanel Grid.Row="2" x:Name="splData">
    <StackPanel x:Name="splName" Orientation="Horizontal">
     <TextBlock Text="Name" VerticalAlignment="Center"/>
     <TextBlock Width="30"/>
 
     <TextBox x:Name="tbxName" Width="Auto"/>
    </StackPanel>
 
    <StackPanel x:Name="splSurName" Orientation="Horizontal">
     <TextBlock Text="SurName" VerticalAlignment="Center"/>
     <TextBlock Width="15"/>
     <TextBox x:Name="tbxSurName" Width="Auto"/>
    </StackPanel>
 
    <StackPanel x:Name="splAge" Orientation="Horizontal">
     <TextBlock Text="Age" VerticalAlignment="Center"/>
     <TextBlock Width="40"/>
     <TextBox x:Name="tbxAge" Width="Auto"/>
    </StackPanel>
   </StackPanel>
  </ScrollViewer>
 
  <Button Grid.Row="4" x:Name="btnDeletePerson" Content="Delete" Tapped="btnDeletePerson_Tapped"/>
 </Grid> 

Again, if all the code is entered correctly, this will be the final appearance of the page.

Image 1.9 The screen data deletion.

 

Event handling is identical to what we saw for the upgrade, we have to handle the event tapped the Delete button, plus the SelectionChanged event of the ListBox, and override On NavigatedTo. With the F7 key, we access the editor and insert the following code.

private void lstDeletePerson_SelectionChanged(object sender, SelectionChangedEventArgs e)
{   
 tbxName.Text = ((Employee)(lstDeletePerson.SelectedValue)).Name;
 tbxSurName.Text = ((Employee)(lstDeletePerson.SelectedValue)).SurName;
 tbxAge.Text = ((Employee)(lstDeletePerson.SelectedValue)).Age.ToString();
}
 
private async void btnDeletePerson_Tapped(object sender, Windows.UI.Xaml.Input.TappedRoutedEventArgs e)
{
 await Validations.MessageConfirmDeleteoUpdatePerson("Vuoi eliminare i dati?");
 
 if(Validations.result.Equals(true))
 {
  DatabaseManagement.DeleteData(tbxName.Text);
 }
}

We will notice that the management SelectionChanged event is identical to that seen previously; the only difference is tapped in the event of btnDelete button, which invokes the method DeleteData that we always find in the file DatabaseManagement. Here is the code:

public async static void DeleteData(string _name)
{
 var deleteemployee = await ConnectionDb().Table<Employee>().Where(w => w.Name.Equals(_name)).FirstOrDefaultAsync();
 
 deleteemployee.Name = _name;
 await ConnectionDb().DeleteAsync(deleteemployee);
}

Also in this case, is identical to the method UpdateData, except for the last line of code.

 

await ConnectionDb().DeleteAsync(deleteemployee); 

As anticipated, the only difference is that we're going to eliminate a given, so the method is invoked DeleteAsync (), that it will eliminate based on the value of the parameter _name the corresponding figure. Still lacks the management override OnNavigatedTo, we still saw at the Update page. Below the portion of the code.

protected async override void OnNavigatedTo(NavigationEventArgs e)
  {
   await  DatabaseManagement.LoadData(lstDeletePerson);
   this.navigationHelper.OnNavigatedTo(e);
  }

I remember that override OnNavigatedTo; you find it in the Record Navigation Helper present in .cs code file.

 


Other classes needed

For now we have managed to insert, update and delete data. However if we tried to fill in the Solution, we will get compilation errors, because this must be added other classes to support data, research and control enhancement before operations on the database. Let's go back to our project, we aim the cursor to the Classes folder, right click and create a class called DatabaseManagement, then we modify the existing code with this.

using SQLite;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using Windows.Storage;
using Windows.UI.Xaml.Controls;
 
namespace SqlLite_Sample.Classes
{
 public static class DatabaseManagement
 {
  private static SQLiteAsyncConnection ConnectionDb()
  {
   var conn = new SQLite.SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"),true);
   return conn;
  }
 
  public static async void CreateDatabase()
  {
   var person = await ConnectionDb().CreateTableAsync<Employee>();
  }
 
  public async static Task LoadData(ListBox box)
  {
   var employee = new List<Employee>();
   var query = ConnectionDb().Table<Employee>();
   var result = await query.ToListAsync();
 
   foreach (var person in result)
   {
    employee.Add(new Employee { Name = person.Name, SurName = person.SurName, Age = person.Age });
   }
   box.ItemsSource = employee;
  }
 
  public async static void InsertData(string _name, string _surname, int _age)
  {
   var newemployee = new Employee
   {
    Name = _name,
    SurName = _surname,
    Age = _age,
   };
   await ConnectionDb().InsertAsync(newemployee);
  }
 
  public async static void DeleteData(string _name)
  {
   var deleteemployee = await ConnectionDb().Table<Employee>().Where(w => w.Name.Equals(_name)).FirstOrDefaultAsync();
 
   deleteemployee.Name = _name;
   await ConnectionDb().DeleteAsync(deleteemployee);
  }
 
  public async static void UpdateData(string _name, string _newname)
  {
   var updateemployee = await ConnectionDb().Table<Employee>().Where(w => w.Name.Equals(_name)).FirstOrDefaultAsync();
 
   updateemployee.Name = _newname;
   await ConnectionDb().UpdateAsync(updateemployee);
  }
 }
}

This is the code that will allow us to communicate and interact with the database, we have already seen before. With the same procedure as before, we create a class called Validations, and replace the existing code with this.

using System;
using System.Linq;
using System.Threading.Tasks;
using Windows.UI.Popups;
using Windows.UI.Xaml.Controls;
 
namespace SqlLite_Sample.Classes
{
 public static class Validations
 {
  public static bool result;
 
  public static bool CheckTextBox(params TextBox []textboxs)
  {
   var result = false;
   foreach (var textBox in textboxs.Where(w => w.Text.Equals("")))
   {
    result = textBox.Text.Equals("");    
   }
   return result;
  }
 
  public static async Task MessageConfirmDeleteoUpdatePerson(string message)
  {
   var dialog = new MessageDialog(message);
   dialog.Commands.Add(new UICommand("No", new UICommandInvokedHandler(Command)));
   dialog.Commands.Add(new UICommand("Yes", new UICommandInvokedHandler(Command)));
   await dialog.ShowAsync(); 
  }
 
  private static void Command(IUICommand command)
  {
   if (command.Label.Equals("Yes"))
   {
    result = true;
   }   
  }
 }
}

We shall not stop to explain what does this code, because it falls outside Article; the important thing knows that run the controls that I mentioned before, about the fact that all the TextBox controls are properly valued, more control MessageDialog that deals alert you in case of errors. Also in the Classes folder, create a class named parametri ricerca, and here we replace the existing code with this following.

namespace SqlLite_Sample.Classes
{
 public static class Parametri_ricerca
 {
  public static string FindData { get; set; }
  public static string TypeSearch { get; set; }
  public static string NewName { get; set; }
 }
}

Coming back to the navigation between pages, this is the method I use to exchange information between pages within the application.

 


Posting namespace needed

Another thing to do is to include the namespace needed since there are two additional folders, and added them to the classes. We put in the order:

Screens Insert, Update and Delete.xaml.cs

using Windows.UI.Popups;
using SqlLite_Sample.Classes;

Screen MainPage.xaml.cs

using Sqlite_Sample.Screen;
using SqlLite_Sample.Classes;

 


Architecture Compilation

The last thing to do, before compiling the Solution is changing the architecture of the target platform. This is the fact that the engine of Sqlite is written in C ++, and the default target platform set in the project is Any CPU. This mode is not supported. To do this in the main menu of VisualStudio have the command "Compile", then "Configuration Manager", as shown in the figure.

Image 1.10 The menu  Build (Compila).

 

In the next dialog, we note that we have several choices of platforms, Any CPU (the default), ARM, x64 and x86.

Image 1.11 The Configuration Manager dialog box.

 

We have to select the target platform second, where we are trying the application. If we use a tablet, or a phone with ARM processor, we have to select the ARM platform. If we are using the emulator or in the case of Windows Phone, a PC in the case of Windows, we have to select x86 or x64, everything depends on your processor if 32 or 64 bits. In my case, I tried the sample application on a Nokia Lumia 925 with Windows Phone OS 8.1, so I chose the way of configuration ARM.

 


Test the Application

At this point we are ready to compile and run the application. F5 key, and if there are no problems, this is the home screen of the application.

Image 1.12 The home screen of the application.

 

Let's tap the Insert button sample page, until you see the screen page for data entry.

Image 1.13 Insert the screen page of the application.

 

Insert one or more names, surnames and age at will, and immediately tap the Insert button. Let's go back by pressing the Back button. I have not entered any MessageDialog about entering data, only for reasons of practicality. Back to the main screen, tap the Update button to sample page, until you see the pages that allow us to perform an update of the data entered.

  

Image 1.14 Screen Update application.

 

In this case, I added two employees in the Employee table, as can be seen in FIG. We touch any item from a ListBox until this condition.

Image 1.15 The screen update of the application, after selecting an item from the ListBox.

 

In the example we are going to change for convenience, only the name of the employee, but by changing the method code UpdateData () that are in the file DatabaseManagement, you can also change the name and age, adding other parameters to the method, and then assign them to the properties surname and the Age variable updateemployee. Modify at will the contents of the TextBox beside the label "Name" and tap the Update button. There will be asked to confirm the update, tap the button yes, and you will notice that it will be upgraded within the ListBox. This means that the procedure is successful.

Image 1.16 The MessageDialog confirmation update name.

 

Image 1.17 The screen updated with the new name.

 

Now we just have to try the part on the disposal of a given. We return to the MainPage with the Back button Button, and then tap the Delete button sample page.

Image 1.18 The screen updated with the new name.

 

The procedure is identical to that for the upgrade, with the difference that we're going to eliminate a given within the Employee table. Touching an item within the ListBox, we display the detail in the TextBox. Tap the Delete button, we will have the usual MessageDialog we ask for confirmation before deleting the data, tap to yes and here is the end result of the procedure.

Image 1.19 The MessageDialog delete confirmation given.

 

Image 1.20 The screen with the ListBox updated.

 


Conclusion

In this first part, we have seen the basics of Sqlite, how to install the engine, the library Sqlite-net.  We have seen the most common operations such as inserting, updating, and deleting data from a table of a database (CRUD). We have seen that the library Sqlite-net support in Linq, although at the moment are supported few operators. We made use of the operator Where () and FirstOrDefaultAsync (). One thing at the moment is not yet supported is the relationship between the tables, so join using foreign keys, but to join one or more tables to each other, for now you have to query multiple nested. This however is detrimental to the performance of the application. In the next article we will see how to search within a table.

 


Other Resources