Post
Ask Question

Part One: SQLite on Windows Phone 8.1

Introduction

This first article introduces a very important tool related to storing data permanently in our device. As developers, you know that the support of the storage of information, such as settings or other data, is very important. Currently, the Windows Runtime has no native support of databases, but provides the ability to serialize / deserialize data in various formats including XML and JSON. Although very important and easy to use, the disadvantages are that all the information must still be kept in memory at the expense of delays and that impacts on the performance of the application. For this reason, the useful approach is to use a database and an excellent choice to resort to is SQLite. SQLite is an open source engine, created and supported by the SQLite Consortium supported by the Windows Runtime thanks to compatibility with C++.

Written in C++, SQLite provides excellent performance in terms of execution and the performance of any operation, working in disconnected mode, unlike for example SQL Server, 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 exstension (such as Language Integrated Query (LINQ)), 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:

  • Installation of Engine of SQLite
  • 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 Engine of SQLite

The first thing to do, since there is support of data in the 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 need 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 we will slowly create in the course of the article.

Creating the Project

It's time to create our sample project. We execute VisualStudio 2013. In my case I used the professional version. From the File menu, select "New" and then "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.

Assuming that the development environment is initialized correctly, as specified in the introduction, let's add all the necessary extensions to interact with SQLite. First, add the reference to the Engine for Windows Phone, in the project in the Solution Explorer, right-click 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 then we will find SQLite among the available extensions as shown in the following figure.



Image 1.2 The engine of SQLite 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 with C++, there are many problems for those unfamiliar with this language, or if we include all of it in a C# project as in our case. For this reason, the libraries were created by multiple developers and abstract what the engine of SQLite actually is, using a series of classes and methods that help the developer to interact writing code with managed languages such as C# and VB .NET. One of these is called Sqlite-net.

Installation Sqlite-Net

With this library, we are able to perform all the operations that you normally do in a database, such as insert, delete, update and query. Sqlite-net also offers an approach of a typical ORM, as in the last current example of LinqToSql or EntityFramework. In addition, it has the support of LinqToObject, so you can do searches on collections of objects such as lists and ObservableCollection. For more details, refer to the official documentation and examples found at this link. Since it is a third-party library, we must add it 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 clicking on the "Install" button, if all goes well and as shown in the figure, there will be a Green circle with a check mark in it, this means that it was installed correctly. We have now installed everything we need; it is time to proceed with the creation of the class for the database, but before that we will look at what was added after the installation of the SQLite Engine and Sqlite-net.



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

We reference SQLite for Windows Phone 8.1 in the references, it is of the engine that we added first and the files SqLite.cs and SQLiteAsync.cs, belong 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 ORM approach, 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 we 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.

  1. using SQLite;  
  2.   
  3. namespace SqlLite_Sample.Classes  
  4. {  
  5.     class Employee  
  6.     {  
  7.         [PrimaryKey,AutoIncrement]  
  8.         public int Id { get; set; }  
  9.   
  10.         [MaxLength(30)]  
  11.         public string Name { get; set; }  
  12.   
  13.         [MaxLength(30)]  
  14.         public string SurName { get; set; }  
  15.   
  16.         [MaxLength(3)]  
  17.         public int Age { get; set; }  
  18.     }  
  19. } 

Now we can see that we simply created a class called Employee, that contains four properties, with the attributes included in square brackets, that will provide other customization. It is good to know that in this case, within the database a table will be created with the same name as the class, four fields with exactly the same name as the property and the data type for the fields will be deduced automatically, varchar for Name and SurName, int for Id and Age. In addition to the attributes, these four properties are further customized. 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. In the screen MainPage, we define a simple graphical interface that will allow us to access the other screens. We copy this code XAML file to MainPage.xaml.

  1. <Grid x:Name="LayoutRoot">  
  2.         <Grid.ChildrenTransitions>  
  3.             <TransitionCollection>  
  4.                 <EntranceThemeTransition/>  
  5.             </TransitionCollection>  
  6.         </Grid.ChildrenTransitions>  
  7.   
  8.         <Grid.RowDefinitions>  
  9.             <RowDefinition Height="Auto"/>  
  10.             <RowDefinition Height="*"/>  
  11.         </Grid.RowDefinitions>  
  12.   
  13.         <!-- Pannello del titolo -->  
  14.         <StackPanel Grid.Row="0" Margin="19,0,0,0">  
  15.             <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>  
  16.             <TextBlock Text="Main page" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>  
  17.         </StackPanel>  
  18.   
  19.         <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->  
  20.         <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">  
  21.             <StackPanel>  
  22.                 <Button   
  23.                     x:Name="btnInsertSample"   
  24.                     Content="Insert sample page"   
  25.                     Tapped="btnInsertSample_Tapped"  
  26.                     Width="300"/>  
  27.   
  28.                 <Button   
  29.                     x:Name="btnUpdateSample"   
  30.                     Content="Update sample page"   
  31.                     Tapped="btnUpdateSample_Tapped"  
  32.                     Width="300"/>  
  33.   
  34.                 <Button   
  35.                     x:Name="btnDeleteSample"   
  36.                     Content="Delete sample page"   
  37.                     Tapped="btnDeleteSample_Tapped"  
  38.                     Width="300"/>  
  39.             </StackPanel>  
  40.         </Grid>  
  41.     </Grid> 

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



Image 1.5 The Screen MainPage.

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

  1. 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, that will create the database if it does not exist in isolated storage. This is the part of code that will create the affected database.

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

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

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

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

  1. 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 entire piece of code on the screen, we're going to handle the event of the tapping of the three buttons with the following piece of code.

  1. private void btnInsertSample_Tapped(object sender, TappedRoutedEventArgs e)  
  2. {  
  3.    Frame.Navigate(typeof(Insert));  
  4. }  
  5.   
  6. private void btnUpdateSample_Tapped(object sender, TappedRoutedEventArgs e)  
  7. {  
  8.    Frame.Navigate(typeof(Update));  
  9. }  
  10.   
  11. private void btnDeleteSample_Tapped(object sender, TappedRoutedEventArgs e)  
  12. {  
  13.    Frame.Navigate(typeof(Delete));  
  14. } 

For those who have had experience with previous versions, you will notice the differences in navigation among pages. If you used the Windows Phone 7/8 Navigate method of the property NavigationService, then passing through the Uri relative type the name of the page on which to access, with any parameters for the exchange of information among 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 the animation is intended to be viewed the page at display time.

Data entry

At this point, we have implemented all the parts 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 the immediately "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.

After creating the template the page appears, enter the following XAML code to define the graphical interface.

  1. <!-- Pannello del titolo -->  
  2. <StackPanel Grid.Row="0" Margin="19,0,0,0">  
  3.     <TextBlock   
  4.         Text="Sqlite sample"   
  5.         Style="{ThemeResource TitleTextBlockStyle}"   
  6.         Margin="0,12,0,0"/>  
  7.       
  8.     <TextBlock   
  9.         Text="Insert page"   
  10.         Margin="0,-6.5,0,26.5"   
  11.         Style="{ThemeResource HeaderTextBlockStyle}"   
  12.         CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>  
  13. </StackPanel>  
  14.   
  15. <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->  
  16. <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">  
  17.     <Grid.RowDefinitions>  
  18.         <RowDefinition Height="Auto"/>  
  19.         <RowDefinition Height="Auto"/>                  
  20.     </Grid.RowDefinitions>  
  21.                   
  22.     <Grid Grid.Row="0">  
  23.         <Grid.RowDefinitions>  
  24.             <RowDefinition Height="Auto"/>  
  25.             <RowDefinition Height="Auto"/>  
  26.             <RowDefinition Height="Auto"/>  
  27.             <RowDefinition Height="Auto"/>  
  28.         </Grid.RowDefinitions>  
  29.   
  30.         <Grid.ColumnDefinitions>  
  31.             <ColumnDefinition Width="Auto"/>  
  32.             <ColumnDefinition Width="*"/>  
  33.         </Grid.ColumnDefinitions>  
  34.   
  35.         <TextBlock   
  36.                     Grid.Column="0"  
  37.                     Grid.Row="0"  
  38.                     x:Name="tbkName"  
  39.                     FontSize="25"                  
  40.                     Text="Name"  
  41.                     VerticalAlignment="Center"  
  42.         />  
  43.   
  44.         <TextBlock   
  45.                     Grid.Column="0"  
  46.                     Grid.Row="1"                  
  47.                     x:Name="tbkSurname"  
  48.                     FontSize="25"  
  49.                     Text="Surname"  
  50.                     VerticalAlignment="Center"  
  51.         />  
  52.   
  53.         <TextBlock   
  54.                     Grid.Column="0"  
  55.                     Grid.Row="2"                  
  56.                     x:Name="tbkAge"  
  57.                     FontSize="25"  
  58.                     Text="Age"  
  59.                     VerticalAlignment="Center"  
  60.         />  
  61.   
  62.         <TextBox   
  63.                     Grid.Column="1"  
  64.                     Grid.Row="0"  
  65.                     x:Name="tbxName"  
  66.         />  
  67.   
  68.         <TextBox   
  69.                     Grid.Column="1"  
  70.                     Grid.Row="1"                  
  71.                     x:Name="tbxSurname"  
  72.         />  
  73.   
  74.         <TextBox   
  75.                     Grid.Column="1"  
  76.                     Grid.Row="2"                  
  77.                     x:Name="tbxAge"  
  78.                     InputScope="Number"  
  79.         />  
  80.     </Grid>  
  81.   
  82.     <Grid Grid.Row="1">  
  83.         <Grid.RowDefinitions>  
  84.             <RowDefinition Height="20"/>  
  85.             <RowDefinition Height="Auto"/>                      
  86.         </Grid.RowDefinitions>  
  87.           
  88.         <Button  
  89.                    Grid.Row="1"  
  90.                    x:Name="btnInsert"  
  91.                    Content="Insert"  
  92.                    HorizontalAlignment="Center"  
  93.                    Tapped="btnInsert_Tapped"  
  94.         />  
  95.           
  96.     </Grid>  
  97. </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 need to do is to manage nothing but the event of the tapping of the Insert button. With the F7 key, we enter the code in the code editor and insert everything you need within the event of the tapping of the button.

  1. private async void btnInsert_Tapped(object sender, Windows.UI.Xaml.Input.TappedRoutedEventArgs e)  
  2.         {  
  3.             if(Validations.CheckTextBox(tbxName,tbxSurname,tbxAge).Equals(true))  
  4.             {  
  5.                 var dialog = new MessageDialog("Valuing all fields ");  
  6.                 await dialog.ShowAsync();  
  7.             }  
  8.   
  9.             else  
  10.             {  
  11.                 DatabaseManagement.InsertData(tbxName.Text, tbxSurname.Text, int.Parse(tbxAge.Text));  
  12.             }  
  13.         } 

We note that we have included one of something if constructed. This is because you may not enter any data and then touch the button so null values are inserted. To avoid this, you have created a new class called Validations, inside that 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 being interested however, is the code located within the construct else, who will do data entry in the Employee table. In the method InsertData, we find him in the class DatabaseManagement, let's see how it works.

  1. public async static void InsertData(string _name, string _surname, int _age)  
  2.         {  
  3.             var newemployee = new Employee  
  4.             {  
  5.                 Name = _name,  
  6.                 SurName = _surname,  
  7.                 Age = _age,  
  8.             };  
  9.   
  10.             await ConnectionDb().InsertAsync(newemployee);  
  11.         } 

The method is very simple, let's create a new Employee object, where we initialize Name, Age and SurName with the values of the parameters that the method InsertData requires when it is invoked. Later we call the method InsertAsync() asynchronously, in the class SqliteAsyncConnection, passing the object as a parameter to add to the Employee table, or the variable newemployee. We have already seen the method ConnectionDb, with 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. The following shows how to insert a page and the XAML code to define the graphical interface.

  1. <!-- Pannello del titolo -->  
  2. <StackPanel Grid.Row="0" Margin="19,0,0,0">  
  3.     <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>  
  4.     <TextBlock Text="Update page" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>  
  5. </StackPanel>  
  6.   
  7. <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->  
  8. <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">  
  9.     <Grid.RowDefinitions>  
  10.         <RowDefinition Height="Auto"/>  
  11.         <RowDefinition Height="Auto"/>  
  12.         <RowDefinition Height="Auto"/>  
  13.         <RowDefinition Height="Auto"/>  
  14.         <RowDefinition Height="Auto"/>  
  15.         <RowDefinition Height="Auto"/>  
  16.         <RowDefinition Height="Auto"/>                  
  17.     </Grid.RowDefinitions>  
  18.   
  19.   
  20.     <ListBox Grid.Row="0" x:Name="lstUpdatePerson" SelectionChanged="lstUpdatePerson_SelectionChanged">  
  21.         <ListBox.ItemTemplate>  
  22.             <DataTemplate>  
  23.                 <StackPanel>  
  24.                     <StackPanel Orientation="Horizontal">  
  25.                         <TextBlock x:Name="tbkName"  
  26.                                    FontWeight="Bold"  
  27.                                    Text="Name"/>  
  28.   
  29.                         <TextBlock Width="30"/>  
  30.   
  31.                         <TextBlock x:Name="tbkSurname"  
  32.                                    FontWeight="Bold"                                             
  33.                                    Text="Surname"/>  
  34.   
  35.                         <TextBlock Width="30"/>  
  36.   
  37.                         <TextBlock x:Name="tbkAge"  
  38.                                    FontWeight="Bold"                                             
  39.                                    Text="Age"/>  
  40.   
  41.                         <TextBlock Height="50"/>  
  42.                     </StackPanel>  
  43.   
  44.                     <StackPanel Orientation="Horizontal">  
  45.                         <TextBlock   
  46.                             x:Name="tbkFindForName"   
  47.                             Text="{Binding Name}"/>  
  48.   
  49.                         <TextBlock Width="20"/>  
  50.   
  51.                         <TextBlock   
  52.                             x:Name="tbkFindForSurName"   
  53.                             Text="{Binding SurName}"/>  
  54.   
  55.                         <TextBlock Width="20"/>  
  56.   
  57.                         <TextBlock   
  58.                             x:Name="tbkFindForAge"                                      
  59.                             Text="{Binding Age}"/>  
  60.                     </StackPanel>  
  61.                 </StackPanel>  
  62.             </DataTemplate>  
  63.         </ListBox.ItemTemplate>  
  64.     </ListBox>  
  65.       
  66.     <TextBlock Grid.Row="3" x:Name="tbkNewData" Text="New data" HorizontalAlignment="Center"/>  
  67.       
  68.     <StackPanel Grid.Row="4" x:Name="splNewData">  
  69.         <StackPanel x:Name="splNewName" Orientation="Horizontal">  
  70.             <TextBlock Text="Name" VerticalAlignment="Center"/>  
  71.             <TextBlock Width="30"/>  
  72.             <TextBox x:Name="tbxNewName" Width="Auto"/>  
  73.         </StackPanel>  
  74.   
  75.         <StackPanel x:Name="splNewSurName" Orientation="Horizontal">  
  76.             <TextBlock Text="SurName" VerticalAlignment="Center"/>  
  77.             <TextBlock Width="15"/>  
  78.             <TextBox x:Name="tbxNewSurName" Width="Auto"/>  
  79.         </StackPanel>  
  80.   
  81.         <StackPanel x:Name="splNewAge" Orientation="Horizontal">  
  82.             <TextBlock Text="Age" VerticalAlignment="Center"/>  
  83.             <TextBlock Width="40"/>  
  84.             <TextBox x:Name="tbxNewAge" Width="Auto" InputScope="Number"/>  
  85.         </StackPanel>  
  86.     </StackPanel>              
  87.       
  88.     <Button Grid.Row="6" x:Name="btnUpdatePerson" Content="Update" Tapped="btnUpdatePerson_Tapped"/>  
  89. </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 of the tapping of the Update button plus the SelectionChanged event of the ListBox, who will see all the data available in the Employee table and the OnNavigatedTo override. The latter is done when 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.

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

Analyze the code for the 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 among pages. Returning to the subject of navigation among pages, for the exchange of information between them, we have the ability to use within the parameters of the Navigate method, as we saw, or the system I used. In other words, create a static class and define all the necessary fields for use. We set the value of the NewName field, with the value of the property SelectedValue of the ListBox and TextBox controls for 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 need to make a cast, in other words convert the type Object returned in Employee, then use the properties of the class, we will need 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 the objects within the SelectionChanged event, we see the code for updating data.

  1. private async void btnUpdatePerson_Tapped(object sender, TappedRoutedEventArgs e)  
  2. {  
  3.     await Validations.MessageConfirmDeleteoUpdatePerson("Vuoi aggiornare i dati?");  
  4.   
  5.     if (Validations.result.Equals(true))  
  6.     {  
  7.         if (Validations.CheckTextBox(tbxNewName, tbxNewSurName, tbxNewAge).Equals(true))  
  8.         {  
  9.             var dialog = new MessageDialog("Valorizzare tutti i campi");  
  10.             await dialog.ShowAsync();  
  11.         }  
  12.   
  13.         else  
  14.         {  
  15.             DatabaseManagement.UpdateData(Parametri_ricerca.NewName,tbxNewName.Text);  
  16.         }  
  17.     }    
  18. } 

To tap the button btnUpdate, we display a MessageDialog for the user, where it asks 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.

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

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

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

We use the extension methods Where(), where depending on a condition, (in our example, we want to change the name of an employee "employee") given by parameter _name, then we recover, using 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 need to enter the code override OnNavigatedTo; can be found in the Record Navigation Helper present in .cs code file.

  1. protected async override void OnNavigatedTo(NavigationEventArgs e)  
  2.         {  
  3.            await  DatabaseManagement.LoadData(lstUpdatePerson);  
  4.   
  5.             this.navigationHelper.OnNavigatedTo(e);  
  6.         } 

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

  1. public async static Task LoadData(ListBox box)  
  2. {  
  3.     var employee = new List<Employee>();  
  4.     var query = ConnectionDb().Table<Employee>();  
  5.     var result = await query.ToListAsync();  
  6.   
  7.     foreach (var person in result)  
  8.     {  
  9.         employee.Add(new Employee { Name = person.Name, SurName = person.SurName, Age = person.Age });  
  10.     }  
  11.   
  12.     box.ItemsSource = employee;  
  13. } 

It is a task with that 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 nearly identical to that for the update, the only difference is called a query differently when 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.

  1. <!-- Pannello del titolo -->  
  2. <StackPanel Grid.Row="0" Margin="19,0,0,0">  
  3.     <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>  
  4.     <TextBlock Text="Delete page" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>  
  5. </StackPanel>  
  6.   
  7.   
  8. <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->  
  9.     <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">  
  10.         <Grid.RowDefinitions>  
  11.             <RowDefinition Height="Auto"/>  
  12.             <RowDefinition Height="Auto"/>  
  13.             <RowDefinition Height="Auto"/>  
  14.             <RowDefinition Height="Auto"/>  
  15.             <RowDefinition Height="Auto"/>  
  16.         </Grid.RowDefinitions>  
  17.           
  18.     <ListBox Grid.Row="0" x:Name="lstDeletePerson" SelectionChanged="lstDeletePerson_SelectionChanged">  
  19.         <ListBox.ItemTemplate>  
  20.             <DataTemplate>  
  21.                 <StackPanel>  
  22.                     <StackPanel Orientation="Horizontal">  
  23.                         <TextBlock x:Name="tbkName"  
  24.                                    FontWeight="Bold"  
  25.                                    Text="Name"/>  
  26.   
  27.                         <TextBlock Width="30"/>  
  28.   
  29.                         <TextBlock x:Name="tbkSurname"  
  30.                                    FontWeight="Bold"                                             
  31.                                    Text="Surname"/>  
  32.   
  33.                         <TextBlock Width="30"/>  
  34.   
  35.                         <TextBlock x:Name="tbkAge"  
  36.                                    FontWeight="Bold"                                             
  37.                                    Text="Age"/>  
  38.   
  39.                         <TextBlock Height="50"/>  
  40.                     </StackPanel>  
  41.   
  42.                     <StackPanel Orientation="Horizontal">  
  43.                         <TextBlock   
  44.                             x:Name="tbkFindForName"   
  45.                             Text="{Binding Name}"/>  
  46.   
  47.                         <TextBlock Width="20"/>  
  48.   
  49.                         <TextBlock   
  50.                             x:Name="tbkFindForSurName"   
  51.                             Text="{Binding SurName}"/>  
  52.   
  53.                         <TextBlock Width="20"/>  
  54.   
  55.                         <TextBlock   
  56.                             x:Name="tbkFindForAge"   
  57.                             Text="{Binding Age}"/>  
  58.                     </StackPanel>  
  59.                 </StackPanel>  
  60.             </DataTemplate>  
  61.         </ListBox.ItemTemplate>  
  62.     </ListBox>  
  63.   
  64.     <ScrollViewer Grid.Row="2">  
  65.         <StackPanel Grid.Row="2" x:Name="splData">  
  66.             <StackPanel x:Name="splName" Orientation="Horizontal">  
  67.                 <TextBlock Text="Name" VerticalAlignment="Center"/>  
  68.                 <TextBlock Width="30"/>  
  69.                 <TextBox x:Name="tbxName" Width="Auto"/>  
  70.             </StackPanel>  
  71.   
  72.             <StackPanel x:Name="splSurName" Orientation="Horizontal">  
  73.                 <TextBlock Text="SurName" VerticalAlignment="Center"/>  
  74.                 <TextBlock Width="15"/>  
  75.                 <TextBox x:Name="tbxSurName" Width="Auto"/>  
  76.             </StackPanel>  
  77.   
  78.             <StackPanel x:Name="splAge" Orientation="Horizontal">  
  79.                 <TextBlock Text="Age" VerticalAlignment="Center"/>  
  80.                 <TextBlock Width="40"/>  
  81.                 <TextBox x:Name="tbxAge" Width="Auto"/>  
  82.             </StackPanel>  
  83.         </StackPanel>  
  84.     </ScrollViewer>  
  85.   
  86.     <Button Grid.Row="4" x:Name="btnDeletePerson" Content="Delete" Tapped="btnDeletePerson_Tapped"/>  
  87. </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 need to handle the event of the tapping of 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.

  1. private void lstDeletePerson_SelectionChanged(object sender, SelectionChangedEventArgs e)  
  2. {              
  3.     tbxName.Text = ((Employee)(lstDeletePerson.SelectedValue)).Name;  
  4.     tbxSurName.Text = ((Employee)(lstDeletePerson.SelectedValue)).SurName;  
  5.     tbxAge.Text = ((Employee)(lstDeletePerson.SelectedValue)).Age.ToString();  
  6. }  
  7.   
  8. private async void btnDeletePerson_Tapped(object sender, Windows.UI.Xaml.Input.TappedRoutedEventArgs e)  
  9. {  
  10.     await Validations.MessageConfirmDeleteoUpdatePerson("Vuoi eliminare i dati?");  
  11.   
  12.     if(Validations.result.Equals(true))  
  13.     {  
  14.         DatabaseManagement.DeleteData(tbxName.Text);  
  15.     }  
  16. } 

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

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

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

  1. await ConnectionDb().DeleteAsync(deleteemployee); 

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

  1. protected async override void OnNavigatedTo(NavigationEventArgs e)  
  2.         {  
  3.            await  DatabaseManagement.LoadData(lstDeletePerson);  
  4.   
  5.             this.navigationHelper.OnNavigatedTo(e);  
  6.         } 

I remembered to override OnNavigatedTo; you find it in the Record Navigation Helper present in the .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 other classes must be added to support data, research and control enhancement before doing 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.

  1. using SQLite;  
  2. using System.Collections.Generic;  
  3. using System.IO;  
  4. using System.Threading.Tasks;  
  5. using Windows.Storage;  
  6. using Windows.UI.Xaml.Controls;  
  7.   
  8.   
  9. namespace SqlLite_Sample.Classes  
  10. {  
  11.     public static class DatabaseManagement  
  12.     {  
  13.         private static SQLiteAsyncConnection ConnectionDb()  
  14.         {  
  15.             var conn = new SQLite.SQLiteAsyncConnection(Path.Combine(ApplicationData.Current.LocalFolder.Path, "people.db"),true);  
  16.             return conn;  
  17.         }  
  18.   
  19.      public static async void CreateDatabase()  
  20.         {  
  21.             var person = await ConnectionDb().CreateTableAsync<Employee>();  
  22.         }  
  23.   
  24.         public async static Task LoadData(ListBox box)  
  25.         {  
  26.             var employee = new List<Employee>();  
  27.             var query = ConnectionDb().Table<Employee>();  
  28.             var result = await query.ToListAsync();  
  29.   
  30.             foreach (var person in result)  
  31.             {  
  32.                 employee.Add(new Employee { Name = person.Name, SurName = person.SurName, Age = person.Age });  
  33.             }  
  34.   
  35.             box.ItemsSource = employee;  
  36.         }  
  37.   
  38.         public async static void InsertData(string _name, string _surname, int _age)  
  39.         {  
  40.             var newemployee = new Employee  
  41.             {  
  42.                 Name = _name,  
  43.                 SurName = _surname,  
  44.                 Age = _age,  
  45.             };  
  46.   
  47.             await ConnectionDb().InsertAsync(newemployee);  
  48.         }  
  49.   
  50.         public async static void DeleteData(string _name)  
  51.         {  
  52.             var deleteemployee = await ConnectionDb().Table<Employee>().Where(w => w.Name.Equals(_name)).FirstOrDefaultAsync();  
  53.             deleteemployee.Name = _name;  
  54.             await ConnectionDb().DeleteAsync(deleteemployee);  
  55.         }  
  56.   
  57.         public async static void UpdateData(string _name, string _newname)  
  58.         {  
  59.             var updateemployee = await ConnectionDb().Table<Employee>().Where(w => w.Name.Equals(_name)).FirstOrDefaultAsync();  
  60.             updateemployee.Name = _newname;  
  61.             await ConnectionDb().UpdateAsync(updateemployee);  
  62.         }  
  63.     }  
  64. } 

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

  1. using System;  
  2. using System.Linq;  
  3. using System.Threading.Tasks;  
  4. using Windows.UI.Popups;  
  5. using Windows.UI.Xaml.Controls;  
  6.   
  7. namespace SqlLite_Sample.Classes  
  8. {  
  9.     public static class Validations  
  10.     {  
  11.         public static bool result;  
  12.   
  13.         public static bool CheckTextBox(params TextBox []textboxs)  
  14.         {  
  15.             var result = false;  
  16.   
  17.             foreach (var textBox in textboxs.Where(w => w.Text.Equals("")))  
  18.             {  
  19.                 result = textBox.Text.Equals("");                 
  20.             }  
  21.             return result;  
  22.         }  
  23.   
  24.         public static async Task MessageConfirmDeleteoUpdatePerson(string message)  
  25.         {  
  26.             var dialog = new MessageDialog(message);  
  27.             dialog.Commands.Add(new UICommand("No", new UICommandInvokedHandler(Command)));  
  28.             dialog.Commands.Add(new UICommand("Yes", new UICommandInvokedHandler(Command)));  
  29.             await dialog.ShowAsync();       
  30.         }  
  31.   
  32.         private static void Command(IUICommand command)  
  33.         {  
  34.             if (command.Label.Equals("Yes"))  
  35.             {  
  36.                 result = true;  
  37.             }             
  38.         }  
  39.     }  
  40. } 

We shall not stop to explain what this code does because it is outside the scope of this article. The important thing is to know about the running of the controls that I specified before, is the fact that all the TextBox controls are properly valued, more the control MessageDialog that alerts you in the case of errors. Also in the Classes folder, create a class named parametri ricerca and here we replace the existing code with the following.

  1. namespace SqlLite_Sample.Classes  
  2. {  
  3.     public static class Parametri_ricerca  
  4.     {  
  5.         public static string FindData { get; set; }  
  6.         public static string TypeSearch { get; set; }  
  7.         public static string NewName { get; set; }  
  8.     }  
  9. } 

Returning to the navigation among 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 necessary namespace since there are two additional folders and added them to the classes. We put in the order:

Screens Insert, Update and Delete.xaml.cs

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

Screen MainPage.xaml.cs

  1. using Sqlite_Sample.Screen;  
  2. using SqlLite_Sample.Classes; 

Architecture Compilation

The last thing to do, before compiling the solution is to change the architecture of the target platform. This is because the SQLite engine 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 we 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 need to select the target platform second, where we are trying the application. If we use a tablet, or a phone with ARM processor, we need 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 need 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 ARM.

Test the Application

At this point we are ready to compile and run the application. Press the 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 do 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 the figure. 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 will only change the name of the employee for convenience, but by changing the method code UpdateData() 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 see 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 need 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 saw the basics of SQLite, how to install the engine, the library Sqlite-net and saw the most common operations (also known as code first), such as inserting, updating and deleting data from a table of a database. We saw that the library Sqlite-net supports LINQ, although at the moment only a few operators are supported. 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 need 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

You can download the complete sample code from the MSDN gallery at this link.


Similar Articles