Post
Ask Question

SQLite on Windows Phone 8.1: Part 2

Introduction

Please go through the first part of the series at:

Part One: SQLite on Windows Phone 8.1

We continue learning using Sqlite. We saw in short the subjects of the previous article before continuing. That described the installation of the Sqlite engine, the test project creation, the installation of the available Sqlite-net on Nuget, how to create the database on the telephone Storage to the first application start, to end with the insertion, the modification and the cancellation of the data from the database. In this article we will see how to search for information in several tables. The current Sqlite version is 8.3.9. You find to this link, we will still base ourselves on the version 3.8.7.4, used in the previous article, we will see in the order:
  • Creation Finddata screen
  • Creation Result screen
  • Creation Job class
  • Creation RoleUser class
  • Modification of the class Parametri_ricerca
  • Insertion of the necessary namespace
  • Implementation of the code in the class DatabaseManagement
  • Modification Insert class
  • Modification Update class
  • Modification MainPage class
  • Test application
  • Conclusion
  • Other resources

Creation Finddata screen

Before continuing, some clear advice is to read the first part to have what was executed. All the code of the example together with the project is available from this link. Returning to the project, place the slider on the folder "Screen", feel the mouse right and choose the command "add" and after "new element". In the next dialogue window, we search for the template "basic page", as shown in the following figure.

 Image 1.1 The available template for the development Windows Phone Store. 

Rename the screen in Finddata and confirm with the button add. After the loading of the screen in the Visual Studio IDE, we modify the existing XAML code with this.

  1. <Page x:Class="SqlLite_Sample.Screen.Finddata" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"  xmlns:local="using:SqlLite_Sample.Screen" xmlns:d="http://schemas.microsoft.com/expression/blend/2008"  xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="d"Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">  
  2.       <Grid x:Name="LayoutRoot">  
  3.       <Grid.ChildrenTransitions>              
  4.             <TransitionCollection>                  
  5.              <EntranceThemeTransition/>               
  6.             </TransitionCollection>           
  7.         </Grid.ChildrenTransitions>  
  8.           <Grid.RowDefinitions>              
  9.             <RowDefinition Height="Auto"/>               
  10.             <RowDefinition Height="*"/>           
  11.         </Grid.RowDefinitions>  
  12.          <!-- Pannello del titolo -->           
  13.         <StackPanel Grid.Row="0" Margin="19,0,0,0">              
  14.             <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>               
  15.             <TextBlock Text="Find data page" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>           
  16.         </StackPanel>  
  17.       <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->           
  18.         <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">              
  19.             <Grid.RowDefinitions>                  
  20.                 <RowDefinition Height="Auto"/>                   
  21.                 <RowDefinition Height="Auto"/>                   
  22.                 <RowDefinition Height="Auto"/>               
  23.             </Grid.RowDefinitions>  
  24.             <StackPanel x:Name="splRadioButton" Grid.Row="0">                  
  25.                 <RadioButton x:Name="rbnName"  IsChecked="True"  Content="Name"  Tapped="rbnName_Tapped"/>                              
  26.                  <RadioButton  x:Name="rbnRole" IsChecked="False"  Content="Role"  Tapped="rbnName_Tapped"/>                            
  27.                    <RadioButton x:Name="rbnAge"IsChecked="False" Content="Age" Tapped="rbnName_Tapped"/>                                 
  28.             </StackPanel>                            
  29.             <StackPanel Grid.Row="1">                  
  30.             <TextBlock  x:Name="tbkName" FontSize="20"  Text="Find data" HorizontalAlignment="Center" VerticalAlignment="Center"/>  
  31.                 <TextBox   x:Name="tbxFindForName"/>             
  32.                   </StackPanel>  
  33.             <Button Grid.Row="2" x:Name="btnFind" Content="Find" HorizontalAlignment="Center" Tapped="btnFind_Tapped"/>          
  34.         </Grid>      
  35.     </Grid>  
  36. </Page>  
The following is how the screen must be after the XAML code insertion.



Image 1.2 The screen Find data page.

We have three Radio Button controls, depending on what was selected, that can execute a research typology. In our case for the name, role and age we will go to type in a term inside the TextBox control, placed under the given TextBlock Find data and at the end of the mediating Button Find insertion will start the research procedure depending on what we have chosen. With the F7 key, we go to the code editor and modify the builder of the class Finddata as follows:
  1. public Finddata()      
  2. {          
  3.    this.InitializeComponent();
       this.navigationHelper = new NavigationHelper(this);      
       this.navigationHelper.LoadState += this.NavigationHelper_LoadState;        
       this.navigationHelper.SaveState += this.NavigationHelper_SaveState;  
  4.    foreach (var control in splRadioButton.Children.OfType<RadioButton>().Where(w => w.Content.Equals("Name")))         
  5.    {      
  6.       Parametri_ricerca.TypeSearch = control.Content.ToString();        
  7.    }     
  8.  }  
What is executed? We set up the default searches for the name that is to provide at once the user the possibility of inserting a name. Everything will increase the value of the TypeSearch property of the class Parametri_ricerca with the property value Content of Radio Button that corresponds to the term "Name". We must still manage the Tapped Find button event and the Tapped event of the Radio Button controls. To do that we insert inside the class the following C# code.
  1. private async void btnFind_Tapped(object sender, TappedRoutedEventArgs e)  
  2. {      
  3.     if (Validations.CheckTextBox(tbxFindForName).Equals(true))   
  4.     {          
  5.         var dialog = new MessageDialog("Inserisci un termine di ricerca!");        
  6.         await dialog.ShowAsync();         
  7.        if (Parametri_ricerca.TypeSearch.Equals(""))     
  8.        {              
  9.            var dialog1 = new MessageDialog("Inserisci un termine di ricerca!");            
  10.            await dialog1.ShowAsync();          
  11.        }      
  12.     }     
  13.     else { Parametri_ricerca.FindData = tbxFindForName.Text;         
  14.        Frame.Navigate(typeof(Result));      
  15.     }  
  16. }  
  17. private void rbnName_Tapped(object sender, TappedRoutedEventArgs e)   
  18. {      
  19.     var radiobuttonTapped = sender as RadioButton;     
  20.     Parametri_ricerca.TypeSearch = radiobuttonTapped.Content.ToString();  
  21. }  
We will now analyze the previous code. We have the Tapped event of the Find button, with which we will execute two validations and for the first if inside the TextBox control there is a value, while the second is on the variable value TypeSearch. If one of the validations have an unsuccessful result, we will inform the mediating user a MessageDialog. If both validations have a positive result, in the else code, we go to increase the value of the FindData property of the class Parametri_ricerca with the control value TextBox tbxFindForName and will be then led to the Result screen that we have said before, while for the event Tapped controls RadioButton recover by the parameter sender of which of the three has selected the user, then exploiting the variable TypeSearch it will take one of the values ​​of the contents of each RadioButton.
 
Creation Result screen

Finished this activity in the class Finddata can be responsible for creating the Result screen. In explores solutions, create the Result screen as we have done for Finddata and in the XAML file we replace the existing code with the following:
  1. <Page x:Class="SqlLite_Sample.Screen.Result"   
  2.     xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"   
  3.     xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"    
  4.     xmlns:local="using:SqlLite_Sample.Screen"    
  5.     xmlns:d="http://schemas.microsoft.com/expression/blend/2008"       
  6.     xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"      
  7.     mc:Ignorable="d"       
  8.     Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">  
  9.       <Grid x:Name="LayoutRoot">  
  10.              <Grid.ChildrenTransitions>              
  11.             <TransitionCollection>                  
  12.              <EntranceThemeTransition/>               
  13.             </TransitionCollection>           
  14.        </Grid.ChildrenTransitions>  
  15.         <Grid.RowDefinitions>              
  16.             <RowDefinition Height="Auto"/>               
  17.             <RowDefinition Height="*"/>           
  18.         </Grid.RowDefinitions>          
  19.         <!-- Pannello del titolo -->   
  20.         <StackPanel Grid.Row="0" Margin="19,0,0,0">              
  21.             <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>               
  22.             <TextBlock Text="Result" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="                           {ThemeResource PivotHeaderItemCharacterSpacing}"/>           
  23.         </StackPanel>  
  24.          <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->           
  25.         <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">              
  26.             <Grid.RowDefinitions>                  
  27.                 <RowDefinition Height="Auto"/>               
  28.             </Grid.RowDefinitions>               
  29.             <ListBox Grid.Row="0" x:Name="lstFindPerson">                  
  30.                 <ListBox.ItemTemplate>                      
  31.                     <DataTemplate>                          
  32.                         <StackPanel>                              
  33.                             <StackPanel Orientation="Horizontal">                                  
  34.                               <TextBlock x:Name="tbkName" FontWeight="Bold"Text="Name"/>  
  35.                              <TextBlock Width="30"/>  
  36.                              <TextBlock x:Name="tbkRole" FontWeight="Bold" Text="Role"/>  
  37.                              <TextBlock Width="30"/>  
  38.                              <TextBlock x:Name="tbkAge"   FontWeight="Bold"  Text="Age"/>  
  39.                             <TextBlock Height="50"/>   
  40.                    </StackPanel>  
  41.                     <StackPanel Orientation="Horizontal">                               
                                   <TextBlock  x:Name="tbkFindForName"Text="{Binding Name}"/>  
  42.                             <TextBlock Width="20"/>  
  43.                              <TextBlockx:NameTextBlockx:Name="tbkFindForRole" Text="{Binding Role}"/>  
  44.                              <TextBlock Width="20"/>  
  45.                              <TextBlock   x:Name="tbkFindForAge" Text="{Binding Age}"/>   
  46.                        </StackPanel>                          
  47.                        </StackPanel>                      
  48.                         </DataTemplate>                   
  49.                       </ListBox.ItemTemplate>             
  50.                    </ListBox>           
  51.             </Grid>    
  52.        </Grid>
  53. </Page>  
How the screen must be here after the XAML code insertion.



Image 1.3 The screen Result page.

What we have created is simply a ListBox control with inside two StackPanels. The first will have three TextBlock controls with static text, that is name, Role and age. In the second StackPanel, there are three TextBox controls with the Text property in binding to the Name properties, Role and Age of one RoleUser collection, that is the class that will be responsible for showing us the research result, also this class will create it during the article. Ended the graphic part, with the F7 key we go to the code editor, we modify the builder of the class Result as follows.

  1. public Result()   
  2. {       
  3.   this.InitializeComponent();  
  4.   this.navigationHelper = new NavigationHelper(this);      
  5.   this.navigationHelper.LoadState += this.NavigationHelper_LoadState;      
  6.   this.navigationHelper.SaveState += this.NavigationHelper_SaveState;  
  7.   DatabaseManagement.FindForName(Parametri_ricerca.FindData,  Parametri_ricerca.TypeSearch, lstFindPerson);   
  8. }  
The part that interests in us is the last code line, in other words:
  1. DatabaseManagement.FindForName(Parametri_ricerca.FindData, Parametri_ricerca.TypeSearch, lstFindPerson);  
This method requires three parameters, the term of research that is what we will need to be to insert inside the TextBox control in the Finddata screen, the second parameter is the type of research that can be in our case for name, role or age. The last parameter is the reference to the control ListBox belonging to the Result screen. We will see the code because header when we must modifying in the class DatabaseManegement.

Creation class Job

After defining also the Result screen, he arrived the moment to create the classes necessary for the research function. We go back to the test project, we position the slider over the Classes folder, feel the mouse right and we choose the commands "add" and immediately later "class" and call her with the name Job. This class does not do anything else but adds a role for every user that we will need to be to insert in the created database to the first application start, by a new table that will have I sharpen the name of the class that we will create in this passage. To the created class, we go to replace the current C# code with this following.
  1. using SQLite;  
  2. namespace SqlLite_Sample.Classes  
  3. {      
  4.     class Job         
  5.     {          
  6.         [SQLite.PrimaryKey,AutoIncrement]  
  7.         public int Id { get; set; }  
  8.         [MaxLength(30)]           
  9.         public string Name { get; set; }  
  10.         [MaxLength(30)]           
  11.         public string Role { get; set; }  
  12.         [MaxLength(3)]           
  13.         public int Age { get; set; }       
  14.     }  
  15. }  
With this code we have simply defined four properties. The first, int that will be the primary key. This by the PrimaryKey.Autoincrement attribute that will think about automatically increasing the property value Id. The second property will be used to define the name, the third the role and the age finishes it. We see that for the Name and Role properties, an attribute MaxLength (30) has been defined, that means that both properties will be not able to have a lace value of beyond 30 characters, finishes it int, serves to memorize the user's age, with a maximum of three characters by the attribute MaxLength (3).

Creation class RoleUser

Create the last one of the two necessary classes with the same procedure that we have previously used always in the Classes folder, the difference that this time we will name her RoleUser. We go to replace the current C# code with the following.
  1. using SQLite;  
  2. namespace SqlLite_Sample.Classes  
  3. {       
  4.     class RoleUser      
  5.     {           
  6.       [MaxLength(30)]          
  7.         public string Name { get; set; }  
  8.         [MaxLength(30)]          
  9.         public string Role { get; set; }  
  10.         [MaxLength(3)]           
  11.         public int Age { get; set; }     
  12.     }  
  13. }  
Also in this case, we have defined three properties, two strings and one int with the attributes used for the properties of the class job on the limit of the 3 and 30 characters. This is the class we will use in binding if you remember on the definite TextBox controls in the class Result.

Modification of the class Parametri_ricerca

We must now modify and insert a few properties in this class, since they will serve us during the article to manage update of the information about the user. With the slider positioned on the Classes folder we open the Parametri_ricerca.cs file and modify the existing C# 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.         public static string NewSurName { get; set; }   
  9.         public static int NewAge { get; set; }   
  10.     }   
  11. }  
Insertion of the necessary namespace

Before modifying the C# code of the class DataBaseManagement, we must insert in the classes' job and RoleUser the following Namespace.

using SqlLite_Sample.Classes;

This is necessary for being able to do use of the class Parametri_ricerca, positioned inside the Classes folder.

Implementation of the code in the class DatabaseManagement

We now go to modify the C# code of the class DatabaseManagement. We will go to modify the Insert, Delete and UpdateData methods so as to manage also the new table that will be created to the first start, in other words the table job. In explores solutions double click with the mouse on the DatabaseManagement file and modify the CreateDatabase method as follows.
  1. public static async void CreateDatabase()  
  2. {        
  3.    await ConnectionDb().CreateTableAsync<Employee>();        
  4.    await ConnectionDb().CreateTableAsync<Job>();   
  5. }  
And been a new line of code that will not do anything else but create as advance a table called job inside the database people.db join together. We now modify the InsertData method how he follows.
  1. public async static void InsertData(string _name, string _surname, int _age, string _role)  
  2. {      
  3.    var newemployee = new Employee      
  4.    {           
  5.      Name = _name,   
  6.      SurName = _surname,          
  7.      Age = _age,       
  8.    };  
  9.    var newjob = new Job      
  10.    {           
  11.       Name = newemployee.Name,          
  12.       Role = _role,           
  13.       Age = newemployee.Age,      
  14.    };  
  15. await ConnectionDb().InsertAsync(newemployee);     
  16. await ConnectionDb().InsertAsync(newjob);  
  17. }  
We analyze the code in detail. A difference with the previous article, we have added a quarter parameter to the method, where the role of the user, must be inserted and a new request of the class job, increasing the value of the Name, Role and Age properties respectively with the newemployee.Name values, the _role parameter of the InsertData and newemployee.Age method and finally we go to memorize and make the information so permanent in the database by the InserAsync method, passing as parameters the instance of the classes Employee and Job. We must modify also the DeleteData method code so as to remove the user at issue from both database tables, the DeleteData method must be modified as follows:
  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.    var deleterole = await ConnectionDb().Table<Job>().Where(w => w.Id.Equals(deleteemployee.Id)).FirstOrDefaultAsync();       
  7.    await ConnectionDb().DeleteAsync(deleterole);  
  8. }  
And been this code part join together
  1. var deleterole = await ConnectionDb().Table<Job>().Where(w => w.Id.Equals(deleteemployee.Id)).FirstOrDefaultAsync();      
  2. await ConnectionDb().DeleteAsync(deleterole);  
where we go to verify by the property Equals method Id of the tables Employee and Job, if it is found a correspondence, the user's data will be removed from the table Job, everything with the DeleteAsync method, deleterole of job type coming as I argue the variable. We now modify the UpdateData method code, replacing the current one with the following.
  1. public async static void UpdateData(string _name, string _newname, string _surname, string _newsurname, int _age, int _newage, string _newrole)  
  2. {     
  3.    var updateemployee = await ConnectionDb().Table<Employee().Where(w => w.Name.Equals(_name) && w.SurName.Equals(_surname) && w.Age.Equals(_age)).FirstOrDefaultAsync();       
  4.    updateemployee.Name = _newname;      
  5.    updateemployee.SurName = _newsurname;       
  6.    updateemployee.Age = _newage;       
  7.    await ConnectionDb().UpdateAsync(updateemployee);  
  8.    var updaterole = await ConnectionDb().Table<Job>().Where(w=> w.Id.Equals(updateemployee.Id)).FirstOrDefaultAsync();      
  9.    updaterole.Name = updateemployee.Name;      
  10.    updaterole.Age = updateemployee.Age;       
  11.    updaterole.Role = _newrole;       
  12.    await ConnectionDb().UpdateAsync(updaterole); 
  13. } 
And been the management join together for update of the table job exactly as for the DeleteData method. In other words going to verify the Id property with the Equals method and if the comparison between the two properties has positive result, Name, Age and Role properties of the table job will assume the new values of updateemployee, Name, updateemployee.Age and parameter_newrole. We now insert the FindForName method; this method there was not in the previous article, as maids to execute the true and real research.
  1. public async static void FindForName(string _name, string _typesearch, ListBox _box)    
  2. {         
  3.    var employee = new List<Employee>();       
  4.    var job = new List<Job>();        
  5.    var roleuser = new List<RoleUser>();    
  6.    var queryName = ConnectionDb().Table<Employee>();            
  7.    var resultName = await queryName.ToListAsync();    
  8.    var queryJob = ConnectionDb().Table<Job>();        
  9.    var resultjob = await queryJob.ToListAsync();    
  10.    foreach (var findperson in resultName)        
  11.    {             
  12.       employee.Add(new Employee { Name = findperson.Name, SurName = findperson.SurName, Age = findperson.Age });        
  13.    }    
  14.    foreach (var findjob in resultjob)        
  15.    {         
  16.       job.Add(new Job { Name = findjob.Name, Role = findjob.Role, Age = findjob.Age });      
  17.    }         
  18.    switch (_typesearch)       
  19.    {            
  20.     case "Name":     
  21.    var resultForName = employee .Join(job, newname => newname.Id, newjob => newjob.Id, (newname, newjob) =>   new { newjob.Name, newjob.Role, newjob.Age }).Where(w => w.Name.Equals(_name))  .Distinct();    
  22.        foreach (var newjob in resultForName)      
  23.        {                     
  24.        roleuser.Add(new RoleUser { Name = newjob.Name, Role = newjob.Role, Age = newjob.Age });              
  25.        }    
  26.       break;    
  27.     case "Role":    
  28.  var resultForRole = employee  .Join(job, newname => newname.Id, newjob => newjob.Id, (newname, newjob) =>   new { newjob.Name, newjob.Role, newjob.Age }).Where(w => w.Role.Equals(_name))                             .Distinct();    
  29.   foreach (var newjob in resultForRole)      
  30.     {          
  31.      roleuser.Add(new RoleUser { Name = newjob.Name, Role = newjob.Role, Age = newjob.Age });               
  32.    }    
  33.      break;    
  34.     case "Age":     
  35. var resultAge = employee  .Join(job, newname => newname.Id, newjob => newjob.Id, (newname, newjob) =>new { newjob.Name, newjob.Role, newjob.Age }).Where(w => w.Age.Equals(int.Parse(_name)))                             .Distinct();    
  36.    foreach (var newjob in resultAge)      
  37.     {          
  38.      roleuser.Add(new RoleUser { Name = newjob.Name, Role = newjob.Role, Age = newjob.Age });                
  39.      }    
  40.             break;        
  41.      }    
  42.     _box.ItemsSource = roleuser;    
  43. }             
We analyze the previous code. Three collections have been defined: one Employee, one of Job type and finishes it RoleUser. 
  1. var employee = new List<Employee>();var job = new List<Job>();var roleuser = new List<RoleUser>();        
This collection except for the RoleUser one will be then increased the value with all the data coming from the tables Employee and Job present in the database people.db. We go to recover the tables Employee and Job later by this code.
  1. var queryName = ConnectionDb().Table<Employee>();var queryJob = ConnectionDb().Table<Job>();  
Exactly with the Table<T>() method, we go to recover the true and real information present in the tables. For finish convert all the content into a list with the following code lines.
  1. var resultName = await queryName.ToListAsync();     var resultjob = await queryJob.ToListAsync();      
Doing so, we convert the information inside the table into a collection that we can then manipulate to liking depending on our demands. Since as I previously see, SqliteNet does not fully support Linq and all Extension Method we must bypass the problem in another way, make it this way, with two foreach cycles increase the value of collection employee and job initially create.
  1. foreach (var findperson in resultName)
  2. {     
  3. `  employee.Add(new Employee { Name = findperson.Name, SurName = findperson.SurName, Age = findperson.Age });
  4. }  
  5. foreach (var findjob in resultjob)
  6. {     
  7.    job.Add(new Job { Name = findjob.Name, Role = findjob.Role, Age = findjob.Age });
  8. }  
Observing the previous code, will understand because we have had converting the information into a list of tables with the ToListAsync () method exposed by the bookshop SqliteNet. We now have all the data in memory to be able to execute the wished research. We now analyze the most important part than the FindForName method, the switch meaning.
  1. switch (_typesearch)  
  2. {       
  3.    case "Name":  
  4.  var resultForName = employee  .Join(job, newname => newname.Id, newjob => newjob.Id, (newname, newjob) => new { newjob.Name, newjob.Role, newjob.Age }).Where(w => w.Name.Equals(_name))  .Distinct();  
  5.  foreach (var newjob in resultForName)   
  6.    {              
  7.     roleuser.Add(new RoleUser { Name = newjob.Name, Role = newjob.Role, Age = newjob.Age });        
  8.    }  
  9.         break;  
  10.     case "Role":          
  11.   var resultForRole = employee  .Join(job, newname => newname.Id, newjob => newjob.Id, (newname, newjob) => new { newjob.Name, newjob.Rol   e, newjob.Age }).Where(w => w.Role.Equals(_name))  .Distinct();  
  12.    foreach (var newjob in resultForRole)          
  13.    {             
  14.      roleuser.Add(new RoleUser { Name = newjob.Name, Role = newjob.Role, Age = newjob.Age });        
  15.     }  
  16.         break;  
  17.     case "Age":         
  18.   var resultAge = employee  .Join(job, newname => newname.Id, newjob => newjob.Id, (newname, newjob) =>  new { newjob.Name, newjob.Role,   newjob.Age }).Where(w => w.Age.Equals(int.Parse(_name)))  .Distinct();  
  19.   foreach (var newjob in resultAge)           
  20.    {               
  21.      roleuser.Add(new RoleUser { Name = newjob.Name, Role = newjob.Role, Age = newjob.Age });        
  22.    }  
  23.           
  24. break;  
  25. }  
We go to verify the variable value _typesearch that can assume three values, Name, Role or Age. In each of the cases he goes to execute a query Linq on collection employee and job, we will not fully explain Linq why lies outside the article at issue. Query do not do anything else but join collection join by extension method by the Id property of both and finally with extension method where go to verify a determinate condition, that is the parameter value _name, if it is found one or more correspondences the information that correspond to the specified condition will be selected and extrapolated. Finally we increase the value of collection roleuser with the final result of query with the Name, Role and Age values and end with increasing the value of the ListBox _box parameter with the value of collection roleuser and explained here because we pass on to the FindForName method the control reference ListBox present in the Result screen.

_box.ItemsSource = roleuser;

Modification Insert class

Have finished the modifications in the class DatabaseManagement, must still execute some precautions in screens of Insert, Update and MainPage will see thing in detail. Leave from the class Insert, will add a TextBox control where we have the possibility of inserting a role for every new user whom we insert in the table Job in the Database people.db. We go back to our project, position the slider on the Screen folder and do click double with the mouse on the Insert.xaml file. Opened the file replace the existing XAML code with this.
  1. <Page x:Class="SqlLite_Sample.Screen.Insert"   
  2.           xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"      
  3.           xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"     xmlns:local="using:SqlLite_Sample.Screen"      
  4.           xmlns:d="http://schemas.microsoft.com/expression/blend/2008"       
  5.           xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"      
  6.           mc:Ignorable="d"     Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">  
  7.        <Grid x:Name="LayoutRoot">  
  8.        <Grid.ChildrenTransitions>              
  9.             <TransitionCollection>                  
  10.               <EntranceThemeTransition/>               
  11.             </TransitionCollection>           
  12.         </Grid.ChildrenTransitions>  
  13.          <Grid.RowDefinitions>              
  14.             <RowDefinition Height="Auto"/>               
  15.             <RowDefinition Height="*"/>           
  16.         </Grid.RowDefinitions>  
  17.          <!-- Pannello del titolo -->           
  18.         <StackPanel Grid.Row="0" Margin="19,0,0,0">              
  19.             <TextBlock  Text="Sqlite sample"  Style="{ThemeResource TitleTextBlockStyle}"  Margin="0,12,0,0"/>   
  20.             <TextBlock  Text="Insert page"  Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}"  CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>         
  21.               </StackPanel>  
  22.         <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->          
  23.         <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">              
  24.             <Grid.RowDefinitions>                   
  25.                <RowDefinition Height="Auto"/>                  
  26.                 <RowDefinition Height="Auto"/>                              
  27.             </Grid.RowDefinitions>                                      
  28.               <Grid Grid.Row="0">                  
  29.                 <Grid.RowDefinitions>                    
  30.                        <RowDefinition Height="Auto"/>                     
  31.                       <RowDefinition Height="Auto"/>                    
  32.                        <RowDefinition Height="Auto"/>                    
  33.                        <RowDefinition Height="Auto"/>                     
  34.                       <RowDefinition Height="Auto"/>                 
  35.                       </Grid.RowDefinitions>  
  36.                 <Grid.ColumnDefinitions>                     
  37.       <ColumnDefinition Width="Auto"/>                       
  38.     <ColumnDefinition Width="*"/>                 
  39.       </Grid.ColumnDefinitions>  
  40.            <TextBlock  Grid.Column="0"  Grid.Row="0" x:Name="tbkName"  FontSize="25"  Text="Name"VerticalAlignment="Center"  />  
  41.            <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"   />                <TextBlock  Grid.Column="0"   Grid.Row="3"   x:Name="tbkRole"  FontSize="25"Text="Role"  VerticalAlignment="Center"   />                 <TextBox     Grid.Column="1"  Grid.Row="0"   x:Name="tbxName"/>  
  42.              <TextBox Grid.Column="1"   Grid.Row="1"   x:Name="tbxSurname" />  
  43.              <TextBox  Grid.Column="1"  Grid.Row="2"   x:Name="tbxAge" InputScope="Number"  />  
  44.               <TextBox  Grid.Column="1"   Grid.Row="3"   x:Name="tbxRole" />               
  45.             </Grid>  
  46.             <Grid Grid.Row="1">                  
  47.     <Grid.RowDefinitions>                       
  48.         <RowDefinition Height="20"/>                      
  49.         <RowDefinition Height="Auto"/>                                     
  50.           </Grid.RowDefinitions>                                   
  51.     <Button  Grid.Row="1 x:Name="btnInsert"  Content="Insert"  HorizontalAlignment="Center" Tapped="btnInsert_Tapped" />    
  52.     </Grid>           
  53. </Grid>       
  54. </Grid></Page>  
This will must be the new graphic interface after the XAML code modification.


Image 1.4 The screen Insert page.

Ended the graphic part, I feel F7 to access the code editor and we modify the Tapped event of Button Insert as follows.
  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("Valorizzare tutti i campi");               
  6.        await dialog.ShowAsync();           
  7.     }  
  8.     else     
  9.     {             
  10.         DatabaseManagement.InsertData(tbxName.Text, tbxSurname.Text, int.Parse(tbxAge.Text),tbxRole.Text);          
  11.     }     
  12. }  
The difference is in this line of code
  1. DatabaseManagement.InsertData(tbxName.Text, tbxSurname.Text, int.Parse(tbxAge.Text),tbxRole.Text);  
If you remember in the InsertData method have added a quarter parameter necessary for inserting the user's role and here they because of this also modifies in the event Tapped Of Button.

Modification class Update

Like the class Insert, also the class Update needs a few modifications and I date from the fact that in the previous article it was possible to modify only the name of the user, we will provide the possibility of modifying the surname, the age and inserting also a new role. Always with the slider positioned on the Screen folder, open the Update.xaml file and we go to modify the existing XAML code with this.
  1. <Pagex:Class="SqlLite_Sample.Screen.Update"      
  2.           xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"       
  3.           xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"       
  4.           xmlns:local="using:SqlLite_Sample.Screen"      
  5.           xmlns:d="http://schemas.microsoft.com/expression/blend/2008"      
  6.           xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"      
  7.           mc:Ignorable="d"       
  8.           Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">  
  9.        <Grid x:Name="LayoutRoot">  
  10.         <Grid.ChildrenTransitions>              
  11.             <TransitionCollection>                  
  12.                 <EntranceThemeTransition/>               
  13.             </TransitionCollection>           
  14.         </Grid.ChildrenTransitions>  
  15.         <Grid.RowDefinitions>              
  16.             <RowDefinition Height="Auto"/>               
  17.             <RowDefinition Height="*"/>           
  18.         </Grid.RowDefinitions>  
  19.          <!-- Pannello del titolo -->           
  20.         <StackPanel Grid.Row="0" Margin="19,0,0,0">              
  21.             <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>               
  22.             <TextBlock Text="Update page" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>           
  23.         </StackPanel>  
  24.          <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->           
  25.         <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">              
  26.             <Grid.RowDefinitions>                  
  27.                 <RowDefinition Height="Auto"/>                   
  28.                 <RowDefinition Height="Auto"/>                   
  29.                 <RowDefinition Height="Auto"/>                   
  30.                 <RowDefinition Height="Auto"/>                   
  31.                 <RowDefinition Height="Auto"/>                   
  32.                 <RowDefinition Height="Auto"/>                   
  33.                 <RowDefinition Height="Auto"/>                               
  34.             </Grid.RowDefinitions>  
  35.              <ListBox Grid.Row="0" x:Name="lstUpdatePerson" SelectionChanged="lstUpdatePerson_SelectionChanged">                  
  36.                 <ListBox.ItemTemplate>                      
  37.                     <DataTemplate>                          
  38.                         <StackPanel>                              
  39.                             <StackPanel Orientation="Horizontal">                                  
  40.                                 <TextBlock x:Name="tbkName"  FontWeight="Bold" Text="Name"/>  
  41.                                 <TextBlock Width="30"/>  
  42.                                 <TextBlock x:Name="tbkSurname" FontWeight="Bold" Text="Surname"/>  
  43.                                 <TextBlock Width="30"/>  
  44.                                 <TextBlock x:Name="tbkAge"  FontWeight="Bold"   Text="Age"/>  
  45.                                 <TextBlock Height="50"/>    
  46.                             </StackPanel>  
  47.                             <StackPanel Orientation="Horizontal">    
  48.                               <TextBlock    x:Name="tbkFindForName" Text="{Binding Name}"/>  
  49.                               <TextBlock Width="20"/>  
  50.                               <TextBlock   x:Name="tbkFindForSurName" Text="{Binding SurName}"/>  
  51.                               <TextBlock Width="20"/>  
  52.                              <TextBlock  x:Name="tbkFindForAge"  Text="{Binding Age}"/>   
  53.                        </StackPanel>                          
  54.                      </StackPanel>                      
  55.                   </DataTemplate>                  
  56.                 </ListBox.ItemTemplate>               
  57.              </ListBox>                           
  58.                  <TextBlock Grid.Row="3" x:Name="tbkNewData" Text="New data" HorizontalAlignment="Center"/>                           
  59.              <StackPanel Grid.Row="4" x:Name="splNewData">                 <StackPanel x:Name="splNewName" Orientation="Horizontal">                       
  60.                 <TextBlock Text="Name" VerticalAlignment="Center"/>                      
  61.                 <TextBlock Width="30"/>                      
  62.                 <TextBox x:Name="tbxNewName" Width="Auto"/>                
  63.              </StackPanel>  
  64.              <StackPanel x:Name="splNewSurName" Orientation="Horizontal">                       
  65.                <TextBlock Text="SurName" VerticalAlignment="Center"/>                      
  66.                <TextBlock Width="15"/>                      
  67.               <TextBox x:Name="tbxNewSurName" Width="Auto"/>                 
  68.            </StackPanel>  
  69.            <StackPanel x:Name="splNewAge" Orientation="Horizontal">                     
  70.              <TextBlock Text="Age" VerticalAlignment="Center"/>                     
  71.             <TextBlock Width="40"/>                     
  72.             <TextBox x:Name="tbxNewAge" Width="Auto" InputScope="Number"/>                  
  73.         </StackPanel>  
  74.         <StackPanel x:Name="splNewRole" Orientation="Horizontal">                     
  75.             <TextBlock Text="Role" VerticalAlignment="Center"/>                     
  76.             <TextBlock Width="40"/>                      
  77.            <TextBox x:Name="tbxNewRole" Width="Auto"/>                  
  78.       </StackPanel>              
  79.       </StackPanel>                                     
  80.      <Button Grid.Row="6" x:Name="btnUpdatePerson" Content="Update" Tapped="btnUpdatePerson_Tapped"/>           
  81.         </Grid>      
  82.     </Grid>
  83. </Page>  
Here the new Update screen after the modifications.



Image 1.5 The new screen Update page.

We now modify also the C# code part, feel F7 to access the editor and go to replace all the content of the Tapped events of button btnUpdatePerson e and SelectionChanged of the ListBox lstUpdatePerson control.
  1. private async void btnUpdatePerson_Tapped(object sender, TappedRoutedEventArgs e)
  2. {    
  3.    await Validations.MessageConfirmDeleteoUpdatePerson("Vuoi aggiornare i dati?");  
  4.  if (Validations.result.Equals(true))    
  5.  {        
  6.     if (Validations.CheckTextBox(tbxNewName, tbxNewSurName, tbxNewAge).Equals(true))        
  7.     {             var dialog = new MessageDialog("Valorizzare tutti i campi");            
  8.       await dialog.ShowAsync();        
  9.     }  
  10.     else        
  11.     {             
  12.       DatabaseManagement.UpdateData(Parametri_ricerca.NewName,tbxNewName.Text,                
  13.        Parametri_ricerca.NewSurName,tbxNewSurName.Text,Parametri_ricerca.NewAge, 
  14.       int.Parse(tbxNewAge.Text),tbxNewRole.Text);         
  15.     }     
  16.  }  
  17. }  
He remains all unchanged except this code line
  1. DatabaseManagement.UpdateData(Parametri_ricerca.NewName,tbxNewName.Text,  Parametri_ricerca.NewSurName,tbxNewSurName.Text,Parametri_ricerca.NewAge, 
  2. int.Parse(tbxNewAge.Text),tbxNewRole.Text);    
Also in this case, if you remember the UpdateData method has been modified inserting the support to the surname modification, age and the possibility of inserting or modifying the user's role. We still modify the code for the ListBox control.
  1. private void lstUpdatePerson_SelectionChanged(object sender, SelectionChangedEventArgs e)        
  2.  {             
  3.      Parametri_ricerca.NewName = ((Employee)(lstUpdatePerson.SelectedValue)).Name;            
  4.      Parametri_ricerca.NewSurName = ((Employee)(lstUpdatePerson.SelectedValue)).SurName;           
  5.      Parametri_ricerca.NewAge = ((Employee)(lstUpdatePerson.SelectedValue)).Age;              
  6.      tbxNewName.Text = ((Employee)(lstUpdatePerson.SelectedValue)).Name;            
  7.      tbxNewSurName.Text = ((Employee)(lstUpdatePerson.SelectedValue)).SurName;         
  8.      tbxNewAge.Text = ((Employee)(lstUpdatePerson.SelectedValue)).Age.ToString();     
  9.  }  
Also in this case have added the support for the surname modification and age adding these code lines that do not do anything else but increase the value of the UpdateData method parameters.
  1. Parametri_ricerca.NewSurName = ((Employee)(lstUpdatePerson.SelectedValue)).SurName;             
  2. Parametri_ricerca.NewAge = ((Employee)(lstUpdatePerson.SelectedValue)).Age;             
  3. tbxNewSurName.Text = ((Employee)(lstUpdatePerson.SelectedValue)).SurName;             
  4. tbxNewAge.Text = ((Employee)(lstUpdatePerson.SelectedValue)).Age.ToString(); 
Modification class MainPage

Finishes modification to be executed, the initial application screen, add such a button to be able to access the Finddata screen created previously. We modify the XAML code how he follows.
  1. <Page x:Class="SqlLite_Sample.MainPage"       
  2.          xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"       
  3.          xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"       
  4.          xmlns:local="using:SqlLite_Sample"       
  5.          xmlns:d="http://schemas.microsoft.com/expression/blend/2008"     
  6.            xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"       
  7.          mc:Ignorable="d"      
  8.          Background="{ThemeResource ApplicationPageBackgroundThemeBrush}">  
  9.      <Grid x:Name="LayoutRoot">  
  10.             <Grid.ChildrenTransitions>              
  11.             <TransitionCollection>                  
  12.              <EntranceThemeTransition/>               
  13.             </TransitionCollection>           
  14.         </Grid.ChildrenTransitions>  
  15.         <Grid.RowDefinitions>              
  16.             <RowDefinition Height="Auto"/>               
  17.             <RowDefinition Height="*"/>           
  18.         </Grid.RowDefinitions>  
  19.          <!-- Pannello del titolo -->           
  20.         <StackPanel Grid.Row="0" Margin="19,0,0,0">             
  21.             <TextBlock Text="Sqlite sample" Style="{ThemeResource TitleTextBlockStyle}" Margin="0,12,0,0"/>              
  22.             <TextBlock Text="Main page" Margin="0,-6.5,0,26.5" Style="{ThemeResource HeaderTextBlockStyle}" CharacterSpacing="{ThemeResource PivotHeaderItemCharacterSpacing}"/>           
  23.         </StackPanel>  
  24.         <!--TODO: il contenuto deve essere inserito all'interno della seguente griglia-->           
  25.        <Grid Grid.Row="1" x:Name="ContentRoot" Margin="19,9.5,19,0">              
  26.             <StackPanel>                 
  27.           <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"/>  
  28.             <Button  x:Name="btnFindSample"  Content="Find sample page" Tapped="btnFindSample_Tapped" Width="300"/>              
  29.             </StackPanel>           
  30.         </Grid>       
  31.     </Grid>  
  32. </Page>  
Here the new Main Page screen after the modifications.



Image 1.6 The modified Main page screen.

Ended the modification to the graphic part, we can proceed with adding the tapped event of the new button Find sample page. I feel F7, entered the code editor we add the code following at once under the event tapped btnDeleteSample_Tapped.
  1. private void btnFindSample_Tapped(object sender, TappedRoutedEventArgs e){    Frame.Navigate(typeof(Finddata));}  
Test application

We have finished all the necessary modifications; we can now proceed with the application test. For what we must modify before how see and already explained the compilation target in the first article on ARM platform. We can execute it either from the menu "Build" then choosing" management configuration", or the Toolstrip control selecting the ARM platform from the combobox control. Ended this activity, press key F5 and we start the debug. After the application is begun we do tap on Button Insert sample page. Entered the next screen, we insert one or more increasing the value.
 

Image 1.7 The Insert page screen with the first inserted user.



Image 1.8 The Insert page screen with the second inserted user.



Image 1.9 The Insert page screen with third inserted user.

I have inserted these three users in sequence and their information has been memorized inside the tables Employee and Job inside the database people.db. Ended the data input, we go back to the main screen and do a tap on button Find sample page. When we are in the research screen, will see that for pre-defined setting the research is selected for name. We leave so the settings and type in inside the TextBox control the name of a user whom we have inserted previously. Ended the insertion, we do a tap on button Find and if it is found the correspondence when we will be look in the Result screen we will be the information on the user whom we have chosen for the research, we will differently not show anything. I will insert the name "Carmelo", and this and the result of the research.



Image 1.10 The Find screen dates page with the research for name.



Image 1.11 The executed research for name Result screen.



Image 1.12 The Find screen dates page with the research for role.



Image 1.13 The executed research for role Result screen.



Image 1.14 The Find screen dates page with the research for age.



Image 1.15 The research for executed age Result screen.

All the possible research combinations have been tried, for name, role and age, with the results that we were expecting in the result screen, everything by the FindForName method that we have inserted in the class DatabaseManagement. There is still a functionality to be tried, that is update of the information that we have inserted. We turn in the main screen and do a tap Update sample page, entered the screen we will show all the information on the users in the ListBox control. We do a tap on an item and TextBox will be increased the value submitting except for that of the duty as visible in the following figures.



Image 1.16 The Update page screen after the selection of an item on the ListBox control.



Image 1.17 The Update page screen after the modification of the role with Developer value.



Image 1.18 The Update page screen with a MessageDialog of data updating confirmation.


Image 1.19 The Find screen dates page with the selection of research set up for role.



Image 1.20 The finished research Result screen.

And been modified the role from Verniciatore To Developer and have executed a piece of research later specifying in the TextBox control of the Find screen he dates page the term "Developer", and in the Result screen we show the information based on the wished search criteria.

Conclusion

In this second part, we have modified the project of the first article depending on demands, inserted a new FindForName called method in the class DatabaseManagement, created two new screens, that is Finddata page and Result page, created two classes Job and RoleUser, the first to be able to save all what that regards the user's role, the second to show the research results, finally have seen as execute a piece of research inside the tables in a Sqlite Database moving however in content of the tables in two collections, since how I previously dictate the Sqlite-net does not support all the Linq Extension method and relationships among tables. In the next article will see as is possible insert, remove and bring up to date several data simultaneously, exploiting some of the methods than the bookshop SqliteNet places at disposal of us developers. 


Similar Articles